SQL-CUMULATIVE datetime/datead

Question

I want to have running date with definite rows from the column num, and datead by minutes by the mins column, and +1 increment for the row ID.

Current code

declare @table table
(
  ID varchar(10), 
  DT datetime, 
  mins int,
  num int
)

insert into @table
  select 'PR0006',getdate(), 30, 3

select * from @table

Output

ID      DT                      mins    num     
PR0006  2019-06-25 08:45:56.227 30  3

Desired output

PR0006  2019-06-25 08:45:56.227 30  3
PR0007  2019-06-25 09:15:56.227 30  3
PR0008  2019-06-25 09:45:56.227 30  3

The `DATE` functions and arithmetic is heavily dependent on the database? What database are you using? DB2, HyperSQL, Sybase, MariaDB, etc. — The Impaler, Jun 25 '19 at 01:04

Gordon Linoff · Accepted Answer · 2019-06-25T01:55:02.233

You can do this with a recursive CTE:

with params as (
      select 30 as minutes, 3 as num
     ),
     n as (
      select 1 as n
      union all
      select n + 1
      from n join
           params
           on n.n < params.num
     )
insert into @table
  select 'PR' + FORMAT(5 + n, '0000'),
         dateadd(minute, (n.n - 1) * params.minutes, getdate()),
         params.minutes, params.num
  from n cross join
       params

Here is a db<>fiddle.

Harry · Answer 2 · 2019-06-25T01:47:40.043

one way to do this is to create another table and insert blank rows based on your num row and then cross join to it.. something like this

      DECLARE   @Numbers TABLE
      ( 
             Number INT IDENTITY(1,1) PRIMARY KEY CLUSTERED 
      ) 



declare @table_test table
(
  ID varchar(10), 
  DT datetime, 
  mins int,
  num int
)

insert into @table_test
  select 'PR0006',getdate(), 30, 3

--select * from @table_test

  WHILE COALESCE(SCOPE_IDENTITY(), 0) < (select num from @table_test)
      BEGIN 
             INSERT @Numbers DEFAULT VALUES 
      END   
      ;with mycte as (
      SELECT  
      Number
      , ID
      , DT
      ,mins
      ,num
      ,sum(mins) over( order by DT  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)  as running_mins
       FROM @Numbers
      cross join @table_test
      )

      Select *
      , dateadd(MINUTE,running_mins-mins,DT)as New_DT 
      from mycte

now you just have to figure out how to increment the ID based on the Number column..

It's not clear if PR is always prefix or how many numbers follows the PR.. but you should be able to figure it out.

but here is a link that shows you how:

Increment a varchar in SQL

SQL-CUMULATIVE datetime/datead

2 Answers2