mister.magoo (11/19/2011)

If you use JonFox' sample data...

SELECT RecordSeq, Account, ActivityDate, Rep,dense_rank() OVER(PARTITION BY Account ORDER BY rep)

from #Cases

If I'm understanding the OP correctly, that's not quite what he's looking for.

texpic

If it passes back to a previous worker, count is increased.

That would seem to mean that for Account A1001, Jim should have "1" for the first three rows, then Doug should have "2" for the next two, then when it gets assigned back to Jim it should change to "3".

JonFox (11/19/2011)

mister.magoo (11/19/2011)

If you use JonFox' sample data...

SELECT RecordSeq, Account, ActivityDate, Rep,dense_rank() OVER(PARTITION BY Account ORDER BY rep)

from #Cases

If I'm understanding the OP correctly, that's not quite what he's looking for.

texpic

If it passes back to a previous worker, count is increased.That would seem to mean that for Account A1001, Jim should have "1" for the first three rows, then Doug should have "2" for the next two, then when it gets assigned back to Jim it should change to "3".

yes you are right - I misread it - i should probably sleep -thanks

MM

`select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);`

First the test data:

`IF OBJECT_ID('TempDB..#Table') IS NOT NULL`

```
```DROP TABLE #Table;

CREATE TABLE #Table

(

RecordSeq INT PRIMARY KEY CLUSTERED

,Account VARCHAR(10)

,Date DATE

,Rep VARCHAR(10)

,ExpectedResult INT

);

insert #Table

SELECT 1000 ,'A1001', '08/01/11' ,'Jim', 1

UNION ALL SELECT 1001 ,'A1001', '08/06/11' ,'Jim', 1

UNION ALL SELECT 1002 ,'A1001', '08/11/11' ,'Jim', 1

UNION ALL SELECT 1003 ,'A1001', '08/16/11' ,'Doug', 2

UNION ALL SELECT 1004 ,'A1001', '08/21/11' ,'Stan', 3

UNION ALL SELECT 1005 ,'A1001', '08/26/11' ,'Stan', 3

UNION ALL SELECT 1006 ,'A1001', '08/31/11' ,'Stan', 3

UNION ALL SELECT 1007 ,'A1001', '09/05/11' ,'Stan', 3

UNION ALL SELECT 1008 ,'A1001', '09/10/11' ,'Stan', 3

UNION ALL SELECT 1009 ,'A1001', '09/15/11' ,'Stan', 3

UNION ALL SELECT 1010 ,'A1001', '09/20/11' ,'Stan', 3

UNION ALL SELECT 1011 ,'A1001', '09/25/11' ,'Stan', 3

UNION ALL SELECT 1012 ,'A1001', '09/30/11' ,'Jim', 4

UNION ALL SELECT 1013 ,'A1001','10/05/11' ,'Jim', 4

UNION ALL SELECT 1014 ,'A1001','10/10/11' ,'Jim', 4

UNION ALL SELECT 1015 ,'A1001','10/15/11' ,'Jim', 4

UNION ALL SELECT 1016 ,'A1001','10/20/11' ,'Jim', 4

UNION ALL SELECT 1017 ,'A1001','10/25/11' ,'Jim', 4

UNION ALL SELECT 1018 ,'A1001','10/30/11' ,'Jim', 4

UNION ALL SELECT 1019 ,'A1001','11/04/11' ,'Doug', 5

UNION ALL SELECT 1020 ,'A1002', '08/01/11' ,'Jim', 1

UNION ALL SELECT 1021 ,'A1002', '08/06/11' ,'Jim', 1

UNION ALL SELECT 1022 ,'A1002', '08/11/11' ,'Jim', 1

UNION ALL SELECT 1023 ,'A1002', '08/16/11' ,'Doug', 2

UNION ALL SELECT 1024 ,'A1002', '08/21/11' ,'Stan', 3

UNION ALL SELECT 1025 ,'A1002', '08/26/11' ,'Jim', 4

UNION ALL SELECT 1026 ,'A1002', '08/31/11' ,'Jim', 4

UNION ALL SELECT 1027 ,'A1002', '09/05/11' ,'Jim', 4

UNION ALL SELECT 1028 ,'A1002', '09/10/11' ,'Jim', 4

UNION ALL SELECT 1029 ,'A1002', '09/15/11' ,'Jim', 4

UNION ALL SELECT 1030 ,'A1002', '09/20/11' ,'Jim', 4

UNION ALL SELECT 1031 ,'A1002', '09/25/11' ,'Jim', 4

UNION ALL SELECT 1032 ,'A1002', '09/30/11' ,'Jim', 4

UNION ALL SELECT 1033 ,'A1002','10/05/11' ,'Jim', 4

UNION ALL SELECT 1034 ,'A1002','10/10/11' ,'Jim', 4

UNION ALL SELECT 1035 ,'A1002','10/15/11' ,'Jim', 4

UNION ALL SELECT 1036 ,'A1002','10/20/11' ,'Jim', 4

UNION ALL SELECT 1037 ,'A1002','10/25/11' ,'Jim', 4

UNION ALL SELECT 1038 ,'A1002','10/30/11' ,'Jim', 4

UNION ALL SELECT 1039 ,'A1002','11/04/11' ,'Jim', 4;

CREATE NONCLUSTERED INDEX NIX_Table_Acct_Date

`ON #Table( Account , Date) INCLUDE ( Rep );`

Now the code:

```
```WITH NumberedData AS

(

SELECT *

, RN1 = ROW_NUMBER() OVER ( PARTITION BY Account,Rep ORDER BY RecordSeq )

, RN2 = ROW_NUMBER() OVER ( PARTITION BY Account ORDER BY RecordSeq )

FROM #Table

)

, MaxRecNumber AS

(

SELECT RecordSeq, Account, Rep , Date , ExpectedResult

, RMax = MAX(RecordSeq) OVER (PARTITION BY Account,Rep,(RN1-RN2))

FROM NumberedData

)

SELECT * , ActualResult = DENSE_RANK() OVER(PARTITION BY Account ORDER BY RMax)

FROM MaxRecNumber

`ORDER BY RecordSeq`

First, nicely done there Cold Coffee. I came up with an almost identical solution and saw yours when I was getting ready to post it. Your solution uses MAX() OVER (brilliant idea, BTW) which I believe is going to be more effecient than the method I'm using to calculate a pseudo-group number because I use an extra ROW_NUMBER() OVER to do it. That's provided that the RecordSeq column always reflects the correct order by Account and Date. If it doesn't, then there will be a problem. Replace the 8/6 date for the second row of "Jim" with 9/6 and see what I mean.

Here's the test data setup using the OP's original data...

`--=======================================================================================`

```
```-- Create and populate a test table from the data given in the post.

-- Note that NOTHING in this section is a part of the solution. We're just creating

-- test data to demonstrate the solution here.

--=======================================================================================

--===== Conditionally drop the test table to make reruns in SSMS easier

IF OBJeCT_ID('TempDB..#TestTable','U') IS NOT NULL DROP TABLE #TestTable;

GO

--===== Create and populate the test table on the fly.

SELECT RecordSeq = ISNULL(CAST(d.RecordSeq AS INT),0), --ISNULL makes a NOT NULL column

Account = CAST(d.Account AS CHAR(5)),

Date = CAST(d.Date AS DATETIME),

Rep = CAST(d.Rep AS VARCHAR(10))

INTO #TestTable

FROM (

SELECT '1000','A1001','08/01/11','Jim' UNION ALL

SELECT '1001','A1001','08/06/11','Jim' UNION ALL

SELECT '1002','A1001','08/11/11','Jim' UNION ALL

SELECT '1003','A1001','08/16/11','Doug' UNION ALL

SELECT '1004','A1001','08/21/11','Stan' UNION ALL

SELECT '1005','A1001','08/26/11','Stan' UNION ALL

SELECT '1006','A1001','08/31/11','Stan' UNION ALL

SELECT '1007','A1001','09/05/11','Stan' UNION ALL

SELECT '1008','A1001','09/10/11','Stan' UNION ALL

SELECT '1009','A1001','09/15/11','Stan' UNION ALL

SELECT '1010','A1001','09/20/11','Stan' UNION ALL

SELECT '1011','A1001','09/25/11','Stan' UNION ALL

SELECT '1012','A1001','09/30/11','Jim' UNION ALL

SELECT '1013','A1001','10/05/11','Jim' UNION ALL

SELECT '1014','A1001','10/10/11','Jim' UNION ALL

SELECT '1015','A1001','10/15/11','Jim' UNION ALL

SELECT '1016','A1001','10/20/11','Jim' UNION ALL

SELECT '1017','A1001','10/25/11','Jim' UNION ALL

SELECT '1018','A1001','10/30/11','Jim' UNION ALL

SELECT '1019','A1001','11/04/11','Doug' UNION ALL

SELECT '1020','A1002','08/01/11','Jim' UNION ALL

SELECT '1021','A1002','08/06/11','Jim' UNION ALL

SELECT '1022','A1002','08/11/11','Jim' UNION ALL

SELECT '1023','A1002','08/16/11','Doug' UNION ALL

SELECT '1024','A1002','08/21/11','Stan' UNION ALL

SELECT '1025','A1002','08/26/11','Jim' UNION ALL

SELECT '1026','A1002','08/31/11','Jim' UNION ALL

SELECT '1027','A1002','09/05/11','Jim' UNION ALL

SELECT '1028','A1002','09/10/11','Jim' UNION ALL

SELECT '1029','A1002','09/15/11','Jim' UNION ALL

SELECT '1030','A1002','09/20/11','Jim' UNION ALL

SELECT '1031','A1002','09/25/11','Jim' UNION ALL

SELECT '1032','A1002','09/30/11','Jim' UNION ALL

SELECT '1033','A1002','10/05/11','Jim' UNION ALL

SELECT '1034','A1002','10/10/11','Jim' UNION ALL

SELECT '1035','A1002','10/15/11','Jim' UNION ALL

SELECT '1036','A1002','10/20/11','Jim' UNION ALL

SELECT '1037','A1002','10/25/11','Jim' UNION ALL

SELECT '1038','A1002','10/30/11','Jim' UNION ALL

SELECT '1039','A1002','11/04/11','Jim'

) d (RecordSeq,Account,Date,Rep)

