SQL Server Ranking: Count Consecutive Values / Reset [Programming SQL]

Prev: error when trying to alter table in transaction
Next: On Update Cascade. Should I use it?

From: alexcn on 13 Jul 2010 06:11

Dear All,

I have the following set of query results, the last column being
derived using ROW_NUMBER and partitioned by TradDate, Account, PLInd
using an OrderBy subclause in the OVER determined on EntryTime. The
values for TradeNum are roughly correct except that I would want to
calculate consecutive Winners and Losers only i.e. in my example the
second subset of "Winners" would start counting at 1, 2 rather than
continue the previous subset value at 5,6 etc.

Can anyone suggest where I am going wrong please? Ultimately I will be
"max'ing" the TradeNum to give maximum consecutive winners and maximum
consecutive losers separately.

Account Currency TrdDate EntryTime PLInd TradeNum
AAA0007 EUR 2010-06-09 2010-06-09 08:25:20.0000000 Winner 1
AAA0007 EUR 2010-06-09 2010-06-09 09:18:57.0000000 Winner 2
AAA0007 EUR 2010-06-09 2010-06-09 09:27:51.0000000 Winner 3
AAA0007 EUR 2010-06-09 2010-06-09 10:18:07.0000000 Winner 4
AAA0007 EUR 2010-06-09 2010-06-09 10:49:22.0000000 Loser 1
AAA0007 EUR 2010-06-09 2010-06-09 11:03:09.0000000 Winner 5
AAA0007 EUR 2010-06-09 2010-06-09 11:38:56.0000000 Winner 6
AAA0007 EUR 2010-06-09 2010-06-09 12:39:25.0000000 Loser 2
AAA0007 EUR 2010-06-09 2010-06-09 13:26:16.0000000 Winner 7
AAA0007 EUR 2010-06-09 2010-06-09 14:30:31.0000000 Winner 8
AAA0007 EUR 2010-06-09 2010-06-09 14:46:59.0000000 Winner 9

Thanks for any suggestion offered,

Alex

From: Mark on 13 Jul 2010 06:34

CREATE TABLE Accounts(Account CHAR(7), Currency CHAR(3), TrdDate
DATETIME, EntryTime DATETIME, PLInd VARCHAR(10));

INSERT INTO Accounts(Account, Currency, TrdDate, EntryTime, PLInd)
SELECT 'AAA0007', 'EUR' , '20100609' , '20100609
08:25:20' , 'Winner' UNION ALL
SELECT 'AAA0007', 'EUR' , '20100609' , '20100609
09:18:57' , 'Winner' UNION ALL
SELECT 'AAA0007', 'EUR' , '20100609' , '20100609
09:27:51' , 'Winner' UNION ALL
SELECT 'AAA0007', 'EUR' , '20100609' , '20100609
10:18:07' , 'Winner' UNION ALL
SELECT 'AAA0007', 'EUR' , '20100609' , '20100609
10:49:22' , 'Loser ' UNION ALL
SELECT 'AAA0007', 'EUR' , '20100609' , '20100609
11:03:09' , 'Winner' UNION ALL
SELECT 'AAA0007', 'EUR' , '20100609' , '20100609
11:38:56' , 'Winner' UNION ALL
SELECT 'AAA0007', 'EUR' , '20100609' , '20100609
12:39:25' , 'Loser ' UNION ALL
SELECT 'AAA0007', 'EUR' , '20100609' , '20100609
13:26:16' , 'Winner' UNION ALL
SELECT 'AAA0007', 'EUR' , '20100609' , '20100609
14:30:31' , 'Winner' UNION ALL
SELECT 'AAA0007', 'EUR' , '20100609' , '20100609
14:46:59' , 'Winner';

WITH CTE AS (
SELECT Account, Currency, TrdDate, EntryTime, PLInd,
ROW_NUMBER() OVER(PARTITION BY TrdDate, Account, PLInd ORDER
BY EntryTime) -
ROW_NUMBER() OVER(PARTITION BY TrdDate, Account ORDER BY
EntryTime) AS rnDiff
FROM Accounts)
SELECT Account, Currency, TrdDate, EntryTime, PLInd,
ROW_NUMBER() OVER(PARTITION BY TrdDate, Account, PLInd, rnDiff
ORDER BY EntryTime)
FROM CTE
ORDER BY EntryTime;

From: alexcn on 13 Jul 2010 08:24

Dear Mark,

Many thanks for your prompt reply, it works perfectly so thanks for
your efforts. I was just wondering whether you could think of a
possible solution again using the SQL server ranking functions that
dont use a CTE? Just that this is already part of a much larger query
which I would prefer not to wrap up or split away in a CTE thats all.

Thanks once again,

Alex

