-5

I have the table like this:

ID      Office_Date        Login_Time
397 2015-09-21 00:00:00 2015-09-21 09:00:00.000
397 2015-09-22 00:00:00 2015-09-22 10:00:04.000
397 2015-09-23 00:00:00 2015-09-23 08:13:41.967
397 2015-09-24 00:00:00 2015-09-24 08:16:29.267
397 2015-09-25 00:00:00 2015-09-25 08:12:41.510
397 2015-09-28 00:00:00 2015-09-28 08:10:23.200
397 2015-09-29 00:00:00 2015-09-29 08:15:57.493
397 2015-09-30 00:00:00 2015-09-30 08:19:33.973
397 2015-10-01 00:00:00 2015-10-01 08:22:52.790

I want to get like this:

id    logintime>7'oclock   logintime >7:30   logintime>8'0 clock
397        0                     0                    5
0

2 Answers 2

1

Assumptions:

  1. logintime>7'oclock means logintime > 7 AND logintime <= 7.30
  2. logintime >7:30 means logintime > 7.30 AND logintime <= 8.00
  3. logintime>8'0 clock means logintime > 8.00

Also as per the data provided, logintime>8'0 should contain 9 instead of 5. Please update the question if any additional logic needs to be considered.

Below select query should help you to get the desired result.

WITH CTE AS(
    SELECT 
        ID
        , CASE 
            WHEN CAST(LoginTime AS TIME) > '07:00:00.0000000' AND CAST(LoginTime AS TIME) <= '07:30:00.0000000' THEN 1
            ELSE 0
        END AS After7
        , CASE 
            WHEN CAST(LoginTime AS TIME) > '07:30:00.0000000' AND CAST(LoginTime AS TIME) <= '08:00:00.0000000' THEN 1
            ELSE 0
        END AS After7_30
        , CASE 
            WHEN CAST(LoginTime AS TIME) > '08:00:00.0000000' THEN 1
            ELSE 0
        END AS After8
        FROM tblCTS_Test
)
SELECT ID
    , SUM(After7) AS 'logintime>7''oclock'
    , SUM(After7_30) AS 'logintime >7:30'
    , SUM(After8) AS 'logintime>8''0 clock'
FROM CTE
GROUP BY ID
0
1

Not sure exactly how you want to group the data (you'll need to make minor changes) but here's an example that uses pivot.

CREATE TABLE #TMP(ID INT ,Office_date DATE,Login_time DATETIME)

INSERT INTO #TMP VALUES
(397,'2015-09-21 00:00:00','2015-09-21 09:00:00.000')
,(397,'2015-09-22 00:00:00','2015-09-22 10:00:04.000')
,(397,'2015-09-23 00:00:00','2015-09-23 08:13:41.967')
,(397,'2015-09-24 00:00:00','2015-09-24 08:16:29.267')
,(397,'2015-09-25 00:00:00','2015-09-25 08:12:41.510')
,(397,'2015-09-28 00:00:00','2015-09-28 08:10:23.200')
,(397,'2015-09-29 00:00:00','2015-09-29 08:15:57.493')
,(397,'2015-09-30 00:00:00','2015-09-30 08:19:33.973')
,(397,'2015-10-01 00:00:00','2015-10-01 08:22:52.790')
--Extra test rows
,(397,'2015-10-02 00:00:00','2015-10-01 07:22:52.790')
,(397,'2015-10-03 00:00:00','2015-10-01 07:32:52.790')

SELECT ID,After7,After730,After8
FROM (
    SELECT
        ID
        ,CASE
            WHEN DATEPART(HH,Login_time) >= 8 THEN 'After8'
            WHEN DATEPART(HH,Login_time) >= 7 AND DATEPART(MI,Login_time) > 30 THEN 'After730'
            WHEN DATEPART(HH,Login_time) >= 7 THEN 'After7'
            END AS Band
        ,1 AS val
    FROM
        #TMP) up
    PIVOT (COUNT(VAL) FOR BAND IN (After7, After730,After8)) AS pvt
ORDER BY ID

alternative CASE statement

SELECT
    ID
    ,CASE 
        WHEN DATEDIFF(MI,Office_date,Login_time)/60.0 BETWEEN 8 AND 8.5 THEN 'After8'
        WHEN DATEDIFF(MI,Office_date,Login_time)/60.0 BETWEEN 7.5 AND 8 THEN 'After730'
        WHEN DATEDIFF(MI,Office_date,Login_time)/60.0 BETWEEN 7 AND 7.5 THEN 'After7'
        END AS Band
    ,1 AS val
FROM
    #TMP

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.