April 30, 2014 at 9:52 am
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
April 30, 2014 at 11:00 am
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
Consultant - Straight Path Solutions
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
April 30, 2014 at 11:28 am
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.
April 30, 2014 at 11:49 am
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
April 30, 2014 at 12:05 pm
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
Consultant - Straight Path Solutions
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
April 30, 2014 at 1:16 pm
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
April 30, 2014 at 1:39 pm
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
Consultant - Straight Path Solutions
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply