SQL Server - Please explain query for me - Asked By Rosie Buchanan on 15-Mar-12 07:54 AM

Earn up to 30 extra points for answering this tough question.
Especially the rank part. Thanks!


if @doc in ('V01')
Begin
;WITH Base AS (

SELECT p.DOC,

p.Reg,

n.RegionAcronym,

p.FO,

p.CLMS,


DATEDIFF(DAY, MAX(p.filedate), GETDATE()) AS Age,

MAX(FileDate) AS FileDate,

o.mailingaddressstate AS DDS,

o.ddsofficecode AS DDSCode,

MIN(ddsrcpt) AS DDSReceipt,



DDSAge = Datediff(day,min(p.ddsrcpt), getdate()),


CASE WHEN MIN(p.Title) <> MAX(p.Title) THEN 'Concurr' Else Min(p.title) End As title

--WHEN min(p.fo) <> max(p.fo) and MIN(p.Title) <> MAX(p.Title) THEN 'Concurr' ELSE MIN(p.Title) END AS title

FROM pendingdds AS p

JOIN offices.dbo.OfficeCodes AS d

ON d.officecode = p.doc

JOIN natdocfile AS n

ON n.doc = p.fo

JOIN offices.dbo.DoorsInfo AS o

ON o.officecode = p.fo

where p.doc in ('V01', 'S67', 'S41', 'S66') AND ( Datediff(DAY, filedate, Getdate()) > 300 )

GROUP BY p.fo,

p.Reg,

n.regionacronym,

p.DOC,

p.CLMSsn,

o.ddsofficecode,

o.mailingaddressstate

),

Ranked AS (

SELECT *,
-- return only the first rank which is 1
ROW_NUMBER() OVER(PARTITION BY clms ORDER BY FileDate DESC) AS rn

FROM Base)

SELECT *

FROM Ranked

WHERE rn = 1

ORDER BY clms, age DESC

end
D Company replied to Rosie Buchanan on 17-Mar-12 09:01 AM
As per my understanding the coder trying to get the record of a user with some filtration and the filters are based on few attributes

1. date difference(date in the receipt and current date)
2.Case when the title is some specific "title"
3.Applied join operation on other data sources with some common column
4.and in the rank section returns only the first rank which is 1 with the file date in descending order.

hope it adds some values to your clarification

Regards
D
 

Rosie Buchanan replied to D Company on 17-Mar-12 09:21 AM
Thanks for your reply. What does row number Over partition means?
D Company replied to Rosie Buchanan on 17-Mar-12 09:46 AM

Here Partition means group of claims(i think the data is related to claims)For each Partition (group of clms ), the data is row numbered following the order by FileDate desc, so 1,2,3,...starting from 1 for the latest FileDate. The WHERE clause then picks up only the latest per clms .


Hope this helps you
Regards
D



Rosie Buchanan replied to Rosie Buchanan on 17-Mar-12 09:55 AM
Thanks for explaining as I understand it all now!