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
|
select Customer_Id, ContactLensSubscriptionStatus_Id , Date_Id#Start , Date_Id#End
,RANK() OVER (PARTITION BY customer_id ORDER BY Date_Id#Start) AS Rank ,
case when ContactLensSubscriptionStatus_Id in (3)
and (select MAX(id) from Date where Year = 2019 and WeekOfYear = 1 ) >= Date_Id#Start
and (select MIN(id) from Date where Year = 2019 and WeekOfYear = 1 ) <= Date_Id#End then 1
when ContactLensSubscriptionStatus_Id in (1,2)
and (select MAX(id) from Date where Year = 2019 and WeekOfYear = 1 ) >= Date_Id#Start then 1
else 0
end as toto
from ContactLensSubscription
where Customer_Id is not null
UNION
select Customer_Id, ContactLensSubscriptionStatus_Id , Date_Id#Start , Date_Id#End
,RANK() OVER (PARTITION BY customer_id ORDER BY Date_Id#Start) AS Rank ,
case when ContactLensSubscriptionStatus_Id in (3)
and (select MAX(id) from Date where Year = 2019 and WeekOfYear = 2 ) >= Date_Id#Start
and (select MIN(id) from Date where Year = 2019 and WeekOfYear = 2 ) <= Date_Id#End then 1
when ContactLensSubscriptionStatus_Id in (1,2)
and (select MAX(id) from Date where Year = 2019 and WeekOfYear = 2 ) >= Date_Id#Start then 1
else 0
end as toto
from ContactLensSubscription
where Customer_Id is not null
UNION
....
--Jusqu'à 25 |
Partager