CODEWITHSUNDEEP
sqlsql-serverwindow-functions
Kelly W
Kelly W

Reputation: 121

I want a query for multiple windowed results (SQL Server 2012+)

Date (A)    B           C        D      E           F          G        H       I           
27-Feb-15   -100000.00  2,000           2,000       50         -2000    2000    -100,000.00             
02-Mar-15   -80000.00   1,000           3,000       40         -2000    4000    -80,000.00          
29-May-15   30000.00            -600    2,400       30           800    3200     24,000.00          
09-Jul-15   500000.00           -1,200  1,200       20          1600    1600     32,000.00          
26-Oct-15   -2000       800             2,000       40          -50     1650    -2,000.00           
20-Nov-15   1500000             -2,000  0           30          1650    0       49,500.00

The table above indicates some financial data that needs to be calculated. Columns E, G-I are fields that need to be calculated with a single query without cursors. If this were an excel, the formula for the row 3 would be as following:

E: =SUM(C3:D3)+E2

G: =IF(B3<0, B3/F3, -H2*(D3/E2))

H: =H2-G3

I: =F3*G3

Note: If you are doing this in excel, you need to leave a blank row as row 1 to avoid reference errors.

I think the trickiness for me lies in the fact that you may need multiple windowed functions (using lag or sum over partition), which I'm not sure how to use with SQL-Server, as both columns E and G depend on previous columns results.

Any help is appreciated.

EDIT: The correct insert statement:

insert into t values
 ('27-Feb-15',-100000.00,2000,null,null,50,null,null,null)
 ,('02-Mar-15',-80000.00,1000,null,null,40,null,null,null)
,('29-May-15',30000.00,null,-600,null,30,null,null,null)
,('09-Jul-15',500000.00,null,-1200,null,20,null,null,null)
,('26-Oct-15',-2000,800,null,null,40,null,null,null)
,('20-Nov-15',1500000,null,-2000,null,30,null,null,null);

Upvotes: 1

Views: 84

Answers (1)

SqlZim
SqlZim

Reputation: 38023

Using lag():

select 
    [Date] 
  , B
  , C
  , D
  , E = (isnull(c,0)+isnull(d,0)) 
        + isnull(lag(e) over (order by [date]),0)
  , F
  , G = convert(decimal(9,2),case 
        when b < 0 
          then b/f
        else (lag(h) over (order by [date])*-1)
            *(d/lag(e) over (order by [date]))
        end)
  , H = lag(h) over (order by [date]) - G
  , I = convert(decimal(9,2),f*g)
from t
order by [date]

test setup: http://rextester.com/GRSY3137

returns:

+------------+------------+---------+----------+---------+-------+----------+---------+------------+
|    Date    |     B      |    C    |    D     |    E    |   F   |    G     |    H    |     I      |
+------------+------------+---------+----------+---------+-------+----------+---------+------------+
| 2015-02-27 | -100000.00 | 2000.00 | NULL     | 2000.00 | 50.00 | -2000.00 | NULL    | -100000.00 |
| 2015-03-02 | -80000.00  | 1000.00 | NULL     | 3000.00 | 40.00 | -2000.00 | 4000.00 | -80000.00  |
| 2015-05-29 | 30000.00   | NULL    | -600.00  | 2400.00 | 30.00 | 800.00   | 3200.00 | 24000.00   |
| 2015-07-09 | 500000.00  | NULL    | -1200.00 | 1200.00 | 20.00 | 1600.00  | 1600.00 | 32000.00   |
| 2015-10-26 | -2000.00   | 800.00  | NULL     | 2000.00 | 40.00 | -50.00   | 1650.00 | -2000.00   |
| 2015-11-20 | 1500000.00 | NULL    | -2000.00 | 0.00    | 30.00 | 1650.00  | 0.00    | 49500.00   |
+------------+------------+---------+----------+---------+-------+----------+---------+------------+

Upvotes: 2

Related Questions