Find PreviousChange of date

• J39L4753

  SSCrazy

  Points: 2803

  More actions

  May 14, 2010 at 8:01 am

  #218911

  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

  PARowNumIDRowNumberPropIDUPRNAppointmentApptDateImportDate

  123671118024323414006805Appt12010-05-13 00:00:00.0002010-05-13 15:39:41.430

  232003118024323414006805Appt12010-05-13 00:00:00.0002010-05-13 15:45:33.267

  340424118024323414006805Appt12010-05-13 00:00:00.0002010-05-13 15:45:58.797

  448667118024323414006805Appt12010-05-13 00:00:00.0002010-05-13 16:07:34.403

  557266118024323414006805Appt12010-05-13 00:00:00.0002010-05-13 16:10:05.280

  665598118024323414006805Appt12010-05-13 00:00:00.0002010-05-13 16:17:09.180

  773930118024323414006805Appt12010-05-13 00:00:00.0002010-05-13 16:18:19.680

  882529118024323414006805Appt12010-05-13 00:00:00.0002010-05-13 16:21:53.123

  990879118024323414006805Appt12010-05-14 00:00:00.0002010-05-14 09:24:43.480

  1099494118024323414006805Appt12010-05-14 00:00:00.0002010-05-14 09:37:46.997

  11107619118024323414006805Appt12010-05-14 00:00:00.0002010-05-14 12:30:56.523

  12116317118024323414006805Appt12010-05-14 00:00:00.0002010-05-14 14:07:27.350

  13124738118024323414006805Appt12010-05-14 00:00:00.0002010-05-14 14:08:35.210

  14132881118024323414006805Appt12010-05-14 00:00:00.0002010-05-14 14:23:44.410

  15141392118024323414006805Appt12010-05-14 00:00:00.0002010-05-14 14:27:03.900

  16149813118024323414006805Appt12010-05-14 00:00:00.0002010-05-14 14:33:09.710

  17158234118024323414006805Appt12010-05-14 00:00:00.0002010-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?

• Bhuvnesh

  SSC Guru

  Points: 59351

  More actions

  May 14, 2010 at 8:11 am

  #1167557

  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

  PARowNumIDRowNumberPropIDUPRNAppointmentApptDateImportDate

  123671118024323414006805Appt12010-05-13 00:00:00.0002010-05-13 15:39:41.430

  232003118024323414006805Appt12010-05-13 00:00:00.0002010-05-13 15:45:33.267

  340424118024323414006805Appt12010-05-13 00:00:00.0002010-05-13 15:45:58.797

  448667118024323414006805Appt12010-05-13 00:00:00.0002010-05-13 16:07:34.403

  557266118024323414006805Appt12010-05-13 00:00:00.0002010-05-13 16:10:05.280

  665598118024323414006805Appt12010-05-13 00:00:00.0002010-05-13 16:17:09.180

  773930118024323414006805Appt12010-05-13 00:00:00.0002010-05-13 16:18:19.680

  882529118024323414006805Appt12010-05-13 00:00:00.0002010-05-13 16:21:53.123

  990879118024323414006805Appt12010-05-14 00:00:00.0002010-05-14 09:24:43.480

  1099494118024323414006805Appt12010-05-14 00:00:00.0002010-05-14 09:37:46.997

  11107619118024323414006805Appt12010-05-14 00:00:00.0002010-05-14 12:30:56.523

  12116317118024323414006805Appt12010-05-14 00:00:00.0002010-05-14 14:07:27.350

  13124738118024323414006805Appt12010-05-14 00:00:00.0002010-05-14 14:08:35.210

  14132881118024323414006805Appt12010-05-14 00:00:00.0002010-05-14 14:23:44.410

  15141392118024323414006805Appt12010-05-14 00:00:00.0002010-05-14 14:27:03.900

  16149813118024323414006805Appt12010-05-14 00:00:00.0002010-05-14 14:33:09.710

  17158234118024323414006805Appt12010-05-14 00:00:00.0002010-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;-)

• J39L4753

  SSCrazy

  Points: 2803

  More actions

  May 14, 2010 at 8:34 am

  #1167580

  Thanks for that but when I put PARowNumber = 1 then it returns invalid column name

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  May 14, 2010 at 4:11 pm

  #1167816

  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!

  _____________________________________________
  "The only true wisdom is in knowing you know nothing"
  "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
  (So many miracle inventions provided by MS to us...)

  How to post your question to get the best and quick help[/url]

• Jeff Moden

  SSC Guru

  Points: 1003894

  More actions

  May 14, 2010 at 9:06 pm

  #1167860

  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

  PARowNumIDRowNumberPropIDUPRNAppointmentApptDateImportDate

  123671118024323414006805Appt12010-05-13 00:00:00.0002010-05-13 15:39:41.430

  232003118024323414006805Appt12010-05-13 00:00:00.0002010-05-13 15:45:33.267

  340424118024323414006805Appt12010-05-13 00:00:00.0002010-05-13 15:45:58.797

  448667118024323414006805Appt12010-05-13 00:00:00.0002010-05-13 16:07:34.403

  557266118024323414006805Appt12010-05-13 00:00:00.0002010-05-13 16:10:05.280

  665598118024323414006805Appt12010-05-13 00:00:00.0002010-05-13 16:17:09.180

  773930118024323414006805Appt12010-05-13 00:00:00.0002010-05-13 16:18:19.680

  882529118024323414006805Appt12010-05-13 00:00:00.0002010-05-13 16:21:53.123

  990879118024323414006805Appt12010-05-14 00:00:00.0002010-05-14 09:24:43.480

  1099494118024323414006805Appt12010-05-14 00:00:00.0002010-05-14 09:37:46.997

  11107619118024323414006805Appt12010-05-14 00:00:00.0002010-05-14 12:30:56.523

  12116317118024323414006805Appt12010-05-14 00:00:00.0002010-05-14 14:07:27.350

  13124738118024323414006805Appt12010-05-14 00:00:00.0002010-05-14 14:08:35.210

  14132881118024323414006805Appt12010-05-14 00:00:00.0002010-05-14 14:23:44.410

  15141392118024323414006805Appt12010-05-14 00:00:00.0002010-05-14 14:27:03.900

  16149813118024323414006805Appt12010-05-14 00:00:00.0002010-05-14 14:33:09.710

  17158234118024323414006805Appt12010-05-14 00:00:00.0002010-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://www.sqlservercentral.com/articles/Best+Practices/61537/

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• WayneS

  SSC Guru

  Points: 95409

  More actions

  May 14, 2010 at 10:04 pm

  #1167864

  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

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• WayneS

  SSC Guru

  Points: 95409

  More actions

  May 14, 2010 at 10:08 pm

  #1167865

  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://www.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

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• J39L4753

  SSCrazy

  Points: 2803

  More actions

  May 15, 2010 at 2:07 am

  #1167884

  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.