Query Report Stock Statement Transacation Wise

Stock Statement Document wise

/* Select t1.docdate from OINV t1 */

DECLARE @d1  AS DATETIME
DECLARE @d2  AS DATETIME
SET @d1 = /* t1.docdate */ ‘[%0]’
SET @d2 = /* t1.docdate */ ‘[%1]’

SELECT tt.Itemcode
       ,tt.Dscription AS ItemName
      ,SUM(tt.Opening) AS Opening
      ,SUM(tt.Sales) AS Sales
      ,SUM(tt.SalesReturn) AS SalesReturn
      ,SUM(tt.Delivery) AS Delivery
      ,SUM(tt.DeliveryReturn) AS DeliveryReturn
       ,SUM(tt.Purchase) AS Purchase
      ,SUM(tt.PurchaseReturn) AS PurchaseReturn
      ,SUM(tt.GoodsReceiptsPo) AS GoodsReceiptPo
      ,SUM(tt.GoodsReturnPo) AS GoodsReturnPo
      ,SUM(tt.Receipt) AS Receipt
       ,SUM(tt.Issue) AS Issue
      ,SUM(tt.ProReceipt) AS ProductionReceipt
      ,SUM(tt.ProIssue) AS ProductionIssue
FROM   (
           SELECT Itemcode
                 ,n.Dscription
                 ,SUM(inqty-outqty) AS Opening
                  ,0 AS Sales
                 ,0 AS SalesReturn
                 ,0 AS Delivery
                 ,0 AS DeliveryReturn
                 ,0 AS Purchase
                 ,0 AS PurchaseReturn
                  ,0 GoodsReceiptsPo
                 ,0 GoodsReturnPo
                 ,0 AS Receipt
                 ,0 AS Issue
                 ,0 AS ProReceipt
                 ,0 AS ProIssue
           FROM   oinm n
            WHERE  docdate<@d1
           GROUP BY
                  Itemcode
                 ,n.Dscription
           UNION ALL
           SELECT Itemcode
                 ,Dscription
                 ,SUM(
                       CASE
                           WHEN c.TransType=’310000001′ THEN (inqty- Outqty)
                           ELSE 0
                      END
                  ) AS Opening
                 ,SUM(CASE WHEN c.TransType=’13’ THEN (Outqty) ELSE 0 END) AS
                   Sales
                 ,SUM(CASE WHEN c.TransType=’14’ THEN (Inqty) ELSE 0 END) AS
                  SalesReturn
                 ,SUM(CASE WHEN c.TransType=’15’ THEN (Outqty) ELSE 0 END) AS
                   Delivery
                 ,SUM(CASE WHEN c.TransType=’16’ THEN (Inqty) ELSE 0 END) AS
                  DeliveryReturn
                 ,SUM(CASE WHEN c.TransType=’18’ THEN (Inqty) ELSE 0 END) AS
                   Purchase
                 ,SUM(CASE WHEN c.TransType=’19’ THEN (outqty) ELSE 0 END) AS
                  PurchaseReturn
                 ,SUM(CASE WHEN c.TransType=’20’ THEN (Inqty) ELSE 0 END) AS
                   GoodsReceiptsPo
                 ,SUM(CASE WHEN c.TransType=’21’ THEN (outqty) ELSE 0 END) AS
                  GoodsReturnPo
                 ,SUM(
                      CASE
                           WHEN c.TransType=’59’
                       AND c.ApplObj<>’202′ THEN (inqty) ELSE 0 END
                  ) AS Receipt
                 ,SUM(
                      CASE
                           WHEN c.TransType=’60’
                       AND c.ApplObj<>’202′ THEN (outqty) ELSE 0 END
                  ) AS Issue
                 ,SUM(
                      CASE
                           WHEN c.TransType=’59’
                       AND c.ApplObj=’202′ THEN (inqty) ELSE 0 END
                  ) AS ProReceipt
                 ,SUM(
                      CASE
                           WHEN c.TransType=’60’
                       AND c.ApplObj=’202′ THEN (outqty) ELSE 0 END
                  ) AS ProIssue
           FROM   oinm c
           WHERE  c.DocDate BETWEEN @d1 AND @d2
           GROUP BY
                  itemcode
                  ,Dscription
       ) AS TT
GROUP BY
       tt.Itemcode
      ,Dscription
ORDER BY
       tt.Itemcode