Different Group By based on values in other column

Question

I have two tables; SalesData and AllOffers.

SalesData table has Brand (can never be null), Product type (can be null), Lot (can never be null), Size of product (can never be null), Stage (can never be null), Province (will be null if Stage = "WIP"), Packed On Date (can never be null) and Quantity (can never be null).

AllOffers table has Province (can never be null), Offer number (can never be null), Product Type (can be null), Brand (can never be null), Product Size (can never be null), Lot (can never be null), Packed on date (can never be null), Quantity (can never be null), and Offer Status (can never be null).

I have an application with two panes. On the left pane, the user creates an "Offer" based on the lots available in SalesData, and the right pane, shows the SalesData table with an additional "Available Quantity" column that is calculated on the fly as the user creates an offer.

The data needs to be grouped by Province, Stage, Product Type, Size, and Packed On date to provide an Available Quantity. I am having trouble grouping my data because of Nulls in Province and Product Type.

This is what I have

SELECT
        SalesData.Province,
        SalesData.Stage,
        SalesData.ProdType,
        SalesData.Brand, 
        SalesData.Size,
        SalesData.Lot,
        SalesData.PackedOn,
        SalesData.Qty, 
        CAST(SalesData.Qty AS decimal(12, 0)) - CAST(AllOffersSub.Qty AS decimal(12, 0)) AS Available 
    FROM
        SalesData LEFT OUTER JOIN 
        (
            SELECT
                    Province,
                    Brand,
                    Stage,
                    Lot,
                    Size,
                    PackedOn,
                    ProdType, 
                    SUM(CAST(Quantity AS decimal(12, 0))) AS Qty
                FROM
                    AllOffers
                WHERE
                    (OfferStatus = 'Unconfirmed')
                OR  (OfferStatus = 'Confirmed')
                GROUP BY
                    Province,
                    Brand,
                    Stage,
                    Lot,
                    Size,
                    PackedOn,
                    ProdType
        ) AS AllOffersSub ON
        AllOffersSub.Brand = SalesData.Brand
    AND AllOffersSub.Lot = SalesData.Lot
    AND AllOffersSub.Size = SalesData.Size
    AND AllOffersSub.PackedOn = SalesData.PackedOn
    AND AllOffersSub.Stage= SalesData.Stage
    AND CASE
            WHEN SalesData.Province IS NULL THEN 1 
            WHEN AllOfferssub.Province = SalesData.Province THEN 1 
            ELSE 0 END =1 
    AND CASE 
            WHEN SalesData.ProdType IS NULL AND AllOffersSub.ProdType IS NULL THEN 1 
            WHEN AllOfferssub.ProdType = SalesData.ProdType THEN 1  
            ELSE 0  
        END = 1;

The problem is that a lot can show up as WIP (work in progress, which means the province=Null) and FG (finished good, which means the province will not be null). For such a lot, the grouping does not take place, and I end up with one lot with same packed on date, Stage, Product Type, Size, showing up as many times as the number of orders pertaining to that lot.

Is there a way to group selectively based on a criterion? For example, if stage = "WIP" then group by Stage, Product Type, Size, and Packed On date else group by Stage, Product Type, Size, and Packed On date ?

This is the sample of the Offer table:

ID|Customer|OfferNum|Stage|ProdType|Brand|Size|Lot|PackedOn|Quantity|Status
1 |A       | A1     |WIP  | Null   |test |Tst |aaa|1/1/2020|100     |Confirmed
2 |B       | B1     |WIP  | Null   |test |Tst |aaa|1/1/2020|100     |Unconfirmed
3 |C       | C1     |WIP  | Null   |test |Tst |aaa|1/1/2020|100     |Unconfirmed
4 |C       | C1     |FG   | Null   |test |Tst |aaa|1/1/2020|100     |Unconfirmed

This is the sample of the SalesData table:

ID|Brand|ProdType|Lot|Size|Stage|Customer|PackedOn|Qty
1 |test | Null   |aaa|Tst |WIP  |Null    |1/1/2020|1000
2 |test | Null   |aaa|Tst |FG   |C       |1/1/2020|1000

The result of the query is:

Customer|Stage|ProdType|Brand|Size|Lot|PackedOn|Qty |Available
Null    |WIP  | Null   |test |Tst |aaa|1/1/2020|1000|900
Null    |WIP  | Null   |test |Tst |aaa|1/1/2020|1000|900
Null    |WIP  | Null   |test |Tst |aaa|1/1/2020|1000|900
C       |FG   | Null   |test |Tst |aaa|1/1/2020|1000|900

The result should be:

Customer|Stage|ProdType|Brand|Size|Lot|PackedOn|Qty |Available
Null    |WIP  | Null   |test |Tst |aaa|1/1/2020|1000|700
C       |FG   | Null   |test |Tst |aaa|1/1/2020|1000|900

Answer 1

I made a few changes to your query, but the crux of it is that you need to change province in your subquery into

iif(stage = 'wip', null, province)

To avoid duplicating code in both the select statement and the group clause, I put it into a cross apply statement called ap and selected from it.

Other than that I just put your subquery into a common table expression, turned your case statements in your on clause into or statements, and restyled a bit.

with modifiedOffers as (

    select      ap.Province, 
                Brand, Stage, Lot, Size, PackedOn, ProdType, 
                Qty = sum(cast(Quantity as decimal(12, 0))) 
    from        @allOffers o
    cross apply (select Province = iif(stage = 'wip', null, province)) ap
    where       o.Status in ('Unconfirmed', 'Confirmed')
    group by    ap.Province, Brand, Stage, Lot, Size, PackedOn, ProdType

)

select      sd.Province, sd.Stage, sd.ProdType, sd.Brand, sd.Size, sd.Lot, sd.PackedOn, sd.Qty, 
            Available = CAST(sd.Qty AS decimal(12, 0)) - o.Qty 
from        @salesData sd
left join   modifiedOffers o  
                on o.Brand = sd.Brand
                and o.Lot = sd.Lot
                and o.Size = sd.Size
                and o.PackedOn = sd.PackedOn
                and o.Stage = sd.Stage
                and (o.Province = sd.Province or sd.Province is null)
                and (o.ProdType = sd.ProdType or sd.ProdType is null and o.ProdType is null);