Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Unique Count with ROW_NUMBER()

Author  Topic 

dssrun
Starting Member

18 Posts

Posted - 2013-02-26 : 11:43:25
I am trying to create a unique counting column with ROW_NUMBER() (or maybe row_number is not the best choice?) and here is my scenario. I have an EmployeeId INT, DOS DATE, SupervisorId INT, ProblemCode VARCHAR(10), TransID INT. I want to uniquely count the EmployeeId, DOS and SupervisorId only if the ProblemCode is different and the Supervisor is different. I want to use ROW_NUMBER() OVER(PARTITION BY EmployeeId, DOS, ?? ORDER BY TransId) within an inner query and then in the outer query filter on that column = 1. For example, the count below should be 2 because the new supervisor has a problem code that is different from the first supervisor.

Record 1:
EmployeeId = 1
DOS = 20120101
SupervisorId = 10
ProblemCode = f25

Record 2:
EmployeeId = 1
DOS = 20120101
SupervisorId = 10
ProblemCode = f10

Record 3:
EmployeeId = 1
DOS = 20120101
SupervisorId = 10
ProblemCode = f01

Record 4:
EmployeeId = 1
DOS = 20120101
SupervisorId = 26
ProblemCode = f01

Record 5:
EmployeeId = 1
DOS = 20120101
SupervisorId = 26
ProblemCode = f55

For example, today I have an employee that goes to Supervisor 10 and that supervisor documents 3 problem codes (which counts as one visit). On that same day the employee goes to Supervisor 26. Supervisor 26 document one of the same ProblemCodes that supervisor 10 did and one new ProblemCode. I want to ignore the first problem code documented by supervisor 26, but count the new ProblemCode because that is considered "a new visit". Does that make more sense? So for each record the ROW_COUNT() should look like this...

1

2

3

4

1



Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-02-26 : 12:25:01
Although I have a solution that works for your sampel data, I suspect that you might have issues with "real" data:
DECLARE @Foo TABLE 
(
Record INT,
EmployeeId INT,
DOS INT,
SupervisorId INT,
ProblemCode CHAR(3)
)
INSERT @Foo VALUES
(1, 1, 20120101, 10, 'f25'),
(2, 1, 20120101, 10, 'f10'),
(3, 1, 20120101, 10, 'f01'),
(4, 1, 20120101, 26, 'f01'),
(5, 1, 20120101, 26, 'f55')

SELECT
*,
ROW_NUMBER() OVER
(
PARTITION BY
EmployeeID,
DOS,
CASE
WHEN
(
SupervisorID <> PreviousSupervisorID
AND ProblemCode = PreviousProblemCode
)
THEN
PreviousSupervisorID
ELSE
SupervisorID
END
ORDER BY
Record
) AS RowNum
FROM
(
SELECT
*,
LAG(SupervisorID, 1, NULL) OVER (ORDER BY Record) AS PreviousSupervisorID,
LAG(ProblemCode, 1, NULL) OVER (ORDER BY Record) AS PreviousProblemCode
FROM @foo
) AS A
Go to Top of Page

dssrun
Starting Member

18 Posts

Posted - 2013-02-26 : 12:43:15
Thank you and yes this is a pretty big view i am querying so i imagine that would be slow, I also should have prefaced that i am using SQL Server 2008 R2. Maybe ROW_NUMBER() is not the best option. I am trying to think of other fuctions.
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-02-26 : 13:00:12
Yeah, this is the 2012 forum, so my solution relies on 2012 specic functionality.

Can a moderator move this to the 2008 forum.
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-02-27 : 12:15:34
No mods on this site anymore?
Go to Top of Page
   

- Advertisement -