How do I calculate a ratio between rows based on criteria in the data in SQL Server?

user_1229591 0
Viewed 179

I have a scenario in which I need to determine the ratio of a value in one row with the total of all values in a several rows. This is simple enough using window functions, I take the value and divide by the SUM(Value) OVER(...) the relevant rows. What complicates this is that I want the relevant rows to be dictated not by a row count but instead using the value in another column compared to the aggregate of the value in question.

Specifically, if the Back value is 20, I would like to get the ratio between C1 and C2 going back enough Weeks such that the SUM(Value) for those Weeks is greater than 20, but the fewest number of weeks that it takes to surpass 20..

Here is an example of my scenario:

example data

The Ratio column is the ratio between

SUM(Value) OVER(PARTITION BY Comp ORDER BY Week ROWS ... )

and Back. The part that I cannot solve is the ... where the window criteria is set. I have attempted this using CTE, but that didn't get me any further than simply using analytic functions.

Example data:

DECLARE @data TABLE (Week INT, Comp VARCHAR(8), Value INT, Back INT);

INSERT INTO @data VALUES (1,'C1',2,10);
INSERT INTO @data VALUES (1,'C2',8,10);
INSERT INTO @data VALUES (2,'C1',4,20);
INSERT INTO @data VALUES (2,'C2',6,20);
INSERT INTO @data VALUES (3,'C1',3,18);
INSERT INTO @data VALUES (3,'C2',7,18);
INSERT INTO @data VALUES (4,'C1',5,8);
INSERT INTO @data VALUES (4,'C2',5,8);
sql-server-2012 sql-server
1 Answers

This is a guess, as I can't test as the sample data can't be consumed, but perhaps this?

CASE Back WHEN 10 THEN Value / (Back * 1.)
          WHEN 20 THEN SUM(Value) OVER (PARTITION BY Week ORDER BY Comp
                                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) / (Back * 1.)
END AS Ratio
user_20299830
Related