# Grouping stock in 6 month ranges

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

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