Count of Consecutive Values
-
Hi everyone,
I have a database as follows:
Card(CardId bigint, SerialNumber varchar, CardStatusId int)
CardStatus(CardStatusId int, Name varchar)
The CardStatus is a lookup table and CardStatusId in Card is the foreign key.
Take this sample
Card Status Table
CardStatusId Name
1 a
2 b
3 c
Card Table
SerialNumber CardStatusId
1 1
2 1
3 1
4 2
5 2
6 3
7 2
8 2
9 2
The values in the CardId column shouldn't affect the query.
The query I need should return something like:
FromSerial ToSerial Count CardStatus
1 3 3 a
4 5 2 b
6 6 1 c
7 9 3 b
The query I need can't use cursors or loops.
Any help would be appreciated.
-
(Assuming you are a student with a homework assignment
)Lookup references for Aggregate functions and Group By. The solution is really pretty easy.
Hope this helps
Mark -
This is a Microsoft interview question...
-
I took this as a challenge and tried to come up with something for you, unfortunately, even though I have SERIALNO in three aggregate functions, QA rejects my ORDER BY SERIALNO clause with this prompt:
"SERIALNO is not a valid ORDERBY field because it is not included in either an AGGREGATE function or GROUP BY clause."
DROP TABLE #CARD
CREATE TABLE #CARD(CARDID TINYINT, SERIALNO TINYINT, STATUSID TINYINT)
INSERT INTO #CARD
SELECT 1,1,1 UNION ALL
SELECT 2,2,1 UNION ALL
SELECT 3,3,1 UNION ALL
SELECT 4,4,2 UNION ALL
SELECT 5,5,2 UNION ALL
SELECT 6,6,3 UNION ALL
SELECT 7,7,2 UNION ALL
SELECT 8,8,2 UNION ALL
SELECT 9,9,2
SELECT * FROM #CARD
DROP TABLE #STATUS
CREATE TABLE #STATUS(STATUSID TINYINT, NAME VARCHAR)
INSERT INTO #STATUS
SELECT 1,'a' UNION ALL
SELECT 2, 'b' UNION ALL
SELECT 3, 'c'
SELECT * FROM #STATUS
SELECT MIN(SERIALNO) MINSNO, MAX(SERIALNO) MAXSNO, COUNT(SERIALNO) CNT, NAME
FROM #CARD C INNER JOIN #STATUS S ON C.STATUSID = S.STATUSID
GROUP BY NAME
This is the result set I get without ORDER BY clause.
MINSNO MAXSNO CNT NAME
133a
495b
661c
which agregates ALL StatusID.
Hope it helps to point you in the correct direction.
-
Where do you get those? Wouldn't mind seeing the rest of them...
--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) -
All previous solutions assume that SerialNumber is sequential.
Have a look at these solutions
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=85913
N 56°04'39.16"
E 12°55'05.25" -
Hi,
By looking at sqlteam.com I've come up with this:
CREATE TABLE #x
(serialnumber BIGINT, cardstatus INT, grp INT )
INSERT #x (serialnumber, cardstatus)
SELECT serialnumber, cardstatus FROM card
DECLARE @Last INT
DECLARE @grp INT
SET @grp = 0
SET @Last = 0
UPDATE #x
SET @grp = grp = CASE WHEN cardstatus = @Last THEN @grp ELSE @grp + 1 END,
= cardstatus
SELECT
MIN(serialnumber) AS FromSerialNo, MAX(serialnumber) AS ToSerialNo, COUNT(*) AS Count, B.NAME AS Status
FROM
#x A
INNER JOIN cardstatus B ON A.cardstatus = B.cardstatusid
GROUP BY grp, cardstatus, B.NAME
ORDER BY MIN(serialnumber)
-
It will work when you add a clustered index over serialnumber.
N 56°04'39.16"
E 12°55'05.25"
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply