March 18, 2008 at 10:05 am
Hello every one well here is my problem.....I have a SQL statement going like this,and the result set is followed,
My problem is If you watch the result set i have data for only 3rd and 4th quarters in 2007.I am using this statement in Crystal Reports and i want to show 1st and 2nd quarters with 0's in it, which doesnot exist in Database.
How can we show them,i tried using Union all by selecting another statement but i am unsuccessfull,Please help me out.
here is the base SQL statement......
Select Ident.business,Ident.Eff_year,Ident.Account_no,Isnull(Ident.Quarters,0) as Quarters,Ident.risk,Identtotal.IdentifiedTotal,IsNull (Ident.Identified,0)Identified,InegTotal.IneligibleTotal,IsNull(Ineg.Ineligible,0) as Ineligible,Isnull(Acteng.ActivelyEngaged,0) as Activelyengaged
From(
(Select a.Line_of_Business as business,Eff_year as eff_year,'000'+a.account_no as Account_no,b.Risk as risk,a.Eff_Qtr as Quarters,Count(a.LM_MBR_KEY) as Identified
from [dbo].vwAllmemberscounts 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
group by Eff_Year,b.risk,a.Line_of_Business,a.Eff_Qtr,a.Account_no
) Ident
Inner join
(Select a.Line_of_Business as business,Eff_year as eff_year,'000'+a.account_no as Account_no,b.Risk as risk,Count(a.LM_MBR_KEY) as IdentifiedTotal
from [dbo].vwAllmemberscounts 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
group by Eff_Year,b.risk,a.Line_of_Business,a.Account_no
) Identtotal
on
Ident.business = IdentTotal.business
And Ident.Eff_year =IdentTotal.Eff_year
And Ident.risk = IdentTotal.risk
And Ident.Account_no = IdentTotal.Account_no
Inner Join
(Select a.Line_of_Business as business,Eff_year as eff_year,'000'+a.account_no as Account_no,b.Risk as risk,Count(a.LM_MBR_KEY) as Ineligibletotal
from [dbo].vwAllmemberscounts 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 ((status = 'Closed ' and status_Rsn = 'Insurance Ineligible')
or (status = 'Closed ' and status_Rsn = 'Not Medically Eligible')
or (status = 'Refused ' and status_Rsn = 'Does Not Have Condition')
or (status = 'Refused ' and status_Rsn = 'Hospice')
or (status = 'Refused ' and status_Rsn = 'Insurance Ineligible')
or (status = 'Refused ' and status_Rsn = 'Insurer Changed')
or (status = 'Refused ' and status_Rsn = 'LifeMasters Declined')
or (status = 'Refused ' and status_Rsn = 'No Program Status')
or (status = 'Refused ' and status_Rsn = 'Not Medically Eligible'))
group by Eff_Year,b.risk,a.Line_of_Business,Account_no) InegTotal
on
Ident.business = InegTotal.business
And Ident.Eff_year =InegTotal.Eff_year
And Ident.risk = InegTotal.risk
And Ident.Account_no = InegTotal.Account_no
Inner join
(Select a.Line_of_Business as business,Eff_year as eff_year,'000'+a.account_no as Account_no,b.Risk as risk,a.Eff_Qtr as Quarters,Count(a.LM_MBR_KEY) as Ineligible
from [dbo].vwAllmemberscounts 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 ((status = 'Closed ' and status_Rsn = 'Insurance Ineligible')
or (status = 'Closed ' and status_Rsn = 'Not Medically Eligible')
or (status = 'Refused ' and status_Rsn = 'Does Not Have Condition')
or (status = 'Refused ' and status_Rsn = 'Hospice')
or (status = 'Refused ' and status_Rsn = 'Insurance Ineligible')
or (status = 'Refused ' and status_Rsn = 'Insurer Changed')
or (status = 'Refused ' and status_Rsn = 'LifeMasters Declined')
or (status = 'Refused ' and status_Rsn = 'No Program Status')
or (status = 'Refused ' and status_Rsn = 'Not Medically Eligible'))
group by Eff_Year,b.risk,a.Line_of_Business,a.eff_qtr,a.Account_no) Ineg
On
Ident.business = Ineg.business
And Ident.Eff_year =Ineg.Eff_year
And Ident.risk = Ineg.risk
and Ident.Quarters = Ineg.Quarters
And Ident.Account_no = Ineg.Account_no
Inner Join
(Select a.Line_of_Business as business,Eff_year as eff_year,'000'+a.account_no as Account_no,b.Risk as risk,a.Eff_Qtr as Quarters,Count(a.LM_MBR_KEY) as ActivelyEngaged
from [dbo].vwAllmemberscounts 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.Status = 'Enrolled' and b.Status_Rsn = 'Active')
group by Eff_Year,b.risk,a.Line_of_Business,a.eff_qtr,a.Account_no) Acteng
On
Ident.business = Acteng.business
And Ident.Eff_year =Acteng.Eff_year
And Ident.risk = Acteng.risk
and Ident.Quarters = Acteng.Quarters
And Ident.Account_no = Acteng.Account_no)
Where Ident.risk = 'High'
here is result set of above SQL
BusinessEff_yearAcc_noQuarterRiskIdtotalIdentified
IL120079974High156
IL1200714323High24093
IL12008812001High342342
March 18, 2008 at 10:17 am
You need a table with the values you want to show as the "base table". LEFT OUTER JOIN from it onto the existing query you have and use its values for Year and Quarter.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 18, 2008 at 12:20 pm
Matt is absolutely spot on. Without actually getting into using a Tally table to do this, here's one way to programmatically generate a Year's worth of dates using a CTE. Study the code... the method is an "ancient" numbering trick that some of us use all the time...
DECLARE @Year INT
SET @Year = 2008
;WITH cteDates AS
(
SELECT DATEADD(yy,@Year-1900,0)+Number AS TheDate
FROM Master.dbo.spt_Values
WHERE Type = 'P'
)
SELECT cd.TheDate, DATEPART(qq,TheDate) AS TheQuarter
FROM cteDates cd
WHERE YEAR(TheDate) = @Year
If you really want to do it right, then create a Tally table as in the following URL... will usually beat a CTE or any other method pretty much hands down...
http://www.sqlservercentral.com/scripts/Advanced+SQL/62486/
--Jeff Moden
Change is inevitable... Change for the better is not.
March 18, 2008 at 12:32 pm
Sorry... forgot to add the line that makes it more than twice as fast...
DECLARE @Year INT
SET @Year = 2008
;WITH cteDates AS
(
SELECT DATEADD(yy,@Year-1900,0)+Number AS TheDate
FROM Master.dbo.spt_Values
WHERE Type = 'P'
AND Number <= 366
)
SELECT cd.TheDate, DATEPART(qq,TheDate) AS TheQuarter
FROM cteDates cd
WHERE YEAR(TheDate) = @Year
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply