Purchase query update

[former_member798634]

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

[jitin_chawla]

Hi,

Are you looking to remove the items where PrchseItem is marked as 'N' in OITM. If yes, try the following:

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  and i.PrchseItem <> 'N'

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

Kr,

Jitin

[Johan_H]

Although less so nowadays (if at all anymore), in earlier versions of B1 certain fields could be NULL untill set. That means that in these cases i.PrchseItem <> 'N', although otherwise correct, would not work reliably.

Also, although in this case the effect is probably negligible, checking for a positive is more efficient than checking for a negative.

As such I recommend using this syntax: WHERE ISNULL(i.PrchseItem, 'N') = 'Y'

[former_member798634]

Good morning Johan and Jitin,

Thanks for your help. Only it doesn't work correctly. Some items marked as purchase item don't appear in this list.

Any thoughts on what this could be? Please advise.

regards,

Mark

[Johan_H]

Hi Mark,

It is because you have use INNER JOIN for OITW. Please change this also to LEFT OUTER JOIN. Change this:

INNER JOIN OITW w

to this:

LEFT OUTER JOIN OITW w

Regards,

Johan

[former_member798634]

Good morning Johan,

Thank you very much. Now i get the inactive items as well in the list, what else should i change?

Please advise.

regards