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

Excel Example Pictures

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

First Data Picture

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

Second Data Picture

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)
)

`tableWith5Row` what is this table. and data present in this. — DarkRob, Aug 13 '19 at 06:28
Its only data with 5 row with only have ID and Name Attribute https://gyazo.com/551c9dc60720244c140babd760c0e28a like this — Wayne, Aug 13 '19 at 06:30
exactly in which table does this data saved for one day, like you marked `true` then entry of this `true` is saved on which table for particular day. — DarkRob, Aug 13 '19 at 06:40
moreover what is this `quantity` column, from where it is coming. — DarkRob, Aug 13 '19 at 06:41
i add more explanation and code before Main problem and on my tvfGenerate5Row Code — Wayne, Aug 13 '19 at 06:54
You said on first day this data is saved in a table, kindly share that table schema because in the end you have to pick data from that table only for creating new records. — DarkRob, Aug 13 '19 at 07:04
Edited, do you mean like that? tblProduce is filled with all data i Execute/Save on VB — Wayne, Aug 13 '19 at 07:19

DarkRob · Accepted Answer · 2019-08-13T08:19:15.957

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

thanks for helping, but i only want to take previous data when the checkbox is True, if the checkbox is False, it should generate new row. so when i changed first row to True, next data should generate 4 Row + 1 Previous Row Data. when i changed the first and third row to true, next data should generate 3 rows + 2 Previous Row data depends on the PrimaryKey and Name — Wayne, Aug 13 '19 at 07:51
Oh i'm sorry, your answer is absolutely works, but i haven't try on my real table yet, but i think your answer will work on my real table, i'll try this on real code when night and currently its 5P.M now, thanks btw... really helps me alot (imma check the solve button after testing it on my real code) — Wayne, Aug 13 '19 at 10:22
And also I need to changes your answer and put it into my [dbo].[tvfGenerate5Row] code, because [dbo].[tvfGenerate5Row] is the one that i called on VB to make the row — Wayne, Aug 13 '19 at 10:24

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

1 Answers1