Can I write this request without CURSOR?

I am trying to figure out the number of events that happened during the timestamp threshold time found in another table for the same category. What is the fastest way to change the delta (in the case below, the delta is 5 minutes)? I just tested the cursor approach (set the variable to 5 and then keep incrementing and doing the same query), but it takes 10 seconds for each iteration. In my actual data, the number of rows in #EVENTS is approximately 100K and #CHANGES is approximately 500K.

My tables:

CREATE TABLE #EVENTS(Category varchar(20), Timestamp datetime)
GO

INSERT INTO #EVENTS VALUES('A', '2013-01-23 05:02:00.000')
INSERT INTO #EVENTS VALUES('A', '2013-01-23 05:04:00.000')
INSERT INTO #EVENTS VALUES('B', '2013-01-23 05:03:00.000')
INSERT INTO #EVENTS VALUES('B', '2013-01-21 05:02:00.000')
GO

CREATE TABLE #CHANGES(Category varchar(10), Timestamp datetime)
GO

INSERT INTO #CHANGES VALUES('A', '2013-01-23 05:00:00.000')
INSERT INTO #CHANGES VALUES('B', '2013-01-21 05:05:00.000')

SELECT *
FROM
(
    SELECT X.Category, X.Timestamp, Y.Timestamp BeforeT, DATEADD(MINUTE, 5, Y.Timestamp) AfterT
    FROM #EVENTS X, #CHANGES Y
    WHERE X.Category = Y.Category
) X
WHERE X.Timestamp BETWEEN BeforeT AND AfterT

DROP TABLE #CHANGES
DROP TABLE #EVENTS
GO

      

+3


source to share


1 answer


Is this what you are looking for? It cross-joins the CTE that the delta defines:

with deltas as (
     select 5 as delta union all
     select 10 union all
     select 20
)
SELECT *
FROM (SELECT e.Category, e.Timestamp, c.Timestamp BeforeT,
             DATEADD(MINUTE, deltas.delta, c.Timestamp) AfterT,
             deltas.delta
     FROM #EVENTS e join
          #CHANGES c
          on e.Category = c.Category cross join
          deltas
    ) X
WHERE X.Timestamp BETWEEN BeforeT AND AfterT

      



I also corrected your aliases. Queries read much better when the aliases are associated with the base table name.

+6


source







All Articles