Solved

Incremental Count

Posted on 2008-06-13
4
274 Views
Last Modified: 2010-05-19
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
0
Comment
Question by:halifaxman
  • 3
4 Comments
 
LVL 1

Expert Comment

by:sqlconsumer
ID: 21778599
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

0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 21780296
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
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 21780304
DECLARE @maxValueToGenerate INT

SET @maxValueToGenerate = 16000

IF OBJECT_ID('seqNums') IS NOT NULL
    DROP TABLE seqNums

CREATE TABLE seqNums (
    seqNum INT,
    CONSTRAINT seqNums_CI --don't remove, improves performance!
        UNIQUE CLUSTERED (seqNum) WITH FILLFACTOR = 100
    )

INSERT INTO seqNums
SELECT [1s] + [10s] + [100s] + [1000s] + [10Ks] + [100Ks] +
      [1Ms] + [10Ms]
FROM (
    SELECT 0 AS [1s] UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL
    SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
    SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) AS digits
CROSS JOIN (
    SELECT 00 AS [10s] UNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL
    SELECT 30 UNION ALL SELECT 40 UNION ALL SELECT 50 UNION ALL SELECT 60 UNION ALL
    SELECT 70 UNION ALL SELECT 80 UNION ALL SELECT 90
) AS [10s]
CROSS JOIN (
      SELECT [100s]
      FROM (
    SELECT 000 AS [100s] UNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL
    SELECT 300 UNION ALL SELECT 400 UNION ALL SELECT 500 UNION ALL SELECT 600 UNION ALL
    SELECT 700 UNION ALL SELECT 800 UNION ALL SELECT 900
      ) inlineData
      WHERE [100s] <= @maxValueToGenerate
) AS [100s]
CROSS JOIN (
      SELECT [1000s]
      FROM (
    SELECT 0000 AS [1000s] UNION ALL SELECT 1000 UNION ALL SELECT 2000 UNION ALL
    SELECT 3000 UNION ALL SELECT 4000 UNION ALL SELECT 5000 UNION ALL SELECT 6000 UNION ALL
    SELECT 7000 UNION ALL SELECT 8000 UNION ALL SELECT 9000
      ) inlineData
      WHERE [1000s] <= @maxValueToGenerate
) AS [1000s]
CROSS JOIN (
      SELECT [10Ks]
      FROM (
    SELECT 00000 AS [10Ks] UNION ALL SELECT 10000 UNION ALL SELECT 20000 UNION ALL
    SELECT 30000 UNION ALL SELECT 40000 UNION ALL SELECT 50000 UNION ALL SELECT 60000 UNION ALL
    SELECT 70000 UNION ALL SELECT 80000 UNION ALL SELECT 90000
      ) inlineData
      WHERE [10Ks] <= @maxValueToGenerate
) AS [10Ks]
CROSS JOIN (
      SELECT [100Ks]
      FROM (
    SELECT 000000 AS [100Ks] UNION ALL SELECT 100000 UNION ALL SELECT 200000 UNION ALL
    SELECT 300000 UNION ALL SELECT 400000 UNION ALL SELECT 500000 UNION ALL SELECT 600000 UNION ALL
    SELECT 700000 UNION ALL SELECT 800000 UNION ALL SELECT 900000
      ) inlineData
      WHERE [100Ks] <= @maxValueToGenerate
) AS [100Ks]
CROSS JOIN (
      SELECT [1Ms]
      FROM (
    SELECT 0000000 AS [1Ms] UNION ALL SELECT 1000000 UNION ALL SELECT 2000000 UNION ALL
    SELECT 3000000 UNION ALL SELECT 4000000 UNION ALL SELECT 5000000 UNION ALL SELECT 6000000 UNION ALL
    SELECT 7000000 UNION ALL SELECT 8000000 UNION ALL SELECT 9000000
      ) inlineData
      WHERE [1Ms] <= @maxValueToGenerate
) AS [1Ms]
CROSS JOIN (
      SELECT [10Ms]
      FROM (
    SELECT 00000000 AS [10Ms] UNION ALL SELECT 10000000 UNION ALL SELECT 20000000 UNION ALL
    SELECT 30000000 UNION ALL SELECT 40000000 UNION ALL SELECT 50000000 UNION ALL SELECT 60000000 UNION ALL
    SELECT 70000000 UNION ALL SELECT 80000000 UNION ALL SELECT 90000000
      ) inlineData
      WHERE [10Ms] <= @maxValueToGenerate
) AS [10Ms]
WHERE [1s] + [10s] + [100s] + [1000s] + [10Ks] + [100Ks] + [1Ms] + [10Ms] <= @maxValueToGenerate
ORDER BY [1s] + [10s] + [100s] + [1000s] + [10Ks] + [100Ks] + [1Ms] + [10Ms]

SELECT COUNT(*) FROM seqNums --verify that the expected number of rows were generated
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 21955170
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
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question