April 10, 2019 at 8:31 pm
I'm trying to add the elapsed time column to a query, but I'm getting several errors regardless if it's a cte with the declare before the with statement or a subquery join. How should the @Time variable be included?
DECLARE @Time1 DATETIME
DECLARE @Time2 DATETIME
SET @Time1 = GETDATE()
SET @Time2 = GETDATE()
select * ,DATEDIFF(SECOND,@Time1,@Time2) AS Elapsed_SECONDS
from
(
SELECT @@SERVERNAME AS Servername
, DB_NAME() AS [Database]
, 'JT'as [Tablename]
, Count( distinct [Property]) as Property_Count
--cast (Count( distinct [LOC]) as varchar(100)) as LOC_Count
,DATEADD(day, -1, CAST(getdate() As date) ) AS Yesterday, min([DATE_TIME]) as Min_Date, Max([Date_Time]) as Max_Date
, AVG([Amount]) as Avg_Amount, min(Amount) as Min_Amount, max(Amount) AS Max_Amount
,COUNT(DISTINCT ID) AS IDTypes
, count(*) rowCounts, 'Prior Day' AS Range
from TABLE
where [Date_Time] >= DATEADD(day, -1, CAST(getdate() As date)) and [DATE_TIME] < DATEADD(day, 0, CAST(getdate() As date)) --
union all
-- 30 days
SELECT @@SERVERNAME AS Servername
, DB_NAME() AS [Database]
, 'JT'as [Tablename]
, Count( distinct [LOC]) as LOC_Count
--cast (Count( distinct [LOC]) as varchar(100)) as LOC_Count
,DATEADD(day, -1, CAST(getdate() As date) ) AS Yesterday, min([Date_Time]) as Min_Date, Max([Date_Time]) as Max_Date
, AVG([Amount]) as Avg_Amount, min(Amount) as Min_Amount, max(Amount) AS Max_Amount
,COUNT(DISTINCT ID) AS IDTypes
, count(*) rowCounts, '28 Days Ago' AS Range
from TABLE
where [Date_Time] >= DATEADD(day, -29, CAST(getdate() As date)) and [DATE_TIME] < DATEADD(day, -28, CAST(getdate() As date)) --
union all
---
SELECT @@SERVERNAME AS Servername
, DB_NAME() AS [Database]
, 'JT'as [Tablename]
, Count( distinct [LOC]) as LOC_Count
--cast (Count( distinct [LOC]) as varchar(100)) as LOC_Count
,DATEADD(day, -1, CAST(getdate() As date) ) AS Yesterday, min([Date_Time]) as Min_Date, Max([Date_Time]) as Max_Date
, AVG([Amount]) as Avg_Amount, min(Amount) as Min_Amount, max(Amount) AS Max_Amount
,COUNT(DISTINCT ID) AS IDTypes
, count(*) rowCounts, '364 Days Ago' AS Range
from TABLE
where [Date_Time] >= DATEADD(day, -365, CAST(getdate() As date)) and [DATE_TIME] < DATEADD(day, -364, CAST(getdate() As date))
--SET @Time2 = GETDATE()
) l
April 10, 2019 at 10:09 pm
Looking at the query, your @Time1 and @Time2 variables are going to show the time difference between how long it takes to call GETDATE(). And that time is going to be near 0.
If you are trying to track the execution time of a query, one way to handle this would be to create a temp table to store the results of your query in (the SELECT * portion, but exclude the start and end times), have "SET @Time1 = GETDATE()" prior to your SELECT statement and have "SET @Time2 = GETDATE()" at the end of the query. Then have a "SELECT *, DATEDIFF(SECOND,@Time1, @Time2) AS [Elapsed Time] FROM @tempTable".
Although I would recommend removing the "SELECT *" and putting in the column names. If you don't actually care about the data, but are just interested in the execution time, skip the stuff about the temp table in the above paragraph and change the last select to exclude "*," and "FROM @tempTable".
Looking at your query (and ignoring that the forum looks like it butchered your comments), you probably realized that your subquery cannot contain the keyword "SET" which is why it looks to be commented out.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 11, 2019 at 7:25 am
This was removed by the editor as SPAM
April 11, 2019 at 7:28 am
This was removed by the editor as SPAM
April 11, 2019 at 7:31 am
This was removed by the editor as SPAM
April 11, 2019 at 7:45 am
This was removed by the editor as SPAM
April 11, 2019 at 9:10 am
-- test
April 11, 2019 at 9:11 am
---------------------------------------------------------------------------------
-- Timing --
---------------------------------------------------------------------------------
-- Repeated Message ? Other message not visible ? Editing makes the message disappear ? Try 17 ???
--
-- ben brugman
-- 20190411
--
--
-- Timing.
-- Total time, time from start.
-- Laptimes, time from previous laptime.
-- Time is determined once of each 'timing section'
--
-- Presentation :
-- As a table. (The select statementent)
-- As texts, supplied with a text that explains what you are timing.
--
-- Remove what is not needed.
--
---------------------------------------------------------------------------------
-- Initialisation and start
declare @starttime datetime = getdate()
declare @laptime datetime = @starttime
declare @previouslaptime datetime = @starttime
--
select convert(varchar(30), @laptime, 126) On_time , convert(float,@laptime-@previouslaptime)*24*60*60 Lap_time
PRINT 'START TIME TIME: ' + convert(varchar(30), @laptime, 126) +' LAPTIME: '+ CONVERT(VARCHAR(20), convert(float,@laptime-@previouslaptime)*24*60*60)
set @previouslaptime = @laptime
--
---------------------------------------------------------------------------------
WAITFOR DELAY '00:00:01.123' -- Simulated action.
---------------------------------------------------------------------------------
-- Lap time and total time. Use repeatedly.
--
select @laptime = getdate()
select convert(varchar(30), @laptime, 126) On_time , convert(float,@laptime-@previouslaptime)*24*60*60 Lap_time
PRINT 'LAP ROUND TIME: ' + convert(varchar(30), @laptime, 126) +' LAPTIME: '+ CONVERT(VARCHAR(20), convert(float,@laptime-@previouslaptime)*24*60*60)
--
select convert(varchar(30), @laptime, 126) On_time , convert(float,@laptime-@starttime)*24*60*60 TOTAL_time
PRINT 'TOTAL TIME TIME: ' + convert(varchar(30), @laptime, 126) +' TOTALTIME: '+ CONVERT(VARCHAR(20), convert(float,@laptime-@starttime)*24*60*60)
set @previouslaptime = @laptime
---------------------------------------------------------------------------------
--
--
-- Greetings,
-- Ben
---------------------------------------------------------------------------------
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply