How to do a complex calculation as this sample

Question

In the stored procedure (I'm using SQL server2008), I'm having a business like this sample:

ID  City    Price   Sold    
1   A         10    3
1   B         10    5
1   A         10    1
1   B         10    3
1   C         10    5
1   C         10    2

2   A         10    1
2   B         10    6
2   A         10    3
2   B         10    4
2   C         10    3
2   C         10    4

What I want to do is:

• with each ID, sort by City first.

• After sort, for each row of this ID, re-calculate Sold from top to bottom with condition: total of Sold for each ID does not exceed Price (as the result below).

And the result like this:

ID  City    Price   Sold_Calculated 
1   A         10    3
1   A         10    1
1   B         10    5
1   B         10    1 (the last one equal '1': Total of Sold = Price)
1   C         10    0 (begin from this row, Sold = 0)
1   C         10    0

2   A         10    1
2   A         10    3
2   B         10    6
2   B         10    0 (begin from this row, Sold = 0)
2   C         10    0
2   C         10    0

And now, I'm using the Cursor to do this task: Get each ID, sort City, calculate Sold then, and save to temp table. After finish calculating, union all temp tables. But it take a long time.

What I know people advise is, DO NOT use Cursor.

So, with this task, can you give me the example (with using select form where group) to finish? or do we have other ways to solve it quickly?

I understand this task is not easy for you, but I still post here, hope that there is someone helps me to go through.

I'm very appriciated for your help.

Thanks.

Answer 1

In order to accomplish your task you'll need to calculate a running sum and use a case statement

Previously I used a JOIN to do the running sum and Lag with the case statement

However using a recursive Cte to calculate the running total as described here by Aaron Bertand, and the case statement by Andriy M we can construct the following, which should offer the best performance and doesn't need to "peek at the previous row"

WITH cte 
     AS (SELECT Row_number() 
                  OVER ( partition BY id ORDER BY id, city, sold DESC) RN, 
                id, 
                city, 
                price, 
                sold 
         FROM   table1), 
     rcte 
     AS ( 
        --Anchor 
        SELECT rn, 
               id, 
               city, 
               price, 
               sold, 
               runningTotal = sold 
        FROM   cte 
        WHERE  rn = 1 
         --Recursion 
         UNION ALL 
         SELECT cte.rn, 
                cte.id, 
                cte.city, 
                cte.price, 
                cte.sold, 
                rcte.runningtotal + cte.sold 
         FROM   cte 
                INNER JOIN rcte 
                        ON cte.id = rcte.id 
                           AND cte.rn = rcte.rn + 1) 
SELECT id, 
       city, 
       price, 
       sold, 
       runningtotal, 
       rn, 
       CASE 
         WHEN runningtotal <= price THEN sold 
         WHEN runningtotal > price 
              AND runningtotal < price + sold THEN price + sold - runningtotal 
         ELSE 0 
       END Sold_Calculated 
FROM   rcte 
ORDER  BY id, 
          rn; 

DEMO

Answer 2

As @Gordon Linoff commented, the order of sort is not clear from the question. For the purpose of this answer, I have assumed the sort order as city, sold.

select id, city, price, sold, running_sum,
       lag_running_sum,
       case when running_sum <= price then Sold
            when running_sum > price and price > coalesce(lag_running_sum,0) then price - coalesce(lag_running_sum,0)
            else 0
       end calculated_sold
       from
       (
        select id, city, price, sold,
               sum(sold) over (partition by id order by city, sold
                               rows between unbounded preceding and current row) running_sum,
               sum(sold) over (partition by id order by city, sold
                               rows between unbounded preceding and 1 preceding) lag_running_sum
          from n_test
       ) n_test_running
 order by id, city, sold;

Here is the demo for Oracle.

Let me break down the query.

I have used SUM as analytical function to calculate the running sum.

1. The first SUM, groups the rows based on id, and in each group orders the row by city and sold. The rows between clause tell which rows to be considered for adding up. Here i have specified it to add current row and all other rows above it. This gives the running sum.
2. The second one does the same thing except for, the current row is excluded from adding up. This essentially creates a running sum but lagging the previous sum by one row.

Using this result as inline view, the outer select makes use of CASE statement to determine the value of new column.

1. As long as the running sum is less than or equal to price it gives sold.
2. If it crosses the price, the value is adjusted so that sum becomes equal to price.
3. For the rest of the rows below it, value is set as 0.

Hope my explanation is quite clear.

Answer 3

To me, it sounds like you could use window functions in a case like this. Is this applicable?

Although in my case your end result would possibly look like:

ID  City Price Sold_Calculated
2   A    10    4
2   B    10    6
2   C    10    0

Which could have an aggregation like

SUM(Sold_Calculated) OVER (PARTITION BY ID, City, Price, Sold_Calculated) 

depending on how far down you want to go.. You could even use a case statement if need be

Answer 4

Are you looking to do this entirely in SQL? A simple approach would be this:

SELECT C.ID,
       C.City,
       C.Price,
       calculate_Sold_Function(C.ID, C.Price) AS C.Sold_Calculated
FROM CITY_TABLE C
GROUP BY C.City

Where calculate_Sold_Function is a T-SQL/MySQL/etc function taking the ID and Price as parameters. No idea how you plan on calculating price.