I am new to SQL. I have a problem which I would like to solve.

1. I would like to duplicate rows from an existing table - "Reports" within the same "Reports" table for tasks that remain outstanding from the previous financial year (e.g. 2021) to the new financial year (e.g. 2022).

2. If "TaskACompletedDate" and "TaskBCompletedDate" are not completed, it will remain outstanding and carried forward from Financial Year "2021" to "2022".

3. We will need to check for users who roll over the outstanding items from Financial Year "2021" to "2022" to ensure that there is no duplicate.

4. If the outstanding items are already roll-over, then check for whether there is an update on "TaskADueDate" and "TaskBDueDate" and update the changes accordingly.

My code is as below. But it is not working as expected.

/* Code for table creation */
IF NOT EXISTS (
select * from sysobjects where name='Report' and xtype='U')
CREATE TABLE Report (
[ReportId] INT,
[TaskADueDate] DATETIME,
[TaskACompletedDate] DATETIME,
[TaskBDueDate] DATETIME,
[TaskBCompletedDate] DATETIME,
[FinancialYear] INT
);
INSERT INTO Report VALUES
(1,'2021-12-12 00:00:00','2021-12-12 00:00:00','2021-12-31 
00:00:00','2021-12-31 00:00:00',2021),
(2,'2021-12-13 00:00:00',NULL,'2022-01-01 00:00:00',NULL,2021),
(3,'2022-02-01 00:00:00',NULL,'2022-03-02 00:00:00',NULL,2021),
(4,'2022-02-02 00:00:00',NULL,'2022-03-03 00:00:00',NULL,2021);

/* Code for Roll Over to next financial year */
DECLARE @FinancialYearFrom int, 
DECLARE @FinancialYearTo int 

SET @FinancialYearFrom = 2021
SET @FinancialYearTo = 2022

/*View Outstanding Tasks in FY2021*/
SELECT * FROM REPORT TABLE
WHERE FinancialYear = @FinancialYearFrom AND  TaskACompletedDate IS NULL AND TasKBCompletedDate IS NULL

/*Check rows to ensure that the rows in ReportID 2,3,4 is not carried forward to FY2022.
 If copied over, just update DueDate if there is any changes.*/
IF(SELECT COUNT(*) FROM Report WHERE FinancialYear =  @FinancialYearTo) <> 0
Update Report SET
TaskADueDate = NewTaskADueDate
TaskBDueDate = NewTaskBDueDate

ELSE
INSERT INTO Report (TaskADueDate, TaskACompletedDate, TaskBDueDate, TaskBCompletedDate, FinancialYear)
VALUES (SELECT TOP (1) [TaskADueDate] FROM Report WHERE FinancialYear = @FinancialYearFrom,
       SELECT TOP (1) [TaskACompletedDate] FROM Report WHERE FinancialYear = @FinancialYearFrom,
       SELECT TOP (1) [TaskBDueDate] FROM Report WHERE FinancialYear = @FinancialYearFrom,
       SELECT TOP (1) [TaskBDueDate] FROM Report WHERE FinancialYear = @FinancialYearFrom,
       @FinancialYearTo)

CodePudding user response：

As an example, I'm just adding 10 to your ReportId to create the new rows, since you didn't use any kind of auto_increment in your schema. Just replace the fixed year with a parameter as needed.

The fiddle

INSERT INTO Report
SELECT ReportId   10
     , TaskADueDate, TaskACompletedDate
     , TaskBDueDate, TaskBCompletedDate
     , FinancialYear   1
  FROM Report
 WHERE FinancialYear = 2021
   AND (TaskBCompletedDate IS NULL AND TaskACompletedDate IS NULL)
;

Note: It's not possible to perform your UPDATE logic without a reference to the original row, or an additional key (unique per year) which allows joining the rows. Add that detail to the design and the question, if you want to update existing rows based on prior "related" rows.