CODEWITHSUNDEEP
t-sql
DauleDK
DauleDK

Reputation: 3443

TSQL: Flag 3 days, starting from 3 workdays ago

My aim is to build a flag, indicating the last 3 days, with the condition that 3 workdays must have passed. Under the assumption that todays date is the 27th of february:

I have the following input:

input:

date              workday
----------        -------
2017-02-27        1
2017-02-26        0
2017-02-25        0
2017-02-24        1
2017-02-23        1
2017-02-22        1
2017-02-21        1
2017-02-20        1
2017-02-19        1
2017-02-18        1
2017-02-17        0

Desired output:

date              workday    flag
----------        -------    ----
2017-02-27        1          0
2017-02-26        0          0
2017-02-25        0          0
2017-02-24        1          0
2017-02-23        1          0
2017-02-22        1          1
2017-02-21        1          1
2017-02-20        1          1
2017-02-19        1          0

I have been working with several ideas, but nothing good.

Upvotes: 0

Views: 26

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522211

One option here, though perhaps not the most optimal, would be to use a correlated subquery to tally workdays for the two previous days and current day. In cases where this tally be 3, we print 1 as the flag, otherwise we print 0.

WITH cte AS (
    SELECT date, workday,
           (SELECT SUM(t2.workday) FROM yourTable t2
            WHERE t2.date BEWTEEN DATEADD(day, -2, t1.date) AND t1.date) AS w_sum
    FROM yourTable t1
)

SELECT t.date, t.workday,
       CASE WHEN t.w_sum = 3 THEN 1 ELSE 0 END AS flag
FROM cte t

Note that this answer assumes that you have continuous data, day by day. If not, then we have to consider how to even handle breaks in a sequence of days.

Upvotes: 1

Related Questions