;

--===== Add what seems to be the PK of this table

ALTER TABLE #TestTable

ADD PRIMARY KEY CLUSTERED (RecordSeq)

`;`

Here's my solution to the problem. Like I said, almost identical but will withstand out-of-order dates.

`--=======================================================================================`

```
```-- Solve the problem using cascaded list sequencing to create groupings along the

-- way and then correctly number the groupings to produce the NEED column

--=======================================================================================

WITH

cteEnumerate AS

(

SELECT *,

Sequence1 = ROW_NUMBER() OVER (PARTITION BY Account ORDER BY Account, Date, Rep),

Sequence2 = ROW_NUMBER() OVER (PARTITION BY Account, Rep ORDER BY Account, Date, Rep)

FROM #TestTable

)

,

cteOrderedGroups AS

(

SELECT RecordSeq, Account, Date, Rep,

OrderedGroup = Sequence1 - ROW_NUMBER() OVER (PARTITION BY Account, (Sequence1-Sequence2) ORDER BY Sequence1)

FROM cteEnumerate

)

SELECT RecordSeq, Account, Date, Rep,

Need = DENSE_RANK() OVER (PARTITION BY Account ORDER BY OrderedGroup)

FROM cteOrderedGroups

ORDER BY Account, Date

`;`

--Jeff Moden

________

"Change is inevitable... change for the better is not".

"If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

How to post code problems

How to Post Performance Problems

Create a Tally Function (fnTally)

Jeff Moden (11/19/2011)

First, nicely done there Cold Coffee. I came up with an almost identical solution and saw yours when I was getting ready to post it. Your solution uses MAX() OVER (brilliant idea, BTW) which I believe is going to be more effecient than the method I'm using to calculate a pseudo-group number because I use an extra ROW_NUMBER() OVER to do it.

Thanks Jeff; this means a lot, really a lot, to me 🙂

Jeff Moden (11/19/2011)

That's provided that the RecordSeq column always reflects the correct order by Account and Date. If it doesn't, then there will be a problem. Replace the 8/6 date for the second row of "Jim" with 9/6 and see what I mean

Yeah, i see your point Jeff. But i assumed that RecordSeq will move forward sequentially with date and for every block it will be in incremental order. The OP told us "*When there is a new worker assigned to the account, I'd like to indicate this is the Xth person to work on the account*" which i assumed to be in chronological.

Understood. I never assume the OP's description is correct in their request and I try to protect them as best I can when I think I see a less than bullet-proof set of requirements. I'll do the same thing at work when given requirements that I don't agree with except I'll get the requirements changed there so cuffs-match-collar between the code, the documentation, and the requirements. 🙂 I'll admit that it sometimes takes a bit of "what if" mind reading, though. :hehe:

--Jeff Moden

________

"Change is inevitable... change for the better is not".

"If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

How to post code problems

How to Post Performance Problems

Create a Tally Function (fnTally)

I knew there had to be a better way than my original attempt! Looking forward to spending some time later today digging in and understanding how these solutions work...

JonFox (11/20/2011)

I knew there had to be a better way than my original attempt! Looking forward to spending some time later today digging in and understanding how these solutions work...

This site has more than a million members. Only 4 of us responded to this thread and you were the first of the 4 to crank out some test data on this thread which is incredibly important IMHO on any of these threads. You took a great shot at a possible solution and then you were humble enough to report a potential performance problem in your own code.

Whether your solution was effecient or not, my hat is off to you for trying, Jon. Please don't ever change.

--Jeff Moden

________

"Change is inevitable... change for the better is not".

"If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

How to post code problems

How to Post Performance Problems

Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply