Skip to Content
0
Sep 21, 2016 at 08:40 AM

Forecasting query written

239 Views

Hi

I need some guidance re this query as below, i cannot get the forecast data in OFCT and FCT1 to populate in the columns, i want one forecast code with the relevant monthly figures to appear in the column so a select function i think would be needed in query?

Also i have a problem where duplicate lines appear on inventory items, i guess my joins are not set properly, i have been adding to this query for our business and getting to complex for me as i am still a novice at writing queries.

select distinct t1.ItemCode, (T1.Dscription), t2.frgnname, t3.whscode, isnull(T6.Descr, 'Trading') AS 'Contract or Trading', t2.iscommited as 'Committed', T2.OnOrder as "Open PO's",

T5.ItmsGrpNam AS 'Customer Group', T4.FirmName, t3.OnHand as 'SOH',

sum(t1.Quantity) AS 'Total Sales 3 Mnth', sum(t1.Quantity)/3 AS 'Average 3mnth',

max(T1.Quantity) AS 'MAX', min(T1.Quantity) AS 'MIN' ,

sum(Case DATENAME(month,t1.DocDate) when 'May' then t1.Quantity else 0 end) as 'May Sales Qty',

sum(Case DATENAME(month,t7.Date) when 'May' then t7.quantity else 0 end) as 'May Fcst Qty',

sum(Case DATENAME(month,t1.DocDate) when 'June' then t1.Quantity else 0 end) as 'June Sales Qty',

sum(Case DATENAME(month,t7.Date) when 'June' then t7.Quantity else 0 end) as 'June Fcst Qty',

sum(Case DATENAME(month,t1.DocDate) when 'July' then t1.Quantity else 0 end) as 'July Sales Qty',

sum(Case DATENAME(month,t7.Date) when 'July' then t7.Quantity else 0 end) as 'July Fcst Qty',

sum(Case DATENAME(month,t1.DocDate) when 'August' then t1.Quantity else 0 end) as 'Aug Sales Qty',

sum(Case DATENAME(month,t7.Date) when 'August' then t7.Quantity else 0 end) as 'Aug Fcst Qty'

from ODLN T0 inner join DLN1 t1 on t0.DocEntry = t1.DocEntry

inner join OITM T2 on t1.ItemCode = T2.ItemCode

inner join OITW T3 on T1.ItemCode = T3.ItemCode

inner join OMRC t4 on T2.FirmCode = T4.FirmCode

inner join OITB T5 on t2.ItmsGrpCod = t5.ItmsGrpCod

left join UFD1 t6 on t2.[U_Chain] = t6.[FldValue] and tableid = 'oitm'

left outer join fct1 t7 on t1.itemcode = t7.absid and tableid = 'fct1'

left outer join ofct t8 on t7.absid = t8.absid

where t0.CardCode not in ('ZT01','ZK01') and t0.DocDate > getdate() - 90 and t3.whscode <> 99

group by t1.ItemCode, T1.Dscription, T6.Descr, T5.ItmsGrpNam, T4.FirmName, t3.OnHand, t3.whscode, t2.iscommited, t2.frgnname, T2.OnOrder

Hope someone can assist with this query or give guidance.