May 14, 2010 at 8:01 am
I have a simple table of which is an extract below, I have a query that will order each job by row number , what I want to do is have a query that will show me the last date which was changed.
This table is linked to a report that can be run whenever the users refresh it, so the date same date can appear any number fo times. But I need to find the last date that has changed
This is the results for a query
PARowNum ID RowNumber PropID UPRN Appointment ApptDate ImportDate
1 23671 1 180243 23414006805 Appt1 2010-05-13 00:00:00.000 2010-05-13 15:39:41.430
2 32003 1 180243 23414006805 Appt1 2010-05-13 00:00:00.000 2010-05-13 15:45:33.267
3 40424 1 180243 23414006805 Appt1 2010-05-13 00:00:00.000 2010-05-13 15:45:58.797
4 48667 1 180243 23414006805 Appt1 2010-05-13 00:00:00.000 2010-05-13 16:07:34.403
5 57266 1 180243 23414006805 Appt1 2010-05-13 00:00:00.000 2010-05-13 16:10:05.280
6 65598 1 180243 23414006805 Appt1 2010-05-13 00:00:00.000 2010-05-13 16:17:09.180
7 73930 1 180243 23414006805 Appt1 2010-05-13 00:00:00.000 2010-05-13 16:18:19.680
8 82529 1 180243 23414006805 Appt1 2010-05-13 00:00:00.000 2010-05-13 16:21:53.123
9 90879 1 180243 23414006805 Appt1 2010-05-14 00:00:00.000 2010-05-14 09:24:43.480
10 99494 1 180243 23414006805 Appt1 2010-05-14 00:00:00.000 2010-05-14 09:37:46.997
11 107619 1 180243 23414006805 Appt1 2010-05-14 00:00:00.000 2010-05-14 12:30:56.523
12 116317 1 180243 23414006805 Appt1 2010-05-14 00:00:00.000 2010-05-14 14:07:27.350
13 124738 1 180243 23414006805 Appt1 2010-05-14 00:00:00.000 2010-05-14 14:08:35.210
14 132881 1 180243 23414006805 Appt1 2010-05-14 00:00:00.000 2010-05-14 14:23:44.410
15 141392 1 180243 23414006805 Appt1 2010-05-14 00:00:00.000 2010-05-14 14:27:03.900
16 149813 1 180243 23414006805 Appt1 2010-05-14 00:00:00.000 2010-05-14 14:33:09.710
17 158234 1 180243 23414006805 Appt1 2010-05-14 00:00:00.000 2010-05-14 14:38:08.980
My code
SELECT *
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY PropID
ORDER BY LatestApptDate) AS PARowNumber
,PrevApptID
,RowNumber
,PropID
,UPRN
,LatestAppt
,LatestApptDate
,ImportDate
FROM dbo.PreviousASVAppts
WHERE UPRN = '23414006805'
) dtOrg
How can I do this?
May 14, 2010 at 8:11 am
jez.lisle (5/14/2010)
I have a simple table of which is an extract below, I have a query that will order each job by row number , what I want to do is have a query that will show me the last date which was changed.This table is linked to a report that can be run whenever the users refresh it, so the date same date can appear any number fo times. But I need to find the last date that has changed
This is the results for a query
PARowNum ID RowNumber PropID UPRN Appointment ApptDate ImportDate
1 23671 1 180243 23414006805 Appt1 2010-05-13 00:00:00.000 2010-05-13 15:39:41.430
2 32003 1 180243 23414006805 Appt1 2010-05-13 00:00:00.000 2010-05-13 15:45:33.267
3 40424 1 180243 23414006805 Appt1 2010-05-13 00:00:00.000 2010-05-13 15:45:58.797
4 48667 1 180243 23414006805 Appt1 2010-05-13 00:00:00.000 2010-05-13 16:07:34.403
5 57266 1 180243 23414006805 Appt1 2010-05-13 00:00:00.000 2010-05-13 16:10:05.280
6 65598 1 180243 23414006805 Appt1 2010-05-13 00:00:00.000 2010-05-13 16:17:09.180
7 73930 1 180243 23414006805 Appt1 2010-05-13 00:00:00.000 2010-05-13 16:18:19.680
8 82529 1 180243 23414006805 Appt1 2010-05-13 00:00:00.000 2010-05-13 16:21:53.123
9 90879 1 180243 23414006805 Appt1 2010-05-14 00:00:00.000 2010-05-14 09:24:43.480
10 99494 1 180243 23414006805 Appt1 2010-05-14 00:00:00.000 2010-05-14 09:37:46.997
11 107619 1 180243 23414006805 Appt1 2010-05-14 00:00:00.000 2010-05-14 12:30:56.523
12 116317 1 180243 23414006805 Appt1 2010-05-14 00:00:00.000 2010-05-14 14:07:27.350
13 124738 1 180243 23414006805 Appt1 2010-05-14 00:00:00.000 2010-05-14 14:08:35.210
14 132881 1 180243 23414006805 Appt1 2010-05-14 00:00:00.000 2010-05-14 14:23:44.410
15 141392 1 180243 23414006805 Appt1 2010-05-14 00:00:00.000 2010-05-14 14:27:03.900
16 149813 1 180243 23414006805 Appt1 2010-05-14 00:00:00.000 2010-05-14 14:33:09.710
17 158234 1 180243 23414006805 Appt1 2010-05-14 00:00:00.000 2010-05-14 14:38:08.980
My code
SELECT *
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY PropID
ORDER BY LatestApptDate) AS PARowNumber
,PrevApptID
,RowNumber
,PropID
,UPRN
,LatestAppt
,LatestApptDate
,ImportDate
FROM dbo.PreviousASVAppts
WHERE UPRN = '23414006805'
) dtOrg
How can I do this?
try this 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
WHERE UPRN = '23414006805' AND PARowNumber = 1
) dtOrg
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 14, 2010 at 8:34 am
Thanks for that but when I put PARowNumber = 1 then it returns invalid column name
May 14, 2010 at 4:11 pm
Try this:
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
WHERE UPRN = '23414006805'
) dtOrg
WHERE AND PARowNumber = 1
I'm not sure about the result you want (no clear requirements provided), but at least you won't get the error in this case...
For getting better help, always provide object creation script, data insert script and required results!
May 14, 2010 at 9:06 pm
jez.lisle (5/14/2010)
I have a simple table of which is an extract below, I have a query that will order each job by row number , what I want to do is have a query that will show me the last date which was changed.This table is linked to a report that can be run whenever the users refresh it, so the date same date can appear any number fo times. But I need to find the last date that has changed
This is the results for a query
PARowNum ID RowNumber PropID UPRN Appointment ApptDate ImportDate
1 23671 1 180243 23414006805 Appt1 2010-05-13 00:00:00.000 2010-05-13 15:39:41.430
2 32003 1 180243 23414006805 Appt1 2010-05-13 00:00:00.000 2010-05-13 15:45:33.267
3 40424 1 180243 23414006805 Appt1 2010-05-13 00:00:00.000 2010-05-13 15:45:58.797
4 48667 1 180243 23414006805 Appt1 2010-05-13 00:00:00.000 2010-05-13 16:07:34.403
5 57266 1 180243 23414006805 Appt1 2010-05-13 00:00:00.000 2010-05-13 16:10:05.280
6 65598 1 180243 23414006805 Appt1 2010-05-13 00:00:00.000 2010-05-13 16:17:09.180
7 73930 1 180243 23414006805 Appt1 2010-05-13 00:00:00.000 2010-05-13 16:18:19.680
8 82529 1 180243 23414006805 Appt1 2010-05-13 00:00:00.000 2010-05-13 16:21:53.123
9 90879 1 180243 23414006805 Appt1 2010-05-14 00:00:00.000 2010-05-14 09:24:43.480
10 99494 1 180243 23414006805 Appt1 2010-05-14 00:00:00.000 2010-05-14 09:37:46.997
11 107619 1 180243 23414006805 Appt1 2010-05-14 00:00:00.000 2010-05-14 12:30:56.523
12 116317 1 180243 23414006805 Appt1 2010-05-14 00:00:00.000 2010-05-14 14:07:27.350
13 124738 1 180243 23414006805 Appt1 2010-05-14 00:00:00.000 2010-05-14 14:08:35.210
14 132881 1 180243 23414006805 Appt1 2010-05-14 00:00:00.000 2010-05-14 14:23:44.410
15 141392 1 180243 23414006805 Appt1 2010-05-14 00:00:00.000 2010-05-14 14:27:03.900
16 149813 1 180243 23414006805 Appt1 2010-05-14 00:00:00.000 2010-05-14 14:33:09.710
17 158234 1 180243 23414006805 Appt1 2010-05-14 00:00:00.000 2010-05-14 14:38:08.980
My code
SELECT *
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY PropID
ORDER BY LatestApptDate) AS PARowNumber
,PrevApptID
,RowNumber
,PropID
,UPRN
,LatestAppt
,LatestApptDate
,ImportDate
FROM dbo.PreviousASVAppts
WHERE UPRN = '23414006805'
) dtOrg
How can I do this?
You've already gotten at least one "wrong" answer for your question. Do you know why? Read the article at the following URL to find out...
http://qa.sqlservercentral.com/articles/Best+Practices/61537/
--Jeff Moden
May 14, 2010 at 10:04 pm
elutin (5/14/2010)
Try this:
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
WHERE UPRN = '23414006805'
) dtOrg
WHERE AND PARowNumber = 1
I'm not sure about the result you want (no clear requirements provided), but at least you won't get the error in this case...
For getting better help, always provide object creation script, data insert script and required results!
Nope, you WILL get an error!!!
Try this (haven't looked at the problem to see if it solves the issue, at least it's capable of running...)
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
WHERE UPRN = '23414006805'
) dtOrg
WHERE /*AND*/ PARowNumber = 1
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 14, 2010 at 10:08 pm
Jeff Moden (5/14/2010)
You've already gotten at least one "wrong" answer for your question. Do you know why? Read the article at the following URL to find out...http://qa.sqlservercentral.com/articles/Best+Practices/61537/
Since you're up to two wrong answers, you might really want to check out that article. If you do what it suggests, you'll be helping us to help you!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 15, 2010 at 2:07 am
Thanks guys, I've had a read of the etiquette of posting and will repost and see where I get.
Thanks for help so far.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply