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 44 45 46 47 48 49 50
| ALTER PROCEDURE [dbo].[usp_Extract_Activities]
(
@ProjectName NVARCHAR(MAX)
)
AS
BEGIN
SET NOCOUNT ON
SELECT [Project_PWA].[dbo].[MSP_GET_Project] (p.[PROJ_UID], t.[TASK_UID]) AS [CodeProjet],
T.[TASK_ID]
INTO #Temp
FROM [dbo].[MSP_ASSI]A
LEFT JOIN [dbo].[MSP_PROJECTS]P ON A.[PROJ_UID]=P.[PROJ_UID]
LEFT JOIN [dbo].[MSP_TASKS]T ON A.[TASK_UID]=T.[TASK_UID]
LEFT JOIN [dbo].[MSP_RES]R ON A.[RES_UID]=R.[RES_UID]
LEFT JOIN [dbo].[MSP_RES_CUST_VALUES]B ON A.[RES_UID]=B.[RES_UID]
LEFT JOIN [dbo].[MSP_CUSTOM_F]C ON B.[MD_PROP_ID]=C.[MD_PROP_ID]
LEFT JOIN [dbo].[MSP_LOOKUP]D ON B.[CODE_VALUE]= D.[LT_STRUCT_UID]
WHERE p.[PROJ_NAME] =@ProjectName AND
D.LT_VALUE_TEXT IS NOT NULL
--DEBUT - Il faut afficher les projets dans l'ordre de création des tâches
SELECT DISTINCT [CodeProjet],
CASE WHEN CHARINDEX('#', [CodeProjet]) > 0 THEN
LEFT([CodeProjet], CHARINDEX('#', [CodeProjet]) - 1)
ELSE
CASE WHEN CHARINDEX('-', [CodeProjet]) > 0 THEN
LEFT([CodeProjet], CHARINDEX('-', [CodeProjet]) - 1)
ELSE
[CodeProjet]
END
END AS [CodeOnly],
[TASK_ID]
INTO #Results
FROM #Temp
WHERE [CodeProjet] IS NOT NULL
SELECT [CodeProjet]
FROM (
SELECT [CodeProjet], [CodeOnly], MIN([TASK_ID]) as [TASK_ID]
FROM #Results
GROUP BY [CodeOnly], [CodeProjet]
) o
ORDER BY [TASK_ID] ASC
--FIN - Il faut afficher les projets dans l'ordre de création des tâches
DROP TABLE #Results
DROP TABLE #Temp
END |
Partager