1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
| with calendar (date)
as
(
select cast(getdate() as date)
union all
select dateadd(day, -1, date) from calendar where date > dateadd(day, -4, cast(getdate() as date))
),
last (cle1, cle2, cle3, der)
as
(
select h.cle1, h.cle2, h.cle3, max(h.date)
from historique h
group by h.cle1, h.cle2, h.cle3
),
intervalle (cle1, cle2, cle3, deb, fin, valeur)
as
(
select h.cle1, h.cle2, h.cle3, h.date, min(isnull(n.date, dateadd(day, 1, cast(getdate() as date)))), h.valeur
from historique h
inner join last l on l.cle1 = h.cle1 and l.cle2 = h.cle2 and l.cle3 = h.cle3 and l.der <= h.date
left outer join historique n on n.cle1 = h.cle1 and n.cle2 = h.cle2 and n.cle3 = h.cle3 and n.date > h.date
where h.date > dateadd(day, -30, cast(getdate() as date))
group by h.cle1, h.cle2, h.cle3, h.date, h.valeur
),
complet (cle1, cle2, cle3, date, valeur)
as
(
select i.cle1, i.cle2, i.cle3, c.date, i.valeur
from intervalle i
inner join calendar c on c.date >= i.deb and c.date < i.fin
where c.date >= dateadd(day, -4, cast(getdate() as date))
)
select c.cle1, c.cle2, c.cle3, max(case when c.date > dateadd(day, -3, cast(getdate() as date)) then c.valeur else 0 end), max(case when c.date > dateadd(day, -4, cast(getdate() as date)) then c.valeur else 0 end)
from complet c
group by c.cle1, c.cle2, c.cle3 |
