Get and filter dates SQL

Question

I have this table without pk

| Peo  | Pos | DATE_BEGIN | DATE_END   | XXXX |
+------+-----+------------+------------+------+
| 9302 | 8   | 2017-10-02 | 2017-12-31 | NULL |
| 9302 | 8   | 2018-01-01 | 2018-01-01 | NULL |
| 9302 | 8   | 2018-01-02 | 2018-05-31 | NULL |
| 9302 | 8   | 2018-06-01 | 9999-12-31 | NULL |

And I want to get the time pass in Pos 8 from the older DATE_BEGIN to the newer Date_END that could be 9999-12-31 when undefined in which case would be the actual date. For this table would be 2017-10-02 to 9999-12-31 (2020/06/23, actual date).

One problem is that for the table below it should only get the last row as is the total time that the person is in the Pos because it was changed. so will be (2018-06-01) - (9999-12-31 - actual date)

| Peo  | Pos | DATE_BEGIN | DATE_END   | XXXX |
+------+-----+------------+------------+------+
| 9302 | 8   | 2017-10-02 | 2017-12-31 | NULL |
| 9302 | 8   | 2018-01-01 | 2018-01-01 | NULL |
| 9302 | 7   | 2018-01-02 | 2018-05-31 | NULL |
| 9302 | 8   | 2018-06-01 | 9999-12-31 | NULL |

"could be 9999-12-31 when undefined" - so, someone was not knowing what NULL means in SQL and now you ahve to deal with a broken data model? IThought we got rid of that in 2000. — TomTom, Jun 23 '20 at 20:16

score 0 · Accepted Answer · answered Jun 23 '20 at 19:35

I think that you want:

select
    peo,
    datediff(
        day, 
        min(date_begin), 
        case when max(date_end) = '9999-12-31' then cast(getdate() as date) else max(date_end) end
    ) days
from mytable t
where not exists (
    select 1 
    from mytable t1 
    where 
        t1.peo = t.peo 
        and t1.date_begin > t.date_end 
        and t1.pos <> 8
)
group by peo

For each peo, the where clause filters the table on the last series of records whose pos is 8. Then the query aggregates by peo, and computes the difference between the minimum date_begin and the maximum date_end (if the date_end is '9999-12-31', we use the current date instead).

I changed the pos comparison to include other positions. This solved my problem, will get the number of days for current user position. where not exists ( select 1 from mytable t1 where t1.peo = t.peo and t1.date_begin > t.date_end and t1.pos <> **t.pos** ) group by peo — Luis, Jun 23 '20 at 23:55

shawnt00 · Answer 2 · 2020-06-23T21:22:05.993

This is a typical gaps and islands problem. You're looking to find the final sequence of rows (per peo) with pos = 8.

So for each peo you'll want to count backward by date looking for breaks in the sequence. After the groups are numbers this way, you'll keep only the first such grouping and then aggregate:

with data as (
    select *,
        sum(case when pos = 8 then 0 else 1 end)
            over (partition by peo order by date_begin desc) as brk
    from TblX t
    -- this is unnecessary if the latest row is always pos = 8
    where date_begin <= (
        select max(t2.date_begin) from TblX t2
        where t2.peo = t.peo and t2.pos = 8
    )
)
select
    peo,
    min(date_begin) as date_begin,
    case max(date_end)
            when '9999-12-31' then cast(getdate() as date)
            else max(date_end) end as date_end,
    datediff(day,
        min(date_begin),
        case max(date_end)
            when '9999-12-31' then cast(getdate() as date)
            else max(date_end) end
    ) as days_difference
from data
where brk = 0
group by peo;

This approach is adaptable to returning more than just the most recent grouping. GMB's answer is quite similar otherwise.

Get and filter dates SQL

2 Answers2