rank() based on certain column condition in SQL

Question

I want to rank the students based on their attendance report on each day. The ranking should start for each student separately. Below is the expected output,

Expected output:

studentid	Date	attendance(Y/N)	rank
3524	01-Jan-2020	N	0
3524	02-Jan-2020	N	0
3524	03-Jan-2020	N	0
3524	04-Jan-2020	Y	1
3524	05-Jan-2020	Y	2
3524	06-Jan-2020	Y	3
3524	07-Jan-2020	N	0
3524	08-Jan-2020	N	0
3524	09-Jan-2020	Y	1
3524	10-Jan-2020	Y	2
3524	11-Jan-2020	Y	3
3524	12-Jan-2020	Y	4
5347	04-Oct-2020	Y	1
5347	05-Oct-2020	Y	2
5347	06-Oct-2020	N	0
5347	07-Oct-2020	N	0
5347	08-Oct-2020	N	0
5347	09-Oct-2020	Y	1
5347	10-Oct-2020	Y	2
5347	11-Oct-2020	Y	3

data:

studentid	startdate	enddate	attendance(Y/N)
3524	01-Jan-2020	03-Jan-2020	N
3524	04-Jan-2020	06-Jan-2020	Y
3524	07-Jan-2020	08-Jan-2020	N
3524	09-Jan-2020	12-Jan-2020	Y
5347	04-Oct-2020	05-Oct-2020	Y
5347	06-Oct-2020	08-Oct-2020	N
5347	09-Oct-2020	11-Oct-2020	Y

output I got from the below code:

studentid	Date	attendance(Y/N)	rank
3524	01-Jan-2020	N	0
3524	02-Jan-2020	N	0
3524	03-Jan-2020	N	0
3524	04-Jan-2020	Y	1
3524	05-Jan-2020	Y	2
3524	06-Jan-2020	Y	3
3524	07-Jan-2020	N	0
3524	08-Jan-2020	N	0
3524	09-Jan-2020	Y	4
3524	10-Jan-2020	Y	5
3524	11-Jan-2020	Y	6
3524	12-Jan-2020	Y	7
5347	04-Oct-2020	Y	1
5347	05-Oct-2020	Y	2
5347	06-Oct-2020	N	0
5347	07-Oct-2020	N	0
5347	08-Oct-2020	N	0
5347	09-Oct-2020	Y	4
5347	10-Oct-2020	Y	5
5347	11-Oct-2020	Y	6

If they are absent on particular day i.e. N, then the rank should be 0, else the rank should be given.

I have tried with the below code, but I am unable to achieve the expected output.

CASE 
WHEN [Date] BETWEEN StartDate AND EndDate AND attendance='N' THEN 0
WHEN [Date] BETWEEN StartDate AND EndDate AND attendance='Y'
THEN RANK() OVER (
PARTITION BY studentid,attendance ORDER BY [Date])
ELSE 0
END AS ranking

The presence of square brackets as identifier delimiters (e.g. `[Date]`) is typical of Microsoft SQL Server, but not MySQL. It's important to tag your question accurately, to make it more likely a solution is posted that will work for your instance. Can you please run `SELECT @@version;` and report what that returns? — Bill Karwin, Apr 06 '23 at 16:44
I am using MSSQL, but the tag is not listed when I searched for. Now edited the tag by adding SQL server. Let me know the available tag that suits well for the MSSQL. — Beginner, Apr 06 '23 at 16:47
Please provides INSERTS SQL code not pictures. Parsing our picture into our SQL editors is quite impossible ! While you are in SM SQL Server there is a wizard for that ! — SQLpro, Apr 06 '23 at 17:16
Should a "non working period" break the ranking of attendancy or not? For example: 1, 2, 3, holiday. Should there be 4 next? or 1 again? — siggemannen, Apr 06 '23 at 17:33
As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. — Dale K, Apr 06 '23 at 20:19
@siggemannen non working period should break the ranking. i.e. 1,2,3, holiday, then it must start again from 1,2,3... — Beginner, Apr 14 '23 at 16:01

score 1 · Answer 1 · answered Apr 06 '23 at 17:41

Your data is a bit of a mess which makes it difficult to figure out exactly what you're trying to do.

Your 'Expected Output' image seems to be the attendance report with the rank column added so I started with that.
Your holiday table image ('data') seems to me to be unnecessary. If it's a holiday, the students won't show up at school which would be reflected on the attendance report.
I'm not sure what happens if a student misses a day. Does the rank start back over at 1 or do you keep counting from where it was?

With that in mind, I wrote this which produces the Expected Output. Fiddle

  SELECT StudentID
       , [Date]
       , Attendance
       , CASE WHEN Attendance = 'N' THEN 0
              ELSE RANK() OVER (PARTITION BY StudentID, Attendance ORDER BY [Date]) 
          END AS Rnk
    FROM data
ORDER BY StudentID, [Date]

I'm not sure what happens if a student misses a day. Does the rank start back over at 1 or do you keep counting from where it was? - my answer to this is the rank must start over at 1 — Beginner, Apr 14 '23 at 16:02

rank() based on certain column condition in SQL

1 Answers1