Solved

SQL query HELP!!!

Posted on 2011-09-22
8
214 Views
Last Modified: 2012-05-12
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
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 #duck

Open in new window

Results

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-DD

Open in new window

0
Comment
Question by:mguptill
  • 7
8 Comments
 
LVL 32

Accepted Solution

by:
ewangoya earned 500 total points
ID: 36582472
try
;with 
cte1 as
(
	select DATEPART(YEAR, SvcDate) [year], DATEPART(MONTH, SvcDate) [month], A, SvcDate,SvcSys,SvcSyscheck
	from #Duck
),
cte2 as
(
	select A, SvcDate,SvcSys,SvcSyscheck, ROW_NUMBER() over (PARTITION BY [year] [month] order by SvcDate desc) rn
	from cte1  
)

select A, SvcDate,SvcSys,SvcSyscheck
from cte2
where rn = 1

Open in new window

0
 

Author Comment

by:mguptill
ID: 36582506
I'm sorry ewangoya where would I insert this?
0
 

Author Comment

by:mguptill
ID: 36582558
I'm not how or where to insert this part into the querry
0
 

Author Comment

by:mguptill
ID: 36582639
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
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:mguptill
ID: 36582697
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
0
 

Author Comment

by:mguptill
ID: 36582728
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 #duck

Open in new window

0
 

Author Comment

by:mguptill
ID: 36582867
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 #duck

Open in new window

0
 

Author Closing Comment

by:mguptill
ID: 36582873
You are the man thanks so much for your quick response..........
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
set default date format in mssql to mm/dd/yyyy 22 66
Unable to save view in SSMS 21 53
sql server query from excel 3 57
SQL Server - Slabs 9 35
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now