Jump to content
kat35601

divided all quantity of items in a part group by the part that ends in -12 of the same part group

Recommended Posts

Hello, I have a  query that I need to divided all quantity of items in a part group by the part that ends in -12 of the same part group

so every part in a 110 partgroup needs to be divided by the Qty of 110-12

example 110-01 with a Qty of 214 would be divided by the qty of part 110-12 which is 355 for a results of .6028.. or 60.28% and then move to the next number in the

Part group 110-02 and so on.

 

I have attached a jpeg of a spreadsheet that shows this.

 

Selection_270.png

Share this post


Link to post
Share on other sites
<?php
$connect =odbc_connect(removed);
if(!$connect) {
    exit("Connection Failed: " . $connect);
}

$sql="select  impPartGroupID, omlPartID, sum(omlOrderQuantity) as Qty, sum(omlExtendedPriceBase) as Total
FROM   m1_kf.dbo.SalesOrders SO
left join  m1_KF.dbo.SalesOrderLines on omlSalesOrderID=ompSalesOrderId
left join m1_KF.dbo.Parts on impPartID=omlPartID
where ompCreatedDate >'01-01-2019' and impPartGroupID in (select impPartGroupID from m1_KF.dbo.Parts where impPartID like '%-12' and impPartClassID in ('FGI','FGM') )
group by  Parts.impPartGroupID, omlPartID
order by  Parts.impPartGroupID, omlPartID
";
$result =odbc_exec($connect,$sql);
if(!$result){
exit("Error in SQL");
}
echo "<table><tr>";
echo "<th>PartGroup</th>";
echo "<th>PartID</th>";
echo "<th>Qty</th>";
echo "<th>Total</th>";
 while ($row = odbc_fetch_array($result))
{

    echo "<tr><td>" .$row['impPartGroupID'] ."</td>";
    echo "<td>" .$row['omlPartID'] ."</td>";
    echo "<td>" .$row['Qty'] ."</td>";
    echo "<td>" .$row['Total'] ."</td></tr>";
 }


odbc_close($connect);

?>

My query

Share this post


Link to post
Share on other sites
Posted (edited)

try

SELECT partgroup
     , partid
     , quantity
     , total
     , round(quantity / q12 * 100, 2) as pcent
FROM part
JOIN (
        SELECT partgroup
             , quantity as q12
        FROM part
        WHERE RIGHT(partid,2) = '12'
     ) as p12 USING (partgroup);

 

Edited by Barand

Share this post


Link to post
Share on other sites

This is what I have if I run the two queries separately  they work fine but together I get erroneous results. lots of the same item over and over again. I could not get the USING to work. so I am sure it's the ON statement but that's the only way I could get it to run.

 

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
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 omlPartGroupID=omlPartGroupID


where ompCreatedDate >'03-01-2019' and impPartClassID in('FGI','FGM')
group by  pt.impPartGroupID, omlPartID, cte.q12
order by  impPartGroupID, omlPartID

 

 

Share this post


Link to post
Share on other sites

That appears to be a far cry from the problem that you posted.

I see you are starting a table subquery with "join (".

What I can't see is a ") aliasname" where the subquery ends.

Share this post


Link to post
Share on other sites

yes I took you suggestion and adapted it to work with my tables. The join aliasname is cte.

Thanks  for the help any suggestion on why I am getting the erroneous results

Share this post


Link to post
Share on other sites

it works thank you.

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

 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.