Increment date value in SQL column-CodePudding

There is a SQL query:

DECLARE sptv cursor local fast_forward for
 SELECT GETDATE() as Time,
       r.session_id,
       r.cpu_time,
       p.physical_io,
       t.text,
       substring(t.text, r.statement_start_offset/2   1, case when r.statement_end_offset = -1 then len(t.text) else (r.statement_end_offset - r.statement_start_offset)/2 end) as text_running,
       p.blocked,
       db_name(p.dbid) as dbname,
       r.status,
       r.command,
       r.start_time,
       r.wait_type,
       p.waitresource,
       p.status,
       p.open_tran,
       p.loginame,         
       p.hostname,
       p.program_name,
       r.percent_complete,
       r.wait_type,
       r.last_wait_type,
       p.waittime
from sys.dm_exec_requests r
       cross apply sys.dm_exec_sql_text(r.sql_handle) t
       inner join sys.sysprocesses p on p.spid = r.session_id

open sptv;
while 1 = 1
begin
 fetch next from sptv;

 if @@fetch_status <> 0
  break;
end;

close sptv;
deallocate sptv;

I want the time in the Time column to increase by 1 second for each line. I managed to do it with the following query:

CREATE TABLE #ActibeUser
    (
        Time DATETIME,
        ID int identity(1,1),
        session_id INT,
        cpu_time INT,
        physical_io INT,
        text VARCHAR(MAX),
        text_running VARCHAR(MAX),
        blocked INT,
        dbname VARCHAR(MAX),
        status1 VARCHAR(MAX),
        command VARCHAR(MAX),
        start_time DATETIME,
        wait_type1 VARCHAR(MAX),
        waitresource VARCHAR(MAX),
        status2 VARCHAR(MAX),
        open_tran INT,
        loginame VARCHAR(MAX),
        hostname VARCHAR(MAX),
        program_name VARCHAR(MAX),
        percent_complete INT,
        wait_type2 VARCHAR(MAX),
        last_wait_type VARCHAR(MAX),
        waittime INT
    )
INSERT INTO #ActibeUser
        (
        Time,
        session_id,
        cpu_time,
        physical_io,
        text,
        text_running,
        blocked,
        dbname,
        status1,
        command,
        start_time,
        wait_type1,
        waitresource,
        status2,
        open_tran,
        loginame,
        hostname,
        program_name,
        percent_complete,
        wait_type2,
        last_wait_type,
        waittime
    )
SELECT GETDATE() AS Time,
       r.session_id,
       r.cpu_time,
       p.physical_io,
       t.text,
       substring(t.text, r.statement_start_offset/2   1, case when r.statement_end_offset = -1 then len(t.text) else (r.statement_end_offset - r.statement_start_offset)/2 end) as text_running,
       p.blocked,
       db_name(p.dbid) as dbname,
       r.status,
       r.command,
       r.start_time,
       r.wait_type,
       p.waitresource,
       p.status,
       p.open_tran,
       p.loginame,         
       p.hostname,
       p.program_name,
       r.percent_complete,
       r.wait_type,
       r.last_wait_type,
       p.waittime
from sys.dm_exec_requests r
       cross apply sys.dm_exec_sql_text(r.sql_handle) t
       inner join sys.sysprocesses p on p.spid = r.session_id

DECLARE sptv cursor local fast_forward for
SELECT * FROM #ActibeUser
open sptv;
            while 1 = 1
            begin
                fetch next from sptv;
                if @@fetch_status <> 0
                break;
                    UPDATE #ActibeUser
                    SET Time = DATEADD(SS,1,Time)
                end;
            close sptv;
            deallocate sptv;
DROP TABLE #ActibeUser

But it does not suit me, I cannot use temporary tables. Help to correct the first request, please. I transfer the received data to Zabbix, and from it to Grafana.

CodePudding user response：

You can just use DATEADD along with a ROW_NUMBER inside your SELECT.

Also you shouldn't use sys.sysprocesses, it's deprecated. Use sys.dm_exec_sessions instead.

SELECT DATEADD(second, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), GETDATE()) AS Time,
       r.session_id,
       r.cpu_time,
       physical_io = s.reads   s.writes,
       t.text,
       substring(t.text, r.statement_start_offset/2   1, case when r.statement_end_offset = -1 then len(t.text) else (r.statement_end_offset - r.statement_start_offset)/2 end) as text_running,
       r.blocking_session_id,
       db_name(s.database_id) as dbname,
       r.status,
       r.command,
       r.start_time,
       r.wait_type,
       r.waitresource,
       s.status,
       s.open_transaction_count,
       s.login_name,         
       s.host_name,
       s.program_name,
       r.percent_complete,
       r.wait_type,
       r.last_wait_type,
       r.waittime
from sys.dm_exec_requests r
inner join sys.dm_exec_sessions s on s.session_id = r.session_id
cross apply sys.dm_exec_sql_text(r.sql_handle) t