DAUPXNJz

1CREATE PROCEDURE [dbo].[p_import_payroll_process_3]
2    @JobQueueID INT,
3    @ClientID   INT
4AS
5BEGIN
6
7    --- 3.1. Create the temp table 
8
9    DROP TABLE IF EXISTS #tempProjectDates
10
11    --- 3.2. Get the list of distinct ProjectID's, Min, Max transaction dates, 
12    --- and corresponding Project Start and End Dates.
13
14    SELECT  PAY.ProjectID,
15            ProjectStartDate,
16			MIN(TransactionDate) AS MinTransactionDate,
17			ProjectEndDate,
18			MAX(TransactionDate) AS MaxTransactionDate   
19        INTO #tempProjectDates
20        FROM STG_Payrolls PAY
21            JOIN Projects P ON PAY.ProjectID = P.ProjectID
22            WHERE JobQueueId = @JobQueueID
23            AND PAY.ProjectID IS NOT NULL
24        GROUP BY PAY.ProjectID,ProjectStartDate,ProjectEndDate
25
26    --- 3.3. Iterate over the list and call the SP as needed.
27
28    DECLARE @ProjectID   INT, 
29            @MaxTransactionDate DATETIME, 
30            @MinTransactionDate DATETIME,
31            @ProjectStartDate DATETIME,
32            @ProjectEndDate  DATETIME
33
34    DECLARE @MyCursor CURSOR; 
35    SET @MyCursor = CURSOR FORWARD_ONLY FAST_FORWARD
36    FOR
37        SELECT 
38            ProjectID,
39            MaxTransactionDate,
40            MinTransactionDate,
41            ProjectStartDate,
42            ProjectEndDate    
43        FROM #tempProjectDates
44
45    OPEN @MyCursor    
46
47    FETCH NEXT FROM @MyCursor
48    INTO @ProjectID,
49         @MaxTransactionDate,
50         @MinTransactionDate,
51         @ProjectStartDate,
52         @ProjectEndDate
53        
54    WHILE @@FETCH_STATUS = 0
55    BEGIN
56        BEGIN TRY
57            IF @ProjectStartDate > @MinTransactionDate
58            BEGIN
59                EXEC p_extend_project_date @ProjectID, @MinTransactionDate;
60            END
61
62            IF @ProjectEndDate < @MaxTransactionDate
63            BEGIN
64                EXEC p_extend_project_date @ProjectID, @MaxTransactionDate;
65            END
66        END TRY
67        BEGIN CATCH  
68     
69        END CATCH 
70
71		FETCH NEXT FROM @MyCursor
72	    INTO @ProjectID,
73    	     @MaxTransactionDate,
74        	 @MinTransactionDate,
75         	 @ProjectStartDate,
76         	 @ProjectEndDate
77    END
78    CLOSE @MyCursor
79 	DEALLOCATE @MyCursor
80END