November 30, 2011 at 8:00 pm
i didnt post the example code because i am hoping the question doesnt demand it...
first piece neively works...am trying to get an amount of money specific to a year that defines an 'Excess Claim', and use that amount as a filter in a subsequent cte (but i didnt make it recursive), which acts as an escalating gate over how bad a claim has to get before it hits excess limits.
so select from fundyears works, gives me a year and an amount...so i put a comma between fundyears and my next foray, hoping to use the specificlevel to filter high dollar members...these members must attain the specific level as a threshhold (aggregate of all claims, not just the dx, but i need the most expensive dx as THE dx)...
my problem (well, one among many <g>) is that highdollarmembers does not recogonize the reference to fundyears.specificlevel.
you can't do that?
;with fundyears (fundyear,specificlevel) as
(select fundyear, specificlevel
from common.dbo.fund
where fundcode='sewerfund')
,
highdollarmembers (employee_id, paid, primary_diagnosis ) as
(select employee_id,SUM(paid)paid , Primary_Diagnosis
from datamartarc.dbo.YearlyControlTotalsClinicalTest z
group by employee_id, primary_diagnosis
having SUM(paid)>=fundyears.specificlevel)
select fundyears.fundyear, highdollarmembers.employee_id,
highdollarmembers.paid as ttlpaid, highdollarmembers.primary_diagnosis
from fundyears cross apply highdollarmembers
thank you for bearing wih me while i detoxify from my cursor habit.
drew
November 30, 2011 at 9:40 pm
drew.georgopulos (11/30/2011)
i didnt post the example code because i am hoping the question doesnt demand it...first piece neively works...am trying to get an amount of money specific to a year that defines an 'Excess Claim', and use that amount as a filter in a subsequent cte (but i didnt make it recursive), which acts as an escalating gate over how bad a claim has to get before it hits excess limits.
so select from fundyears works, gives me a year and an amount...so i put a comma between fundyears and my next foray, hoping to use the specificlevel to filter high dollar members...these members must attain the specific level as a threshhold (aggregate of all claims, not just the dx, but i need the most expensive dx as THE dx)...
my problem (well, one among many <g>) is that highdollarmembers does not recogonize the reference to fundyears.specificlevel.
you can't do that?
;with fundyears (fundyear,specificlevel) as
(select fundyear, specificlevel
from common.dbo.fund
where fundcode='sewerfund')
,
highdollarmembers (employee_id, paid, primary_diagnosis ) as
(select employee_id,SUM(paid)paid , Primary_Diagnosis
from datamartarc.dbo.YearlyControlTotalsClinicalTest z
group by employee_id, primary_diagnosis
having SUM(paid)>=fundyears.specificlevel)
select fundyears.fundyear, highdollarmembers.employee_id,
highdollarmembers.paid as ttlpaid, highdollarmembers.primary_diagnosis
from fundyears cross apply highdollarmembers
thank you for bearing wih me while i detoxify from my cursor habit.
drew
No, you can't.
try this (untested):
;with fundyears (fundyear,specificlevel) as
(select fundyear, specificlevel
from common.dbo.fund
where fundcode='sewerfund')
,
--here's where DDL would help....
--
member_dx ( employeeid, fundyear, paid, Primary_Diagnosis, dx_rank, total_paid)
as (select employee_id,
fundyear, paid , Primary_Diagnosis,
rank() over (partition by employeeid, fundyear order by paid) dx_rank,
sum(paid) over (partition by employeeid, fundyear) total_paid
from datamartarc.dbo.YearlyControlTotalsClinicalTest z
),
select dx.* from member_dx dx
inner join fundyears f
on dx.fundyear = f.fundyear
where dx_rank =1 and total_paid >specificlevel
December 1, 2011 at 6:08 am
that looks really wonderful...thanks a ton
i will work on it today
drew
December 1, 2011 at 4:17 pm
You can refer to previously defined CTEs in a later CTE, but you must reference it like a table, i.e. JOIN to it, since that is effectively what it is.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply