Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to combine 3 different select statements into one Expand / Collapse
Author
Message
Posted Wednesday, April 30, 2014 9:52 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 10:02 AM
Points: 70, Visits: 143
Hello,
I am creating an ssrs report for this it should have single dataset because its grouping with system name (group header) how can I combine 3 below select statements into one.
I used left outer join and union,union all not sure why its not working?

Any help please....

select distinct SystemID, SystemName from dbo].[vSystemSchool]
where schoolyear = @FiscalYear

SELECT SystemID,ProgramID,
CASE WHEN ProgramName='M' THEN 'L' ELSE ProgramName END AS 'ProgramName',
AdjustmentsSequenceNo,sum(Paid) as Paid,sum(Free) as Free,sum(Reduced) as Reduced,sum(paidamount),sum(freeamount),sum(reducedamount),
sum(paidamount)+sum(freeamount)+sum(reducedamount) as dollars
from dbo].[vForAllSystemsForOneMonth] SCH
where SCH.FiscalYear = @FiscalYear and SCH.ClaimMonth =@ClaimMonth
and AdjustmentsSequenceNo=0
GROUP BY systemID,programID,ProgramName,AdjustmentsSequenceNo
order by AdjustmentsSequenceNo

SELECT SystemID,ProgramID,
CASE WHEN ProgramName='M' THEN 'L' ELSE ProgramName END AS 'ProgramName',
sum(Paid) as Paid,sum(Free) as Free,sum(Reduced) as Reduced,IsNull(sum(paidamount),0),IsNull(sum(freeamount),0),IsNull(sum(reducedamount),0),
isNull(sum(paidamount)+sum(freeamount)+sum(reducedamount),0) as dollars
from [dbo].[vForAllSystemsForOneMonth] SCH
where SCH.FiscalYear = @FiscalYear and SCH.ClaimMonth =@ClaimMonth
and AdjustmentsSequenceNo>0
GROUP BY systemID,programID,ProgramName
order by systemID,ProgramID
Post #1566474
Posted Wednesday, April 30, 2014 11:00 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:29 PM
Points: 10,260, Visits: 13,230
Based on the information you have provided I would do this:

SELECT
SCH.SystemID,
SCH.ProgramID,
SS.SystemName,
CASE WHEN SCH.ProgramName = 'M' THEN 'L'
ELSE SCH.ProgramName
END AS 'ProgramName',
SCH.AdjustmentsSequenceNo,
SUM(SCH.Paid) AS Paid,
SUM(SCH.Free) AS Free,
SUM(SCH.Reduced) AS Reduced,
SUM(SCH.paidamount),
SUM(SCH.freeamount),
SUM(SCH.reducedamount),
SUM(SCH.paidamount) + SUM(freeamount) + SUM(reducedamount) AS dollars
FROM
[dbo].[vForAllSystemsForOneMonth] SCH JOIN
[dbo].[vSystemSchool] AS SS ON SCH.SystemID = SS.SystemID
WHERE
SCH.FiscalYear = @FiscalYear AND
SCH.ClaimMonth = @ClaimMonth AND
SCH.AdjustmentsSequenceNo >= 0
GROUP BY
SCH.systemID,
SS.SystemName,
SCH.programID,
SCH.ProgramName,
SCH.AdjustmentsSequenceNo
ORDER BY
SCH.AdjustmentsSequenceNo;

Then I would let SSRS do the higher level grouping based only on SystemID, ProgramID, and ProgramName. I may be misunderstanding why queries 2 and 3 in your example are needed, but I think you can bring the data across at the granularity that you need (SystemID, ProgramID, ProgramName, AdjustmentSequenceNo) and then handle the rest using SSRS grouping and filtering.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1566510
Posted Wednesday, April 30, 2014 11:28 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 10:02 AM
Points: 70, Visits: 143
Thank you for the reply tried your sql code the problem is that the amounts are varying in free column and dollars column?

Before that in free column for particular system its shown as 414 after rewriting as per your code its showing the free column as 2898


Not sure why amounts are varying.
Post #1566520
Posted Wednesday, April 30, 2014 11:49 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, October 12, 2014 4:00 PM
Points: 341, Visits: 751
If you post the following:

1. Create table ... -- for all tables in the queries
2. Insert into ... -- to populate the tables with sample data
3. expected results

we can take a look at it
Post #1566533
Posted Wednesday, April 30, 2014 12:05 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:29 PM
Points: 10,260, Visits: 13,230
mcfarlandparkway (4/30/2014)
Thank you for the reply tried your sql code the problem is that the amounts are varying in free column and dollars column?

Before that in free column for particular system its shown as 414 after rewriting as per your code its showing the free column as 2898


Not sure why amounts are varying.


I know why. I didn't include fiscalYear in the JOIN between vForAllSystemsForOneMonth and vSystemSchool. If you add that to the JOIN it should work. So it looks like you have data fro 7 fiscal years.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1566536
Posted Wednesday, April 30, 2014 1:16 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 10:02 AM
Points: 70, Visits: 143
Its still not working;But I changed the stored procedure as like below

I don't understand why I am getting error at first select statement like "error converting datatype varchar to numeric"...


select DISTINCT SystemID,SystemName,'' as ProgramID,'' as ProgramName,'' as AdjustmentsSequenceNo,
'' as Paid,'' as Free,'' as Reduced,'' as paidamount,'' as freeamount,'' as reducedamount,'' as dollars
from [SchoolNutrition].[dbo].[vSystemSchool]
where schoolyear = @FiscalYear

UNION

SELECT SystemID,ProgramID,
CASE WHEN ProgramName='Meal' THEN 'Lunch' ELSE ProgramName END AS 'ProgramName',
AdjustmentsSequenceNo,sum(Paid) as Paid,sum(Free) as Free,sum(Reduced) as Reduced,sum(paidamount),sum(freeamount),sum(reducedamount),
sum(paidamount)+sum(freeamount)+sum(reducedamount) as dollars,'' as SystemName
from [SchoolNutrition].[dbo].[vDE107sForAllSystemsForOneMonth] SCH
--[dbo].[vSystemSchool] AS SS ON SCH.SchoolID = SS.SchoolId
where SCH.FiscalYear = @FiscalYear and SCH.ClaimMonth =@ClaimMonth
and AdjustmentsSequenceNo>=0
GROUP BY systemID,programID,ProgramName,AdjustmentsSequenceNo
order by AdjustmentsSequenceNo,SystemID,programID
Post #1566577
Posted Wednesday, April 30, 2014 1:39 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:29 PM
Points: 10,260, Visits: 13,230
Based on the limited information you've provided I have no idea what you really need for output as the new query you've provided with the UNION doesn't makes sense to me from a business perspective.

You are getting an error because '' (empty string) does not convert to numeric so wherever you have '' and it is matching to a numeric in the other part you need to use 0 or NULL. So '' as Paid need to be either 0 as Paid or NULL as Paid.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1566588
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse