Cross Joining Queries, Left Joining Queries

  • ackrite55

    Mr or Mrs. 500

    Points: 562

    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

    • This topic was modified 1 year, 3 months ago by  ackrite55.
  • Mr. Brian Gale

    SSC-Insane

    Points: 22934

    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.

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • ben.brugman

    SSChampion

    Points: 13350

    -- test
  • ben.brugman

    SSChampion

    Points: 13350

    ---------------------------------------------------------------------------------
    -- 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 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply