Detailed Monthly Sell Thru Report

[Former Member]

Hello Everyone,

I need real help. For the past several months I have been trying to find a way to simply this process and creating queries to do it and I cant seem to make it work. Can anyone help with creating this:

Item No.

Bar Code

Item Description

YTD Purchase

Cost

Retail Price

Jan Sale Unit Qty

Feb Sale Unit Qty

Mar Sale Unit Qty

Apr Sale Unit Qty

May Sale Unit Qty

Jun Sale Unit Qty

Jul Sale Unit Qty

Aug Sale Unit Qty

Sept Sale Unit Qty

Oct Sale Unit Qty

Nov Sale Unit Qty

Dec Sale Unit Qty

YTD Sale

In Stock

I can get the sales but amount not units then but cant get the ytd or in stock correct. Please help.

Kind Regards,

Sonia

[Former Member]

Hi Sonia,

Hope this helps you.

SELECT A.ItemCode, A.ItemName,

(select max(b.CalcPrice) from OINM B where b.ItemCode = A.ItemCode) 'Cost',

(select max(b.Price) from OINM B where b.ItemCode = A.ItemCode) 'Retail Price',

(select sum(b.InQty-b.OutQty) from OINM B where b.ItemCode = A.ItemCode and year(b.DocDate) < year(getdate()) ) 'Beg. Bal.',

(select sum(b.InQty-b.OutQty) from OINM B where b.TransType in (18,19,20,21) and b.ItemCode = A.ItemCode and

year(b.DocDate) = year(getdate())) 'YTD Purchase',

(select sum(b.InQty-b.OutQty)*-1 from OINM B where b.TransType in (13,14,15,16) and b.ItemCode = A.ItemCode and

year(b.DocDate)= year(getdate()) and month(b.DocDate) = 1) 'JanQtySales',

(select sum(b.InQty-b.OutQty)*-1 from OINM B where b.TransType in (13,14,15,16) and b.ItemCode = A.ItemCode and

year(b.DocDate)= year(getdate()) and month(b.DocDate) = 2) 'FebQtySales',

(select sum(b.InQty-b.OutQty)*-1 from OINM B where b.TransType in (13,14,15,16) and b.ItemCode = A.ItemCode and

year(b.DocDate)= year(getdate()) and month(b.DocDate) = 3) 'MarQtySales',

(select sum(b.InQty-b.OutQty)*-1 from OINM B where b.TransType in (13,14,15,16) and b.ItemCode = A.ItemCode and

year(b.DocDate)= year(getdate()) and month(b.DocDate) = 4) 'AprQtySales',

(select sum(b.InQty-b.OutQty)*-1 from OINM B where b.TransType in (13,14,15,16) and b.ItemCode = A.ItemCode and

year(b.DocDate)= year(getdate()) and month(b.DocDate) = 5) 'MayQtySales',

(select sum(b.InQty-b.OutQty)*-1 from OINM B where b.TransType in (13,14,15,16) and b.ItemCode = A.ItemCode and

year(b.DocDate)= year(getdate()) and month(b.DocDate) = 6) 'JunQtySales',

(select sum(b.InQty-b.OutQty)*-1 from OINM B where b.TransType in (13,14,15,16) and b.ItemCode = A.ItemCode and

year(b.DocDate)= year(getdate()) and month(b.DocDate) = 7) 'JulQtySales',

(select sum(b.InQty-b.OutQty)*-1 from OINM B where b.TransType in (13,14,15,16) and b.ItemCode = A.ItemCode and

year(b.DocDate)= year(getdate()) and month(b.DocDate) = 😎 'AugQtySales',

(select sum(b.InQty-b.OutQty)*-1 from OINM B where b.TransType in (13,14,15,16) and b.ItemCode = A.ItemCode and

year(b.DocDate)= year(getdate()) and month(b.DocDate) = 9) 'SepQtySales',

(select sum(b.InQty-b.OutQty)*-1 from OINM B where b.TransType in (13,14,15,16) and b.ItemCode = A.ItemCode and

year(b.DocDate)= year(getdate()) and month(b.DocDate) = 10) 'OctQtySales',

(select sum(b.InQty-b.OutQty)*-1 from OINM B where b.TransType in (13,14,15,16) and b.ItemCode = A.ItemCode and

year(b.DocDate)= year(getdate()) and month(b.DocDate) = 11) 'NovQtySales',

(select sum(b.InQty-b.OutQty)*-1 from OINM B where b.TransType in (13,14,15,16) and b.ItemCode = A.ItemCode and

year(b.DocDate)= year(getdate()) and month(b.DocDate) = 12) 'DecQtySales',

(select sum(b.InQty-b.OutQty)*-1 from OINM B where b.TransType in (13,14,15,16) and b.ItemCode = A.ItemCode and

year(b.DocDate)= year(getdate())) 'YTDSales',

(select sum(b.InQty-b.OutQty) from OINM B where b.ItemCode = A.ItemCode) 'InStock'

FROM OITM A

GROUP BY A.ItemCode, ItemName

Regards,

[Former Member]

This is perfect!!!!!! Thank you! I have tried for a while (I wont even say long, just know....it was a while) =/

😃 Thank you again.

[Former Member]

Hi Sonia,

If you got the answer, please close the thread by marking correct or helpful answer.

Thanks and Welcome!

Regards,

[kothandaraman_nagarajan]

Hi,

Below query is query to get quantity of sales per month. Try to add additional fields.

declare @code as varchar(15)

set @code = ( select max(ta.cardcode) from OINV ta where ta.cardcode = [%0])

Select [a] as Cardcode, [B] as Cardname, [c] as Item#, [D] as Descr,[1] as Jan,[2] as Feb,[3]as Mar,[4] as April,[5] as May,[6] as June,[7] as July ,[8] as Aug,[9] as Sept,[10] as Oct ,[11]as Nov,[12] as Dec

from(

SELECT T0.[CardCode] as  A , T0.[CardName] as  B, T1.[ItemCode] as C, T1.[Dscription] as D, sum(T1.[Quantity]) as t,month(T0.[DocDate]) as month FROM OINV T0  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry WHERE year( T0.[DocDate]) = 2014 and t0.cardcode = @code GROUP BY T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription],T0.[DocDate] ) S

pivot

(sum(t) for month IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P

Thanks.

[Former Member]

Thank you but it should be by item per month for any and all sales. IT should say the qty sold per month and the sales amount per month. According to the header I posted. Ther one I was using was giving me errors: