Solved

SQL query HELP!!!

Posted on 2011-09-22
8
220 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
8 Comments
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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
 

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

726 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