Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


how to combine 3 different select statements into one


how to combine 3 different select statements into one

Author
Message
mcfarlandparkway
mcfarlandparkway
SSC-Enthusiastic
SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)

Group: General Forum Members
Points: 183 Visits: 459
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
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11040 Visits: 14858
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
mcfarlandparkway
mcfarlandparkway
SSC-Enthusiastic
SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)

Group: General Forum Members
Points: 183 Visits: 459
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.
gbritton1
gbritton1
Old Hand
Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)

Group: General Forum Members
Points: 369 Visits: 840
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
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11040 Visits: 14858
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
mcfarlandparkway
mcfarlandparkway
SSC-Enthusiastic
SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)

Group: General Forum Members
Points: 183 Visits: 459
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
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11040 Visits: 14858
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search