CODEWITHSUNDEEP
sql-servert-sql
BiTZOiD
BiTZOiD

Reputation: 69

T-SQL: group date ranges and set end date based on, on/off column

I'm struggling to find an effective, concise way without a loop to produce groups of active dates where activity (grant/rescind activity) is based on the Switch column which has values 0 off and 1 on and start and end dates.

TransactionDate    EffectiveDate    TerminationDate    Switch
-------------------------------------------------------------------
2013-06-14         2013-05-29       NULL               1
2013-06-14         2013-06-05       2013-06-05         0
2013-10-03         2013-05-29       2013-05-29         0
2013-10-12         2013-05-29       NULL               1
2013-10-12         2013-06-06       2013-06-06         0

The final output should be but one row:

2013-05-29 to 2013-06-06

The output is one row because the the last two transactions were switch on for 5/29/2013 and the last switch off was for 2013-06-06, which becomes the end date for the span.

Even more, the dates should also be grouped by active spans. If there were another year record in here it would need to be on a separate row.

Can I please get some help with a query to solve this issue?

Upvotes: 1

Views: 39

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

Is this what you want?

select max(case when switch = 1 then effective_date end) as on_date,
       (case when max(case when switch = 1 then effective_date end) <
                  max(case when switch = 0 then effective_date end)
             then max(case when switch = 0 then effective_date end)
        end) as off_date  -- if any
from t;

This gets the last date the switch is on. And then the last date after that it is off.

Upvotes: 3

Related Questions