CODEWITHSUNDEEP
sqlsql-server
Dinesh
Dinesh

Reputation: 423

Multiple Where conditions or Join on same table SQL Server

In SQL, i need the result set based on each hour difference between two datetime field . For example, The result set is need to be like this

I need to Groupby by USERID, StartDate

I am bit confused about using Sub Query or join between same table. So, help me to get out from this

+----------------------------------------------------------------+
| USERID    Date          6to7    7to8    8to9    9to10   10to11 |
+----------------------------------------------------------------+
| 119       2016-03-07    5                                      |
| 119       2016-03-10                     18                    |
| 5         2016-03-08    8                                      |
| 5         2016-03-10                             7             |
| 25        2016-03-09               2     20                    |
| 30        2016-03-11                     5                     |
+----------------------------------------------------------------+

The table looks like below

  ID    UserId         StartDate            EndDate
7494    119    2016-03-07 06:35:55.000      2016-03-07 06:40:55.000
7495    5      2016-03-08 06:02:33.000      2016-03-08 06:10:33.000
7496    25     2016-03-09 07:58:33.000      2016-03-09 08:20:33.000
7497    25     2016-03-09 07:54:20.000      2016-03-09 07:56:20.000
7498    119    2016-03-10 08:35:55.000      2016-03-10 08:53:55.000
7499    5      2016-03-10 09:02:33.000      2016-03-10 09:09:33.000
7500    30     2016-03-11 08:58:33.000      2016-03-11 08:59:33.000
7501    30     2016-03-11 08:54:20.000      2016-03-11 08:58:20.000

This is the code I have tried

SELECT  UserID, 
        StartDate,
        (SUM(DATEDIFF(SECOND, CAST(SWITCHOFFSET(TODATETIMEOFFSET(StartDate,      '-00:00'), '+06:00') AS DATETIME),
CAST(SWITCHOFFSET(TODATETIMEOFFSET(EndDate, '-00:00'), '+06:00') AS    DATETIME))
))'HRS6TO7' 
FROM TMCallResultLog 
WHERE  CAST(StartDate AS TIME) BETWEEN '06:00:00' AND '07:00:00' AND
EndDate IS NOT NULL AND 
StartDate IS NOT NULL 
GROUP BY UserID, StartDate

Upvotes: 0

Views: 496

Answers (1)

IVNSTN
IVNSTN

Reputation: 9299

That's called conditional aggregation. Also you need to cast StartDate to date (to cut off time) before grouping:

select
  UserID,
  CAST(StartDate as DATE) StartDate,
  SUM(
    CASE
      WHEN CAST(StartDate AS TIME) BETWEEN '06:00:00' AND '07:00:00' AND
      THEN DATEDIFF(...)
      ELSE 0
    END
   ) as [6to7],
  SUM(
    CASE
      WHEN CAST(StartDate AS TIME) BETWEEN '07:00:00' AND '08:00:00' AND
      THEN DATEDIFF(...)
      ELSE 0
    END
   ) as [7to8],
   ...
from ...
where EndDate IS NOT NULL AND StartDate IS NOT NULL 
group by
  UserID,
  CAST(StartDate as DATE)

Upvotes: 1

Related Questions