Skip to Content
0
Jan 24 at 05:09 PM

Purchase query update

56 Views

Good afternoon,

I have a query what gives me a proposal on what to order from my suppliers:

SELECT i.ItemCode,

i.CodeBars AS Barcode,

'' AS LevCode,

COALESCE(SOLD.Dscription, i.ItemName, '') AS [Artikel naam],

i.frozenFor,

c.CardName AS [Standaard Leverancier],

SOLD.WhsCode AS [Magazijn],

CAST(SUM(Sold.Qty) as decimal(8,2)) AS [Totaal verkocht (6 weken)],

CAST(isnull(SUM(CASE WHEN DATEDIFF(WEEK, SOLD.DocDate, GETDATE()) = 6 THEN SOLD.Qty ELSE 0 END),0) AS DECIMAL(8,2)) AS [Week 6],

CAST(isnull(SUM(CASE WHEN DATEDIFF(WEEK, SOLD.DocDate, GETDATE()) = 5 THEN SOLD.Qty ELSE 0 END),0) AS DECIMAL(8,2)) AS [Week 5],

CAST(isnull(SUM(CASE WHEN DATEDIFF(WEEK, SOLD.DocDate, GETDATE()) = 4 THEN SOLD.Qty ELSE 0 END),0) AS DECIMAL(8,2)) AS [Week 4],

CAST(isnull(SUM(CASE WHEN DATEDIFF(WEEK, SOLD.DocDate, GETDATE()) = 3 THEN SOLD.Qty ELSE 0 END),0) AS DECIMAL(8,2)) AS [Week 3],

CAST(isnull(SUM(CASE WHEN DATEDIFF(WEEK, SOLD.DocDate, GETDATE()) = 2 THEN SOLD.Qty ELSE 0 END),0) AS DECIMAL(8,2)) AS [Week 2],

CAST(isnull(SUM(CASE WHEN DATEDIFF(WEEK, SOLD.DocDate, GETDATE()) = 1 THEN SOLD.Qty ELSE 0 END),0) AS DECIMAL(8,2)) AS [Week 1],

CAST(isnull(SUM(CASE WHEN DATEDIFF(WEEK, SOLD.DocDate, GETDATE()) < 1 THEN SOLD.Qty ELSE 0 END),0) AS DECIMAL(8,2)) AS [Week 0],

CAST(SUM(Sold.Qty)/6 AS DECIMAL(8,2)) AS [Gemiddelde verkoop per week],

CAST(w.OnHand AS DECIMAL(8,0)) AS [In magazijn],

CAST(w.OnOrder AS DECIMAL(8,0)) AS [Besteld Leverancier],

CAST(w.IsCommited AS DECIMAL(8,0)) AS [In Bestelling klanten],

CAST((w.OnHand + w.OnOrder - w.IsCommited) AS DECIMAL(8,0)) AS [Beschikbaar],

i.InvntryUom,

CAST((CASE

WHEN isnull(SUM(Sold.Qty),0)=0 THEN 99

ELSE ((w.OnHand + w.OnOrder - w.IsCommited)/SUM(Sold.Qty)/6)

END) AS DECIMAL(10,0)) AS [Gemiddeld beschikbaar per week],

(CASE

WHEN i.BuyUnitMsr = i.InvntryUom THEN i.BWeight1

ELSE i.BWeight1/i.NumInBuy

END) AS [Gewicht inkoop],

(CASE

WHEN i.BuyUnitMsr = i.InvntryUom THEN i.BVolume

ELSE i.BVolume/i.NumInBuy

END) AS [Volume inkoop]

FROM OITM i

LEFT OUTER JOIN OCRD c ON i.CardCode = c.CardCode

LEFT OUTER JOIN (select r.ItemCode, r.Dscription, r.docdate, sum(r.quantity) as qty, r.WhsCode

from INV1 r

inner join OINV l on l.DocEntry = r.DocEntry

where isnull(l.CANCELED, 'N') = 'N'

and datediff(week, r.docdate, getdate()) <= 6

group by r.ItemCode, r.Dscription, r.docdate, r.WhsCode

union all

select r.ItemCode, r.Dscription, r.docdate, 0 - sum(r.quantity) as qty, r.WhsCode

from RIN1 r

inner join ORIN l on l.DocEntry = r.DocEntry

where isnull(l.CANCELED, 'N') = 'N'

and datediff(week, r.docdate, getdate()) <= 6

group by r.ItemCode, r.Dscription, r.docdate, r.WhsCode

) SOLD on SOLD.ItemCode = i.ItemCode

INNER JOIN OITW w ON i.ItemCode = w.ItemCode AND SOLD.WhsCode = w.WhsCode

WHERE 1=1

GROUP BY i.ItemCode,i.CodeBars, COALESCE(SOLD.Dscription, i.ItemName, ''),

i.CardCode, i.frozenfor, i.InvntryUom, w.OnHand,

w.OnOrder, w.IsCommited,i.BWeight1, I.BVolume,

I.NumInBuy, I.InvntryUom, I.BuyUnitMsr,

c.CardName, SOLD.WhsCode

ORDER BY 4, 7




With this query I also see the items which i Unmarked as purchase item. Can anyone help me on this query so i don't see the items anymore which I don't want to buy anymore.

Hopefully you can help me.

Regards