Simplify sql query(date to period)

Question

Need to get table like this: Target table

what I used:

Select ID_CLIENT, BALANCE, HIST_DT as Start_dt, isnull(lead(hist_dt) over (partition by id_client order by hist_dt asc), '2999.12.31') as End_dt
from (
select ID_CLIENT, ID_STATUS, balance, hist_dt, lag(id_status) over (partition by id_client order by id_status) as Prev_ID_status
from Client_History) a
where a.ID_STATUS = a.Prev_ID_status or a.ID_STATUS = 1
order by ID_CLIENT, HIST_DT

I think its very complicated. Will be glad to hear any suggestions of simplyfing this query.

I think you can make a query on whenever your `Balance` changes for `id_client` — PIG, Dec 28 '19 at 20:15

Gordon Linoff · Answer 1 · 2019-12-29T22:38:16.457

0

This is a gaps-and-islands problem, most easily solved with the difference of row numbers and aggregation:

select id_client, balance, min(hist_dt), max(hist_dt)
from (select ch.*,
             row_number() over (partition by id_client order by balance_hist_dt) as seqnum,
             row_number() over (partition by id_client, balance order by balance_hist_dt) as seqnum_2
      from client_history ch
     ) ch
group by id_client, balance, (seqnum - seqnum_2)
order by id_client, min(hist_dt);

Why this works is a little tricky to describe. But if you look at the results of the subquery, you will see how the difference captures adjacent rows with the same balance.

edited Dec 29 '19 at 22:38

answered Dec 28 '19 at 16:53

Gordon Linoff

1,242,037
58
646
786

thanks for your answer. Read some information about this problem. But dont quite get the first underscore in balance_hist_dt in your query – Stepnosound Dec 28 '19 at 17:27
@Stepnosound . . . I don't know what you mean by "get the first underscore". – Gordon Linoff Dec 28 '19 at 22:55
There are two column: balance, and hist_dt. And in your query they stick together with "_" – Stepnosound Dec 29 '19 at 07:29
@Stepnosound . . . I revised the answer. – Gordon Linoff Dec 29 '19 at 22:38

Simplify sql query(date to period)

1 Answers1