T-SQL: Different SELECT Executed Depending On Condition.

Execute SendMail Depending On A Condition
 

Hello All,
 
I have an Execute SQL Task and a SendMail in the control flow of my package.
 
If the Execute SQL Task inserts records in the Database, I want the SendMail task to be executed.
 
But if Execute SQL Task does not inserts even a single record in the Database, I dont want the SendMail task to be executed.
 
How can I achieve this...
 
Thanks,
Kapadia Shalin P.

View Replies !

---

INSERT-SELECT Depending On The Select:ed Order
I'm doing a INSERT...SELECT where I'm dependent on the records SELECT:ed to be in a certain order. This order is enforced through a clustered index on that table - I can see that they are in the proper order by doing just the SELECT part.

However, when I do the INSERT, it doesn't work (nothing is inserted) - can the order of the records from the SELECT part be changed internally on their way to the INSERT part, so to speak?

Actually - it is a view that I'm inserting into, and there's an instead-of-insert trigger on it that does the actual insertions into the base table. I've added a "PRINT" statement to the trigger code and there's just ONE record printed (there should be millions).

View Replies !

---

How To Get Recordset From Only Last SELECT Stmt Executed???
How to get recordset from only last SELECT stmt executed???

this is part of my code.. in T-Sql


Create proc some mySp
...
AS
...
set nocount on
if (@SelUserID is null)
select 0
else if (@SelUserID = @userID)

select 1

else
begin

select * -- select a.
from dms_prsn_trx_log
where @incNo = ult_incid_no and
prsn_trx_no = 10 and
trx_log_txt = @logText
if (@@rowcount != 0)
set @isForwardedByUser = 1
select 2 -- select b. I NEED This value.

end
set nocount off
GO

here it executes select a, b.
But, I want mySp return last executed select result. which is here "select 2"

I thought set nocount ON does that, but it doesn't.
How can I do???

View Replies !

---

Can A Stored Procedure Be Executed From Within A Select Statement?
Can a stored procedure be executed from within a select statement?

Given a store procedure named: sp_proc

I wish to do something like this:

For each row in the table
execute sp_proc 'parameter1', parameter2'...
end for
...but within a select statement. I know you can do this with stored functions, just not sure what the syntax is for a stored procedure.

View Replies !

---

SELECT UNION SELECT Condition
let say i got such condition

INSERT INTO TABLE
SELECT
WHERE XX NOT EXISTS (SELECT 1 FROM TABLE)
UNION
SELECT
WHERE XX NOT EXISTS (SELECT 1 FROM TABLE)

do you think that mssql will produce error or problem?
from what i heard it will.

View Replies !

---

Select Permission Obligatory Need To Execute Procedure (that Has Executed Permssion)
I've all procedures running over EXECUTE permission. They're running properly without the SELECT or DRY permissions on involved tables.
But some procedures of above cited,  in a particular tables or particular the procedure don't runs properly with out enabling SELECT permission on involved tables.
The EXECUTE permision runs overs other permissions on tables if the're not implicit denied, it's the best segurity practice. Then what is happen?? why need extra SELECT permision on some tables ?. The usser, and function role are ok.
You troube the same, some help please :)

View Replies !

---

If Condition In Select Statement...
Hi,
I need to write an if condition in SELECT statement. Below is the code for the same. But its throwing error. Can some refine the code below.




SELECT tblCustomer.Customer_LegalName,
(IF (tblCustomer.IsNRACustomer = TRUE) SELECT tblCustomer.Customer_PassportNo ELSE
ISNULL(tblCustomer.Customer_TaxId, tblCustomer.Customer_PassportNo)) AS TAXID,

tblCustomer.Customer_DoingBusinessAs, tblSeed_EDDCategory.CategoryName, '2' AS DCS, tblUser_OfficerCode.User_OfficerCode,
tblCustomer.Customer_AreaId, tblCustomer.Customer_BranchId, CONVERT(VARCHAR(11), tblCustomer_EDDCategory.CreateDate)
AS CreateDate, tblSeed_EDDCategory.EDDCategoryId, tblCustomer_EDDCategory.Category_CreateEmpId,
tblCustomer_EDDCategory.CustCatId, tblCustomer.Customer_Id, tblSeed_Area.AreaName, tblSeed_Employee.Name,
tblUser_OfficerCode.User_OfficerName, tblCustomer.Customer_TaxId, tblCustomer.IsNRACustomer,
tblCustomer.Customer_PassportNo
FROMtblCustomer INNER JOIN
blCustomer_EDDCategory ON tblCustomer.Customer_Id = tblCustomer_EDDCategory.CustomerId INNER JOIN
tblSeed_EDDCategory ON tblCustomer_EDDCategory.EDDCategoryId = tblSeed_EDDCategory.EDDCategoryId INNER JOIN
tblSeed_Employee ON tblCustomer.Customer_CreateEmpId = tblSeed_Employee.EmployeeId INNER JOIN
tblUser_OfficerCode ON tblCustomer.Customer_CreateEmpId = tblUser_OfficerCode.EmployeeId INNER JOIN
tblSeed_Area ON tblCustomer.Customer_AreaId = tblSeed_Area.AreaId


Thanks,
Rahul Jha

View Replies !

---

'IF' Condition On Select Statement?
Can you implement the same type of feature in SQL as MS Access uses with it's "immediate if"
or iif() function? In other words can you look at a specific row and change the contents
of one field based on the contents of another field in the same row?

I am trying to create a view like the the following:

CREATE VIEW AS Test
SELECT
name,
city ,
IF city = 'NY' state ELSE country
FROM address_table

The view's 3rd field will be either [state] or [country] depending on the contents of [city]

Thanks in advance!

..Marisa

View Replies !

---

Select Condition From Log Table
Hi guys

Im not even sure how to word this right, but how do I compare one element(unsure word number 1) namely Filename, from a list of results, and use that value as a variable for another condition in the same select... I think?!

So if I get a list of 50 filenames (from a log table), some might be double.
If the file was successfully processed, it will log that filename and the status as OK and if not then BAD.
The file will be modified and then reprocessed until OK. Each time the file was processed, it will log the filename and status.
I then run a statement that shows all the files that were BAD (from the log table) and it will bring up then filename and BAD status.
What I want to do is check the status' and if the file is BAD, check if there is another logged entry with that filename and status is OK, if found then not produce output as Im only looking for the files that status' are BAD with no OK status for any of the logged entries for that filename.

Is that understandable? If not then Ill try reword it.

The help is much appreciated!
Justin

View Replies !

---

Need Help On Select Statement Wit Like Condition
I tried following code and it return no data after query run successfully. I m testing it on msaccess . 
 
SELECT Book.ID, Book.Title, Book.Arthor, Book.Publisher, Book.Year, Book.Price, Inventory.B_ID, Inventory.Quantity
FROM Book INNER JOIN Inventory ON Book.ID=Inventory.B_ID
WHERE Book.ID=Inventory.B_ID And ("Book.Arthor" Like '%es%');


-------

I have test and test2 in Arthor column ... Thanks all !!
 

View Replies !

---

Condition In Select Statement
col1          col2 col3   col4

36930.60   145    N   . 00
17618.43   190   N    . 00
6259.20    115    N    .00
8175.45     19    N     .00
18022.54   212   N    .00
111.07      212   B     100
13393.05   67   N     .00


In above 4 col

if col3 value is B then cursor has to fectch appropriate value from col4.
if col3 value is N then cursor has to fectch appropriate value from col1.
and also wants the sum of result above conditions when column 3 has two values(i.e N,B) for single col2 value
 
here col2 values are unique
 
take an example for col2=212
if col3=B
  then result is 100
if col3=N
then result is 18022.54
for  a single col 2 value ,if col3= N and B then we want sum of above 2 conditions i.e (100+18022.54)
but not the sum of (100+18022.54+111.07+0.0) .

 
Can any one reply for this..............
 

View Replies !

---

WHERE Condition In SELECT Statement
 

Hi All,
I want get the result in a single SELECT statement
 
 SELECT
 SUM (PAID_LOSS),
SUM (PAID_EXP)
GROUP BY COMPANY
FROM VIEW_POLICY
WHERE  Accounting_Period  GE  200701  and LE 200712
-************************************
SELECT
 SUM (MEDICAL_RESERVE)
GROUP BY COMPANY
FROM VIEW_POLICY
WHERE  Accounting_Period  LE 200712
 
 

View Replies !

---

Condition In Select Query
create function stzipcustinfo

( @campaign varchar(50), @startdate datetime, @enddate datetime,@audience_type char(10) )


RETURNS TABLE

AS

RETURN


(Select distinct t.campaign,t.Sopnumbe,t.Custnmbr,t.Custname,

t.Address1,t.Address2,t.City,t.State,t.Country,t.Zipcode, t.Phone,sum(t.totalquantity) as Totalquantity,

t.Audience_type

from

(

select distinct

v.sopnumbe, v.campaign, v.custnmbr, v.custname, v.address1, v.address2,v.city,v.state,v.country,v.zipcode,

v.Phone, v.totalquantity as totalquantity,


case @audience_type

when v.custclas then v.custclas

end as audience_type
from vwstzipcustaudienceinfo v

Where (v.itemnmbr = '' or v.itemnmbr=v.itemnmbr) and v.Campaign = @Campaign and (v.docdate between @startdate and @enddate)


) as t

where t.audience_type is not null

group by t.campaign,t.Sopnumbe,t.Custnmbr,t.Custname,

t.Address1,t.Address2,t.City,t.State,t.Country,t.Zipcode, t.Phone, t.Audience_type

)

--select * from mystatezipcustaudienceinfo('cpd','1/1/2008','5/15/2008','INDPATIENT') --getting 500rows

--select * from mystatezipcustaudienceinfo('Cpd'','1/1/2008','5/15/2008','INST-HOSPITAL') -- note getting single row

problem have a “-“ in them..i m not getting result..so what condition should be in red color one..


thanks

View Replies !

---

How To Select Train Data With Condition
Hi, in SQL Management Studio, we just click 'Process' to train the data, how could I conditionally select a subset of Data Source (View) to train?

Thanks in advance.
Ricky.

View Replies !

---

Same DTS Fails Executed As Job ,but Run Fine When Executed From DTS Designer
I created DTS a while ago and placed in job to run once a day (it worked fine for 3 months)
2 days ago I changed sa password and now job fails with error (Login failed for user 'sa'.), but it run fine from DTS !!!


1. My DTS created with domain Account DomainSVCSQL2000( sa rights and local admin)
2. SVCSQL service use DomainSVCSQL2000 to run
3. SVCSQL agent use DomainSVCSQL2000 to run
4. DTS use 'osql -E

Where should look for reference to sa ?







Executed as user: MONTREALsvcsql2000. DTSRun: Loading... Error: -2147217843 (80040E4D); Provider Error: 18456 (4818) Error string: Login failed for user 'sa'. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0. Process Exit Code 1. The step failed.

View Replies !

---

If Condition Within Select Query Sql Server 2000
Hi all,
 
I have to write a select query which need some logic to be implemented.
Query is like
select name,number,active,
if active ="true" then
select @days=days from tbl_shdsheet
else
@days=''
end
from tbl_emp
 In the above query there will be days row for that employee if active is true else there won't be any data for that emp in the tbl_shdsheet
So how can i write queery for this.

View Replies !

---

How To Put Condition In Select Statement To Write A Cursor
col1          col2 col3   col4
36930.60   145    N   . 00
17618.43   190   N    . 00
6259.20    115    N    .00
8175.45     19    N     .00
18022.54   212   N    .00
111.07      212   B     .00
13393.05   67   N     .00
In above 4 col
if col3 value is B then cursor has to fectch appropriate value from col4.
if col3 value is N then cursor has to fectch appropriate value from col1.
here col2 values are unique.

Can any one reply for this..............

View Replies !

---

Execute &&"Select&&" Depending On The Input Parameters
Hello, a question please. Could anyone say me if I can create a store procedure with 2 'select's statements into. Besides, I'd want to know if I can execute a "select" depending on input parameters.

Something like this:

create storeproc
Param1, param2

if param1 <> null

Select *
from table
where id = param1

else

Select *
from table
where id = param2

end if

Thanks in advance....

 

 

 

 

View Replies !

---

HELP With SQL Query: Select Multiple Values From One Column Based On &&<= Condition.
Hello all.  I hope someone can offer me some help.  I'm trying to construct a SQL statement that will be run on a Dataset that I have.  The trick is that there are many conditions that can apply.  I'll describe my situation:
 
I have about 1700 records in a datatable titled "AISC_Shapes_Table" with 49 columns.  What I would like to do is allow the user of my VB application to 'create' a custom query (i.e. advanced search).  For now, I'll just discuss two columns;  The Section Label titled "AISC_MANUAL_LABEL" and the Weight column "W".  The data appears in the following manner:
 
(AISC_Shapes_Table)
 
AISC_MANUAL_LABEL          W
W44x300                               300
W42x200                               200
(and so on)
WT22x150                             150
WT21x100                             100

(and so on)
MT12.5x12.4                          12.4
MT12x10                               10
(etc.)
 
I have a listbox which users can select MULTIPLE "Manual Labels" or shapes.  They then select a property (W for weight, in this case) and a limitation (greater than a value, less than a value, or between two values).  From all this, I create a custom Query string or filter to apply to my BindingSource.Filter method.  However I have to use the % wildcard to deal with exceptions.  If the user only wants W shapes, I use "...LIKE 'W%'" and "...NOT LIKE 'WT%" to be sure to select ONLY W shapes and no WT's.  The problems arises, however, when the user wants multiple shapes in general.  If I want to select all the "AISC_MANUAL_LABEL" values with W  <= 40, I can't do it.  An example of a statement I tried to use to select WT% Labels and MT% labels with weight (W)<=100 is:
 



Code SnippetSELECT     AISC_MANUAL_LABEL, W
FROM         AISC_Shape_Table
WHERE     (W <= 100) AND ((AISC_MANUAL_LABEL LIKE 'MT%') AND (AISC_MANUAL_LABEL LIKE 'WT%'))
 
 

 It returns a NULL value to me, which i know is NOT because no such values exist.  So, I further investigated and tried to use a subquery seeing if IN, ANY, or ALL would work, but to no avail.  Can anyone offer up any suggestions?  I know that if I can get an example of ONE of them to work, then I'll easily be able to apply it to all of my cases.  Otherwise, am I just going about this the hard way or is it even possible?  Please, ANY suggestions will help.  Thank you in advance.
 
Regards,
 
Steve G.
 
 
 
 

View Replies !

---

Differentiate Between Whether Stored Procedure A Is Executed Inside Query Analyzer Or Executed Inside System Application Itself.
Just wonder whether is there any indicator or system parameters that can indicate whether stored procedure A is executed inside query analyzer or executed inside application itself so that if execution is done inside query analyzer then i can block it from being executed/retrieve sensitive data from it?

What i'm want to do is to block someone executing stored procedure using query analyzer and retrieve its sensitive results.
Stored procedure A has been granted execution for public user but inside application, it will prompt access denied message if particular user has no rights to use system although knew public user name and password. Because there is second layer of user validation inside system application.
 
However inside query analyzer, there is no way control execution of stored procedure A it as user knew the public user name and password.

Looking forward for replies from expert here. Thanks in advance.

Note: Hope my explaination here clearly describe my current problems.

 

View Replies !

---

Conditionally Select A Field Depending On Another Field Value
I want in my query to select a different field in case another one is null. in mysql i'd do it like this:

select
a
,if(b is null, c, b)
,d
from
alphabet

how can this be done in sql server?
thanks

View Replies !

---

Can I Print The Results Of A Condition Based On The Condition?
For example..

select * from mytable where MyNum = 7

If this brings back more than 1 row, I want to display a message that says,

Print 'There is more than one row returned'

Else (If only 1 row returned), I don't want to print anything.

Can I do this? Thx!

View Replies !

---

COndition Spli - Error Date Condition
Dear friends,

I'm having a problem... maybe it's very simple, but with soo many work, right now I can't think well...

 

I need to filter rows in a dataflow...

I created a condition spli to that... maybe there is a better solution...

And the condition is: Datex != NULL(DT_DATE)

(Some DATE != NULL)

 

 

[Eliminar Datex NULL [17090]] Error: The expression "Datex != NULL(DT_DATE)" on "output "Case 1" (17123)" evaluated to NULL, but the "component "Eliminar Datex NULL" (17090)" requires a Boolean results. Modify the error row disposition on the output to treat this result as False (Ignore Failure) or to redirect this row to the error output (Redirect Row).  The expression results must be Boolean for a Conditional Split.  A NULL expression result is an error.

 

What is wrong??

Regards,

Pedro

View Replies !

---

SELECT Query - Different Columns/Number Of Columns In Condition
I am working on a Statistical Reporting system where:


Data Repository: SQL Server 2005
Business Logic Tier: Views, User Defined Functions, Stored Procedures
Data Access Tier: Stored Procedures
Presentation Tier: Reporting ServicesThe end user will be able to slice & dice the data for the report by


different organizational hierarchies
different number of layers within a hierarchy
select a organization or select All of the organizations with the organizational hierarchy
combinations of selection criteria, where this selection criteria is independent of each other, and also differeBelow is an example of 2 Organizational Hierarchies:
Hierarchy 1


Country -> Work Group -> Project Team (Project Team within Work Group within Country) 
Hierarchy 2


Client -> Contract -> Project (Project within Contract within Client)Based on 2 different Hierarchies from above - here are a couple of use cases:


Country = "USA", Work Group = "Network Infrastructure", Project Team = all teams
Country = "USA", Work Group = all work groups

Client = "Client A", Contract = "2007-2008 Maint", Project = "Accounts Payable Maintenance"
Client = "Client A", Contract = "2007-2008 Maint", Project = all
Client = "Client A", Contract = allI am totally stuck on:


How to implement the data interface (Stored Procs) to the Reports
Implement the business logic to handle the different hierarchies & different number of levelsI did get help earlier in this forum for how to handle a parameter having a specific value or NULL value (to select "all")
(WorkGroup = @argWorkGroup OR @argWorkGrop is NULL)

Any Ideas?  Should I be doing this in SQL Statements or should I be looking to use Analysis Services.

Thanks for all your help!

View Replies !

---

COUNT Depending On Data
Hello.I have one serious problem with COUNT in TSQL.I use MS SQL Server 2000.I would like to count rows depending on data in it.What I meat is:I have fields like:region | month | year | some_count |-------|-------|------|-------------|LA | 1 | 2003| 4 |LA | 2 | 2003| 2 |LA | 3 | 2003| 1 |LA | 4 | 2003| 6 |VV | 1 | 2003| 3 |VV | 2 | 2003| 7 |VV | 4 | 2003| 20 |VV | 6 | 2003| 3 |BB | 2 | 2002| 1 |etc...And what I would like to get from it is:region | jan2003 | feb2003 | mar2003 | apr2003 | may2003 | june2003 | etc...-------|----------|---------|-----------|----------|----------|-----------|--LA | 4 | 2 | 1 | 6 |0 | 0 | 0VV | 3 | 7 | 0 | 20 | 0| 3 | 0BB | 0 | 1 | 0 | 0 |0 | 0 | 0etc...Is it possible to do it in views? if yes then how?I could use temp tables for each month but it is not solution when I have 3yearsand more later then it will be round 40 temporary tables.Thanks for any response.Regards,Casper

View Replies !

---

Adding Two Parameters With One Depending On The Value Of The Other
 

hello
 
sorry to distub you again
 
in fact i'm a beginner and i don't really see how i can do some tasks
 
when i display a report i want the reader to choose a client, and when he chooses a client, he can choose in the second parameter list, the missions that correspond to THIS client
 
so i have 3 datasets
 
one in which i get all my data needed
and i have a filter in where part of query
 



Code Snippet
where client=@client
and mission=@mission
 
 

 
and i filter on these 2 values
 
in the second dataset , i gather all the clients i have by doing this query



 



Code Snippet
select distinct name from client
 
 



and i have this third dataset where i gather all the missions corresponding to one client
 
and here i write
 



Code Snippet
select mission_label from client
where name=@client
 
 
the two fields are in the same time and in this third dataset i apply a filter on Parameters!client.value
 
but when i execute the report, the second parameter doesn't work, it gives me no choice of mission for a client, it doesn't return anything while when i execute the query in the dataset i have these values
 
how can i do this please?
 
thanks a lot in advance
 
and sorry or bothering with such questions


 
 

View Replies !

---

How Not To Display A Row Depending On Other Rows.
Hello,
I am very new to SQL and ran into a problem using Access. I hope you can help me here.
The question is the following:
I have to tables:
Table 4x4: CommodityCode(string)/NeedofBU(high,low)
Table ASDBComplete: CommodityCode/Manufacturer/Rating(red,green,yellow)

What I want to get as a result is to display all ComodityCodes that have a 'red' rating and a 'high' NeedOfBU. So far no problem. But now there usually is more than one manufacturer for one CommodityCode. What I need now is that the CommodityCode is not displayed if there actually exist a Manufacturer with 'green' or 'yellow' rating.
How would you do that?
What I have so far is:

SELECT [4x4].[CommodityCode], [4x4].NeedofBU, ASDBComplete.CommodityCode, ASDBComplete.Rating, ASDBComplete.Manufacturer
FROM 4x4 LEFT JOIN ASDBComplete ON [4x4].[CommodityCode] = ASDBComplete.CommodityCode
WHERE ((([4x4].NeedofBU)="high") AND ((ASDBComplete.Rating)="red"));

But this gives me all the Commodities with red ratings. Even if there is a supplier with a green or yellow rating.

I would need somting like:
if exists 'commodityCode.XY with 'manufacuturer rating = green OR yellow' do not display commodityCode.xy

I really appreciate your help

View Replies !

---

T-SQL Create Self-reference In Depending Table
Hello all,I have two tables - Projects and ProjectStructTable Projects contains master records of the projects, ProjectStructallows to define a project herarchie and contains the fieldsPrjStructId, ProjectId, PrjStructName, ..., ParentIdPrjStructParent contains a reference to the parent or to itselves ifrecord is top-level-record for a project.I try to create a trigger on table Projects (INSERT) whichautomatically creates the top-level-entry in ProjectStruct but Ididn't succed.Tried to use (several variations similar to)INSERT INTO ProjectStruct (ProjectId, PrjStructName, ParentId)SELECT prjProjectId, 'top-level',IDENT_CURRENT('ProjectStruct'))FROM INSERTEDbut this inserts a reference to the last inserted record. Why thishappens is pretty clear to me, but I found no way to get the referenceto the identity column of the record currently inserted.Is there a way to do this?

View Replies !

---

Calculated Member Depending On Dimension Value
Might be easy, but couldn't figure out the solution...
Want to create a calculated member from Amount measure, If member of Account dimension is in certain range (e.g. revenue accounts), then the calculated member value is measure Amount by reversing the sign, otherwise keep the same as Amount.
My problem is I can't provide correct MDX expression for the calculated member. Please help!!!

View Replies !

---

Updating A Table Depending On 2 Databases
In a package we have statements pointing to 2(or more) different databases on the server. When moving between environments, is there an easy way to change statements like:

UPDATE t1
SET t1.name = t2.name,
     t1.age = t2.age
FROM DB1..Person t1
INNER JOIN DB2..PersonToo t2
ON t1.PersonID = t2.PersonID

I can think only of building the statements replacing the database names with variables, but that's not an easy way. I do not know how to use package variables in this situation.
1 way of doing this may be by using a Lookup or Conditional Split and use the resulting dataflow in a SP or such to update, using parameters...
It all sounds very messy, and I still don't know how
Any ideas?

 
TIA,

View Replies !

---

How To Sum A Column Depending Of Another Colum Into Different Vars?
Hi all, I am trying to sum a column into different variables depending on another column. Let me explain my self better with an example

DECLARE @Initial decimal(18,2), @incomings decimal(18,2), @outgoings decimal(18,2)

SELECT
@initial = CASE WHEN type = 1 THEN SUM(amount) END,
@incomings = CASE WHEN type = 2 THEN SUM(amount) END,
@outgoings = CASE WHEN type = 3 THEN SUM(amount) END,
FROM Transactions
WHERE date = '05/14/2006' AND STATION = 'apuyinc'
GROUP BY type, amount

What I am trying to do is to sum all of the incomings transactions into @incomings, all of the outgoing transactions into @outgoings and the initial transaction into @initial where
The incoming transactions is type 2,
outgoing transactions is type 3

Thanks for the help


@puy

View Replies !

---

Merging Cells (horizontally) Depending On The Row
Is it possible to merge cells within several columns depending on the row number?
 
e.g.
 
   colA   colB   colC   colD

1    x        a       b        c
2    y     -------------------------

3    z     -------------------------
 
for row # 2, #3, colB colC colD are merged.

View Replies !

---

Data To Get From 3 Different Tables Depending On Some Conditions
Hi,
 
I know it sounds very simple with the subject line but for me it's not.
 
BACK GROUND
 
My Client is using Crystal Reports 8.5 and SQL Server.
a View is bound to this report and they use Select Expert of CR 8.5 IDE to generate Reports manually.
 
now there are 3 tables in Question
Billing_Revenue, Billing_Revenue_History and Billing_Revenue_Revision
and the Fields in Question are Interstate_Revenue and International_Revenue.
 
Every end of the Quarter the Billing_Revenue data is moved to Billing_Revenue_History.
Billing_Revenue_Revision has the revised data if the "Filer" has submited the changed/Revised Data again to the Company.

Bill Run = is the Process they call when they generate the Reports for a perticular Billing Period.
they do it every month. But sometimes they need to generate Invoices for the OLD Periods.
 
if we put the problem in simple words,
 
they want to get the Revenue Columns from Billing_Revenu if they run the "Bill Run" in the Current Quarter (Billing Cycle),
else if the Billing Cycle is older than current Quarter (cycle), then they want to first check in the Revision Table and if that Filer has submitted the revised data in that Cycle then get it from there
else
look into History Table and if the filer is found there for that cycle than get the data from there
 
here's the requirement given to me by my system Analyst.
 
User enters list of filer ID=USER_FILER_ID and cycle ID=USER_CYCLE_ID
 
Get CURRENT_CYCLE= Max CYCLE_ID from Billing_Cycle
Get USER_PERIOD_ID = Period ID from Billing_Cycle using USER_CYCLE_ID
Get PERIOD_FIRST_CYCLE= Min Cycle ID for USER_PERIOD_ID from Billing_Cycle
 
For each USER_FILER_ID
   If USER_CYCLE_ID=CURRENT_CYCLE
                Pull revenue from BILLING_REVENUE   /*Process stops here*/
   ELSE   
   {                        
      If USER_PERIOD_ID  exists in BILLING_REVENUE_REVISIONS BRR for USER_FILER_ID
      
Get the max (cycle_id) from BRR to pull revenue columns with most recent filing of USER_PERIOD_ID from BRR      /*one filer may have more than one revision for the same period id*/                                             
 
      ELSE
 
IF PERIOD_FIRST_CYCLE exists in BILLING_REVENUE_HISTORY for USER_FILER_ID
Pull revenue information        
   }          
 
here's the View being used which is the First Case which is happening currently
 

SELECT dbo.BILLING_PROVIDER.CONT_NAME_FIRST, dbo.BILLING_PROVIDER.CONT_NAME_LAST, dbo.BILLING_PROVIDER.ADD_STREET_LINE2,

dbo.BILLING_PROVIDER.ADD_STREET_LINE1, dbo.BILLING_PROVIDER.ADD_STREET_LINE3, dbo.BILLING_PROVIDER.ADD_CITY,

dbo.BILLING_PROVIDER.ADD_STATE, dbo.BILLING_PROVIDER.ADD_ZIP, dbo.BILLING_PROVIDER.CONTACT_TEL,

dbo.BILLING_SUMMARY.INVOICE_NUM, dbo.BILLING_SUMMARY.BALANCE_DUE, dbo.BILLING_SUMMARY.PREVIOUS_BALANCE,

dbo.BILLING_SUMMARY.SUM_TRANS, dbo.BILLING_SUMMARY.M_BASE, dbo.BILLING_SUMMARY.M_CONTRIBUTION,

dbo.BILLING_SUMMARY.ADJ_Q_CONTRIBUTION_BASE, dbo.BILLING_SUMMARY.EST_Q_CONTRIBUTION,

dbo.BILLING_SUMMARY.PRE_DM_ADJ_Q_CONTRIBUTION_BASE, dbo.BILLING_SUMMARY.Q_CONTRIBUTION_BASE,

dbo.BILLING_SUMMARY.Q_COMB_REVENUE, dbo.BILLING_SUMMARY.Q_LIRE_PERCENATAGE, dbo.BILLING_SUMMARY.Q_LIRE_STATUS,

dbo.BILLING_SUMMARY.A_DM_STATUS, dbo.BILLING_SUMMARY.A_COMB_REVENUE, dbo.BILLING_SUMMARY.Q_DM_STATUS,

dbo.BILLING_SUMMARY.A_LIRE_PERCENATAGE, dbo.BILLING_SUMMARY.A_LIRE_STATUS, dbo.BILLING_DETAILS.TRANSACTION_TYPE,

dbo.BILLING_DETAILS.LINE_ITEM_TYPE, dbo.BILLING_DETAILS.SUPPORT_MECHANISM_TYPE, dbo.BILLING_DETAILS.LINE_ITEM_NUMBER,

dbo.BILLING_DETAILS.LINE_ITEM_AMT, dbo.BILLING_DETAILS.TRANSACTION_DATE, dbo.BILLING_DETAILS.REASON_CODE,

dbo.BILLING_CYCLE.STATEMENT_DT, dbo.BILLING_CYCLE.PAYMENT_DUE_DT, dbo.BILLING_CYCLE.MAILING_DT,

dbo.BILLING_REVENUE.INTERSTATE_REVENUE, dbo.BILLING_REVENUE.INTERNATIONAL_REVENUE, dbo.BILLING_SUMMARY.CYCLE_ID,

dbo.BILLING_SUMMARY.FILER_ID, dbo.BILLING_PROVIDER.CARRIER_NAME, dbo.BILLING_CYCLE.PERIOD_ID,

dbo.BILLING_PERIOD.PERIOD_TYPE_ID, dbo.ACCT_LINE_ITEM_TYPE.LINE_ITEM_DESCR, dbo.BILLING_PERIOD.FCC_PERCENTAGE_ACTUAL,

dbo.BILLING_PERIOD.FCC_PERCENTAGE_EST, dbo.BILLING_PERIOD.HC_PERCENTAGE, dbo.BILLING_PERIOD.SL_PERCENTAGE,

dbo.BILLING_PERIOD.RHC_PERCENTAGE, dbo.BILLING_PERIOD.LI_PERCENTAGE, dbo.BILLING_SUMMARY.U_CONTRIBUTION_BASE,

dbo.BILLING_PERIOD.FCC_CIRCULARITY_FACTOR, dbo.BILLING_SUMMARY.A_LIRE_REVENUE, dbo.BILLING_SUMMARY.ANNUAL_BASE,

dbo.BILLING_REVENUE.RECEIVED_DT

FROM dbo.ACCT_LINE_ITEM_TYPE RIGHT OUTER JOIN

dbo.BILLING_DETAILS RIGHT OUTER JOIN

dbo.BILLING_PERIOD INNER JOIN

dbo.BILLING_CYCLE INNER JOIN

dbo.BILLING_SUMMARY ON dbo.BILLING_CYCLE.CYCLE_ID = dbo.BILLING_SUMMARY.CYCLE_ID ON

dbo.BILLING_PERIOD.PERIOD_ID = dbo.BILLING_CYCLE.PERIOD_ID ON dbo.BILLING_DETAILS.FILER_ID = dbo.BILLING_SUMMARY.FILER_ID AND

dbo.BILLING_DETAILS.CYCLE_ID = dbo.BILLING_SUMMARY.CYCLE_ID ON

dbo.ACCT_LINE_ITEM_TYPE.LINE_ITEM_TYPE = dbo.BILLING_DETAILS.LINE_ITEM_TYPE LEFT OUTER JOIN

dbo.BILLING_PROVIDER LEFT OUTER JOIN

dbo.BILLING_REVENUE ON dbo.BILLING_PROVIDER.FILER_ID = dbo.BILLING_REVENUE.FILER_ID ON

dbo.BILLING_SUMMARY.FILER_ID = dbo.BILLING_PROVIDER.FILER_ID

WHERE (dbo.BILLING_SUMMARY.FILER_ID NOT IN

(SELECT DISTINCT BILLING_DETAILS.FILER_ID

FROM dbo.BILLING_DETAILS

WHERE (dbo.BILLING_DETAILS.CYCLE_ID = dbo.BILLING_CYCLE.CYCLE_ID) AND (BILLING_DETAILS.LINE_ITEM_TYPE IN ('SLDADJ', 'SLDC',

'RHCDC', 'RHCDADJ'))))
 
Current Situation.
1. They use CR 8.5 IDE to enter the parameters
and this version of CR doesn't allow using Stored PRocedures.
2. I'm not very good with the Database side and Complex Queries always leave me in the middle of nowhere.
But i really really need to do this
3. please give me some clue..  some idea how to resolve this.
 

here's the primary database diagram
http://deepak.palkar.googlepages.com/Billing2.jpg
 
thanks a lot..
Deepak

View Replies !

---

Number Of Rows Depending On A Column Value
 

Hi all,
I have a table with artikels and count, sample:
 
Art   Count
------------
12A     3
54G     2
54A     4
 
I would like to query this table and for each 'count' retrieve one row:
query result:
 
Art   Count
------------
12A     3
12A     3
12A     3
54G     2
54G     2
54A     4
54A     4
54A     4
54A     4

 
 
Is this possible?
 
Thanks, Perry

View Replies !

---

Insert The Rows Depending On The Cell Value
I want to insert the rows automatically depending on the cell value in column from another table.
Like if the value of cell "blabla" is 4 it automatically insert the 4 rows in my table with values.

Is it possible in TSQL?

View Replies !

---

Insert Identity Depending On The Value Of A Column
Would like to have Identity Inserted wrt a date column
Eg:
ProdDate ID Details
============================
2008.04.01 1 afafafaf
2008.04.01 2 GAFSGHFGF
2008.04.02 1 GAGJAGSDH
2008.04.02 2 QYTYTT
2008.04.03 3 QYTWRRT

At present it is not an Auto increment , I check for the Maximum ID and add One to the next Inserted record. Is there any simpler method?

View Replies !

---

How To Apply An Inflation Factor Depending On The
Anybody knows how to apply an inflation factor depending on the date to a measure



The Inflation Factor is (Current Month Rate / X Month Rate)



So lets say for current year the rates are



Jan 121.64

Feb 121.98

March 122.244

April 122.171



For example



If I want to calculate for March I would show a column for January February and March, the column for January would be multiplied by (122.244/121.64) the column for February would be multiplied by (122.244/121.98) and march by one (122.244/122.244)



But in April the factors would be different I would have 4 columns, January February March and April, January amounts would be multiplied by (122.171/121.64) February by (122.171/121.98) March by (122.171/122.244) and April by one (122.171/122.171)


Im using SSAS 2005

Thanks in advance



Isaac

View Replies !

---

Setting Comparison Operator Depending On Parameter Value?
Hello,Is it possible to set a comparison operator using a parameter value?The code below shows what I'm after;declare @co char(1)declare  @date datetimeset @co = '<'set @date = '02/02/2002'select * from recipe where date @co @dateI would use an if statement perform two seperate statements depending on the value of co, but this is only one of 13 statements where i need to have different combinations of comparision operators.thanks

View Replies !

---

Insert Data Depending On Year Value In Table
Hi there,

Can anyone help? I currently have this query that imports distinct data into a prices table with a couple of contraints. This table became to large so I have now split this down into yearly tables (dbo.price --> dbo.price2001, dbo.price2002 etc). I get the data each day in another table that may contain data for different years so I need to be able to look at this data and insert into the right yearly tables.

E.g. present query:

Insert Prices
select distinct M.ids, C.zdateT, 1.0,0 from mapid as M,datacorrect as C
where M.asset = 'money'
and C.zDate not in (select zdate from price where sid >=15)


So I need to run this query for each yearly table with a date listed in the datacorrect table (insert prices(yr) yr = 2002,2003,2004 etc)

Any ideas would be appreciated!!

Thanks

S

View Replies !

---

List Tables Depending If Column Exists
Hi there,

Is there a quick way to list all the tables in a DB that contain a certain column name?

Thanks

S

View Replies !

---

Altering Strings Depending On Data In A Table.
Hi Guys,

I'm wondering if an idea I'm playing with is feasible and if so, how you would recommend implementing it.

Let's say I have a Dictionary table, 2 columns:

Word | Definition

And I have a string - "The cat sat on the dog"

If there's a definition for "cat" in the dictionary table, I want to alter the string so it becomes "The >>cat<< sat on the dog"

At the same time, if there's also a definition for "dog" then my string now becomes "The >>Cat<< sat on the >>Dog<<"

The idea being that when I manipulate the data in my ASP I can replace() the >><< with specific HTML code. (I'm trying to recreate the "in text" advertising thing that lots of people seem to be using - but not doing adverts, just information for our users - Someone hovers over a highlighted word, and with a little bit of Ajax, I can pull the definition out...

I'm not sure (but I'm suspecting) that it would make more sense to do this as I'm storing the string in a table, rather than as I'm pulling it out ready for use (don't want to be slowing my end users down )

Any ideas?

Thanks in advance
-Craig

View Replies !

---

Conditional Where Clause Depending On Input Parameter
I am trying to merge 2 pieces( i.e procedures , or stored proc) of sql together.
 
My simple QueryA
 
SELECT colA, colB, colC, colD
FROM tableA
WHERE
colD IS NOT NULL
 
My simple QueryB
 
SELECT colA, colB, colC, colD
FROM tableA
WHERE
colC IS NOT NULL
 
I am trying to merge these 2 pieces if sql together by passing a input parameter which will decide which query to run. So if I pass an input parameter QueryA , it will run QueryA. If I pass an imput parameter QueryB, it will run QueryB.
 
Essentially both my queries are the same besides the where condition. Is there a way to merge it into one query (and not use if conditions and make my storedproc long) and apply the where condition depending on what input parameter is passed in ?
 
I know it can be done using dynamic SQL construction. But any other ways ?
 
Also can someone also give in the solution in PL/SQL.
 
Thanks a bunch.
 
Jaffery.

View Replies !

---

Set Visibility Of An Image Depending On If A Certain Table Has No Rows
Hi,

I'm wondering if it's possible in SSRS 2005 to have an image's hidden expression to depend on if some table in the report has no rows.

Thanks,
Liron

View Replies !

---

Urgent!! Pictures In Reports Depending On A Field
Sorry ... I don't like to put Urgent in the title but I only have a couple of hours to fix this iisue.
I have a report where I have a table with only two columns. The first column is going to have a picture depending on what kind of value its field has in the database. The second column is just a comments field. The table is going to have 3 rows. Something like this:
 
Status:              Comments:
Strength-pic      Blablabla
Threat-pic        Blablabla
No-pic             Blablabla
 
In the database I have a row that contains all these 6 fields separately for a specific company. The field that contains the Status has a nr, 0 for no status and no-pic, 1 for Strength and then its pic, and 2 for Threat and its pic.
 
How do I make the table column Status and its cells to understand that when the field that corresponds to that cell has a specific nr then show its corresponding picture? I have a feeling that I have to put some Visual Basic code €¦ which I€™m not that good at.
 
As always
Thanks in advanced
 

View Replies !

---

Hiding A Table Row Depending On Page Number?
Hi, Everyone.
i am wondering if there is some way to hide a table row depending on the page number.
I have tried to find a way to access the global page number to use it in an expression for the visibility property on the table row, but i havent found anything useful.

View Replies !

---

Conditional Format Within Matrix, Depending On Subtotal?
Hi there,

I'm having trouble inserting a conditional format to a specific column.

e.g.: Matrix within the rows the "weeks" (1, 2, 3, ... , 52), and in the column a "lastyear revenu", "thisyear revenue" and a difference between them, "delta %", in percent grouped by the stores.
I added a subtotal to it so I get in the latest column the "total lastyear revenue", the "total thisyear revenue" and a difference between them in percent for all stores, "total delta %", for a specific week.

Problem: I want to colour the "delta %" column green when it is greater then the "total delta %" value.

I thougt this would be quite easy, but it really is a pain in the *** because, in the background expression dialog box, I can't refer  to the subtotal cells ...

View Replies !

---

Selecting Data Depending On Matrix Visibility
Hello,
 
I have a matrix report where I drill down from a business' divisional level to an office level. A subreport is generated by clicking on the relevant data field. However, when at divisional level, clicking on the data field simply generates the data from the first office row. I want it so that when the column is collapsed to divisional level, it shows the data for the entire division.
 
So... is there any way I can write an expression that creates different subreports based on whether a matrix column is expanded or collapsed?
 
Cheers,
Peter Marshall

View Replies !

---

Depending On The Drop Down Selection I Need To Show Another RDL File.
Hi all,

i have a requirement that user only sees drop down list and a button. when he selects an item from drop down and click button then i need to load rdl dynamically depending on the selection from the DDL.

i have created 3 rdl files, each one contains different content. I need to show the user, the selected rdl from the drop down.

is it possible in SSRS?

if you didn't understand well, feel free to ask.

please help me out.

thanks
-Praveen.

View Replies !

---