· 8 years ago · Dec 10, 2017, 08:36 PM
1---DROP PROCEDURE [dbo].[p_payroll_extend_project_dates.sql]
2---
3--- 3. Find the Max and Min Transaction Dates for the Projects and expand as required.
4--- TO-DO - Add the Project Settings to "allow" for Project extension. For now, all SI projects allow it anyway
5
6CREATE PROCEDURE [dbo].[p_payroll_extend_project_dates.sql]
7 @JobQueueID INT,
8 @ClientID INT
9AS
10BEGIN
11
12--- 3.1. Create the temp table
13
14DROP TABLE IF EXISTS tempdb.dbo.#tempProjectDates
15
16CREATE TABLE #tempProjectDates
17 (
18 ProjectID INT,
19 MaxTransactionDate DATETIME,
20 MinTranactionDate DATETIME,
21 ProjectStartDate DATETIME,
22 ProjectEndDate DATETIME
23 )
24
25--- 3.2. Get the list of distinct ProjectID's, Min, Max transaction dates,
26--- and corresponding Project Start and End Dates.
27
28SELECT PAY.ProjectID,
29 MAX(TransactionDate) AS MaxTransactionDate,
30 MIN(TransactionDate) AS MinTransactionDate,
31 ProjectStartDate,
32 ProjectEndDate
33 INTO #tempProjectDates
34 FROM STG_Payrolls PAY
35 JOIN Projects P ON PAY.ProjectID = P.ProjectID
36 WHERE JobQueueId = @JobQueueID
37 AND PAY.ProjectID IS NOT NULL
38 GROUP BY PAY.ProjectID
39
40--- 3.3. Iterate over the list and call the SP as needed..
41
42DECLARE @MyCursor CURSOR;
43 SET @MyCursor = CURSOR FORWARD_ONLY FAST_FORWARD
44 FOR
45 SELECT
46 ProjectID,
47 MaxTransactionDate,
48 MinTransactionDate,
49 ProjectStartDate,
50 ProjectEndDate
51 FROM #tempProjectDates
52
53 OPEN @MyCursor
54
55 FETCH NEXT FROM @MyCursor
56 INTO @STG_ID,
57 @STG_Type,
58 @STG_JobNumber,
59 @STG_PhaseCode,
60 @STG_WbsCode,
61
62 WHILE @@FETCH_STATUS = 0
63 BEGIN
64 BEGIN TRY
65 --- If Project Start Date > MinTransactionDate
66 BEGIN
67 EXEC p_extend_project_date @STG_ProjectID, @STG_TransactionDate;
68 END
69
70 --- If Project End Date < MinTransactionDate
71 BEGIN
72 EXEC p_extend_project_date @STG_ProjectID, @STG_TransactionDate;
73 END