Unique Count with ROW_NUMBER()

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

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.

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.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-02-27 : 12:15:34

No mods on this site anymore?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources