[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 188

Compute Total Hours with shared time using SQL

Table 1. RAW DATA

Start Time                             End Time
7-29-07 7:30 AM               7-29-07 3:30 PM
7-29-07 7:47 AM                      7-29-07 2:13 PM
7-29-07 7:52 AM                      7-29-07 3:05 PM
7-29-07 3:10 PM                       7-29-07 3:30 PM

DIAGRAM

7:30 AM  --------------------------------------------------------------------------------------------------------3 30 PM

7:47 AM ----------------------------------------------------------------------- 2:13 PM

7:52 AM ---------------------------------------------------------------------- 3:05 PM

3:10 AM --------- 3:30 PM

COMPUTATION SAMPLE

For record 1:                                                Hours
7:30 AM - 7:47 AM -     Unshared                   0.28
7:52 AM - 7:47 Am -     .08/ 2 Recs                0.04
2:13 PM - 7:52 AM -      6.35 / 3 Recs            2.12
3:05 PM - 2:13 PM -       0.87 / 2                     0.43
3:10 PM -  3:05 PM -      Unshared                 0.08
3:10 PM - 3:30 PM  -     .33 / 2                        0.17

For Record 2
7:52 AM - 7:47 Am -     .08/ 2 Recs                  .04
2:13 PM - 7:52 AM -      6.35 / 3 Recs            2.12

For Record 3
2:13 PM - 7:52 AM -      6.35 / 3 Recs            2.12
3:05 PM - 2:13 PM -       0.87 / 2                     0.43

For Record 4
3:10 PM - 3:30 PM  -     .33 / 2                        .17

Table 2 RESULT

Start Time                            End Time                       Output
7-29-07 7:30 AM               7-29-07 3:30 PM           3.12
7-29-07 7:47 AM                      7-29-07 2:13 PM           2.16
7-29-07 7:52 AM                      7-29-07 3:05 PM           2.55
7-29-07 3:10 PM                       7-29-07 3:30 PM           0.17

Thank you so much....
0
conray
• 2
1 Solution

Commented:
Hello conray,

Try this (I hope your table has primary key)

WITH TimeIntervals([Time]) AS
(
SELECT DISTINCT [Start Time] FROM [Table Name Goes Here ]
UNION
SELECT DISTINCT [End Time] FROM [Table Name Goes Here ]
)
SELECT RecordID, [Start Time], [End Time], SUM(Output) AS Output
FROM (
SELECT x.RecordID, x.[Start Time], x.[End Time],
(DATEDIFF(minute, a.[Time], b.[Time])/60.0)/(SELECT COUNT(*)+1  FROM [Table Name Goes Here ] WHERE a.[Time] >= [Start Time] AND a.[Time] < [End Time] AND RecordID <> x.RecordID) AS Output
FROM [Table Name Goes Here ] AS x INNER JOIN TimeIntervals AS a ON a.[Time] >= x.[Start Time] AND a.[Time] < x.[End Time]
INNER JOIN TimeIntervals AS b ON b.[Time] = (SELECT TOP 1 [Time] FROM TimeIntervals WHERE [Time] > a.[Time])
) AS x GROUP BY RecordID, [Start Time], [End Time]

Regards,

Rimvis
0

Senior DBACommented:
Here's one possibility, requiring a (small) temp table (the other "temp" table is for the main data table):

DROP TABLE #rawData
DROP TABLE #times
GO
SET NOCOUNT ON
CREATE TABLE #rawData (
[Start Time] DATETIME,
[End Time] DATETIME
)
CREATE TABLE #times (
time DATETIME,
[count] SMALLINT,
UNIQUE CLUSTERED (time),
value AS 1.0000 / [count]
)

INSERT INTO #rawData VALUES('7-29-07 7:30 AM' , '7-29-07 3:30 PM')
INSERT INTO #rawData VALUES('7-29-07 7:47 AM' , '7-29-07 2:13 PM')
INSERT INTO #rawData VALUES('7-29-07 7:52 AM' , '7-29-07 3:05 PM')
INSERT INTO #rawData VALUES('7-29-07 3:10 PM' , '7-29-07 3:30 PM')

DECLARE @minStart DATETIME
DECLARE @maxEnd DATETIME
SELECT @minStart = MIN([Start Time]),
@maxEnd = MAX([End Time])
FROM #rawData

INSERT INTO #times (time, [count])
SELECT DATEADD(MINUTE, ones + tens + hundreds + thousands, @minStart), 0
FROM (
SELECT 0 AS ones UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
SELECT 8 UNION ALL SELECT 9
) AS ones
CROSS JOIN (
SELECT 0 AS tens UNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL SELECT 30 UNION ALL
SELECT 40 UNION ALL SELECT 50 UNION ALL SELECT 60 UNION ALL SELECT 70 UNION ALL
SELECT 80 UNION ALL SELECT 90
) AS tens
CROSS JOIN (
SELECT 0 AS hundreds UNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300 UNION ALL
SELECT 400 UNION ALL SELECT 500 UNION ALL SELECT 600 UNION ALL SELECT 700 UNION ALL
SELECT 800 UNION ALL SELECT 900
) AS hundreds
CROSS JOIN (
SELECT 0 AS thousands UNION ALL SELECT 1000 UNION ALL SELECT 2000 UNION ALL SELECT 3000
) AS thousands
WHERE ones + tens + hundreds + thousands <= DATEDIFF(MINUTE, @minStart, @maxEnd)
ORDER BY 1

UPDATE time
FROM #times time
INNER JOIN (
SELECT time, COUNT(*) AS [count]
FROM #times
INNER JOIN #rawData ON time BETWEEN [Start Time] AND [End Time]
GROUP BY time

SET NOCOUNT OFF

SELECT CAST(FLOOR((SELECT SUM(value)
FROM #times
WHERE time BETWEEN [Start Time] AND [End Time])
/ 60.0 * 100) / 100.0 AS DECIMAL(4, 2)) AS [Output],
[Start Time], [End Time]
FROM #rawdata
0

Author Commented:
Thank you so much Rimvis and ScottPletcher for your response... 5 stars to the both of you. However I would like to give an extra star to Rimvis for the shortness of his answer.
0

Senior DBACommented:
OK, I must have done my testing wrong.  I got a wrong answer on the sample data using Rimvis's code.
0

Featured Post

• 2
Tackle projects and never again get stuck behind a technical roadblock.