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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Christopher GordonSenior 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jesus RodriguezIT 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.