Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have been pulling to the total stock within the first 6 months.My question is:How can I pull to total stock within the last 6 months in the same query? Also,I want to be to the result of the query as shown below.

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

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
share|improve this question

1 Answer 1

up vote 7 down vote accepted

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

Edit: If i understand your question 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
share|improve this answer
    
@devzero-First of all,thanks for your reply.But,I getting an error in 'last' AS t .What might be the problem? –  Cell-o Sep 13 '11 at 8:47
    
I'm missing a comma (,) after 'last' AS t, sorry about that. –  devzero Sep 14 '11 at 7:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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