# Simply SQL Query problem

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
``````
-

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
``````
-
@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