Create multiple rows from single row sql

Question

i have table structure like

ATM         Ticket Open Time    Ticket Closed Time

M30G324202  17-02-2013 06:15    19-02-2013 20:54
M30G324202  28-02-2013 21:00    01-03-2013 11:18
M30G324203  27-02-2013 19:10    28-02-2013 07:14
M30G324203  28-02-2013 07:15    28-02-2013 11:18

If Ticket Open Time OR Ticket Closed Time is lies between '20:00:00' and '06:00:00' i.e. 8 PM & 6 AM then new row should get created which does not have that time frame

e.g. for first row in above table

ATM         Ticket Open Time    Ticket Closed Time

M30G324202  17-02-2013 06:15    17-02-2013 20:00
M30G324202  18-02-2013 06:00    18-02-2013 20:00
M30G324202  19-02-2013 06:00    19-02-2013 20:00

//Above was for Only 1st Row

//Second Row Change AS Follows

M30G324202  01-03-2013 06:00    01-03-2013 11:18 
   (Time From 28-02-2013 21:00 Will get neglected till next day morning 6 AM 
    as it is after 8 PM )

//Third Row Change AS Follows

M30G324203  27-02-2013 19:10    27-02-2013 20:00
M30G324203  28-02-2013 06:00    28-02-2013 07:14

//Fourth Row Change AS Follows

M30G324203  28-02-2013 07:15    28-02-2013 11:18 (No Change as it is)

I have written 20:00 instead of 20:54 because 54 min. are after 8 PM for 19th feb.

So, just to be clear, you're after selecting an extra row for each day that falls within those bounds for every row in the data? — KaraokeStu
– KaraokeStu, Commented May 15, 2013 at 11:39
Please add the expected result for the second entry M30G324202 also. — TechDo
– TechDo, Commented May 15, 2013 at 12:40

Ann L. · Accepted Answer · 2013-05-15 12:48:12Z

You can do this using a recursive CTE. The following code shows how. For the test data in CTE A, use something like

SELECT ATM, [Ticket Open Time], [Ticket Close Time] FROM Table1

The test data is here to show that all cases have been taken care of. If you don't want case ATM = W included, or you want to adjust the start date, you can modify the SQL accordingly.

Also, I used an old-fashioned technique to get the date portion of the datetime. Again, adjust according to the version of SQL Server you're on.

WITH A
AS  (

    SELECT 'X' as ATM
        ,   convert(datetime, '2/17/2013 6:15') as [Ticket Open Time]
        ,   convert(datetime, '2/19/2013 20:54') as [Ticket Close Time]
    UNION ALL 
    SELECT  'Y'
        ,   convert(datetime, '2/24/2013 7:32')
        ,   convert(datetime, '2/25/2013 14:26')
    UNION ALL
    SELECT  'Z'
        ,   convert(datetime, '2/20/2013 9:00')
        ,   convert(datetime, '2/20/2013 13:43')    
    UNION ALL
    SELECT  'W'
        ,   convert(datetime, '3/1/2013 3:34')
        ,   convert(datetime, '3/1/2013 6:45')  
)
,   B
AS  (

    SELECT  ATM
        ,   [Ticket Open Time]
        ,   [Original Ticket Close Time] = A.[Ticket Close Time]
        ,   [Ticket Close Time] = CASE WHEN  DateAdd(hh, 20, convert(datetime, convert(varchar(10), [Ticket Open Time], 101))) > A.[Ticket Close Time]
                 THEN  [Ticket Close Time]
                 ELSE  DateAdd(hh, 20, convert(datetime, convert(varchar(10), [Ticket Open Time], 101)))
            END
    FROM    A
    UNION ALL
    SELECT  ATM
        ,   [Ticket Open Time] = DateAdd(hh, 10, B1.[Ticket Close Time])
        ,   [Original Ticket Close Time] = b1.[Original Ticket Close Time]
        ,   [Ticket Close Time] = CASE
                WHEN DateAdd(hh, 24, b1.[Ticket Close Time]) > b1.[Original Ticket Close Time]
                AND  b1.[Original Ticket Close Time] <= DateAdd(hh,24, b1.[Ticket Close Time])
                THEN b1.[Original Ticket Close Time]
                ELSE DateAdd(hh, 24, b1.[Ticket Close Time])
            END
    FROM    B b1
    WHERE   [Ticket Close Time] < b1.[Original Ticket Close Time]

)
,   C
as  (
    select *
    from    B
    where   [Ticket Open Time] < [Ticket Close Time]
)   
      -- Your actual output
select    ATM, 
            [Ticket Open Time],
        [Ticket Close Time],
 from     C  
 order by  ATM,
         [Ticket Open Time]

+1: This is pretty good, however, recursive CTE's are not very fast. It would probably be better to join against a Numbers or a Calendar table/CTE instead.
@Ann L. Great effort..but for W ATM Ticket Open Time = 2013-03-01 06:00:00.000 & Ticket Closed Time = 2013-03-01 06:45:00.000 ....as 8 PM to 6 AM will get neglected.

TechDo · Accepted Answer · 2013-05-16 04:33:56Z

A great question! Please check my try:

declare @tbl as table (ATM nvarchar(20), TicketOpenTime datetime, TicketClosedTime datetime)
insert into @tbl values
('M30G324202', '02-17-2013 06:15', '02-19-2013 20:54'),
('M30G324202', '02-28-2013 21:00', '03-01-2013 11:18'),
('M30G324203', '02-27-2013 19:10', '02-28-2013 07:14'),
('M30G324203', '02-28-2013 07:15', '02-28-2013 11:18')

declare @min datetime, @max datetime
select @min = MIN(TicketOpenTime), @max = max(TicketClosedTime) from @tbl

;with T as(
    select CONVERT(datetime, convert(numeric(20), @min, 101)) dt
    union all
    select dt+1 from T where dt<@max
) 
select 
    a.ATM, 
    case when a.TicketOpenTime>dt1 then a.TicketOpenTime else dt1 end TicketOpenTime,
    case when a.TicketClosedTime>dt2 then dt2 else a.TicketClosedTime end TicketClosedTime
From @tbl a
cross apply(
    select 
        dt, 
        DATEADD(minute, 360, dt) dt1, 
        DATEADD(minute, 1200, dt) dt2 from T b 
    where 
        dt between CAST(a.TicketOpenTime as DATE) and cast(a.TicketClosedTime as DATE)
)x
where a.TicketOpenTime<=x.dt2
order by a.ATM, a. TicketOpenTime

Collectives™ on Stack Overflow

Create multiple rows from single row sql

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related