asked on

Incremental Count

I have the following data in a table call_data.

An example of which is.

Sessionid SeQNum
100 0
100 0
100 0
100 0
100 0
101 0
101 0
101 0
102 0
130 0

I would like to develop a query to get the following results

Sessionid SeQNum OrigCalls
100 0 3
100 0 2
100 0 1
100 0 0
100 0 0
101 0 1
101 0 0
101 0 0
102 0 0
130 0 0
130 0 0

The logic is as follows

If there are more than 2 sessionid that are the same, in the example below there are 5.
The origcalls column would be as follows, count of the sessionid, i.e 5 - 2 /(the last two
will always be 0) gives us 3. The origcalls decrease until 1, i.e 3,2,1,0,0

Sessionid SeQNum OrigCalls
100 0 3
100 0 2
100 0 1
100 0 0
100 0 0

Another example

101 0 1
101 0 0
101 0 0

The last two will always be 0, so count sessionid = 3 - 2 which are 0, therefore 1st sessionid = 1 and the last two are zero

For sessionid where the count is two or 1, the origcall will always be zero i.e.

102 0 0
130 0 0
130 0 0

Any help would be great

sqlconsumer

Does this help ?

SELECT rn.Sessionid, CASE WHEN (SessionCount - RowNum -1) < 0 THEN 0 ELSE (SessionCount - RowNum -1) END origCall
FROM
(
 
SELECT COUNT(Sessionid) SessionCount, Sessionid
 
  FROM [Test].[dbo].[Test]
GROUP BY Sessionid
) grp
INNER JOIN
(
SELECT Sessionid, 
ROW_NUMBER() OVER (PARTITION BY Test.Sessionid ORDER BY Test.Sessionid DESC) AS RowNum
FROM
Test
) rn
ON
rn.Sessionid = grp.Sessionid

Open in new window

Scott Pletcher

First, create a work table of sequential numbers (naturally only has to be done once -- code to follow in next post).

Then:

SELECT Sessionid,
CASE WHEN seqNum = 8000 THEN 0 ELSE SeqNum END AS OrigCalls
FROM (
SELECT Sessionid, COUNT(*) AS SessionCount
FROM call_data
GROUP BY Sessionid
) AS derived
INNER JOIN seqNums s1 ON seqNum BETWEEN 0 AND SessionCount - 2 OR seqNum = 16000
ORDER BY Sessionid, OrigCalls DESC

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Pletcher

CORRECTION: Oops, as you're probably noticed, the high value got out of sync there:

SELECT Sessionid,
CASE WHEN seqNum = 16000 THEN 0 ELSE SeqNum END AS OrigCalls --<<--chg'd from 8000 to 16000 to match JOIN
FROM (
SELECT Sessionid, COUNT(*) AS SessionCount
FROM call_data
GROUP BY Sessionid
) AS derived
INNER JOIN seqNums s1 ON seqNum BETWEEN 0 AND SessionCount - 2 OR seqNum = 16000
ORDER BY Sessionid, OrigCalls DESC