Solved

Incremental Count

Posted on 2008-06-13
4
271 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:ScottPletcher
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:
ScottPletcher 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:ScottPletcher
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now