CODEWITHSUNDEEP
sqlsql-serverdatetime
Philip Gullick
Philip Gullick

Reputation: 995

Increment date in row by 1 day each time

In SQL I have an output table like the following:

MemNo | PipNomDt | PIPStrtDt 1739 | 2007-03-31 00:00:00.000 | 2009-02-01 00:00:00.000 1739 | NULL | 2007-04-01 00:00:00.000 1739 | NULL | 2008-04-01 00:00:00.000 1739 | NULL | 2009-04-01 00:00:00.000 1739 | NULL | 2010-04-01 00:00:00.000 1739 | NULL | 2011-04-01 00:00:00.000 1739 | NULL | 2012-04-01 00:00:00.000 1739 | NULL | 2013-04-01 00:00:00.000

I need to update this table so that when the first row of Date1 is populated, all of the rows below are populated with an increment of 1 day so it would appear as the following.

MemNo | PipNomDt | PIPStrtDt 1739 | 2007-03-31 00:00:00.000 | 2009-02-01 00:00:00.000 1739 | 2007-04-01 00:00:00.000 | 2007-04-01 00:00:00.000 1739 | 2007-04-02 00:00:00.000 | 2008-04-01 00:00:00.000 1739 | 2007-04-03 00:00:00.000 | 2009-04-01 00:00:00.000 1739 | 2007-04-04 00:00:00.000 | 2010-04-01 00:00:00.000 1739 | 2007-04-05 00:00:00.000 | 2011-04-01 00:00:00.000 1739 | 2007-04-06 00:00:00.000 | 2012-04-01 00:00:00.000 1739 | 2007-04-07 00:00:00.000 | 2013-04-01 00:00:00.000

I did try the following code but I'm not really sure how to actually go about doing this

            UPDATE  pip
            SET     PIPNomDt = DATEADD(DAY, rn - 1,
                                       ( SELECT TOP 1
                                                n.PIPNomDt
                                         FROM   dbo.wtPIPNomDt_List n ,
                                                dbo.wtPIPStrtDt_List s
                                         WHERE  n.RowID = s.RowID
                                       ))
            FROM    dbo.htPensionInputPeriod pip
                    JOIN ( SELECT   ROW_NUMBER() OVER ( ORDER BY MemNo ) rn ,
                                    MemNo
                           FROM     dbo.htPensionInputPeriod
                         ) t2_numbered ON t2_numbered.MemNo = @MemNo

Upvotes: 0

Views: 3199

Answers (2)

Satheesh Variath
Satheesh Variath

Reputation: 680

Are you looking for this?

WITH cte AS
(SELECT *, 
            Row_number() 
              OVER( 
                partition BY memno 
                ORDER BY CASE WHEN pipnomdt IS NULL THEN 1 ELSE 0 END, 
              pipstrtdt) 
            AS 
               RN 
     FROM   table1
)
,cte_next AS 
(SELECT * 
     FROM   cte 
     WHERE  rn = 1 
     UNION ALL 
     SELECT C1.memno, 
            dateadd(day,1,c1.pipnomdt), 
            c2.pipstrtdt, 
            c2.rn 
     FROM   cte_next c1 
            JOIN cte c2 
              ON c1.memno = c2.memno 
                 AND c1.rn = c2.rn - 1
) 
SELECT * 
FROM   cte_next

SQL Fiddle Here

Upvotes: 1

AHiggins
AHiggins

Reputation: 7219

My following test was able to produce results similar to those you are looking for: I'm not sure if I've correctly understood the relationship between MemNo and RowID, but it should give you a start. I made the assumption that you only wanted to update values with a matching MemNo, in the order of RowID - you can change that if necessary. 

CREATE TABLE pip
  (
RowID INT IDENTITY(1,1) NOT NULL,
MemNo INT NOT NULL,
Date1 DATETIME NULL
  )

INSERT INTO pip (MemNo, Date1) VALUES (1, '8/3/2013')
INSERT INTO pip (MemNo) VALUES (1739)
INSERT INTO pip (MemNo) VALUES (1739)
INSERT INTO pip (MemNo) VALUES (1739)
INSERT INTO pip (MemNo) VALUES (1739)
INSERT INTO pip (MemNo) VALUES (1739)
INSERT INTO pip (MemNo) VALUES (1739)
INSERT INTO pip (MemNo) VALUES (1739)
INSERT INTO pip (MemNo) VALUES (1739)
INSERT INTO pip (MemNo) VALUES (1739)

GO

CREATE TRIGGER T_pip ON pip
    FOR UPDATE 
AS 
  BEGIN 
    UPDATE pip2
    SET pip2.date1 = DATEADD(DAY, s.RowNum, inserted.Date1)
    FROM 
        inserted 
         INNER JOIN 
          (
            SELECT
                RowID,
                MemNo,
                ROW_NUMBER() OVER (PARTITION BY MemNo ORDER BY RowID )     RowNum
            FROM pip
          ) s ON 
            inserted.RowID < s.RowID AND
            inserted.MemNo = s.MemNo
         INNER JOIN 
        pip pip2 ON 
            s.RowID = pip2.RowID


  END

GO

SELECT * FROM pip

UPDATE pip
SET Date1 = '3/31/2007' 
WHERE pip.RowID = 2

SELECT * FROM pip


-- DROP TABLE pip

Upvotes: 0

Related Questions