Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Incremental Count

Posted on 2008-06-13
4
Medium Priority
?
280 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 70

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 70

Accepted Solution

by:
Scott Pletcher earned 2000 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 70

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

609 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