0

I have been pulling the total stock within the first 6 months. How can I pull the total stock within the last 6 months in the same query?

Also I want the result of the query as shown below:

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

My SQL query:

 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
7

You should be able to do it with a dateadd and getdate functions:

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

If I understand your question correctly, you want to union the results, then pivot it. That leads to the following query:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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