SQL Query Correction

SQL 2005 / 2010

From the below query, i am trying to fetch records affected in the Last 90 Days. with Most Recent
( Only one Row ) of Rx.FillDate


My syntax which i attached below returns Distinct Fill Date for a person between those days. So, if that person had more than one filldate is also reflecting.

We need only one most recent Fill Date in the Last 90 Days.





select 	distinct pat.NameLast as [Last Name],                
		pat.NameFirst as [First Name],            
		case when len(pat.DOB) = 7 then dateadd(d, (cast(RIGHT(pat.DOB,3) as int) -1), cast(LEFT(pat.DOB,4) as    DATE))else null end as [DOB],             
		pat.AdrsAdd1 as [Address1],
		pat.AdrsAdd2 as [Address2],
		pat.AdrsCity as [City],
		pat.AdrsState,
		convert(varchar, Left(Pat.AdrsZip,5),101) as [ZipCode]
		
		,case when len(Rx.FillDate) = 7 then dateadd(d, (cast(RIGHT(Rx.FillDate,3) as int) -1), cast(LEFT    (Rx.FillDate,4) as DATE))else null end as [Fill Date]                    
from	patient pat
inner join rx rx
on pat.PatKey = rx.PatNbrKey
Where  
--  pat.AdrsZip like '281%' and 
  Rx.FillDate BETWEEN YEAR('09/15/2011')*1000 + DATEPART(dy, '09/15/2011')                
AND   YEAR('12/13/2011')*1000 + DATEPART(dy, '12/13/2011')  
order by pat.NameLast Asc

Open in new window

chokkaStudentAsked:
Who is Participating?
 
Christopher GordonConnect With a Mentor Senior Developer AnalystCommented:
I use a ROW_NUMBER() function to handle these types of scenarios.


with Rx_Instances as
(
	select 	distinct pat.NameLast as [Last Name],                
			pat.NameFirst as [First Name],            
			case when len(pat.DOB) = 7 then dateadd(d, (cast(RIGHT(pat.DOB,3) as int) -1), cast(LEFT(pat.DOB,4) as    DATE))else null end as [DOB],             
			pat.AdrsAdd1 as [Address1],
			pat.AdrsAdd2 as [Address2],
			pat.AdrsCity as [City],
			pat.AdrsState,
			convert(varchar, Left(Pat.AdrsZip,5),101) as [ZipCode]
			
			,case when len(Rx.FillDate) = 7 then dateadd(d, (cast(RIGHT(Rx.FillDate,3) as int) -1), cast(LEFT    (Rx.FillDate,4) as DATE))else null end as [Fill Date]                    

			, ROW_NUMBER() over (Partition By pat.PatKey order by Rx.FillDate desc) as Instance_Counter
	from	patient pat
	inner join rx rx
	on pat.PatKey = rx.PatNbrKey
	Where  
	--  pat.AdrsZip like '281%' and 
	  Rx.FillDate BETWEEN YEAR('09/15/2011')*1000 + DATEPART(dy, '09/15/2011')                
	AND   YEAR('12/13/2011')*1000 + DATEPART(dy, '12/13/2011')  
)

select	*
from	Rx_Instances
where	Instance_Counter = 1
order by pat.NameLast Asc

Open in new window

0
 
Jesus RodriguezConnect With a Mentor IT ManagerCommented:
Try this
 
select [Last Name],[First Name],[DOB],[Address1],[Address2],[city],[ZipCode],MAX([FillDate])
 from (
		select 	distinct pat.NameLast as [Last Name],                
				pat.NameFirst as [First Name],            
				case when len(pat.DOB) = 7 then dateadd(d, (cast(RIGHT(pat.DOB,3) as int) -1), cast(LEFT(pat.DOB,4) as    DATE))else null end as [DOB],             
				pat.AdrsAdd1 as [Address1],
				pat.AdrsAdd2 as [Address2],
				pat.AdrsCity as [City],
				pat.AdrsState,
				convert(varchar, Left(Pat.AdrsZip,5),101) as [ZipCode]
				
				,case when len(Rx.FillDate) = 7 then dateadd(d, (cast(RIGHT(Rx.FillDate,3) as int) -1), cast(LEFT    (Rx.FillDate,4) as DATE))else null end as [Fill Date]                    
		from	patient pat
		inner join rx rx
		on pat.PatKey = rx.PatNbrKey
		Where  
		--  pat.AdrsZip like '281%' and 
		  Rx.FillDate BETWEEN YEAR('09/15/2011')*1000 + DATEPART(dy, '09/15/2011')                
		AND   YEAR('12/13/2011')*1000 + DATEPART(dy, '12/13/2011')) AS A 
group by [Last Name],[First Name],[DOB],[Address1],[Address2],[city],[ZipCode] 
order by [Last Name] Asc

Open in new window

0
 
Christopher GordonSenior Developer AnalystCommented:
Note:

ROW_NUMBER() over (Partition By pat.PatKey order by Rx.FillDate desc) as Instance_Counter

This code places a unique number for each instance of fill date for a customer within your date range.  


The where clause outside of the common table expression then filters to the first instance, which in this case is the most recent as determined by Fill Date in descending order.
0
 
chokkaStudentAuthor Commented:
Both of you reflecting same number of records !! Thank you, I will check the results !!

But, i guess both of you helped me a lot by sharing the correct answer !!
0
 
chokkaStudentAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.