Bonjour,

Je suis plutôt débutant en sql server.

Je souhaite faire une union de la même requête lancée sur des périodes différentes.

en gros je souhaite faire l'équivalent de :

Ce qui va varier c'est la variable WeekOfYear 1 à 23
Code : Sélectionner tout - Visualiser dans une fenêtre à part
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
ESt-ce que c'est possible ?

Merci d'avance de votre aide.