Monday 8 February 2016

Previous Month Total Sales Qty in PO Fms Query

;WITH CTE_INVQty AS
(
Select
Case when t1.Targettype =  '13' then (T1.Quantity*0)
     when t1.TrgetEntry IS NULL and t1.BaseEntry is null then (T1.Quantity)
     when t1.Targettype = '16' and t1.BaseEntry is null then (T1.Quantity)
End  'Qty' ,
 t0.DocDate , t0.docnum , t0.docentry ,
case when t0.doctype = 'I' then 'Delivery'
     when t0.doctype = 's' then 'Delivery' End 'Doctype'
From ODLN T0
  Inner Join DLN1 T1 on T0.DocEntry = T1.DocEntry
where
T1.ItemCode = $[$38.1.0]
and
month(T0.[DocDate]) = month(getdate())-1
and
year(T0.[DocDate]) = year(getdate())

Union All-- Sales Return
Select -(T1.Quantity)[Qty] , t0.DocDate , t0.docnum , t0.docentry ,
case when t0.doctype = 'I' then 'Return'
     when t0.doctype = 's' then 'Return' End 'Doctype'
From ORDN T0
  Inner Join RDN1 T1 on T0.DocEntry = T1.DocEntry
where
T1.ItemCode = $[$38.1.0]
and
month(T0.[DocDate]) = month(getdate())-1
and
year(T0.[DocDate]) = year(getdate())

union all
Select
Case when basetype = '17' then (T1.Quantity)
     when t1.TrgetEntry IS NULL and t1.BaseEntry is null then (T1.Quantity)
     when basetype = '15' then (T1.Quantity)
End 'Qty' ,
 t0.DocDate , t0.docnum , t0.docentry ,
case when t0.doctype = 'I' then 'AR Invoice'
     when t0.doctype = 's' then 'AR Invoice' End 'Doctype'
From OINV T0
  Inner Join INV1 T1 on T0.DocEntry = T1.DocEntry
where
T1.ItemCode = $[$38.1.0]
and
month(T0.[DocDate]) = month(getdate())-1
and
year(T0.[DocDate]) = year(getdate())

Union All-- AR Credit Memo

Select
(-T1.Quantity)[Qty] ,
 t0.DocDate ,
 t0.docnum ,
 t0.docentry ,
case when t0.doctype = 'I' then 'AR Cr.Memo'
     when t0.doctype = 's' then 'AR Cr.Memo' End 'Doctype'
From ORIN T0 Inner Join RIN1 T1 on T0.DocEntry = T1.DocEntry
where T1.ItemCode = $[$38.1.0]
             and
month(T0.[DocDate]) = month(getdate())-1
             and
year(T0.[DocDate]) = year(getdate())
)
Select Sum(A.QTy) as [Total Qty] ,'' as [Docdate], '' as [DocNum] , '' as [Docentry] from CTE_INVQty A

1 comment:

  1. TitaniumPrice Per Pound (VAT) in T-Shirt | T-Shirt
    Buy this T-Shirt: TitaniumPrice Per Pound (VAT). T-Shirt. Designed thunder titanium lights by. T.D. Smith. T-Shirt. T-Shirt. titanium tv apk Sizes: Size. titanium framing hammer 8.7 x burnt titanium 8.6 titanium water bottle cm x 5 cm. Rating: 4.5 · ‎7 reviews · ‎$37.50 · ‎In stock

    ReplyDelete