October 22, 2013 at 3:05 pm
I created my first CTE, I may have been overly ambitious but it looks like something I want to learn. Its not pretty but it works. The only thing I am having a issue with is trying to figure out how to filter out some results.
The part I'm trying to filter out is multiple effective dates. If a person has had two date entries for the year I only want the most recent.
I have tried different versions of this but cant get it to work right. The part that is bolded is my attempt at taking only the most recent value
GO
With PovertyPct_CTE
(
PersonID,SFMonthlyIncome,SFFamilySize,Povertypct,
Poverty, sfeffectivedte)
AS
(Select Distinct
PersonID=c.PersonID,
SFMonthlyIncome = ISNULL(SFMonthlyIncome,0), SFFamilySize = ISNULL(SFFamilySize,0)
,Povertypct = (ISNULL(SFMonthlyIncome,0) * 12 * 100)/(11170 +
(3960 * ((ISNULL(SFFamilySize,0) - 1))))
,SFFamilySize
,SFEffectiveDte = (SELECT MAX(SFEffectiveDte)
FROM vwUDSSlidingFeeHistory sfs
Inner Join vwUDSTMP5 t on t.PersonID=sfs.HeadofHouseholdPerID
WHERE SFEffectiveDte <= '10/18/13')
From dbo.CHCViewPersonSlidingFeeHistory c
Inner Join vwUDSTMP5 v on v.PersonID=c.PersonID
Where
v.DateOfServiceFrom between '01/01/13' and '10/18/13'and
v.EncounterType Not In ('No Encounter Type'))
Select SFMonthlyIncome,SFFamilySize ,Povertypct,PersonID,
CASE
WHEN ISNULL(SFFamilySize,0) = 0 THEN '5'
WHEN PovertyPct BETWEEN 0 AND 100 THEN '1'
WHEN PovertyPct BETWEEN 101 AND 150 THEN '2'
WHEN PovertyPct BETWEEN 151 AND 200 THEN '3'
WHEN PovertyPct > 200 THEN '4'
ELSE '5'
END
AS Poverty
,sfeffectivedte
From PovertyPct_CTE
***SQL born on date Spring 2013:-)
October 22, 2013 at 3:12 pm
Hm. Without table definitions and a bit of sample data, it's hard to make an exact suggestion, though I can see the problem you're having; you're using MAX(SFEffectiveDte), but there's no other columns to draw from.
In essence, you're just pulling the highest SFEffectiveDte from that table, without regard to who it belongs to. Coincidentally, though, this is the sort of thing a CTE is good for! If you could write up a CTE that did, say...
SELECT PersonId,MAX(SFEffectiveDte)
FROM (table)
GROUP BY PersonId
... You could JOIN that to your original CTE. Granted, you may or may not need a CTE for that large query, though I don't know the situation you're tailoring it for. This way, you get each PersonID, and the maximum SFEffectiveDte for each one, and then JOIN it into the original query in place of the subquery you're having trouble with.
- 😀
October 22, 2013 at 4:15 pm
Thank you so much for your reply. I have been hearing so much about CTE's that I thought I would try to make one. What you are saying definitely makes sense considering some of my results I have had. I'm going to give it a few more try's to see if that added bit of info helps me figure out the logic of what I'm trying to do.
***SQL born on date Spring 2013:-)
October 23, 2013 at 8:10 am
So I figured this on out. I ended up using three CTE's. I also started with the Max(Date) first on advise from a friend then went from there.
Here is the finished version.
GO
WITH SFEffectiveDte_CTE
AS
(
SELECT DISTINCT
sfs.PersonID,
MAX(sfs.SFEffectiveDte) AS SFEffectiveDte
FROM
vwUDSSlidingFeeHistory sfs INNER JOIN dbo.CHCViewPersonSlidingFeeHistory c
ON c.PersonID=sfs.PersonID INNER JOIN dbo.vwUDSTMP5 v ON v.PersonID=sfs.PersonID
WHERE
c.PersonID=sfs.HeadofHouseholdPerID AND
sfs.SFEffectiveDte <= @DateTo AND v.EncounterType
NOT IN ('No Encounter Type')AND v.DateOfServiceFrom BETWEEN @DateFrom and @DateTo
GROUP BY sfs.PersonID
)
,
PovertyPct_CTE
(PersonID,
SFMonthlyIncome,SFFamilySize,Povertypct
)
AS
(SELECT DISTINCT
C.PersonID,
SFMonthlyIncome = ISNULL(SFMonthlyIncome,0),
SFFamilySize = ISNULL(SFFamilySize,0)
,PovertyPct = (ISNULL(SFMonthlyIncome,0) * 12 * 100)/(11170 +
(3960 * ((ISNULL(SFFamilySize,0) - 1))))
FROM dbo.CHCViewPersonSlidingFeeHistory c
INNER JOIN vwUDSTMP5 v ON v.PersonID=c.PersonID
INNER JOIN SFEffectiveDte_CTE sf ON sf.PersonID = v.PersonID
AND sf.SFEffectiveDte = c.SFEffectiveDte)
,
PovertyLevel_CTE
(PersonID,
PovertyLevel
)
AS
(SELECT DISTINCT
PovertyPct_cte.PersonID,
PovertyLevel =CASE
WHEN ISNULL(SFFamilySize,0) = 0 THEN '5'
WHEN PovertyPct BETWEEN 0 AND 100 THEN '1'
WHEN PovertyPct BETWEEN 101 AND 150 THEN '2'
WHEN PovertyPct BETWEEN 151 AND 200 THEN '3'
WHEN PovertyPct > 200 THEN '4'
ELSE '5'
End
FROM povertyPct_CTE)
SELECT * FROM PovertyLevel_CTE pl
INNER JOIN PovertyPct_CTE pp ON pl.PersonID=pp.PersonID
***SQL born on date Spring 2013:-)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply