mguptill
asked on
SQL query HELP!!!
How can I get the following query to show me results of the top date for each month reported?
any help you can provide will be greatly appreciated. I've attatched my query and results set for you consideration. FYI the results I would like to see would be like below. This client has 78 records and I only want to see the most recent service dates which would bring me too 3 records for this client.
A SvcDate SvcSys SvcSyscheck
9 2011-07-28 00:00:00.000 X9834 445-DD
9 2011-08-31 00:00:00.000 X9827 445-DD
9 2011-09-20 00:00:00.000 X9825 445-DD
any help you can provide will be greatly appreciated. I've attatched my query and results set for you consideration. FYI the results I would like to see would be like below. This client has 78 records and I only want to see the most recent service dates which would bring me too 3 records for this client.
A SvcDate SvcSys SvcSyscheck
9 2011-07-28 00:00:00.000 X9834 445-DD
9 2011-08-31 00:00:00.000 X9827 445-DD
9 2011-09-20 00:00:00.000 X9825 445-DD
use BHS
select aw.clientcode_c,
'A' = 9
--,'Duck'= right(cl.clientcode_c,9)
,'SvcDate' = aw.activitydate_d
,'SvcSys' = aw.activity_c
,'SvcSyscheck' =
case
when aw.activity_c like ('IHS%') then '446-IHS'
when aw.activity_c in ('W3482','W3303','W3304','W3300','9837A','W3200','W3220','W3411',
'W3402','W3403','W3440','W3005','W3416','W3400','W3100','W3106','W3102','W3000','W3001')
THEN '444-ABD'
when aw.attendance_c in('ST','N') then
'1074-Non Medicaid'
when aw.activity_c in ('X9846','X9847','X9840','X9841','H2015','X9834','X9837','9837F','9837W','X9835','X9839','X9980','W9820','W9821',
'W9822','W9823','W9824','W9866','X9831','X9827','X3333','X4444','X9825','X9845','T9825')
THEN '445-DD'
else 'No Data'
end
into #Duck
from ar.client cl
inner join ar.activwork aw on cl.uniqueid_c = aw.clientid_c
where aw.activitydate_d
between '07/01/2011'and CONVERT(varchar, CONVERT(datetime, getdate(), 101), 101)
and aw.attendance_c in ('01','R','ST','N','B','E','C')and aw.program_c not in ('ESS','ASD','FSP')
order by aw.activitydate_d, aw.clientcode_c desc
go
select A, SvcDate,SvcSys,SvcSyscheck
from #Duck
group by A,SvcDate,SvcSys,SvcSyscheck
order by SvcDate
go
Drop table #duckResults
A SvcDate SvcSys SvcSyscheck
9 2011-07-01 00:00:00.000 X9834 445-DD
9 2011-07-05 00:00:00.000 X9834 445-DD
9 2011-07-06 00:00:00.000 X9834 445-DD
9 2011-07-12 00:00:00.000 966 No Data
9 2011-07-12 00:00:00.000 X9827 445-DD
9 2011-07-15 00:00:00.000 X9834 445-DD
9 2011-07-18 00:00:00.000 X9834 445-DD
9 2011-07-19 00:00:00.000 966 No Data
9 2011-07-19 00:00:00.000 X9825 445-DD
9 2011-07-19 00:00:00.000 X9827 445-DD
9 2011-07-19 00:00:00.000 X9834 445-DD
9 2011-07-21 00:00:00.000 X9834 445-DD
9 2011-07-26 00:00:00.000 966 No Data
9 2011-07-26 00:00:00.000 X9827 445-DD
9 2011-07-28 00:00:00.000 X9834 445-DD
9 2011-08-02 00:00:00.000 966 No Data
9 2011-08-02 00:00:00.000 X9827 445-DD
9 2011-08-02 00:00:00.000 X9834 445-DD
9 2011-08-05 00:00:00.000 X9834 445-DD
9 2011-08-08 00:00:00.000 966 No Data
9 2011-08-08 00:00:00.000 X9827 445-DD
9 2011-08-09 00:00:00.000 966 No Data
9 2011-08-09 00:00:00.000 X9827 445-DD
9 2011-08-10 00:00:00.000 966 No Data
9 2011-08-10 00:00:00.000 X9827 445-DD
9 2011-08-10 00:00:00.000 X9834 445-DD
9 2011-08-11 00:00:00.000 966 No Data
9 2011-08-11 00:00:00.000 X9827 445-DD
9 2011-08-12 00:00:00.000 966 No Data
9 2011-08-12 00:00:00.000 X9827 445-DD
9 2011-08-16 00:00:00.000 966 No Data
9 2011-08-16 00:00:00.000 X9827 445-DD
9 2011-08-16 00:00:00.000 X9834 445-DD
9 2011-08-17 00:00:00.000 966 No Data
9 2011-08-17 00:00:00.000 X9827 445-DD
9 2011-08-19 00:00:00.000 966 No Data
9 2011-08-19 00:00:00.000 X9827 445-DD
9 2011-08-22 00:00:00.000 966 No Data
9 2011-08-22 00:00:00.000 X9827 445-DD
9 2011-08-23 00:00:00.000 966 No Data
9 2011-08-23 00:00:00.000 X9827 445-DD
9 2011-08-24 00:00:00.000 966 No Data
9 2011-08-24 00:00:00.000 X9827 445-DD
9 2011-08-25 00:00:00.000 966 No Data
9 2011-08-25 00:00:00.000 X9827 445-DD
9 2011-08-25 00:00:00.000 X9834 445-DD
9 2011-08-26 00:00:00.000 966 No Data
9 2011-08-26 00:00:00.000 X9827 445-DD
9 2011-08-30 00:00:00.000 966 No Data
9 2011-08-30 00:00:00.000 X9827 445-DD
9 2011-08-31 00:00:00.000 966 No Data
9 2011-08-31 00:00:00.000 X9825 445-DD
9 2011-08-31 00:00:00.000 X9827 445-DD
9 2011-09-01 00:00:00.000 966 No Data
9 2011-09-01 00:00:00.000 X9827 445-DD
9 2011-09-02 00:00:00.000 966 No Data
9 2011-09-02 00:00:00.000 X9827 445-DD
9 2011-09-06 00:00:00.000 966 No Data
9 2011-09-06 00:00:00.000 X9827 445-DD
9 2011-09-07 00:00:00.000 966 No Data
9 2011-09-07 00:00:00.000 X9827 445-DD
9 2011-09-08 00:00:00.000 966 No Data
9 2011-09-08 00:00:00.000 X9827 445-DD
9 2011-09-09 00:00:00.000 966 No Data
9 2011-09-09 00:00:00.000 X9827 445-DD
9 2011-09-12 00:00:00.000 966 No Data
9 2011-09-12 00:00:00.000 X9827 445-DD
9 2011-09-13 00:00:00.000 966 No Data
9 2011-09-13 00:00:00.000 X9827 445-DD
9 2011-09-14 00:00:00.000 966 No Data
9 2011-09-14 00:00:00.000 X9827 445-DD
9 2011-09-15 00:00:00.000 966 No Data
9 2011-09-15 00:00:00.000 X9827 445-DD
9 2011-09-16 00:00:00.000 966 No Data
9 2011-09-16 00:00:00.000 X9827 445-DD
9 2011-09-19 00:00:00.000 966 No Data
9 2011-09-20 00:00:00.000 966 No Data
9 2011-09-20 00:00:00.000 X9825 445-DDASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I'm not how or where to insert this part into the querry
ASKER
I figgured out how to insert into the querry and it works for that one client now when I take out the limiting criteria for the one client i still only get three records. how can I apply this to all so I get the top three for every client listed?
Example
A SvcDate SvcSys SvcSyscheck Client
9 2011-07-28 00:00:00.000 X9834 445-DD A
9 2011-08-31 00:00:00.000 X9827 445-DD A
9 2011-09-20 00:00:00.000 X9825 445-DD A
9 2011-07-28 00:00:00.000 X9834 445-DD B
9 2011-08-31 00:00:00.000 X9827 445-DD B
9 2011-09-20 00:00:00.000 X9825 445-DD B
Example
A SvcDate SvcSys SvcSyscheck Client
9 2011-07-28 00:00:00.000 X9834 445-DD A
9 2011-08-31 00:00:00.000 X9827 445-DD A
9 2011-09-20 00:00:00.000 X9825 445-DD A
9 2011-07-28 00:00:00.000 X9834 445-DD B
9 2011-08-31 00:00:00.000 X9827 445-DD B
9 2011-09-20 00:00:00.000 X9825 445-DD B
ASKER
If I run this for all clients I get these results.
I should see all clients top date for the months of july, august and sept my results are
A Duck SvcDate SvcSys SvcSyscheck
9 123 2011-07-31 00:00:00.000 X9980 445-DD
9 456 2011-08-31 00:00:00.000 X9980 445-DD
9 876 2011-09-22 00:00:00.000 996F No Data
I should see all clients top date for the months of july, august and sept my results are
A Duck SvcDate SvcSys SvcSyscheck
9 123 2011-07-31 00:00:00.000 X9980 445-DD
9 456 2011-08-31 00:00:00.000 X9980 445-DD
9 876 2011-09-22 00:00:00.000 996F No Data
ASKER
Here is what I did.
use BHS
select aw.clientcode_c,
'A' = 9
,'Duck'= right(cl.clientcode_c,9)
,'SvcDate' = aw.activitydate_d
,'SvcSys' = aw.activity_c
,'SvcSyscheck' =
case
when aw.activity_c like ('IHS%') then '446-IHS'
when aw.activity_c in ('W3482','W3303','W3304','W3300','9837A','W3200','W3220','W3411','W3402','W3403','W3440','W3005','W3416','W3400','W3100','W3106','W3102','W3000','W3001')
THEN '444-ABD'
when aw.attendance_c in('ST','N') then
'1074-Non Medicaid'
when aw.activity_c in ('X9846','X9847','X9840','X9841','H2015','X9834','X9837','9837F','9837W','X9835','X9839','X9980','W9820','W9821','W9822','W9823','W9824','W9866','X9831','X9827','X3333','X4444','X9825','X9845','T9825')
THEN '445-DD'
else 'No Data'
end
into #Duck
from ar.client cl
inner join ar.activwork aw on cl.uniqueid_c = aw.clientid_c
where aw.activitydate_d
between '07/01/2011'and CONVERT(varchar, CONVERT(datetime, getdate(), 101), 101)
and aw.attendance_c in ('01','R','ST','N','B','E','C')and aw.program_c not in ('ESS','ASD','FSP')
and aw.clientcode_c = 'DS11123767'
--like ('DS%')
order by aw.activitydate_d, aw.clientcode_c desc
;with
cte1 as
(
select DATEPART(YEAR, SvcDate) as [year], DATEPART(MONTH, SvcDate)as [month], A,Duck,SvcDate,SvcSys,SvcSyscheck
from #Duck
),
cte2 as
(
select A,Duck, SvcDate,SvcSys,SvcSyscheck, ROW_NUMBER() over (PARTITION BY [year] ,[month] order by SvcDate desc) rn
from cte1
)
select A,Duck, SvcDate,SvcSys,SvcSyscheck
from cte2
where rn = 1
--Toggle HighlightingOpen in New Window
/*
select A,Duck, SvcDate,SvcSys,SvcSyscheck
from #Duck
group by A,Duck,SvcDate,SvcSys,SvcSyscheck
order by SvcDate*/
go
Drop table #duckASKER
Ok I figgured my question out here is what is working for me I will award you the points since with out you I wouldn't have gotten this far . Thanks again
use BHS
select aw.clientcode_c,
'A' = 9
,'Duck'= right(cl.clientcode_c,9)
,'SvcDate' = aw.activitydate_d
,'SvcSys' = aw.activity_c
,'SvcSyscheck' =
case
when aw.activity_c like ('IHS%') then '446-IHS'
when aw.activity_c in ('W3482','W3303','W3304','W3300','9837A','W3200','W3220','W3411','W3402',
'W3403','W3440','W3005','W3416','W3400','W3100','W3106','W3102','W3000','W3001')
THEN '444-ABD'
when aw.attendance_c in('ST','N') then
'1074-Non Medicaid'
when aw.activity_c in ('X9846','X9847','X9840','X9841','H2015','X9834','X9837','9837F','9837W','X9835','X9839','X9980','W9820',
'W9821','W9822','W9823','W9824','W9866','X9831','X9827','X3333','X4444','X9825','X9845','T9825')
THEN '445-DD'
else 'No Data'
end
into #Duck
from ar.client cl
inner join ar.activwork aw on cl.uniqueid_c = aw.clientid_c
where aw.activitydate_d
between '07/01/2011'and CONVERT(varchar, CONVERT(datetime, getdate(), 101), 101)
and aw.attendance_c in ('01','R','ST','N','B','E','C')and aw.program_c not in ('ESS','ASD','FSP')
and aw.clientcode_c like ('DS%')
order by aw.activitydate_d, aw.clientcode_c desc
;with
cte1 as
(
select DATEPART(YEAR, SvcDate) as [year], DATEPART(MONTH, SvcDate)as [month], A,Duck,SvcDate,SvcSys,SvcSyscheck
from #Duck
),
cte2 as
(
select A,Duck, SvcDate,SvcSys,SvcSyscheck, ROW_NUMBER() over (PARTITION BY duck, [year] ,[month] order by duck,SvcDate desc) rn
from cte1
)
select A,Duck, SvcDate,SvcSys,SvcSyscheck,rn
from cte2
where rn = 1
go
Drop table #duckASKER
You are the man thanks so much for your quick response..........
ASKER