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

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

Compute Total Hours with shared time using SQL

Experts please help me generate the output in table 2.

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
Asked:
conray
  • 2
1 Solution
 
RimvisCommented:
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
 
Scott PletcherSenior 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
SET [count] = timeTasks.[count]
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
) AS timeTasks ON time.time = timeTasks.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
 
conrayAuthor 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
 
Scott PletcherSenior 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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

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