CODEWITHSUNDEEP
sqlsql-servert-sql
user3115933
user3115933

Reputation: 4453

How to insert values into the following table by incrementing the date column by 1 day for the next 23 days?

I am using SQL Server 2014. I have a table names T1 (extract shown below):

Company     ReviewDate    Rank     Type       Reviews
  ...
  A         2020-10-12    8        Hotel      675
  B         2020-10-12    10       Hotel      450 
  C         2020-10-12    21       Hotel       89
  D         2020-10-12    60       Hotel      1200
  A         2020-10-13    5        Hotel      688
  B         2020-10-13    10       Hotel      500 
  C         2020-10-13    21       Hotel       89
  D         2020-10-13    60       Hotel      1200

I need to update table T1 for period 2020-10-13 to 2020-11-04 with the following logic: All rows to be appended to the table will have the same values as those for ReviewDate on 2020-10-12 except the ReviewDate which will increment by 1 day until 2020-11-04 is reached.

So here is an extract of how the final table will look like (only appended ReviewDate 2020-10-13 shown:

    Company     ReviewDate   Rank      Type     Reviews
      ...
      A         2020-10-11    8        Hotel      675
      B         2020-10-11    10       Hotel      450 
      C         2020-10-11    21       Hotel       89
      D         2020-10-11    60       Hotel      1200 
      A         2020-10-12    5        Hotel      688
      B         2020-10-12    10       Hotel      500 
      C         2020-10-12    21       Hotel       89
      D         2020-10-12    60       Hotel      1200
      A         2020-10-13    5        Hotel      688
      B         2020-10-13    10       Hotel      500 
      C         2020-10-13    21       Hotel       89
      D         2020-10-13    60       Hotel      1200 
      ...

NOTE: the table also contain entries with ReviewDate before 2020-10-12 but I just need to insert values into the table for a specific period with data related to 2020-10-12

How can I do that with a T-SQL query?

Upvotes: 0

Views: 455

Answers (2)

Fabiano Carvalho
Fabiano Carvalho

Reputation: 512

Try this code.

DECLARE @TABLE TABLE (
    COMPANY VARCHAR(1),
    ReviewDate Date,
    [Rank] int
)
INSERT INTO @TABLE VALUES ('A','2020-10-11',5)
INSERT INTO @TABLE VALUES ('B','2020-10-11',10)
INSERT INTO @TABLE VALUES ('C','2020-10-11',21)
INSERT INTO @TABLE VALUES  ('D','2020-10-11',60)

;WITH CTE AS (
SELECT * FROM @TABLE
UNION ALL
SELECT 
    COMPANY,
    dateadd(day,1,ReviewDate),
    [Rank]
from CTE
WHERE ReviewDate <= '2020-10-13'
)
SELECT * FROM CTE
ORDER BY 2,1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270411

You need to insert rows. One method is to cross join to the dates you want and use that:

insert into t1 (Company, ReviewDate, Rank, Type, Reviews)
    select t1.Company, v.date, t1.Rank, t1.Type, t1.Reviews
    from t1 cross join
         (values ('2020-10-13'),  ('2020-10-14'), . . .
         ) v(date
    where t1.reviewdate = '2020-10-12';

There are other ways to create the dates. One method is a recursive CTE:

with dates as (
      select convert(date, '2020-10-13') as dte
      union all
      select dateadd(day, 1, dte)
      from dates
      where dte < '2020-11-04'
     )
select . . .
from t1 cross join
     dates d;

Upvotes: 3

Related Questions