Início / dba / Perguntas / 160041
Accepted
Ganesh_55
Asked: 2017-01-06 01:19:31 +0800 CST

Obter contagem do SQL Server

  • 772

Tenho a tabela assim:

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

Eu quero ficar assim:

id    logintime>7'oclock   logintime >7:30   logintime>8'0 clock
397        0                     0                    5
sql-server pivot

2 respostas

  1. Best Answer

    Suposições:

    1. logintime>7'oclocksignificalogintime > 7 AND logintime <= 7.30
    2. logintime >7:30 significalogintime > 7.30 AND logintime <= 8.00
    3. logintime>8'0relógio significalogintime > 8.00

    Também de acordo com os dados fornecidos, logintime>8'0deve conter 9 em vez de 5. Atualize a pergunta se alguma lógica adicional precisar ser considerada.

    A consulta de seleção abaixo deve ajudá-lo a obter o resultado desejado.

    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
    • 1

  2. Não tenho certeza de como você deseja agrupar os dados (você precisará fazer pequenas alterações), mas aqui está um exemplo que usa pivô.

    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

    instrução CASE alternativa

    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
    • 1

relate perguntas