SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Unique Count with ROW_NUMBER()
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dssrun
Starting Member

18 Posts

Posted - 02/26/2013 :  11:43:25  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4612 Posts

Posted - 02/26/2013 :  12:25:01  Show Profile  Reply with Quote
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 - 02/26/2013 :  12:43:15  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4612 Posts

Posted - 02/26/2013 :  13:00:12  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4612 Posts

Posted - 02/27/2013 :  12:15:34  Show Profile  Reply with Quote
No mods on this site anymore?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000