SQL Server Query to split row into multiple row according to date and time

p3rjfoxz 于 2023-03-28 发布在其他

关注(0)|答案(2)|浏览(143)

I try to create one query to split the start and end date by day if the range is over 1 day.
My query will return multiple row and each row have their own start date time and end date time

Example:

Scenario 1 - Row 1 raw data as below :

Start Date Time           END Date time
2022-08-16 16:19:05.550   2022-08-16 16:19:24.093

Expected output is only 1 row as below:

Start Date time           End Date Time
2022-08-16 16:19:05.550   2022-08-16 16:19:24.093

My Query below able to achieve this Scenario 1.

Scenario 2- Row 2 raw data as below :

Start Date Time          END Date time
2022-08-17 14:22:56.277  2022-08-19 15:02:41.523

Expected Output will have 3 rows as below:

Start Date time 2022-08-**17** 14:22:56.277, End Date Time 2022-08-**17 23:59:59.000**   
Start Date time 2022-08-**17** 00:00:00.000, End Date Time 2022-08-**18 23:59:59.000**   
Start Date time >2022-08-**18** 00:00:00.000, End Date Time 2022-08-**19 15:02:41.523**

My Query below NOT able to achieve this Scenario.

WITH date_range_cte AS (
SELECT 
    CLOCKING_ID, 
    CAST(START_DATE_TIME AS datetime) AS START_DATE_TIME,
    CAST(END_DATE_TIME AS datetime) AS END_DATE_TIME,
    CAST(START_DATE_TIME AS date) AS START_DATE,
    CAST(END_DATE_TIME AS date) AS END_DATE
FROM [time_tbl] 
WHERE ID = 18
    AND END_DATE_TIME IS NOT NULL 
    AND START_DATE_TIME IS NOT NULL
    UNION ALL
SELECT 
    CLOCKING_ID, 
    DATEADD(day, 1, START_DATE_TIME) AS START_DATE_TIME,
    END_DATE_TIME,
    DATEADD(day, 1, START_DATE) AS START_DATE,
    END_DATE AS END_DATE
FROM date_range_cte
WHERE DATEADD(day, 1, START_DATE) <= END_DATE
) SELECT 
    CLOCKING_ID,
    CASE 
        WHEN START_DATE = CAST(START_DATE_TIME AS date) AND END_DATE = CAST(END_DATE_TIME AS date) THEN START_DATE_TIME 
        WHEN START_DATE = CAST(START_DATE_TIME AS date) THEN START_DATE_TIME
        ELSE CAST(START_DATE AS datetime) + CAST('00:00:00.000' AS datetime)
    END AS START_DATE_TIME,
    CASE 
        WHEN START_DATE = CAST(START_DATE_TIME AS date) AND END_DATE = CAST(END_DATE_TIME AS date) THEN END_DATE_TIME
        WHEN END_DATE = CAST(END_DATE_TIME AS date) THEN END_DATE_TIME
        ELSE CAST(END_DATE AS datetime) + CAST('23:59:59.000' AS datetime)
    END AS END_DATE_TIME
FROM date_range_cte
ORDER BY START_DATE_TIME
OPTION (MAXRECURSION 0);

Above is the code that I tried. But the result is not as expected
Result as below:

Suppose for ID 112, start date is correct, but time is incorrect, end date time both incorrect.
ID 112 raw data is START DATE TIME: 2022-08-17 14:22:56.277 , END DATE TIME: 2022-08-22 15:02:41.523

sql-server

来源：https://stackoverflow.com/questions/75810059/query-to-split-row-into-multiple-row-according-to-date-and-time

2条答案

按热度按时间

az31mfrm1#

With your example data as input:
| Id | StartDT | EndDT |
| ------------ | ------------ | ------------ |
| 99 | 2022-08-16 16:19:05.550 | 2022-08-16 16:19:24.093 |
| 112 | 2022-08-17 14:22:56.277 | 2022-08-19 15:02:41.523 |

Query:

SELECT  T.Id,
        CASE
            WHEN numbers.[Value] = 0
            THEN T.StartDT
            ELSE CAST(DATEADD(DAY, numbers.[Value], T.StartDT) AS DATE)
        END AS NewStartDT,
        CASE
            WHEN numbers.[Value] = DATEDIFF(DAY, T.StartDT, T.EndDT)
            THEN T.EndDT
            ELSE DATEADD(SECOND, -1, CAST(CAST(DATEADD(DAY, numbers.[Value] + 1, T.StartDT) AS DATE) AS DATETIME))
        END AS NewEndDT
FROM    time_tbl AS T
JOIN    GENERATE_SERIES(0, 100, 1) AS numbers
    ON  numbers.[Value] <= DATEDIFF(DAY, T.StartDT, T.EndDT)

Result:

Id	NewStartDT	NewEndDT
99	2022-08-16 16:19:05.550	2022-08-16 16:19:24.093
112	2022-08-17 14:22:56.277	2022-08-17 23:59:59.000
112	2022-08-18 00:00:00.000	2022-08-18 23:59:59.000
112	2022-08-19 00:00:00.000	2022-08-19 15:02:41.523

If your version of sql server doesn't support GENERATE_SERIES() function,

you can use instead the system "spt_values" table, like this:

SELECT  T.Id,
        CASE
            WHEN numbers.number = 0
            THEN T.StartDT
            ELSE CAST(DATEADD(DAY, numbers.number, T.StartDT) AS DATE)
        END AS NewStartDT,
        CASE
            WHEN numbers.number = DATEDIFF(DAY, T.StartDT, T.EndDT)
            THEN T.EndDT
            ELSE DATEADD(SECOND, -1, CAST(CAST(DATEADD(DAY, numbers.number + 1, T.StartDT) AS DATE) AS DATETIME))
        END AS NewEndDT
FROM    time_tbl AS T
JOIN    master..spt_values  AS numbers
    ON  numbers.type = 'p'
    AND numbers.number <= DATEDIFF(DAY, T.StartDT, T.EndDT)

赞(0）回复(0）举报 2023-03-28

hyrbngr72#

Here is an option using an ad-hoc Tally/Numbers table and a CROSS APPLY to calculate the nDays . The beginning/end just becomes a small matter of a CASE expression

You may notice I used TOP 1000 for the ad-hoc numbers table. Feel free to adjust to a more reasonable number.

You may also notice I added the milliseconds of 997 rather than using 000.

Example

Declare @YourTable Table ([Clocking_ID] int,[Start_Date_Time] datetime,[END_Date_Time] datetime)  
Insert Into @YourTable Values 
 (99,'2022-08-16 16:19:05.550','2022-08-16 16:19:24.093')
,(112,'2022-08-17 14:22:56.277','2022-08-19 15:02:41.523')

 
Select A.[Clocking_ID]
      ,Beg_Date_Time = case when N     = 0 then [Start_Date_Time] else convert(date,dateadd(day,N,[Start_Date_Time])) end
      ,End_Date_Time = case when nDays = N then [END_Date_Time]   else left(convert(date,dateadd(day,N,[Start_Date_Time])),10)+' 23:59:59.997' end
 From  @YourTable A
 Cross Apply ( values ( datediff(day, [Start_Date_Time],[END_Date_Time]) ) ) B(nDays)
 Join  ( Select Top 1000 N=-1+Row_Number() Over (Order By (Select NULL)) From master..spt_values n1, master..spt_values n2  ) C
   on  N<= nDays

Results

赞(0）回复(0）举报 2023-03-28

我来回答

SQL Server Query to split row into multiple row according to date and time

2条答案

相关问题

热门标签

最新问答