Show Previous Row - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Show Previous Row

Author

Topic

JezLisle
Posting Yak Master

132 Posts

Posted - 2010-05-17 : 04:14:17

I have created this query below as a view, to give me the row number for each property and show me all the appointments.

I want to be able to find the row that is second to last, with the latest appointment the highest number, so if last rownumber is 3 then I want to show row 2


SELECT     *
FROM         (SELECT     ROW_NUMBER() OVER (PARTITION BY PropID
                       ORDER BY LatestApptDate) AS PARowNumber, PrevApptID, RowNumber, PropID, UPRN, LatestAppt, LatestApptDate, ImportDate
FROM         dbo.PreviousASVAppts) dtOrg

Using the view above I create another query to run my report, but it returns with no rows at all, how can I get around this?


SELECT PR.PropID, PR.UPRN, PR.Address, PR.Postcode, PR.LatestCP12Date, PA.LatestApptDate AS PrevApptDate, PR.ApptDate AS LatestAppt, 
PR.NA1, PR.NA2, COALESCE (AA.Appt5, AA.Appt4, AA.Appt3, AA.Appt2, AA.Appt1) AS LatestAppt, AA.ASVStatus, PR.ContractualDueDate, 
PR.ContractualMonthDue, PR.ContractualWeekDue, PR.TimeContractualDueIn, 
CASE 
	WHEN AA.ASVStatus = 'Complete' AND DATEDIFF(DAY, PR.ContractualDueDate, PR.CompletedDate) < 60 THEN 'WITHIN'
	ELSE PR.ContractualCompliance 
END AS ContractualCompliance,
PR.LegalDueDate, PR.LegalMonthDue, 
PR.LegalWeekDue, PR.TimeLegallyDueIn, 
CASE 
	WHEN AA.ASVStatus = 'Complete' AND DATEDIFF(DAY, PR.LegalDueDate, PR.CompletedDate) < 60 THEN 'WITHIN'
	ELSE PR.LegalCompliance 
END AS LegalCompliance,
PR.OutOfCompliance,
CASE
	WHEN AA.ASVStatus = 'Pending' THEN 'Help'
	WHEN AA.ASVStatus = 'No Access' THEN 'OK'
	WHEN AA.ASVStatus = 'Complete' AND PR.ApptDate <> PR.LatestCP12Date THEN 'Help'
	ELSE 'LookAt'
END AS ProcessMap
FROM dbo.PlymouthASVReport PR 
INNER JOIN dbo.vwASVActivity AA 
ON PR.UPRN = AA.UPRN 
INNER JOIN dbo.vwPreviousASVAppts PA
ON PR.PropID = PA.PropID
GROUP BY PR.PropID, PR.UPRN, PR.Address, PR.Postcode, PR.ApptDate, PR.LatestCP12Date, PR.NA1, PR.NA2, 
PR.ContractualDueDate, PR.ContractualMonthDue, PR.ContractualWeekDue, PR.TimeContractualDueIn, PR.ContractualCompliance, PR.LegalDueDate, 
PR.LegalMonthDue, PR.LegalWeekDue, PR.TimeLegallyDueIn, PR.LegalCompliance, PR.OutOfCompliance, AA.ASVStatus, AA.ASVSubStatus, AA.FirstTimeFix, 
PA.LatestApptDate, AA.RowNumber, COALESCE (AA.Appt5Date, AA.Appt4Date, AA.Appt3Date, AA.Appt2Date, AA.Appt1Date), 
COALESCE (AA.Appt5, AA.Appt4, AA.Appt3, AA.Appt2, AA.Appt1), PR.RowNumber, PR.CompletedDate, PARowNumber
HAVING PR.RowNumber = 1
AND AA.RowNumber = 1
AND PA.PARowNumber = MAX(PA.PARowNumber)-1
AND PA.LatestApptDate <= COALESCE (AA.Appt5Date, AA.Appt4Date, AA.Appt3Date, AA.Appt2Date, AA.Appt1Date)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-17 : 11:59:00

i think it should be


SELECT     *
FROM         (SELECT     ROW_NUMBER() OVER (PARTITION BY PropID
                       ORDER BY LatestApptDate DESC) AS PARowNumber, PrevApptID, RowNumber, PropID, UPRN, LatestAppt, LatestApptDate, ImportDate
FROM         dbo.PreviousASVAppts) dtOrg

and then look for PARowNumber = 2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources