CODEWITHSUNDEEP
mysqlsql
mohan111
mohan111

Reputation: 8865

How to get open and close time slots in MYSQL

I have sample data like this :

    ID Val   Name        Dt                 Status
    1, 145, 'Test1', '2020-01-28 02:18:00', 'open'
    2, 145, 'Test1', '2020-01-28 04:10:00', 'open'
    3, 145, 'Test1', '2020-01-28 05:50:00', 'open'
    4, 145, 'Test1', '2020-01-28 05:56:00', 'close'
    5, 145, 'Test2', '2020-01-28 07:36:00', 'open'
    6, 145, 'Test2', '2020-01-28 07:42:00', 'open'
    7, 145, 'Test2', '2020-01-28 07:44:00', 'open'
    8, 145, 'Test2', '2020-01-28 07:47:00', 'close'

How can i get the output like this :

ID Val   Name        o_Dt                 o_gate      c_Dt             c_gate
1, 145, 'Test1', '2020-01-28 02:18:00', 'open'        NULL               NULL
2, 145, 'Test1', '2020-01-28 04:10:00', 'open'        NULL               NULL
3, 145, 'Test1', '2020-01-28 05:50:00', 'open'  '2020-01-28 05:56:00', 'close'
4, 145, 'Test2', '2020-01-28 07:36:00', 'open'        NULL               NULL
5, 145, 'Test2', '2020-01-28 07:42:00', 'open'        NULL               NULL
6, 145, 'Test2', '2020-01-28 07:44:00', 'open'  '2020-01-28 07:47:00', 'close'

I Have tried with different scenarios but not moving forward Using

COALESCE(LAG(Status) OVER (ORDER BY dt)

ROW_NUMBER()OVER(PARTITION BY vehicle_id,status )

Not getting exact result . Can anyone suggest on this .

Previously I have asked question for same data but haven't got exact answer .

How to transpose rows to columns based on time intervals in MYSQL

Upvotes: 0

Views: 133

Answers (2)

Sreenu131
Sreenu131

Reputation: 2516

Try this Below Code

;WITH CTE(ID ,Val,Name,Dt,[Status])
AS
(
          SELECT 1, 145, 'Test1', '2020-01-28 02:18:00', 'open'
UNION ALL SELECT 2, 145, 'Test1', '2020-01-28 04:10:00', 'open'
UNION ALL SELECT 3, 145, 'Test1', '2020-01-28 05:50:00', 'open'
UNION ALL SELECT 4, 145, 'Test1', '2020-01-28 05:56:00', 'close'
UNION ALL SELECT 5, 145, 'Test2', '2020-01-28 07:36:00', 'open'
UNION ALL SELECT 6, 145, 'Test2', '2020-01-28 07:42:00', 'open'
UNION ALL SELECT 7, 145, 'Test2', '2020-01-28 07:44:00', 'open'
UNION ALL SELECT 8, 145, 'Test2', '2020-01-28 07:47:00', 'close'
UNION ALL SELECT 9, 145, 'Test3', '2020-01-28 11:42:00', 'open'
UNION ALL SELECT 10, 145, 'Test3', '2020-01-28 14:44:00', 'open'
UNION ALL SELECT 11, 145, 'Test3', '2020-01-28 15:56:00', 'close'
)
SELECT ROW_NUMBER()OVER(ORDER BY (SELECT NULL)) AS ID,*
FROM
(
SELECT Val,Name,[Ogate],[O_Dt],LEAD([C_Dt])OVER(PARTITION BY Name ORDER BY ID)AS [C_Dt] ,LEAD([C_gate])OVER(PARTITION BY Name  ORDER BY ID) AS [C_gate]
FROM CTE c
CROSS APPLY (VALUES (   CASE WHEN [Status] = 'open' THEN [Status] END,  
                        CASE WHEN [Status] = 'open' THEN [Dt] END,
                        CASE WHEN [Status] = 'close' THEN [Status] END,
                        CASE WHEN [Status] = 'close' THEN [Dt] END
                    )) AS Dt ([Ogate],[O_Dt],[C_gate],[C_Dt])
)Dt 
WHERE COALESCE (Ogate,O_Dt,C_Dt,C_gate,'1') <> '1'

Result

ID  Val Name    Ogate       O_Dt                    C_Dt                C_gate
---------------------------------------------------------------------------
1   145 Test1   open    2020-01-28 02:18:00     NULL                    NULL
2   145 Test1   open    2020-01-28 04:10:00     NULL                    NULL
3   145 Test1   open    2020-01-28 05:50:00     2020-01-28 05:56:00     close
4   145 Test2   open    2020-01-28 07:36:00     NULL                    NULL
5   145 Test2   open    2020-01-28 07:42:00     NULL                    NULL
6   145 Test2   open    2020-01-28 07:44:00     2020-01-28 07:47:00     close
7   145 Test3   open    2020-01-28 11:42:00     NULL                    NULL
8   145 Test3   open    2020-01-28 14:44:00     2020-01-28 15:56:00     close

Upvotes: 0

Akina
Akina

Reputation: 42632

SELECT t1.ID, t1.Val, t1.Name, t1.Dt o_Dt, t1.Status o_gate, t2.Dt c_Dt, t2.Status c_gate
FROM test t1
LEFT JOIN test t2 ON t1.Dt < t2.Dt
                 AND t1.Name = t2.Name
                 AND t2.Status = 'close'
                 AND NOT EXISTS ( SELECT NULL
                                  FROM test t3
                                  WHERE t1.Dt < t3.Dt
                                    AND t3.Dt < t2.Dt
                                    AND t1.Name = t3.Name)
WHERE t1.Status = 'open'

fiddle

Upvotes: 1

Related Questions