Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Table_1
- Name | Activity | LogTime
- A | 0 | 2018-12-17 10:16:04.877
- A | 1 | 2018-12-15 10:16:04.877
- A | 0 | 2018-12-16 10:16:04.877
- A | 0 | 2018-12-10 10:16:04.877
- A | 0 | 2018-12-10 10:10:04.877
- B | 1 | 2018-12-16 10:16:04.877
- B | 0 | 2018-12-17 10:16:04.877
- C | 1 | 2018-12-14 10:16:04.877
- C | 1 | 2018-12-12 10:16:04.877
- C | 1 | 2018-12-18 10:16:04.877
- Name | TOTALActivity_0 | TOTALActivity_1 | LatestActivity_0_Logtime | LatestActivity_1_Logtime
- A | 4 | 1 | 2018-12-17 10:16:04.877 | 2018-12-15 10:16:04.877
- B | 1 | 1 | 2018-12-17 10:16:04.877 | 2018-12-16 10:16:04.877
- C | 0 | 3 | NULL | 2018-12-18 10:16:04.877
- Name | TOTALActivity_0 | TOTALActivity_1
- A | 4 | 1
- B | 1 | 1
- C | 0 | 3
- SELECT
- NAME,
- SUM(CASE WHEN Activity = 0 THEN 1 ELSE 0 END) TOTALActivity_0,
- SUM(CASE WHEN Activity = 1 THEN 1 ELSE 0 END) TOTALActivity_1
- FROM Table_1
- GROUP BY NAME
- SELECT
- NAME,
- SUM(CASE WHEN Activity = 0 THEN 1 ELSE 0 END) TOTALActivity_0,
- SUM(CASE WHEN Activity = 1 THEN 1 ELSE 0 END) TOTALActivity_1,
- CASE WHEN Activity = 0 THEN LogTime ELSE NULL END AS LatestActivity_0_Logtime,
- CASE WHEN Activity = 1 THEN LogTime ELSE NULL END AS LatestActivity_1_Logtime
- FROM Table_1
- GROUP BY NAME,Activity,LogTime
- SELECT
- NAME,
- SUM(CASE WHEN Activity = 0 THEN 1 ELSE 0 END) TOTALActivity_0,
- SUM(CASE WHEN Activity = 1 THEN 1 ELSE 0 END) TOTALActivity_1,
- NULL AS LatestActivity_0_Logtime,
- NULL AS LatestActivity_1_Logtime
- FROM Table_1
- GROUP BY NAME
- UNION
- SELECT NULL,NULL,NULL,CASE WHEN Activity = 0 THEN LogTime ELSE NULL END AS LatestActivity_0_Logtime,
- CASE WHEN Activity = 1 THEN LogTime ELSE NULL END AS LatestActivity_1_Logtime
- FROM Table_1
- Name | TOTALActivity_0 | TOTALActivity_1 | LatestActivity_0_Logtime | LatestActivity_1_Logtime
- A | 4 | 1 | NULL | NULL
- B | 1 | 1 | NULL | NULL
- B | 1 | 3 | NULL | NULL
- NULL | NULL | NULL | 2018-12-17 10:16:04.877 | 2018-12-15 10:16:04.877
- NULL | NULL | NULL | 2018-12-16 10:16:04.877 | 2018-12-16 10:16:04.877
- NULL | NULL | NULL | 2018-12-10 10:16:04.877 | 2018-12-18 10:16:04.877
- NULL | NULL | NULL | 2018-12-10 10:10:04.877 | 2018-12-15 10:16:04.877
- NULL | NULL | NULL | 2018-12-17 10:16:04.877 | 2018-12-16 10:16:04.877
- .
- .
- .
- .
- .
- .
Add Comment
Please, Sign In to add comment