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 40 41 42 43
|
SELECT DISTINCT
AO_NUM, AO_DEVIS_NUM, AO_REF_CHANTIER, AO_MAITRE_OUVRAGE, AO_SECTEUR, AO_DATE_CDE, AO_DATE_LIMITE, AO_RESPONSABLE, AO_LOT,
AO_NUM_SEMAINE
FROM
(SELECT DISTINCT
[ADAM SAS PROD$Job].No_ AS AO_NUM, [ADAM SAS PROD$Sales Header].No_ AS AO_DEVIS_NUM,
[ADAM SAS PROD$Job].Description AS AO_REF_CHANTIER, [ADAM SAS PROD$Job].[Bill-to Name] AS AO_MAITRE_OUVRAGE,
[ADAM SAS PROD$Job].[Job Post Code] AS AO_SECTEUR, [ADAM SAS PROD$Job].[Date Commande Dossier] AS AO_DATE_CDE,
[ADAM SAS PROD$Job].[Date Limite Depot Dossier] AS AO_DATE_LIMITE, [ADAM SAS PROD$Salesperson_Purchaser].Name AS AO_RESPONSABLE,
[ADAM SAS PROD$Sales Comment Line].Comment AS AO_LOT, DATEPART(ISO_WEEK, [ADAM SAS PROD$Job].[Date Limite Depot Dossier])AS AO_NUM_SEMAINE
FROM
[ADAM SAS PROD$Sales Comment Line] RIGHT OUTER JOIN
[ADAM SAS PROD$Sales Header] ON [ADAM SAS PROD$Sales Comment Line].No_ = [ADAM SAS PROD$Sales Header].No_ LEFT OUTER JOIN
[ADAM SAS PROD$Salesperson_Purchaser] ON
[ADAM SAS PROD$Sales Header].[Salesperson Code] = [ADAM SAS PROD$Salesperson_Purchaser].Code RIGHT OUTER JOIN
[ADAM SAS PROD$Job] ON [ADAM SAS PROD$Sales Header].[Job No_] = [ADAM SAS PROD$Job].No_
WHERE
([ADAM SAS PROD$Job].[Date Limite Depot Dossier] <> CONVERT(DATETIME, '1753-01-01 00:00:00', 102)) AND
([ADAM SAS PROD$Sales Comment Line].Comment LIKE 'Lot%') AND (DATEPART(ISO_WEEK, [ADAM SAS PROD$Job].[Date Limite Depot Dossier])
>= @numSemaine) AND (DATEPART(ISO_WEEK, [ADAM SAS PROD$Job].[Date Limite Depot Dossier]) <= @numSemaine + @horizon) AND
(YEAR([ADAM SAS PROD$Job].[Date Limite Depot Dossier]) = YEAR(GETDATE()))
UNION
SELECT DISTINCT
[ADAM SAS PROD$Job_1].No_ AS AO_NUM, [ADAM SAS PROD$Sales Header_1].No_ AS AO_DEVIS_NUM,
[ADAM SAS PROD$Job_1].Description AS AO_REF_CHANTIER, [ADAM SAS PROD$Job_1].[Bill-to Name] AS AO_MAITRE_OUVRAGE,
[ADAM SAS PROD$Job_1].[Job Post Code] AS AO_SECTEUR, [ADAM SAS PROD$Job_1].[Date Commande Dossier] AS AO_DATE_CDE,
[ADAM SAS PROD$Job_1].[Date Limite Depot Dossier] AS AO_DATE_LIMITE, [ADAM SAS PROD$Salesperson_Purchaser_1].Name AS AO_RESPONSABLE,
[ADAM SAS PROD$Sales Comment Line_1].Comment AS AO_LOT, DATEPART(ISO_WEEK, [ADAM SAS PROD$Job_1].[Date Limite Depot Dossier]) AS AO_NUM_SEMAINE
FROM
[ADAM SAS PROD$Sales Comment Line] AS [ADAM SAS PROD$Sales Comment Line_1] RIGHT OUTER JOIN
[ADAM SAS PROD$Sales Header] AS [ADAM SAS PROD$Sales Header_1] ON
[ADAM SAS PROD$Sales Comment Line_1].No_ = [ADAM SAS PROD$Sales Header_1].No_ LEFT OUTER JOIN
[ADAM SAS PROD$Salesperson_Purchaser] AS [ADAM SAS PROD$Salesperson_Purchaser_1] ON
[ADAM SAS PROD$Sales Header_1].[Salesperson Code] = [ADAM SAS PROD$Salesperson_Purchaser_1].Code RIGHT OUTER JOIN
[ADAM SAS PROD$Job] AS [ADAM SAS PROD$Job_1] ON [ADAM SAS PROD$Sales Header_1].[Job No_] = [ADAM SAS PROD$Job_1].No_
WHERE
([ADAM SAS PROD$Job_1].[Date Limite Depot Dossier] <> CONVERT(DATETIME, '1753-01-01 00:00:00', 102)) AND
([ADAM SAS PROD$Sales Comment Line_1].Comment LIKE 'Lot%') AND (DATEPART(ISO_WEEK, [ADAM SAS PROD$Job_1].[Date Limite Depot Dossier])
>= @numSemaine) AND (DATEPART(ISO_WEEK, [ADAM SAS PROD$Job_1].[Date Limite Depot Dossier]) <= @numSemaine + @horizon) AND
(YEAR([ADAM SAS PROD$Job_1].[Date Limite Depot Dossier]) = YEAR(GETDATE()) + 1) AND (DATEPART(ISO_WEEK,
[ADAM SAS PROD$Job_1].[Date Limite Depot Dossier]) + @horizon > 52)) AS derivedtbl_1
ORDER BY AO_NUM_SEMAINE |
Partager