get percentage of sale quantity based on items from same group verses the Part-12 item
-
So in the example I divide the quantity of each item by the quantity of 230-12. I would like to do this in the QUERY . The results will be a long list of different PartClasses, Partgroup's and PartID's and for some the Part-12 quantity will be 0
so I can't divided by that. Thank you.
select impPartClassID,impPartGroupID,omlPartID, sum(omlOrderQuantity) as qty
from salesorders
left outer join SalesOrderLines on omlSalesOrderID=ompSalesOrderID
left outer join parts on impPartID=omlPartID
where ompCreatedDate >='03-01-2019' and impPartClassID in ('FGI','FGM')
and impPartGroupID in(select impPartGroupID from Parts where impPartID like '%-12' and
impPartClassID in ('FGI','FGM'))group by impPartClassID,impPartGroupID,omlPartID
order by impPartClassID,impPartGroupID,omlPartID -
This is the answer. Per a suggestion from Barand from Php Freaks
SELECT pt.impPartGroupID
,sl.omlPartID
,sum(sl.omlExtendedPriceBase) AS Total
,sum(sl.omlOrderQuantity) AS qty
,sum(sl.omlOrderQuantity) / q12 AS pcent
FROM m1_kf.dbo.SalesOrders so
LEFT JOIN m1_KF.dbo.SalesOrderLines sl ON omlSalesOrderID = ompSalesOrderId
LEFT JOIN m1_KF.dbo.Parts pt ON impPartID = omlPartID
LEFT JOIN (
SELECT imppartgroupid
,sum(omlOrderQuantity) AS q12
FROM m1_kf.dbo.SalesOrders
LEFT JOIN m1_KF.dbo.SalesOrderLines ON omlSalesOrderID = ompSalesOrderId
LEFT JOIN m1_KF.dbo.Parts ptt ON impPartID = omlPartID
WHERE ompCreatedDate > '03-01-2019'
AND impPartClassID IN (
'FGI'
,'FGM'
)
AND omlpartid LIKE '%-12'
GROUP BY impPartGroupID
) AS cte ON cte.impPartGroupID = omlPartGroupID
WHERE ompCreatedDate > '03-01-2019'
AND impPartClassID IN (
'FGI'
,'FGM'
)
GROUP BY pt.impPartGroupID
,omlPartID
,cte.q12
ORDER BY impPartGroupID
,omlPartID -
kat35601 - Thursday, March 21, 2019 2:24 PM
That may be AN answer, but I don't think it is THE answer. I believe the following will perform much better, because you don't have to read/scan each of the tables multiple times. It's completely untested, since you didn't provide consumable data.
SELECT
pt.impPartGroupID
, sl.omlPartID
, sum(sl.omlExtendedPriceBase) AS Total
, sum(sl.omlOrderQuantity) AS qty
, 1.0 * sum(sl.omlOrderQuantity) / NULLIF(SUM(SUM(q12)) OVER(PARTITION BY pt.impPartGroupID), 0) AS pcent
FROM m1_kf.dbo.SalesOrders so
LEFT JOIN m1_KF.dbo.SalesOrderLines sl
ON omlSalesOrderID = ompSalesOrderId
LEFT JOIN m1_KF.dbo.Parts pt
ON impPartID = omlPartID
OUTER APPLY
(
SELECT omlOrderQuantity
WHERE omlpartid LIKE '%-12'
) cte(q12)
WHERE ompCreatedDate > '03-01-2019'
AND impPartClassID IN ('FGI','FGM')
GROUP BY pt.impPartGroupID,omlPartID
ORDER BY impPartGroupID,omlPartIDYou may need to add a CTE to get the windowed function to operate properly.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
Drew that works very well. Thanks You
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply