Skip to Content
0
Aug 14, 2015 at 11:40 AM

T-SQL Syntax help

27 Views

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