Início / dba / Perguntas / 5653
Accepted
Cell-o
Asked: 2011-09-13 03:57:20 +0800 CST

Agrupamento de estoque em intervalos de 6 meses

  • 772

Eu tenho puxado o estoque total nos primeiros 6 meses. Como posso puxar o estoque total nos últimos 6 meses na mesma consulta?

Também quero o resultado da query conforme abaixo:

  Stock Code | the first 6 months   |  the second 6 months   etc.

Minha consulta SQL:

 SELECT TOP (100) PERCENT STOK_KODU, 
    COALESCE (SUM(CASE WHEN kod1 = 'G' THEN kod2 ELSE 0 END), 0) 
   - COALESCE (SUM(CASE WHEN kod1 = 'C' THEN kod2 ELSE 0 END), 0) AS StokToplam1 
FROM s_hareket WHERE (STOK_KODU BETWEEN '01001' AND '75000') 
and (tarih BETWEEN '2011-01-01' and '2011-06-30') 
GROUP BY STOK_KODU 
ORDER BY STOK_KODU
sql-server group-by

1 respostas

  1. Best Answer

    Você deve ser capaz de fazer isso com as funções dateadd e getdate:

    SELECT TOP ( 100 ) PERCENT
        STOK_KODU ,
        COALESCE(SUM(CASE WHEN kod1 = 'G' THEN kod2
                          ELSE 0
                     END), 0) - COALESCE(SUM(CASE WHEN kod1 = 'C' THEN kod2
                                                  ELSE 0
                                             END), 0) AS StokToplam1
FROM    s_hareket
WHERE   ( STOK_KODU BETWEEN '01001' AND '75000' )
        AND ( tarih BETWEEN dateadd(month,-6,GETDATE()) AND GETDATE() )
GROUP BY STOK_KODU
ORDER BY STOK_KODU

    Se entendi sua pergunta corretamente, você deseja unir os resultados e, em seguida, girá-lo. Isso leva à seguinte consulta:

    SELECT * FROM 
    (SELECT TOP ( 100 ) PERCENT
            'last' AS t,
            STOK_KODU ,
            COALESCE(SUM(CASE WHEN kod1 = 'G' THEN kod2
                              ELSE 0
                         END), 0) - COALESCE(SUM(CASE WHEN kod1 = 'C' THEN kod2
                                                      ELSE 0
                                                 END), 0) AS StokToplam1
    FROM    s_hareket
    WHERE   ( STOK_KODU BETWEEN '01001' AND '75000' )
            AND ( tarih BETWEEN dateadd(month,-6,GETDATE()) AND GETDATE() )
    GROUP BY STOK_KODU
    ORDER BY STOK_KODU

    UNION ALL 
    SELECT TOP ( 100 ) PERCENT
            'first' AS t,
            STOK_KODU ,
            COALESCE(SUM(CASE WHEN kod1 = 'G' THEN kod2
                              ELSE 0
                         END), 0) - COALESCE(SUM(CASE WHEN kod1 = 'C' THEN kod2
                                                      ELSE 0
                                                 END), 0) AS StokToplam1
    FROM    s_hareket
    WHERE   ( STOK_KODU BETWEEN '01001' AND '75000' )
            AND ( tarih BETWEEN '2011-01-01' and '2011-06-30' )
    GROUP BY STOK_KODU
    ORDER BY STOK_KODU) AS sourceTable 
PIVOT (
    SUM(StokToplam1)
    FOR T IN ([first], [last])
) AS p
    • 7

relate perguntas