Post reply

Find PreviousChange of date

  • 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

    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

    #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

    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

    #1167580

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

  • 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]

  • 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

    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

    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)
    Intro to Tally Tables and Functions

  • 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

  • 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://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

    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

  • 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.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply