How do I count the occurrences of a sequence of records

Question

How do I count the occurrences of a sequence of records

Viewing 5 posts - 16 through 19 (of 19 total)

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

RobD1 SSC Veteran Points: 232 More actions · Answer 1

I just noticed the question about size of the table. There are currently 2,069,372 records in it. The counts will end up being over specified date ranges that will encompass about half of that at the biggest and go down from there.

RobD1 SSC Veteran Points: 232 More actions · Answer 2

Once again thanks for all the help on this. I went through the posts, took the code from the cte example, changed it to use a temp table and things are working. And by coming up with a few more constraints to cut down on the size of the temp table, the performance is good too.

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 3

RobD1 (5/19/2010)
Once again thanks for all the help on this. I went through the posts, took the code from the cte example, changed it to use a temp table and things are working. And by coming up with a few more constraints to cut down on the size of the temp table, the performance is good too.

Would you post your code, please? It may help others in the future.

Also, how long did it take to process your 2 millions rows?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RobD1 SSC Veteran Points: 232 More actions · Answer 4

The code I ended up with is below. With the additional constraints and joins I added, the query only takes 2 to 3 seconds when the TS_CHANGEACTIONS table has 2 million plus rows.

IF OBJECT_ID('TempDB..#TBL','U') IS NOT NULL

DROP TABLE #TBL

CREATE TABLE #TBL

(

ID INT IDENTITY NOT NULL,

ITEMIDINT,

ISSUEIDINT,

StateChgDt INT,

[State] CHAR(15)

)

INSERT INTO #TBL

(ITEMID, ISSUEID, StateChgDt, State)

(

SELECT

TS_ITEMID,

UPD.TS_ISSUEID,

TS_TIME,

TS_TRANSITIONLABEL

FROM TS_PROJECTS PRJ

INNER JOIN USR_PRODUCT_DEVELOPMENT UPD ON UPD.TS_PROJECTID = PRJ.TS_ID

INNER JOIN TS_CHANGEACTIONS CHGA ON UPD.TS_ID = CHGA.TS_ITEMID

WHERE TS_TRANSITIONLABEL IN ('Re-open','Ready for build')

AND TS_TABLEID = 1002

AND PRJ.TS_WORKFLOWID IN (134, 142)

)

SELECT t.ITEMID, t.ISSUEID,

COUNT(t.StateChgDt)

FROM #TBL t

LEFT JOIN #TBL t_2

ON t_2.ID = t.ID - 1

AND t_2.ITEMID = t.ITEMID

WHERE t.[State] = 'Re-open'

AND t_2.[State] <> 'Re-open'

AND T.STATECHGDT > @StartDate

AND T.STATECHGDT <= @EndDate

AND EXISTS

(

SELECT sq.ITEMID

FROM #TBL sq

WHERE sq.ITEMID = t.ITEMID

AND sq.[State] = 'Ready for build'

AND sq.ID > t.ID

)

GROUP BY t.ITEMID, t.ISSUEID

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 5

Thanks for the feedback and the code. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)