How to select group of codes and represent a specific weight in SQL?

user_12129150 0
Viewed 43

We have product codes like so:

0001101 -- 1kg
0001396 -- 6 x 1kg
0001400 -- 10 x 1kg

xxxxyyy = x represents the product code and y represents the weight code.

I am aiming to group them together as an individual pack size.

So I had help to get this current query:

DECLARE @date Date;
SET @date = '2020-11-26';

SELECT p.code AS ProductCode, 
        p.description AS ProductDescription,
        SUM(requiredQty * v.factor) as RequiredQty,
        p.weight
FROM SalesOrderLine sol JOIN 
        SalesOrder so
        ON sol.salesOrderID = so.id JOIN
        Product p 
        ON sol.productID = p.id CROSS APPLY
        (VALUES (CASE WHEN p.code LIKE '%101' THEN 1
                    WHEN p.code LIKE '%396' THEN 6     -- Because it is  6 x 1kg
                    WHEN p.code LIKE '%400' THEN 10     -- Because it is  10 x 1kg
                END)
        ) v(factor)
WHERE so.dueDate = @date AND
        v.factor IS NOT NULL
GROUP BY p.code, p.description, p.weight order by p.code;

This is the current output:

enter image description here

The codes ending with 396 and 400 RequiredQty column now represents individual pack sizes.

As an example, the code 0004400 was originally 10 x 1kg and had requiredQty of 1. Now it is 10 because in the query I have changed it to represent individual pack sizes. This means there is 10 packs of 1kg.

Desired output

enter image description here

I need to add the same product codes together. How would I do that?

sql-server sql
1 Answers

You just need to fix the aggregation:

SELECT LEFT(p.code, 4) AS ProductCode, p.description AS ProductDescription,
       SUM(requiredQty * v.factor) as RequiredQty,
       '1 kg' as weight
FROM SalesOrderLine sol JOIN 
        SalesOrder so
        ON sol.salesOrderID = so.id JOIN
        Product p 
        ON sol.productID = p.id CROSS APPLY
        (VALUES (CASE WHEN p.code LIKE '%101' THEN 1
                    WHEN p.code LIKE '%396' THEN 6     -- Because it is  6 x 1kg
                    WHEN p.code LIKE '%400' THEN 10     -- Because it is  10 x 1kg
                END)
        ) v(factor)
WHERE so.dueDate = @date AND
      v.factor IS NOT NULL
GROUP BY LEFT(p.code, 4), p.description
ORDER BY LEFT(p.code, 4);
user_11440350
Related