cancel
Showing results for 
Search instead for 
Did you mean: 

Help With Sales / Invoice Query Monthly Summary

Former Member
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

I figured this out. For anyone else looking to combine Sales & Invoicing together for a basic forecast.


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, 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())

  Union All

     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.shipdate >= DATEADD(DAY, -180, GETDATE()) and R1.OpenQty >0

  ) x

  pivot

  (

  Sum(Qty)

  for M1 in (' + @cols + ')

  ) p

  '

execute(@query)

Answers (0)