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 36 37 38 39
| declare @park char(6) ;
set @park = 'Parc 3' ;
declare @date date ;
set @date = '2023-12-18' ;
with LV_lvoit(LV_ident, LV_lieudep, LV_dtdep, LV_lieuarr, LV_dtarr)
as (select 1, 'Parc 1', '2023-12-12', 'Parc 6', '2023-12-20' union all
select 2, 'Parc 6', '2023-12-18', 'Parc 1', '2023-12-20' union all
select 3, 'Parc 2', '2023-12-15', 'Parc 3', '2023-12-18' union all
select 4, 'Parc 6', '2023-12-16', 'Parc 3', '2023-12-17'
)
, LL_ligne (LV_ident, LL_seq, BI_ident)
as (select 1, 1, 206 union all
select 1, 2, 208 union all
select 2, 1, 208 union all
select 2, 2, 206 union all
select 3, 1, 300 union all
select 4, 1, 400
)
select subq.LV_lieuarr
, sum(subq.nbr)
from
(select LV.LV_ident
, LL_seq
, LV_lieudep
, LV_lieuarr
, case when LV_lieudep = @park then -1
when LV_lieuarr = @park then +1
else 0
end as nbr
from LV_lvoit as LV
inner join LL_ligne as LL
on LL.LV_ident = LV.LV_ident
where LV_dtdep <= @date
and LV_dtarr >= @date
) subq
where subq.LV_lieuarr=@park
group by subq.LV_lieuarr
; |
Partager