February 28, 2008 at 1:02 pm
HI Group,
I have three SQL queries from which i am getting three fields which i need to caluclate later on in Crystal Reports.
But here is my question how can we join these 3 queries into a single query ...please help me out..
Out of single query my result set must be something like this.... Line_of_business,Eff_Year,RISK,Ineligible, Identified,ActivelyEngaged.
here are my queries....
1)SELECT TOP 100 PERCENT a.Line_of_business, COUNT(a.LM_MBR_KEY) AS Ineligible, a.Eff_Year, b.RISK
FROM u30597.AllMembers a INNER JOIN
dbo.tbl_LM_MBR_PRG b ON a.LM_MBR_KEY = b.LM_MBR_KEY AND a.maxeff_dt = b.EFF_DT AND b.RISK <> 'low' AND (b.STATUS = 'Closed ' AND
b.STATUS_RSN = 'Insurance Ineligible' OR
b.STATUS = 'Closed ' AND b.STATUS_RSN = 'Not Medically Eligible' OR
b.STATUS = 'Refused ' AND b.STATUS_RSN = 'Does Not Have Condition' OR
b.STATUS = 'Refused ' AND b.STATUS_RSN = 'Hospice' OR
b.STATUS = 'Refused ' AND b.STATUS_RSN = 'Insurance Ineligible' OR
b.STATUS = 'Refused ' AND b.STATUS_RSN = 'Insurer Changed' OR
b.STATUS = 'Refused ' AND b.STATUS_RSN = 'LifeMasters Declined' OR
b.STATUS = 'Refused ' AND b.STATUS_RSN = 'No Program Status' OR
b.STATUS = 'Refused ' AND b.STATUS_RSN = 'Not Medically Eligible')
GROUP BY a.Line_of_business, a.Eff_Year, b.RISK
ORDER BY a.Line_of_business, a.Eff_Year, b.RISK
2)SELECT TOP 100 PERCENT a.Line_of_business, COUNT(a.LM_MBR_KEY) AS ActivelyEngaged, a.Eff_Year, b.RISK
FROM u30597.AllMembers a INNER JOIN
dbo.tbl_LM_MBR_PRG b ON a.LM_MBR_KEY = b.LM_MBR_KEY AND a.maxeff_dt = b.EFF_DT AND b.RISK <> 'low' AND b.STATUS = 'Enrolled' AND
b.STATUS_RSN = 'Active' in
GROUP BY a.Line_of_business, a.Eff_Year, b.RISK
ORDER BY a.Line_of_business, a.Eff_Year, b.RISK
3)SELECT TOP 100 PERCENT a.Line_of_business, COUNT(a.LM_MBR_KEY) AS Identified, a.Eff_Year, b.RISK
FROM u30597.AllMembers a INNER JOIN
dbo.tbl_LM_MBR_PRG b ON a.LM_MBR_KEY = b.LM_MBR_KEY AND a.maxeff_dt = b.EFF_DT AND b.RISK <> 'low'
GROUP BY a.Line_of_business, a.Eff_Year, b.RISK
ORDER BY a.Line_of_business, a.Eff_Year, b.RISK
February 28, 2008 at 1:22 pm
If you want these queries to be columns of a result set then you will have to use the cross apply join. You can save the results into a temp or table variable then use the cross apply. I have provided an example below.
select 1 [1],2 [2],3 [3] into #temp
select 4 [4],5 [5],6 [6] into #temp2
select 5 [5],6 [6],7 [7] into #temp3
select *
from #temp a
cross apply
(select * from #temp2) as b
cross apply
(select * from #temp3)as c
drop table #temp
drop table #temp2
drop table #temp3
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply