Window Function Issue with Results

Question

My CASE Statement is not working, it's not outputting the expected result.

Current Results:

I want the person with the lowest amount of MondayHours to = 1 and so on. If null, then ignore in the window function.

Expected results:

I only need one CASE statement, I've put in a couple I've been working with. If the MondayHours is null, then they need to be ignored in the count. How can I get my expected results (above) with a CASE statement, please?

Sample code for testing:

DROP TABLE IF EXISTS #t1

CREATE TABLE #t1 ( EmpID VARCHAR(36), [IsMonday] bit, IsTuesday bit, IsWednesday bit, IsThursday bit, [MondayHours] decimal(9,2), TuesdayHours decimal(9,2), WednesdayHours decimal(9,2), ThursdayHours decimal(9,2), [LastName] varchar(30), [FirstName] varchar(30) )

INSERT INTO #t1 (EmpID, [IsMonday], IsTuesday, IsWednesday, IsThursday, [MondayHours], TuesdayHours, WednesdayHours, ThursdayHours, [LastName], [FirstName])
VALUES
( 'FDD12035-792D-4C53-80F1-AT28065A0968', 1, NULL, NULL, NULL, 0.00, NULL, NULL, NULL, 'SMITH', 'JOHN' ),
( 'B9968A1F-0203-4A69-EW14-366A05BC2B2E', 1, NULL, NULL, NULL, 1.50, NULL, NULL, NULL, 'HIGHTOWER', 'OFFICER' ),
( 'FDD12035-792D-4C53-80F1-AT28065A0968', NULL, NULL, 1, NULL, NULL, NULL, 18.50, NULL, 'SMITH', 'JOHN' ),
( 'FDD12035-792D-4C53-80F1-AT28065A0968', NULL, NULL, NULL, 1, NULL, NULL, NULL, 0.00, 'SMITH', 'JOHN' )



SELECT empid
, CASE WHEN IsMonday IS NULL THEN NULL ELSE Dense_Rank()OVER(PARTITION BY EmpID  ORDER BY MondayHours, LastName, FirstName) END AS MondayHours_DenseRank_1
, CASE WHEN IsMonday IS NULL THEN NULL ELSE Dense_Rank()OVER( ORDER BY MondayHours, LastName, FirstName) END AS MondayHours_DenseRank_2
, IsMonday
, MondayHours
, LastName
, FirstName
FROM #T1

Answer 1

OK, I will try:

SELECT empid
, CASE WHEN IsMonday IS NULL 
       THEN NULL 
       ELSE Dense_Rank()OVER(ORDER BY MondayHours, LastName, FirstName)-1
       END AS MondayHours_DenseRank_1
, Dense_Rank()OVER(PARTITION BY EmpID  ORDER BY MondayHours, LastName, FirstName) mdr_test
, CASE WHEN IsMonday IS NULL THEN NULL ELSE Dense_Rank()OVER( ORDER BY MondayHours, LastName, FirstName) END AS MondayHours_DenseRank_2
, IsMonday
, MondayHours
, LastName
, FirstName
FROM #T1
ORDER BY MondayHours

output:

empid	MondayHours_DenseRank_1	mdr_test	MondayHours_DenseRank_2	IsMonday	MondayHours	LastName	FirstName
FDD12035-792D-4C53-80F1-AT28065A0968	null	1	null	null	null	SMITH	JOHN
FDD12035-792D-4C53-80F1-AT28065A0968	null	1	null	null	null	SMITH	JOHN
FDD12035-792D-4C53-80F1-AT28065A0968	1	2	2	True	0.00	SMITH	JOHN
B9968A1F-0203-4A69-EW14-366A05BC2B2E	2	1	3	True	1.50	HIGHTOWER	OFFICER

• I added the column mdr_test with the DENSE_RAND, but without the CASE WHEN.
• The first two lines get value 1,
  • then 2 because of the change in MondayHours
  • then 1 again because of the partition by EmpID

When changing that to: Dense_Rank()OVER(ORDER BY -MondayHours desc, LastName, FirstName) mdr_test

• The first two line will the be MondayHours=0 from SMITH
• The second line will be the MondaysHours=1.5, followed by the two lines with a NULL value.

The behaviour is explained by the DENSE_RANK() also counting the row that you exclude by using the CASE WHEN...END construction.