Sql Server - Running Totals Based on Conditions

Question

I have been banging my head trying to come up with the correct logic (SQL Server 2012) needed to achieve something I would imagine would be fairly routine but I have been unable to find any examples of this anywhere. Basically, I have 3 columns in a table: product, flag, value. It is possible for a product to be listed multiple times within the table but only once with a unique flag (i.e. product1 can have flag1 or flag2 with different/identical but there will never be 2 records with product1 and flag1 and different/identical values).

The flag represents a pre-defined value (1,2,3,4) and the intention behind this field is to be able to assign a unique mathematical equation based on the value of the flag. The end result would yield a single product, the unique flag, and a new cumulative total based on the mathematical equation output. For instance, let's say product1 was listed 4 times with flag values of flag1, flag2, flag3, flag4 (see below):

Product-----Flag-----Value
Product1----Flag1----1.00
Product1----Flag2----3.00
Product1----Flag3----5.00
Product1----Flag4----7.00

Product-----Flag-----Value
Product1----Flag1----1.00  (flag1 value)
Product1----Flag2----4.00  (flag1+flag2 value)
Product1----Flag3----6.00  (flag1+flag3 value)
Product1----Flag4----10.00 (flag2+flag4 value)

Flag1 is defined as add flag1 only. Flag2 is defined as add flag1 and flag2. Flag 3 is defined as add flag1 and flag 3. Flag 4 is defined as add flag2 and flag4. the new output would be product1 listed four times with flag values of flag1, flag2, flag3, flag4 but new values as flag1, flag1_flag2, flag1+flag3, flag2+flag4.

I have tried to apply the logic via a case statement but I can't figure out how to traverse all the products for each condition and I have tried to go with a running totals solution but I am not sure how to incorporate the flag condition into it so it only performs a running total for when those conditions are true. Any assistance and/or article to help get me going down the right path would be greatly appreciated.

Is the "unique mathematical equation" based on the flag fixed for all time, or is there some variance? Will different products have different calculations for their flags? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Sep 22, 2014 at 13:20
the flag equation is fixed for all products so all products will share the same mathematical equation. Corrected the product name. Thanks! — TStewartFan
– TStewartFan, Commented Sep 22, 2014 at 13:51

jpw · Accepted Answer · 2014-09-22 13:50:15Z

1

While I'm not sure I fully understand your question I think this might be what you want. For this to work it assumes flag1 is always present when flags 1 through 3 are and that flag2 is present when flag4 is.

;with cte as (
    select 
       product, 
       max(case when flag = 'Flag1' then Value end) as f1Value,
       max(case when flag = 'Flag2' then Value end) as f2Value,
       max(case when flag = 'Flag3' then Value end) as f3Value,
       max(case when flag = 'Flag4' then Value end) as f4Value
    from flags group by Product
)

select 
    flags.Product,
    flags.Flag,
    flags.Value as "Org. value",
    case flag 
       when 'Flag1' then f1Value 
       when 'Flag2' then f1Value + f2Value
       when 'Flag3' then f1Value + f3Value
       when 'Flag4' then f2Value + f4Value
       else flags.Value -- take the present value when flag is not Flag1-4
     end as "New value"    
from flags
inner join cte on flags.Product = cte.Product

Take a look at this Sample SQL Fiddle to see it in action.

answered Sep 22, 2014 at 13:50

jpw

45k6 gold badges71 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

TStewartFan Over a year ago

Sorry for the delay in writing back but I just got home from school. Apparently I can potentially have duplicate products with the same flag because of overlapping date ranges so the max() is currently excluding those values. Basically I have two additional columns start date, stop date. On some days I could have overlapping products and there is no guarantee that I will have at least 1 flag of each type.

jpw Over a year ago

@TStewartFan Ok, so how do you want to handle the duplicate rows? If you want to sum them (according to the same formula as before) and don't care about dates, maybe this is what you need? sqlfiddle.com/#!3/967cc/2

TStewartFan Over a year ago

I have the day off to devote to this equation and every path I take leads me farther away from my true answer. The dates are important because they represent the start time of the cost. For instance, in your example product2/flag1 on 01/01 should have a cumulative cost of 1.00 associated to it. However product2/flag1 on 01/02 should have a cumulative cost of 12.00 associated to it.

jpw Over a year ago

@TStewartFan I don't think I can solve this without devoting a lot more time than I can spare, pretty sure it's solvable though. Good luck!

TStewartFan Over a year ago

Actually your solution is getting me real close. Hopefully once I get back home from school, I will be able to hash out my remaining failed test cases. Thanks!

Damien_The_Unbeliever · Accepted Answer · 2014-09-22 14:33:12Z

0

You can join a table to itself, and pick the conditions appropriately:

SELECT p1.product,p1.Flag,p1.Value + COALESCE(p2.Value,0)
FROM
    Products p1
        left join
    Products p2
        on
           p1.Product = p2.Product and
           p2.Flag = CASE p1.Flag
                       --1 doesn't need a previous value
                       WHEN 2 THEN 1
                       WHEN 3 THEN 1
                       WHEN 4 THEN 2
                     END

answered Sep 22, 2014 at 14:33

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

Comments

Dinesh Reddy · Accepted Answer · 2014-09-24 17:13:20Z

I assumed and tried on Range values.

CREATE TABLE #tmp (Product VARCHAR(10), flag VARCHAR(10),value numeric(13,2))
GO
INSERT INTO #tmp
    SELECT 'Product1' , 'Flag1',1
    UNION
SELECT 'Product1' , 'Flag2',3
    UNION
SELECT 'Product1' , 'Flag3',5
    UNION
SELECT 'Product1' , 'Flag4',7
GO
            ;WITH cte
            AS
    (
    SELECT row_number () OVER(
            ORDER BY flag)  'row',*
        FROM #tmp
    )
SELECT  *,value 'RT'
    FROM    cte
    WHERE    row = 1
    UNION
SELECT    * ,(
    SELECT  cte.value
        FROM cte
        WHERE row  = 1
    )                    + value 'RT'
    FROM cte
    WHERE row BETWEEN 2 
        AND 3
    UNION
SELECT    * ,(
    SELECT  cte.value
        FROM cte
        WHERE row  =2
    )                    + value 'RT'
    FROM cte
    WHERE row >3
GO
DROP TABLE #tmp

Collectives™ on Stack Overflow

Sql Server - Running Totals Based on Conditions

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related