Post reply

complex query

  • August 3, 2014 at 2:33 am

    #291673

    hi all

    fot the given data :

    create table logs (inside datetime,outside datetime)

    insert logs (inside,outside)

    select '2014-07-15 00:30:00.000','2014-07-15 00:35:00.000'

    union all

    select '2014-07-15 00:38:00.000','2014-07-15 00:40:00.000'

    i need the follwoing out put :

    2014-07-15 00:00:00.000 2014-07-15 00:30:00.000

    2014-07-15 00:30:00.000 2014-07-15 00:35:00.000

    2014-07-15 00:35:00.000 2014-07-15 00:38:00.000

    2014-07-15 00:38:00.000 2014-07-15 00:40:00.000

    2014-07-15 00:40:00.000 2014-07-16 00:00:00.000

    thank you vary much

    sharon

    תודה שרון

  • August 3, 2014 at 3:18 am

    #1734652

    Here's one way to do it:

    with cte as

    (

    -- UNPIVOT the two columns

    SELECT inside

    FROM logs

    UNION

    SELECT outside

    FROM logs

    UNION

    -- add the start time

    SELECT DATEADD(dd,DATEDIFF(dd,0,MIN(inside)),0)

    FROM logs

    UNION

    -- add the end time

    SELECT DATEADD(dd,DATEDIFF(dd,0,MAX(inside)),1)

    FROM logs

    ), cte2 as -- add a row number

    (

    SELECT *, ROW_NUMBER() OVER(ORDER BY inside) as Sort

    FROM cte

    )

    -- final output using a "self reference" to the previous row

    SELECT cte2a.inside AS Col1, cte2b.inside AS Col2

    FROM cte2 AS cte2a INNER JOIN cte2 AS cte2b ON cte2a.Sort=cte2b.Sort-1


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • August 3, 2014 at 3:43 am

    #1734655

    perfect

    thank you 🙂

    sharon

Viewing 3 posts - 1 through 2 (of 2 total)

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