TID StartTime Uid WId
1 2011-06-06 09:30:00.000 10 1.5
2 2011-06-06 09:40:00.000 10 3.5
3 2011-06-06 09:50:00.000 10 1.0
4 2011-06-06 10:45:00.000 10 2.5
5 2011-06-06 10:50:00.000 10 1.5
6 2011-06-06 10:55:00.000 10 0
7 2011-06-06 11:30:00.000 10 0
8 2011-06-06 11:35:00.000 10 0
9 2011-06-06 11:40:00.000 10 0
10 2011-06-06 11:43:00.000 10 0
11 2011-06-06 11:20:00.000 11 7.0
12 2011-06-06 11:30:00.000 11 1.0
I have the data in Table TblTask , I need to write a query to calculate (sum(Wid) or Count(TID's)) based on time difference of each Starttime with another starttime having time difference 1 hour or close to 1 hr Group by UID.
For example take first start time(2011-06-06 09:30:00.000); the nearest start time having <=1 hr is (2011-06-06 10:30:00.000) (If the SUM(Wid) >=5.0 OR Count of Rows >=5). Here Weights are (1.5+3.5+1.0=6.0) First 3 rows TID's (1,2,3) as weight>=5 we need to show the records and TID's (3,4,5) Here Weights are (1.0+2.5+1.5=5.0) For TID'd(4,5,6,7,8,9,10) Here Count (TID's is 7) So i want display all the records from 4 to 10
For TID's(5,6,7,8,9,10) as Count (TID's is > 5 ) i want display all the records from 5 to 10
For TID's(6,7,8,9,10) as Count (TID's is = 5 ) i want display all the records from 6 to 10
For TID 11 as WID > 5 i want to display record 11
For TID's (11,12) as WID >5 again i want to display record 11 and 12
Desired output will be:
TID StartTime Uid
1 2011-06-06 09:30:00.000 10
2 2011-06-06 09:40:00.000 10
3 2011-06-06 09:50:00.000 10
3 2011-06-06 09:50:00.000 10
4 2011-06-06 10:45:00.000 10
5 2011-06-06 10:50:00.000 10
4 2011-0开发者_如何学编程6-06 10:45:00.000 10
5 2011-06-06 10:50:00.000 10
6 2011-06-06 10:55:00.000 10
7 2011-06-06 11:30:00.000 10
8 2011-06-06 11:35:00.000 10
9 2011-06-06 11:40:00.000 10
10 2011-06-06 11:43:00.000 10
5 2011-06-06 10:50:00.000 10
6 2011-06-06 10:55:00.000 10
7 2011-06-06 11:30:00.000 10
8 2011-06-06 11:35:00.000 10
9 2011-06-06 11:40:00.000 10
10 2011-06-06 11:43:00.000 10
6 2011-06-06 10:55:00.000 10
7 2011-06-06 11:30:00.000 10
8 2011-06-06 11:35:00.000 10
9 2011-06-06 11:40:00.000 10
10 2011-06-06 11:43:00.000 10
11 2011-06-06 11:20:00.000 11
11 2011-06-06 11:20:00.000 11
12 2011-06-06 11:30:00.000 11
Alternative I: Will not catch the lone row 11, since it will try to pick the longest valid sequence from each starting point.
SELECT C.*
FROM (
SELECT
A.UID,
A.TID AS TID1,
MAX(B.TID) AS TID2
FROM TblTask A
INNER JOIN TblTask B
ON B.UID = A.UID
AND B.StartTime BETWEEN A.StartTime AND DATEADD(hour, 1, A.StartTime)
GROUP BY A.TID, A.UID
HAVING SUM(B.WId) >= 5 OR COUNT(*) >= 5
) Ranges
INNER JOIN TblTask C
ON C.UID = Ranges.UID
AND C.TID BETWEEN Ranges.TID1 AND Ranges.TID2
Alternative II: Will report both (4..9) and (4..10), since those fall within the criteria.
SELECT D.*
FROM (
SELECT
A.Uid,
A.TID AS TID1,
B.TID AS TID2
FROM TblTask A
INNER JOIN TblTask B
ON B.Uid = A.Uid
AND B.StartTime BETWEEN A.StartTime AND DATEADD(hour, 1, A.StartTime)
INNER JOIN TblTask C
ON C.UID = A.UID
AND C.TID BETWEEN A.TID AND B.TID
GROUP BY A.TID, B.TID, A.Uid
HAVING SUM(C.WId) >= 5 OR COUNT(*) >= 5
) Ranges
INNER JOIN TblTask D
ON D.Uid = Ranges.Uid
AND D.TID BETWEEN Ranges.TID1 AND Ranges.TID2;
In either alternative, you could add columns from the Ranges table to distinguish between the concatenated sub-sequences.
Result of Alternative I:
TID StartTime Uid WId
1 2011-06-06 09:30:00.000 10 1.5
2 2011-06-06 09:40:00.000 10 3.5
3 2011-06-06 09:50:00.000 10 1.0
3 2011-06-06 09:50:00.000 10 1.0
4 2011-06-06 10:45:00.000 10 2.5
5 2011-06-06 10:50:00.000 10 1.5
4 2011-06-06 10:45:00.000 10 2.5
5 2011-06-06 10:50:00.000 10 1.5
6 2011-06-06 10:55:00.000 10 0.0
7 2011-06-06 11:30:00.000 10 0.0
8 2011-06-06 11:35:00.000 10 0.0
9 2011-06-06 11:40:00.000 10 0.0
10 2011-06-06 11:43:00.000 10 0.0
5 2011-06-06 10:50:00.000 10 1.5
6 2011-06-06 10:55:00.000 10 0.0
7 2011-06-06 11:30:00.000 10 0.0
8 2011-06-06 11:35:00.000 10 0.0
9 2011-06-06 11:40:00.000 10 0.0
10 2011-06-06 11:43:00.000 10 0.0
6 2011-06-06 10:55:00.000 10 0.0
7 2011-06-06 11:30:00.000 10 0.0
8 2011-06-06 11:35:00.000 10 0.0
9 2011-06-06 11:40:00.000 10 0.0
10 2011-06-06 11:43:00.000 10 0.0
11 2011-06-06 11:20:00.000 11 7.0
12 2011-06-06 11:30:00.000 11 1.0
Result of Alternative II:
TID StartTime Uid WId
1 2011-06-06 09:30:00.000 10 1.5
2 2011-06-06 09:40:00.000 10 3.5
1 2011-06-06 09:30:00.000 10 1.5
2 2011-06-06 09:40:00.000 10 3.5
3 2011-06-06 09:50:00.000 10 1.0
3 2011-06-06 09:50:00.000 10 1.0
4 2011-06-06 10:45:00.000 10 2.5
5 2011-06-06 10:50:00.000 10 1.5
4 2011-06-06 10:45:00.000 10 2.5
5 2011-06-06 10:50:00.000 10 1.5
6 2011-06-06 10:55:00.000 10 0.0
7 2011-06-06 11:30:00.000 10 0.0
8 2011-06-06 11:35:00.000 10 0.0
4 2011-06-06 10:45:00.000 10 2.5
5 2011-06-06 10:50:00.000 10 1.5
6 2011-06-06 10:55:00.000 10 0.0
7 2011-06-06 11:30:00.000 10 0.0
8 2011-06-06 11:35:00.000 10 0.0
9 2011-06-06 11:40:00.000 10 0.0
4 2011-06-06 10:45:00.000 10 2.5
5 2011-06-06 10:50:00.000 10 1.5
6 2011-06-06 10:55:00.000 10 0.0
7 2011-06-06 11:30:00.000 10 0.0
8 2011-06-06 11:35:00.000 10 0.0
9 2011-06-06 11:40:00.000 10 0.0
10 2011-06-06 11:43:00.000 10 0.0
5 2011-06-06 10:50:00.000 10 1.5
6 2011-06-06 10:55:00.000 10 0.0
7 2011-06-06 11:30:00.000 10 0.0
8 2011-06-06 11:35:00.000 10 0.0
9 2011-06-06 11:40:00.000 10 0.0
5 2011-06-06 10:50:00.000 10 1.5
6 2011-06-06 10:55:00.000 10 0.0
7 2011-06-06 11:30:00.000 10 0.0
8 2011-06-06 11:35:00.000 10 0.0
9 2011-06-06 11:40:00.000 10 0.0
10 2011-06-06 11:43:00.000 10 0.0
6 2011-06-06 10:55:00.000 10 0.0
7 2011-06-06 11:30:00.000 10 0.0
8 2011-06-06 11:35:00.000 10 0.0
9 2011-06-06 11:40:00.000 10 0.0
10 2011-06-06 11:43:00.000 10 0.0
11 2011-06-06 11:20:00.000 11 7.0
11 2011-06-06 11:20:00.000 11 7.0
12 2011-06-06 11:30:00.000 11 1.0
![Interactive visualization of a graph in python [closed]](https://www.devze.com/res/2023/04-10/09/92d32fe8c0d22fb96bd6f6e8b7d1f457.gif)
加载中,请稍侯......
精彩评论