Solved

SQL query HELP!!!

Posted on 2011-09-22
8
213 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

757 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

17 Experts available now in Live!

Get 1:1 Help Now