On 13 July, 11:34, Mark <markc...(a)hotmail.com> wrote:
> CREATE TABLE Accounts(Account CHAR(7), Currency CHAR(3), TrdDate
> DATETIME, EntryTime DATETIME, PLInd VARCHAR(10));
>
> INSERT INTO Accounts(Account, Currency, TrdDate, EntryTime, PLInd)
> SELECT 'AAA0007', 'EUR' , '20100609' , '20100609
> 08:25:20' , 'Winner' UNION ALL
> SELECT 'AAA0007', 'EUR' , '20100609' , '20100609
> 09:18:57' , 'Winner' UNION ALL
> SELECT 'AAA0007', 'EUR' , '20100609' , '20100609
> 09:27:51' , 'Winner' UNION ALL
> SELECT 'AAA0007', 'EUR' , '20100609' , '20100609
> 10:18:07' , 'Winner' UNION ALL
> SELECT 'AAA0007', 'EUR' , '20100609' , '20100609
> 10:49:22' , 'Loser ' UNION ALL
> SELECT 'AAA0007', 'EUR' , '20100609' , '20100609
> 11:03:09' , 'Winner' UNION ALL
> SELECT 'AAA0007', 'EUR' , '20100609' , '20100609
> 11:38:56' , 'Winner' UNION ALL
> SELECT 'AAA0007', 'EUR' , '20100609' , '20100609
> 12:39:25' , 'Loser ' UNION ALL
> SELECT 'AAA0007', 'EUR' , '20100609' , '20100609
> 13:26:16' , 'Winner' UNION ALL
> SELECT 'AAA0007', 'EUR' , '20100609' , '20100609
> 14:30:31' , 'Winner' UNION ALL
> SELECT 'AAA0007', 'EUR' , '20100609' , '20100609
> 14:46:59' , 'Winner';
>
> WITH CTE AS (
> SELECT Account, Currency, TrdDate, EntryTime, PLInd,
> ROW_NUMBER() OVER(PARTITION BY TrdDate, Account, PLInd ORDER
> BY EntryTime) -
> ROW_NUMBER() OVER(PARTITION BY TrdDate, Account ORDER BY
> EntryTime) AS rnDiff
> FROM Accounts)
> SELECT Account, Currency, TrdDate, EntryTime, PLInd,
> ROW_NUMBER() OVER(PARTITION BY TrdDate, Account, PLInd, rnDiff
> ORDER BY EntryTime)
> FROM CTE
> ORDER BY EntryTime;

From: alexcn on 13 Jul 2010 08:25

From: Mark on 13 Jul 2010 08:43

On 13 July, 13:25, alexcn <ale...(a)writeme.com> wrote:
> Dear Mark,
>
> Many thanks for your prompt reply, it works perfectly so thanks for
> your efforts. I was just wondering whether you could think of a
> possible solution again using the SQL server ranking functions that
> dont use a CTE? Just that this is already part of a much larger query
> which I would prefer not to wrap up or split away in a CTE thats all.
>
> Thanks once again,
>
> Alex
>
> On 13 July, 11:34, Mark <markc...(a)hotmail.com> wrote:
>
>
>
> > CREATE TABLE Accounts(Account CHAR(7), Currency CHAR(3), TrdDate
> > DATETIME, EntryTime DATETIME, PLInd VARCHAR(10));
>
> > INSERT INTO Accounts(Account, Currency, TrdDate, EntryTime, PLInd)
> > SELECT 'AAA0007', 'EUR' , '20100609' , '20100609
> > 08:25:20' , 'Winner' UNION ALL
> > SELECT 'AAA0007', 'EUR' , '20100609' , '20100609
> > 09:18:57' , 'Winner' UNION ALL
> > SELECT 'AAA0007', 'EUR' , '20100609' , '20100609
> > 09:27:51' , 'Winner' UNION ALL
> > SELECT 'AAA0007', 'EUR' , '20100609' , '20100609
> > 10:18:07' , 'Winner' UNION ALL
> > SELECT 'AAA0007', 'EUR' , '20100609' , '20100609
> > 10:49:22' , 'Loser ' UNION ALL
> > SELECT 'AAA0007', 'EUR' , '20100609' , '20100609
> > 11:03:09' , 'Winner' UNION ALL
> > SELECT 'AAA0007', 'EUR' , '20100609' , '20100609
> > 11:38:56' , 'Winner' UNION ALL
> > SELECT 'AAA0007', 'EUR' , '20100609' , '20100609
> > 12:39:25' , 'Loser ' UNION ALL
> > SELECT 'AAA0007', 'EUR' , '20100609' , '20100609
> > 13:26:16' , 'Winner' UNION ALL
> > SELECT 'AAA0007', 'EUR' , '20100609' , '20100609
> > 14:30:31' , 'Winner' UNION ALL
> > SELECT 'AAA0007', 'EUR' , '20100609' , '20100609
> > 14:46:59' , 'Winner';
>
> > WITH CTE AS (
> > SELECT Account, Currency, TrdDate, EntryTime, PLInd,
> > ROW_NUMBER() OVER(PARTITION BY TrdDate, Account, PLInd ORDER
> > BY EntryTime) -
> > ROW_NUMBER() OVER(PARTITION BY TrdDate, Account ORDER BY
> > EntryTime) AS rnDiff
> > FROM Accounts)
> > SELECT Account, Currency, TrdDate, EntryTime, PLInd,
> > ROW_NUMBER() OVER(PARTITION BY TrdDate, Account, PLInd, rnDiff
> > ORDER BY EntryTime)
> > FROM CTE
> > ORDER BY EntryTime;- Hide quoted text -
>
> - Show quoted text -

I think the CTE is unavoidable - SQL Server doesn't support nesting of
ranking functions, you get messages such as this

"Windowed functions cannot be used in the context of another windowed
function or aggregate"

so the solution I used is to split the query into separate CTEs.

| Next | Last
Pages: 1 2
Prev: error when trying to alter table in transaction
Next: On Update Cascade. Should I use it?