C# SQL issue, grouping based on time interval presence

Question

0.00/5 (No votes)

See more:

Hello everybody
I have an issue...
I have table in the form:

event ID        |     event category        |      event start               |   event end
_________________________________________

1                   |                   1                | 2012-12-16 02:02:55    | 2012-12-16 02:05:55

2                   |                   2                | 2012-12-16 02:05:56    | 2012-12-16 02:55:25

3                   |                   1                | 2012-12-16 02:55:26    | 2012-12-16 03:02:55

4                   |                   2                | 2012-12-16 03:02:56    | 2012-12-16 03:07:26

5                   |                   1                | 2012-12-16 03:07:27    | 2012-12-16 04:02:55

What I need to achieve is following:

I need somehow get reult saying:

time interval                                      |       category                                 |              duration

_________________________________________________________________________

 2012-12-16 02:00:00 -03:00:00      |                    1                                |(180+34+240) 454s

 2012-12-16 02:00:00 -03:00:00      |                    2                                |2969s

..........

please, give me a hint how to make this...thanks a lot upfront....

Posted 3-May-14 22:22pm

Zdeno Liška

Updated 3-May-14 23:40pm

Sander Rossel

v2

Add a Solution

Comments

ZurdoDev 4-May-14 7:54am

Where are you stuck? Are you just missing duration? You can use DateDiff.

Zdeno Liška 4-May-14 15:54pm

Hello, no, I can get duration with mentioned dateDiff function, thats not an issue.
What is issue, is , how to group input event records based on defined scope level
i mean: I need hour by hour report with information, how big part of each single hour was taken by each category,based on that, which events occured in which hour...

Maciej Los 5-May-14 3:32am

How do you calculate duration?
Category 1: from where comes values 34 and 240?
Category 2: why there is only 2696s?

Maciej Los 5-May-14 4:46am

I got it! Check my solution ;)

Prasad Avunoori 5-May-14 1:09am

Didn't understand. Please elaborate properly.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2014-05-04T22:18:00

OK, i got it:

SQL

DECLARE @tmp TABLE (event_ID INT IDENTITY(1,1) , event_category INT, event_start DATETIME, event_end DATETIME)

INSERT INTO @tmp (event_category, event_start, event_end)
VALUES(1, '2012-12-16 02:02:55', '2012-12-16 02:05:55'),
        (2, '2012-12-16 02:05:56', '2012-12-16 02:55:25'),
        (1, '2012-12-16 02:55:26', '2012-12-16 03:02:55'),
        (2, '2012-12-16 03:02:56', '2012-12-16 03:07:26'),
        (1, '2012-12-16 03:07:27', '2012-12-16 04:02:55')

--uncomment below lines to see details
--SELECT event_id, event_category, event_start, event_end, interval_start, interval_end,
--	CASE
--		WHEN event_end>interval_end THEN DATEDIFF(ss,event_start, interval_end)
--		WHEN event_end<interval_end then="" mode="hold" />--		ELSE 0
--	END AS Duration
--FROM(
--	SELECT event_id, event_category, event_start, event_end, DATEADD(hh, DATEDIFF(hh, 0, event_start), 0) AS interval_start, DATEADD(hh, DATEDIFF(hh, 0, event_start)+1, 0) AS interval_end
--	FROM @tmp
--) AS T
--ORDER BY event_category, event_start, event_end 

SELECT event_category, interval_start, interval_end,
    SUM(CASE
        WHEN event_end>interval_end THEN DATEDIFF(ss,event_start, interval_end)
        WHEN event_end<interval_end THEN DATEDIFF(ss,event_start, event_end)
        ELSE 0
    END) AS Duration
FROM(
    SELECT event_category, event_start, event_end, DATEADD(hh, DATEDIFF(hh, 0, event_start), 0) AS interval_start, DATEADD(hh, DATEDIFF(hh, 0, event_start)+1, 0) AS interval_end
    FROM @tmp
) AS T
GROUP BY event_category, interval_start , interval_end
ORDER BY event_category

Result:

cat interval_start          interval_end            Duration
1   2012-12-16 02:00:00.000 2012-12-16 03:00:00.000 454
1   2012-12-16 03:00:00.000 2012-12-16 04:00:00.000 3153
2   2012-12-16 02:00:00.000 2012-12-16 03:00:00.000 2969
2   2012-12-16 03:00:00.000 2012-12-16 04:00:00.000 270

NOTE: Some values have been rejected!
For example:
event_id = 3: 2min.55sec
event_id = 5: 2min.55sec
Above values haven't been moved to the next time interval! If you would like to add it, then you need to use CTE[^]

For further information about CTE, please see:
CTE In SQL Server[^]
Common Table Expressions(CTE) in SQL SERVER 2008[^]
SQL Server CTE Basics[^]
CTEs (Common Table Expressions)[^]
Optimize Recursive CTE Query[^]

Below CTE:

SQL

;WITH MyIntervals AS
(
    SELECT event_id, event_category, event_start, event_end, DATEADD(hh, DATEDIFF(hh, 0, event_start), 0) AS interval_start, DATEADD(hh, DATEDIFF(hh, 0, event_start)+1, 0) AS interval_end
    FROM @tmp
    UNION ALL
    SELECT event_id, event_category, interval_end AS event_start, event_end, interval_end AS interval_start, DATEADD(hh, 1, interval_end) AS interval_end
    FROM MyIntervals
    WHERE event_end>interval_end
)
SELECT event_category, interval_start, interval_end,
    SUM(CASE
        WHEN event_end>interval_end THEN DATEDIFF(ss,event_start, interval_end)
        WHEN event_end<interval_end THEN DATEDIFF(ss,event_start, event_end)
        ELSE 0
    END) AS Duration
FROM(
    SELECT event_category, event_start, event_end, interval_start, interval_end
    FROM MyIntervals
) AS T
GROUP BY event_category, interval_start, interval_end

produces the following result:

1   2012-12-16 02:00:00.000 2012-12-16 03:00:00.000 454
1   2012-12-16 03:00:00.000 2012-12-16 04:00:00.000 3328
1   2012-12-16 04:00:00.000 2012-12-16 05:00:00.000 175
2   2012-12-16 02:00:00.000 2012-12-16 03:00:00.000 2969
2   2012-12-16 03:00:00.000 2012-12-16 04:00:00.000 270

Do you see the difference?