Decreasing Days Count SQL

Question

I have a very simple requirement but I'm struggling to find a way around this. However, I understand it may be better using code rather than SQL.

I have a very simple query:

SELECT
  ServiceCode,
  StartDate,
  Available
FROM @tmpAvailability
ORDER BY StartDate;

Which produces an output like this:

Code    |    Dates    |    Available
TEST    | 2018-01-04  |        1
TEST    | 2018-01-05  |        1
TEST    | 2018-01-06  |        0    
TEST    | 2018-01-07  |        0
TEST    | 2018-01-08  |        0
TEST    | 2018-01-09  |        0
TEST    | 2018-01-10  |        1
TEST    | 2018-01-11  |        1
TEST    | 2018-01-12  |        1
TEST    | 2018-01-13  |        0

What I need is a decreasing number of days available, for example:

Code    |    Dates    |    Available    |  Days Available
TEST    | 2018-01-04  |        1        |        2
TEST    | 2018-01-05  |        1        |        1
TEST    | 2018-01-06  |        0        |        0
TEST    | 2018-01-07  |        0        |        0
TEST    | 2018-01-08  |        0        |        0
TEST    | 2018-01-09  |        0        |        0
TEST    | 2018-01-10  |        1        |        3
TEST    | 2018-01-11  |        1        |        2
TEST    | 2018-01-12  |        1        |        1
TEST    | 2018-01-13  |        0        |        0

Please, explain better your logic for days available algorithm — Joe Taras, Jan 04 '18 at 11:24
Seems you want to create query for something what is called: `running sum`. Check this: [Calculate a Running Total in SQL Server](https://stackoverflow.com/questions/860966/calculate-a-running-total-in-sql-server) — Maciej Los, Jan 04 '18 at 11:27
Ok, so the available column states whether that corresponding date is available on that day. What I need in the Days available column is the number of days ahead of that date that are also available until the available column is 0. It needs to repeat this each time the available column is 1 — user3455191, Jan 04 '18 at 11:28
Would a running sum not increment by however much each time? I need it to sum the number of days ahead and then decrease the closer it gets to available 0. — user3455191, Jan 04 '18 at 11:32
It looks like you need a running sum in reverse date order, with a reset whenever a zero is encountered. You could do this either with a recursive query, or by implementing a group number on each series of 1's, and then simply run-summing by partitioning within each group. — Steve, Jan 04 '18 at 12:37

Rams · Accepted Answer · 2018-01-04T13:28:19.990

By using row_number with partition on available column and simple row_number we can achieve the output

SELECT code,dates,available, 
CASE WHEN days_available=-1 THEN 0 ELSE ROW_NUMBER() over(PARTITION BY  days_available ORDER BY dates desc) END   as days_available
FROM (
        SELECT  code,dates,available,CASE WHEN available=0 THEN -1 ELSE  rn1-rn2 END AS days_available  
        FROM (
            SELECT  k.*,ROW_NUMBER() over(ORDER BY dates) rn1,ROW_NUMBER() over(PARTITION BY available ORDER BY dates) rn2  
            FROM    bltg.mytable k
            ) AS T1
    ) fin ORDER BY dates

score 0 · Answer 2 · answered Jan 04 '18 at 15:06

Thought this might be a slight improvement on @Rams' answer. Note, by using the SUM function, the zero availability groups automatically sum to zero, so I can eliminate the logic that was there purely to handle those cases in terms of ROW_NUMBER.

It will also accommodate different codes in the same table (if that's a possibility).

SELECT
    code
    ,dates
    ,available
    ,SUM(available) OVER (PARTITION BY code, available, avail_group_num ORDER BY dates DESC) AS days_available

FROM
    (
        SELECT
            *
            ,( ROW_NUMBER() OVER (PARTITION BY code ORDER BY dates ASC)
                - ROW_NUMBER() OVER (PARTITION BY code, available ORDER BY dates ASC) 
                ) AS avail_group_num

        FROM
            tmpAvailability

    ) AS grouped_availability

ORDER BY
    dates ASC

And the results:

code       dates      available   days_available
---------- ---------- ----------- --------------
TEST       2018-01-04 1           2
TEST       2018-01-05 1           1
TEST       2018-01-06 0           0
TEST       2018-01-07 0           0
TEST       2018-01-08 0           0
TEST       2018-01-09 0           0
TEST       2018-01-10 1           3
TEST       2018-01-11 1           2
TEST       2018-01-12 1           1
TEST       2018-01-13 0           0

(10 rows affected)

Decreasing Days Count SQL

2 Answers2