Skip to Content
0
Former Member
May 30, 2016 at 01:26 AM

Help With Sales / Invoice Query Monthly Summary

28 Views

Hi,

I'm trying to develop a query which combines sales and invoicing into a monthly report.

As shown below lines 1 & 2

Line 1 is the Previous Invoicing for a particular item code

Line 2 is the Sales Order Lines going into a future date.

I would like to combine this into one line. like shown below. That way I can see the previous sales for an item and the future sales orders

I have the following query which unions the Sales and Invoicing tables. I'm just stuck as to how to roll this one up.

DECLARE @cols AS NVARCHAR(MAX),
  @query  AS NVARCHAR(MAX)


select @cols = STUFF((SELECT ',' + QUOTENAME(convert (varchar(7), ShipDate,20)) as M1 
                    from RDR1 I1
  where shipdate <= DATEADD(DAY, 120, GETDATE()) And shipdate >= DATEADD(DAY, -180, GETDATE())
                    group by convert (varchar(7), ShipDate,20)
                    order by convert (varchar(7), ShipDate,20) desc
            FOR XML PATH(''), TYPE 
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'') 


set @query = 'Select ItemCode, ' + @cols + ' from 


  (SELECT ItemCode, Code, ItemName, CardName, ' + @cols + ' from 


  (
  Select I1.ItemCode, I1.SuppCatNum Code, I1.ItemName, C0.CardName, R1.Quantity as Qty, convert (varchar(7), R1.ShipDate,20) M1 
  From INV1 R1 
  Inner Join OINV R0 on R1.DocEntry = R0.DocEntry 
  Inner Join OITM I1 on R1.ItemCode = I1.ItemCode 
  Inner Join OCRD C0 on I1.CardCode = C0.CardCode
  Where R0.Canceled=''N'' And R1.shipdate <= DATEADD(DAY, -1, GETDATE()) And R1.shipdate >= DATEADD(DAY, -180, GETDATE()) and I1.ItemCode=''0024991''
  ) x


  pivot 
  (
  Sum(Qty)
  for M1 in (' + @cols + ')
  ) p 


  union all


  SELECT ItemCode, Code, ItemName, CardName, ' + @cols + ' from 
                (
  Select I1.ItemCode, I1.SuppCatNum Code, I1.ItemName, C0.CardName, R1.OpenQty as Qty, convert (varchar(7), R1.ShipDate,20) M1 
  From RDR1 R1 
  Inner Join OINV R0 on R1.DocEntry = R0.DocEntry 
  Inner Join OITM I1 on R1.ItemCode = I1.ItemCode 
  Inner Join OCRD C0 on I1.CardCode = C0.CardCode
  Where R0.Canceled=''N'' And R1.OpenQty >0 and I1.ItemCode=''0024991''
  ) x


  pivot 
  (
  Sum(Qty)
  for M1 in (' + @cols + ')
  ) p 
  ) z 
  '
execute(@query)

I'm open to an alternative approach if this has been done before

thanks

Brendan

Attachments

qry-1.jpg (42.3 kB)
qry-2.jpg (26.2 kB)