J Livingston SQL (4/10/2013)

---

maybe something along these lines....??

declare @dt1 datetime

declare @dt2 datetime

set @dt1 = '2012-09-01'

set @dt2 = '2012-10-01'

;WITH cte AS

(

SELECT

V1.item,

--V1.voucherno AS VNO,

SUM(case when v.dt < @dt1 AND v.vouchertype = 'PURCHASE' then V1.quantity else 0 end) - SUM(case when v.dt < @dt1 AND v.vouchertype = 'SALES' then V1.quantity else 0 end) AS OPENING,

SUM(case when v.dt >= @dt1 AND v.dt < @dt2 AND v.vouchertype = 'PURCHASE' then V1.quantity else null end) AS INCOMING,

SUM(case when v.dt >= @dt1 AND v.dt < @dt2 AND v.vouchertype = 'SALES' then V1.quantity else null end) AS OUTGOING,

SUM(case when v.dt < @dt2 AND v.vouchertype = 'PURCHASE' then V1.quantity else 0 end) - SUM(case when v.dt < @dt2 AND v.vouchertype = 'SALES' then V1.quantity else 0 end) AS CLOSING

FROM voucher1 AS V1 INNER JOIN

voucher AS V ON V.vouchertype = V1.vouchertype

AND V.voucherprefix = V1.voucherprefix

AND V.voucherno = V1.voucherno

AND V.invoicetype = V1.invoicetype

GROUP BY V1.item

--,V1.voucherno

)

SELECT items.srno, items.itemname, CTE.*

FROM items LEFT OUTER JOIN

CTE ON items.srno = CTE.item