April 26, 2010 at 12:47 pm
I have this code below which will give me a row number for each visit against a property.
My problem is that I want to be able to create a query from this code when I've made it as a view. The quiery I want to build is to use the max visit but making sure the max visit is the latest ApptDate as well, the problem I have is that the Max Apptdate could actually show as either row1 or 2, but in some case the row number 2 could have a ApptDate which is earlier than row number 1
SELECT *
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY P.prop_seql
ORDER BY P.prop_seql DESC) AS RowNumber
,P.prop_seql AS PropID
,P.prop_ref AS UPRN
,LTRIM(ISNULL(P.suffix, '') + ' ' + ISNULL(P.number, '') + ' ' + REPLACE(P.address_1, ',', ' ')) AS Address
,ISNULL(P.postcode, '') AS Postcode
,ASV.booking_date AS ApptDate
,ASV.[1st_na] AS NA1
,ASV.[2nd_na] AS NA2
,ASV.completion_date AS CompletedDate
,CASE
WHEN ASV.completion_date IS NULL AND ASV.booking_date < GETDATE() THEN ASV.booking_date
ELSE ASV.completion_date
END AS LastServiceDate
,DATEADD(DAY, 304,
CASE
WHEN ASV.completion_date IS NULL AND ASV.booking_date < GETDATE() THEN ASV.booking_date
ELSE ASV.completion_date
END) AS ContractualDueDate
,CONVERT(VARCHAR, DATEPART(MM, DATEADD(DAY, 304,
CASE
WHEN ASV.completion_date IS NULL AND ASV.booking_date < GETDATE() THEN ASV.booking_date
ELSE ASV.completion_date
END))) + CONVERT(VARCHAR, '/') + CONVERT(VARCHAR, DATEPART(YYYY, DATEADD(DAY, 304,
CASE
WHEN ASV.completion_date IS NULL AND ASV.booking_date < GETDATE() THEN ASV.booking_date
ELSE ASV.completion_date
END))) AS ContractualMonthDue
,CONVERT(VARCHAR, DATEPART(WW, DATEADD(DAY, 304,
CASE
WHEN ASV.completion_date IS NULL AND ASV.booking_date < GETDATE() THEN ASV.booking_date
ELSE ASV.completion_date
END))) + CONVERT(VARCHAR, '/') + CONVERT(VARCHAR, DATEPART(YYYY, DATEADD(DAY, 304,
CASE
WHEN ASV.completion_date IS NULL AND ASV.booking_date < GETDATE() THEN ASV.booking_date
ELSE ASV.completion_date
END))) AS ContractualWeekDue
,DATEDIFF(DAY, GETDATE(),
CASE
WHEN ASV.completion_date IS NULL AND ASV.booking_date < GETDATE() THEN ASV.booking_date
ELSE ASV.completion_date
END + 304) AS TimeContractualDueIn
,CASE
WHEN GETDATE() > DATEADD(DAY, 304,
CASE
WHEN ASV.completion_date IS NULL AND ASV.booking_date < GETDATE() THEN ASV.booking_date
ELSE ASV.completion_date
END) THEN 'OVERDUE'
WHEN DATEADD(DAY, 304,
CASE
WHEN ASV.completion_date IS NULL AND ASV.booking_date < GETDATE() THEN ASV.booking_date
ELSE ASV.completion_date
END) IS NULL THEN 'OVERDUE'
WHEN DATEDIFF(DAY, GETDATE(),
CASE
WHEN ASV.completion_date IS NULL AND ASV.booking_date < GETDATE() THEN ASV.booking_date
ELSE ASV.completion_date
END + 304) BETWEEN 0 AND 80 THEN 'DUE'
ELSE 'WITHIN'
END AS ContractualCompliance
,DATEADD(YEAR, 1,
CASE
WHEN ASV.completion_date IS NULL AND ASV.booking_date < GETDATE() THEN ASV.booking_date
ELSE ASV.completion_date
END) AS LegalDueDate
,CONVERT(VARCHAR, DATEPART(MM, DATEADD(YEAR, 1,
CASE
WHEN ASV.completion_date IS NULL AND ASV.booking_date < GETDATE() THEN ASV.booking_date
ELSE ASV.completion_date
END))) + CONVERT(VARCHAR, '/') + CONVERT(VARCHAR, DATEPART(YYYY, DATEADD(YEAR, 1,
CASE
WHEN ASV.completion_date IS NULL AND ASV.booking_date < GETDATE() THEN ASV.booking_date
ELSE ASV.completion_date
END))) AS LegalMonthDue
,CONVERT(VARCHAR, DATEPART(WW, DATEADD(YEAR, 1,
CASE
WHEN ASV.completion_date IS NULL AND ASV.booking_date < GETDATE() THEN ASV.booking_date
ELSE ASV.completion_date
END))) + CONVERT(VARCHAR, '/') + CONVERT(VARCHAR, DATEPART(YYYY, DATEADD(YEAR, 1,
CASE
WHEN ASV.completion_date IS NULL AND ASV.booking_date < GETDATE() THEN ASV.booking_date
ELSE ASV.completion_date
END))) AS LegalWeekDue
,DATEDIFF(DAY, GETDATE(),
CASE
WHEN ASV.completion_date IS NULL AND ASV.booking_date < GETDATE() THEN ASV.booking_date
ELSE ASV.completion_date
END + 365) AS TimeLegallyDueIn
,CASE
WHEN GETDATE() > DATEADD(YEAR, 1,
CASE
WHEN ASV.completion_date IS NULL AND ASV.booking_date < GETDATE() THEN ASV.booking_date
ELSE ASV.completion_date
END) THEN 'OVERDUE'
WHEN DATEADD(YEAR, 1,
CASE
WHEN ASV.completion_date IS NULL AND ASV.booking_date < GETDATE() THEN ASV.booking_date
ELSE ASV.completion_date
END) IS NULL THEN 'OVERDUE'
WHEN DATEDIFF(DAY, GETDATE(),
CASE
WHEN ASV.completion_date IS NULL AND ASV.booking_date < GETDATE() THEN ASV.booking_date
ELSE ASV.completion_date
END + 365) BETWEEN 0 AND 80 THEN 'DUE'
ELSE 'WITHIN'
END AS LegalCompliance
,DATEADD(DAY, 366,
CASE
WHEN ASV.completion_date IS NULL AND ASV.booking_date < GETDATE() THEN ASV.booking_date
ELSE ASV.completion_date
END) AS OutOfCompliance
FROM dbo.property P
INNER JOIN dbo.servicing_jobs ASV ON P.prop_seql = ASV.prop_seql
INNER JOINdbo.contracts C ON P.contract_id = C.id
WHERE P.contract_id IN (74, 75)
AND P.postcode IS NOT NULL
) dtOrg
How can I do this?
April 26, 2010 at 12:54 pm
The ORDER BY clause used with ROW_NUMBER specifies the order within in each PARTITION BY group. You might want to try
SELECT ROW_NUMBER() OVER (PARTITION BY P.prop_seql
ORDER BY ASV.booking_date DESC)
April 27, 2010 at 2:02 am
Ok thanks, I used that in the view, and then created a query from this to then show me the max(rownumber) in the hope it would only show me one record for that property. my problem is still that it shows all properties regardless of the rownumber.
How can I show only one property but the problem is that the latest apptdate may be row1 or 2 how can i make sure that i pick out the latest apptdate for the property?
SELECT MAX(RowNumber) AS Row, PropID, UPRN, Address, Postcode, ApptDate, NA1, NA2, LastServiceDate, ContractualDueDate, ContractualMonthDue, ContractualWeekDue,
TimeContractualDueIn, ContractualCompliance, LegalDueDate, LegalMonthDue, LegalWeekDue, TimeLegallyDueIn, LegalCompliance,
OutOfCompliance
FROM dbo.PlymouthASVReport
GROUP BY RowNumber, PropID, UPRN, Address, Postcode, ApptDate, NA1, NA2, LastServiceDate, ContractualDueDate, ContractualMonthDue, ContractualWeekDue,
TimeContractualDueIn, ContractualCompliance, LegalDueDate, LegalMonthDue, LegalWeekDue, TimeLegallyDueIn, LegalCompliance, OutOfCompliance
ORDER BY PropID
April 27, 2010 at 10:11 am
Please provide DDL for a sample data together with some sample records and your expected results. Based on your verbal description it gets confusing (to me, at least...). Please read and follow the first link in my signature on how to do that.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply