T-SQL Syntax help

[former_member239601]

Hi

Could someone help me please, with some T-SQL syntax?

My query is intended to identify from SAP B1 details about the quantities of product sold in a specified period, belonging to a specified item group in order to perform a calculation to identify a proposed minimum stock holding.

We do use MRP already but this report is intended to provide an overview without running the scenarios.

Although the query is working generally, it is putting an entry per item each time a itemcode is listed in the specified period but I want to group them so there is one cumulative entry per itemcode, so the calculation of the proposed minimum is working from one total value.

Can someone help me to correct my syntax so that I can get the itemcode grouped?

SELECT distinct

a.CardCode

,  a.ITmsGrpCod

, a.[ItemCode]

, a.[ItemName]

, Sum(b.OnHand) as 'On Hand'

, a.OrdrIntrvl

, a.MinOrdrQty

, a.LeadTime

, a.InvntItem

, a.validFor

, a.[Quantity] as 'Sold Qty on inv in period'

, SUM(a.Quantity) / 20 * a.LeadTime as 'Prop. Min Stock Qty (count of sold qty divide by 20 mult by lead time)'

FROM OITM a INNER JOIN OITW b

ON a.ItemCode = b.ItemCode

LEFT JOIN INV1 c

ON c.ItemCode = a.ItemCode

WHERE c.DocDate >= '2015-06-01 00:00:00.000'

GROUP BY

a.CardCode

,  a.ITmsGrpCod

, a.[ItemCode]

, a.[ItemName]

, a.OrdrIntrvl

, a.MinOrdrQty

, a.LeadTime

, a.InvntItem

, a.validFor

, a.[Quantity]

HAVING

a.ItmsGrpCod = 193

Thanks very much in advance!

Jon

[kothandaraman_nagarajan · Accepted solution]

Hi,

Try this query:

SELECT distinct

a.CardCode

,  a.ITmsGrpCod

, a.[ItemCode]

, a.[ItemName]

, Sum(b.OnHand) as 'On Hand'

, a.OrdrIntrvl

, a.MinOrdrQty

, a.LeadTime

, a.InvntItem

, a.validFor

, c.[Quantity] as 'Sold Qty on inv in period'

, SUM(c.Quantity) / 20 * a.LeadTime as 'Prop. Min Stock Qty (count of sold qty divide by 20 mult by lead time)'

FROM OITM a INNER JOIN OITW b

ON a.ItemCode = b.ItemCode

LEFT JOIN INV1 c

ON c.ItemCode = a.ItemCode

WHERE c.DocDate >= '2015-06-01 00:00:00.000'

GROUP BY

a.CardCode

,  a.ITmsGrpCod

, a.[ItemCode]

, a.[ItemName]

, a.OrdrIntrvl

, a.MinOrdrQty

, a.LeadTime

, a.InvntItem

, a.validFor

, c.[Quantity]

HAVING

a.ItmsGrpCod = 193

Thanks

[former_member239601 · Accepted solution]

Hi Nagarajan

Thanks for reply, I see that you changed a line in the Group By to group by c.quantity and not a.quantity but its still not grouping the itemcodes cumulatively when there is a repeated instance in the selection.

Is there anything else i can try?

Thanks

[kothandaraman_nagarajan · Accepted solution]

Hi,

try this:

SELECT distinct

a.CardCode

,  a.ITmsGrpCod

, a.[ItemCode]

, a.[ItemName]

, Sum(b.OnHand) as 'On Hand'

, a.OrdrIntrvl

, a.MinOrdrQty

, a.LeadTime

, a.InvntItem

, a.validFor

, Sum(c.[Quantity]) as 'Sold Qty on inv in period'

, SUM(c.Quantity) / 20 * a.LeadTime as 'Prop. Min Stock Qty (count of sold qty divide by 20 mult by lead time)'

FROM OITM a INNER JOIN OITW b

ON a.ItemCode = b.ItemCode

LEFT JOIN INV1 c

ON c.ItemCode = a.ItemCode

WHERE c.DocDate >= '2015-06-01 00:00:00.000'

GROUP BY

a.CardCode

,  a.ITmsGrpCod

, a.[ItemCode]

, a.[ItemName]

, a.OrdrIntrvl

, a.MinOrdrQty

, a.LeadTime

, a.InvntItem

, a.validFor

HAVING

a.ItmsGrpCod = 193

[former_member239601 · Accepted solution]

Hi Nagarajan

That's great, done the trick... I see that I was applying the Sum function incorrectly.

Thanks very much indeed for your help