Query help on previous record

  • Hello experts,

    -----------------------------------------------------------

    I have below data

    ID start enddate p1 p2 Update date

    31441872015-07-01 00:00:00.0002015-12-31 00:00:00.00018005test1 2015-09-18 12:28:05.197

    30965052014-12-06 00:00:00.0002015-05-20 00:00:00.00018005test1 2015-08-31 12:24:59.353

    30071232014-12-06 00:00:00.0002015-06-30 00:00:00.00018005test1 2015-07-14 12:56:57.683

    When the user selects a date range from '07/01/2015' to '07/20/2015' for the update date. I need result like below (both matched record and data from previous record)

    30965052014-12-06 00:00:00.0002015-05-20 00:00:00.00018005test1 2015-08-31 12:24:59.353

    30071232014-12-06 00:00:00.0002015-06-30 00:00:00.00018005test1 2015-07-14 12:56:57.683

    ----------------------------------------------------

    create table #test (id integer,start datetime,enddate datetime, p1 integer,p2 varchar(20), updatedate datetime)

    insert into #test

    values (3144187,'2015-07-01 00:00:00.000','2015-12-31 00:00:00.000',18005,'test1','2015-09-18 12:28:05.197')

    insert into #test

    values (3096505,'2014-12-06 00:00:00.000','2015-05-20 00:00:00.000',18005,'test1','2015-08-31 12:24:59.353')

    insert into #test

    values (3007123,'2014-12-06 00:00:00.000','2015-06-30 00:00:00.000',18005,'test1','2015-07-14 12:56:57.683')

  • If you're using 2012, use LAG to get the previous record, then you can compare/do math on the two in the same row.

  • I use 2014. I thought about that but it gives it on the same row. But, I need the data it in separate rows.

  • Could you please post your expected output?

  • You could use ROW_NUMBER() and maybe select the columns you need and filter by ROW_NUMBER() to put them into separate records.

    Please show your desired output. (My psychic skills are failing)

  • I had posted in my first post, but here you go

    expected output result (even if the date range on update date is 07/01-07/30)

    ID start enddate p1 p2 Update date

    30965052014-12-06 00:00:00.0002015-05-20 00:00:00.00018005test1 2015-08-31 12:24:59.353

    30071232014-12-06 00:00:00.0002015-06-30 00:00:00.00018005test1 2015-07-14 12:56:57.683

  • Can't really drop that into SQL Server and play with it. Help us help you. Read this[/url].

  • I had posted everything in the original post, but here it is --

    create table #test (id integer,start datetime,enddate datetime, p1 integer,p2 varchar(20), updatedate datetime)

    insert into #test

    values (3144187,'2015-07-01 00:00:00.000','2015-12-31 00:00:00.000',18005,'test1','2015-09-18 12:28:05.197')

    insert into #test

    values (3096505,'2014-12-06 00:00:00.000','2015-05-20 00:00:00.000',18005,'test1','2015-08-31 12:24:59.353')

    insert into #test

    values (3007123,'2014-12-06 00:00:00.000','2015-06-30 00:00:00.000',18005,'test1','2015-07-14 12:56:57.683')

  • What's previous?

    The order of rows has no meaning in SQL Server. The way you insert them doesn't matter.

    Right now your second row has a larger id and a later last updatedate, than the row you search for. I'm assuming you are searching on updatedate, but you haven't made this clear. When the user searches, what fields do you match on? How do you think rows need to be ordered?

  • I am ordering by updatedate desc and then I am applying the filter.

  • For the same [start]?

  • misstryguy (11/23/2015)


    I am ordering by updatedate desc and then I am applying the filter.

    If i've got right

    create table #test (id integer

    ,start datetime

    ,enddate datetime

    ,p1 integer

    ,p2 varchar(20)

    ,updatedate datetime)

    insert into #test

    values (3144187, '2015-07-01 00:00:00.000', '2015-12-31 00:00:00.000', 18005, 'test1','2015-09-18 12:28:05.197')

    insert into #test

    values (3096505, '2014-12-06 00:00:00.000', '2015-05-20 00:00:00.000', 18005, 'test1','2015-08-31 12:24:59.353')

    insert into #test

    values (3007123, '2014-12-06 00:00:00.000', '2015-06-30 00:00:00.000', 18005, 'test1','2015-07-14 12:56:57.683')

    ;

    with t1 as (

    select *, rn = row_number() over (order by updatedate desc)

    from #test

    ), t2 as (

    select *

    from t1

    where updatedate between '2015-07-01' and '2015-07-30'

    )

    select *

    from t2

    union

    select t1.*

    from t1

    join t2 on t2.rn=t1.rn+1;

  • UNION ALL should perfom better

    ...

    select *

    from t2

    union all

    select t1.*

    from t1

    join t2 on t1.updatedate > '2015-07-30' and t2.rn=t1.rn+1;

  • I was using window functions, and this appeared to work, but it's a small sample size, so perhaps there's a flaw here:

    SELECT t.*

    FROM ( SELECT top 10

    id

    , start

    , 'prev id' = LEAD(id) OVER (PARTITION BY p1 ORDER BY updatedate)

    , 'prev start' = LEAD(enddate) OVER (PARTITION BY p1 ORDER BY updatedate)

    , updatedate

    FROM #test AS t

    ORDER BY updatedate desc) a

    INNER JOIN #test t ON t.id IN (a.id, a.[prev id])

    WHERE a.updatedate > '20150701'

    AND a.updatedate < '20150720'

    Some extra columns in there, just to show what's happening.

Viewing 14 posts - 1 through 13 (of 13 total)

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