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

Answer 1

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

1. Your 'Expected Output' image seems to be the attendance report with the rank column added so I started with that.
2. 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.
3. 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]