How to replace row value with previous saved row value in SQL SERVER 2017 SSMS

Question

first of all my problem is so complicated and sorry for my bad english

So, I got some Table-Valued Function query in SQL to Automatically Generate 5 rows for every single day I select on VB

Here some Example how this Table-Valued Function works in 2 days

Default Value for Column quantity is 1 everytime

Default Name is always A , B , C , D , E

It Will generate new 5 row every day then all Data from above will commited into another table with Stored Procedure (tblProduce) all columns in the picture were from VB DataViewGrid, i only make Table-Valued Function for getting ID, Name. The rest of it will be filled on VB

Main Problem:

What I want is, If I create and save this data on 07/25/2019 and I changed one of the CheckBox's row value to True

Then, the next day, when I want to create another data, it should be like this This was the expected Data Should be on the 2nd day

Green background means data were taken from last saved row when checkbox = True

This is for SSMS SQL Server 2017, I've tried using UNION / SUB QUERY on the Table Valued Function but still haven't figured it out yet how to do this thing

QUERY For getting how many day are selected :

CREATE FUNCTION [dbo].[tvfCustomDateRange] (@Increment char(1), @StartDate date, @EndDate date) 
RETURNS @SelectedRange TABLE (cDate date) 
AS
BEGIN 

      ;WITH cteRange (DateRange) AS (
            SELECT @StartDate
            UNION ALL
            SELECT 
                  CASE
                        WHEN @Increment = 'd' THEN DATEADD(dd, 1, DateRange)
                        WHEN @Increment = 'w' THEN DATEADD(ww, 1, DateRange)
                        WHEN @Increment = 'm' THEN DATEADD(mm, 1, DateRange)
                  END
            FROM cteRange
            WHERE DateRange <= 
                  CASE
                        WHEN @Increment = 'd' THEN DATEADD(dd, -1, @EndDate)
                        WHEN @Increment = 'w' THEN DATEADD(ww, -1, @EndDate)
                        WHEN @Increment = 'm' THEN DATEADD(mm, -1, @EndDate)
                  END)

      INSERT INTO @SelectedRange (cDate)
      SELECT DateRange
      FROM cteRange
      OPTION (MAXRECURSION 3660);
      RETURN
END

Query for Generate Row 5 row every how many day I selected on vb is:

CREATE FUNCTION [dbo].[tvfGenerate5Row] (@BeginDate DateTimeOffset, @EndDate Datetimeoffset)
RETURNS TABLE 
AS
RETURN 
(
    SELECT IsNuLL(tblProduce.Idtbl5Row,tblCustom.Idtbl5Row),
           IsNULL(tblProduce.Name,tblCustom.Name),
           IsNULL(tblProduce.Quantity,1),
           IsNULL(tblProduce.cDate,tblCustom.cDate),
           IsNULL(tblProduce.CheckBox,'')

    FROM  (SELECT Name, cDate, Idtbl5Row
           FROM tvfCustomDateRange('d', @BeginDate, @EndDate) CROSS JOIN tblWith5Row) AS tblCustom LEFT OUTER JOIN tblProduce ON tblCustom.cDate=tblProduce.cDate
)

CREATE TABLE tblProduce
(
    IdtblProduce  BigInt Primary Key,
    Idtbl5Row     BigInt,
    Name          VarChar(25),
    Quantity      Integer,
    cDate         DateTime,
    CheckBox      Bit,
    FOREIGN KEY (Idtbl5Row) REFERENCES tblWith5Row(Idtbl5Row)
)

Answer 1

Since you already have these 5 rows in your table. Now everytime you just want the entry of same with new date, so its better to take the previous date data and select it with your new date data.

Your function to generate 5 dates:-

    CREATE FUNCTION [dbo].[tvfCustomDateRange] (@Increment char(1), @StartDate date, @EndDate date) 
    RETURNS @SelectedRange TABLE (cDate date) 
    AS
    BEGIN 

          ;WITH cteRange (DateRange) AS (
                SELECT @StartDate
                UNION ALL
                SELECT 
                      CASE
                            WHEN @Increment = 'd' THEN DATEADD(dd, 1, DateRange)
                            WHEN @Increment = 'w' THEN DATEADD(ww, 1, DateRange)
                            WHEN @Increment = 'm' THEN DATEADD(mm, 1, DateRange)
                      END
                FROM cteRange
                WHERE DateRange <= 
                      CASE
                            WHEN @Increment = 'd' THEN DATEADD(dd, -1, @EndDate)
                            WHEN @Increment = 'w' THEN DATEADD(ww, -1, @EndDate)
                            WHEN @Increment = 'm' THEN DATEADD(mm, -1, @EndDate)
                      END)

          INSERT INTO @SelectedRange (cDate)
          SELECT DateRange
          FROM cteRange
          OPTION (MAXRECURSION 3660);
          RETURN
    END


    CREATE FUNCTION [dbo].[tvfGenerate5Row] (@BeginDate DateTimeOffset, @EndDate Datetimeoffset)
    RETURNS TABLE 
    AS
    RETURN 
    (
        SELECT tblCustom.Name,tblCustom.cDate, tblCustom.ID, 0   AS CHECKBOX
        FROM  (SELECT cDate, Name, ID
               FROM tvfCustomDateRange('d', @BeginDate, @EndDate) CROSS JOIN tableWith5Row) AS tblCustom 
    )

And to get your output result:

    ; WITH CTE AS (
    select ID, MAX(CDATE) AS CDATE from dbo.tvfGenerate5Row( '2019-08-13', '2019-08-15' ) GROUP BY ID 
    )
    SELECT CTE.ID, CTE.CDATE , CASE WHEN D.QUANTITY=1 THEN 1 ELSE 0 END AS CC FROM CTE 
    CROSS APPLY (
           SELECT ID, MAX(QUANTITY) AS QUANTITY 
           FROM tblProduce AS TP 
           WHERE CTE.ID=TP.Idtbl5Row 
           AND QUANTITY = 1
    ) AS D

For the case you mentioned in the comment, it is considered that you already have entries in back date. So your required query to get the expected result will be:

    ; WITH CTE AS (
    select ID, MAX(CDATE) AS CDATE from dbo.tvfGenerate5Row( '2019-08-13', '2019-08-15' ) GROUP BY ID )
    , CT AS (
    SELECT CTE.ID,  CTE.CDATE  , CASE WHEN D.QUANTITY=1 THEN 1 ELSE 0 END AS CC FROM CTE 
    CROSS APPLY (SELECT ID, MAX(QUANTITY) AS QUANTITY  FROM tblProduce AS TP WHERE CTE.ID=TP.Idtbl5Row and QUANTITY=1  ) AS D
    ) 
    SELECT CT.ID, 
    CASE WHEN tblProduce.CHECKBOX = 1 THEN tblProduce.CDATE ELSE CT.CDATE END AS CDATE, 
    CASE WHEN tblProduce.CHECKBOX = 1 THEN tblProduce.QUANTITY ELSE 1 END AS QUANTITY 
    FROM CT LEFT JOIN ( SELECT * FROM tblProduce WHERE CHECKBOX=1 ) tblProduce ON CT.ID=tblProduce.Idtbl5Row