CODEWITHSUNDEEP
sqlt-sqlsql-server-2016
MISNole
MISNole

Reputation: 1062

Dividing value in one row by value in second row - based on the same grouping

What I am hoping to accomplish is divide the closed claims by the open claims for each month after a hurricane. In the example below, I show the desired output - I am trying to divide the number of closed claims by the number of open claims to get a percentage to display on a report. My example shows the % Closed values in only the "C" rows - but it would not matter if the same values are in the "O" open row as well - I can use a row number function to ensure I'm only displaying the values once on the report.

I appreciate all of the responses - they all appear to get me to my desired outcome - just need to figure out which approach works the best for my situation. Thanks!

enter image description here

Sample data:

CREATE TABLE #Hurricanes
(
    Hurricane VARCHAR (50) NOT NULL,
    MonthNumber INT NOT NULL,
    CurrentClaimStatus CHAR (1) NOT NULL,
    ClaimCount INT NOT NULL
);

INSERT INTO #Hurricanes
(
    Hurricane,
    MonthNumber,
    CurrentClaimStatus,
    ClaimCount
)
VALUES
('Hurricane Michael', 1, 'C', 22),
('Hurricane Michael', 1, 'O', 344),
('Hurricane Michael', 2, 'C', 62),
('Hurricane Michael', 2, 'O', 354),
('Hurricane Michael', 3, 'C', 93),
('Hurricane Michael', 3, 'O', 357),
('Hurricane Michael', 4, 'C', 126),
('Hurricane Michael', 4, 'O', 365),
('Hurricane Michael', 5, 'C', 174),
('Hurricane Michael', 5, 'O', 369),
('Hurricane Michael', 6, 'C', 219),
('Hurricane Michael', 6, 'O', 369),
('Hurricane Michael', 7, 'C', 242),
('Hurricane Michael', 7, 'O', 372),
('Hurricane Michael', 8, 'C', 252),
('Hurricane Michael', 8, 'O', 373),
('Hurricane Michael', 9, 'C', 258),
('Hurricane Michael', 9, 'O', 373),
('Hurricane Michael', 10, 'C', 263),
('Hurricane Michael', 10, 'O', 376),
('Hurricane Michael', 11, 'C', 274),
('Hurricane Michael', 11, 'O', 377),
('Hurricane Michael', 12, 'C', 283),
('Hurricane Michael', 12, 'O', 377);

SELECT *
FROM #Hurricanes
ORDER BY Hurricane,
         MonthNumber,
         CurrentClaimStatus;

DROP TABLE #Hurricanes;

Upvotes: 0

Views: 819

Answers (5)

Gordon Linoff
Gordon Linoff

Reputation: 1270411

If I understand correctly, you just want cumulative sums:

SELECT Hurricane, MonthNumber,
       SUM(CASE WHEN CurrentClaimStatus = 'O' THEN ClaimCount ELSE 0 END) OVER (PARTITION BY Hurricane, MonthNumber) as openclaims,
       SUM(CASE WHEN CurrentClaimStatus = 'C' THEN ClaimCount ELSE 0 END) OVER (PARTITION BY Hurricane, MonthNumber) as closedclaims,
       (SUM(CASE WHEN CurrentClaimStatus = 'C' THEN ClaimCount ELSE 0.0 END) OVER (PARTITION BY Hurricane, MonthNumber) /
       SUM(CASE WHEN CurrentClaimStatus = 'O' THEN ClaimCount END) OVER (PARTITION BY Hurricane, MonthNumber)
      ) as ratio
FROM #Hurricanes h
ORDER BY Hurricane, MonthNumber;

Upvotes: 1

Kevin
Kevin

Reputation: 781

I don't think you need a row_number function to solve your issue... I've included also the year...

Query

SELECT  h1.*, CASE WHEN h1.CurrentClaimStatus = 'C' THEN CAST(h1.ClaimCount * 1.0 / h2.ClaimCount AS varchar(25) ) ELSE '' END AS [%Closed]
FROM    #Hurricanes h1
    left outer join #Hurricanes h2 
       on h2.Hurricane = h1.Hurricane and h2.YearNumber = h1.YearNumber and h2.MonthNumber = h1.MonthNumber and h2.CurrentClaimStatus = 'O'

Result

Hurricane         | YearNumber | MonthNumber | Status | ClaimCount | %Closed
===================================================================================
Hurricane Michael | 2019       | 1           | C      | 22         | 0.063953488372
Hurricane Michael | 2019       | 1           | O      | 344        |
Hurricane Michael | 2019       | 2           | C      | 62         | 0.175141242937
Hurricane Michael | 2019       | 2           | O      | 354        |
Hurricane Michael | 2019       | 3           | C      | 93         | 0.260504201680
Hurricane Michael | 2019       | 3           | O      | 357        |
Hurricane Michael | 2019       | 4           | C      | 126        | 0.345205479452
Hurricane Michael | 2019       | 4           | O      | 365        |
...
...

Upvotes: 1

BarneyL
BarneyL

Reputation: 1362

This approach pivots the data into one row for each month:

WITH ClaimsPivot AS
(
SELECT 
Hurricane, MonthNumber,
SUM(IIF(CurrentClaimStatus = 'C', ClaimCount,NULL)) AS ClosedClaims,
SUM(IIF(CurrentClaimStatus = 'O', ClaimCount,NULL)) AS OpenClaims
FROM #Hurricanes
GROUP BY Hurricane,
         MonthNumber
)
SELECT 
Hurricane, MonthNumber,ClosedClaims,OpenClaims,
1.0 * ClosedClaims / OpenClaims AS ClaimsPct
FROM ClaimsPivot

Upvotes: 1

Squirrel
Squirrel

Reputation: 24783

use conditional case statement with window sum()

[% Closed]  = case  when    CurrentClaimStatus = 'C' 
              then  
                    sum(case when CurrentClaimStatus = 'C' then ClaimCount end) over (partition by MonthNumber) * 1.0
                /   sum(case when CurrentClaimStatus = 'O' then ClaimCount end) over (partition by MonthNumber)
              end

Upvotes: 1

JMabee
JMabee

Reputation: 2300

Based on your data this will get you the desired result. Obviously if you needed to cross over more years you would have to change it up a bit

SELECT t1.Hurricane, t1.MonthNumber,t1.ClaimCount OpenCount, t2.ClaimCount CloseCount, t2.ClaimCount / (t1.ClaimCount * 1.0) [% Closed]
FROM #Hurricanes t1
INNER JOIN #Hurricanes t2 on t1.MonthNumber = t2.MonthNumber and t2.CurrentClaimStatus = 'C'
WHERE t1.CurrentClaimStatus = 'O'
ORDER BY Hurricane,
         MonthNumber

Upvotes: 1

Related Questions