0

I'm trying to find the last Sunday's date of a month using the HQL.

I'm trying the below code:

select Stud_no,doj,last_value(doj) OVER(partition by Stud_no) 
from(select Stud_no,doj,date_format(doj,'E') as 
day_name 
from Students where date_format(doj,'E')='Sun')sq
group by Stud_no,doj,date_format(doj,'Y'),date_format(doj,'M') 
order by doj

But the above code gives the last day of the table.

The Output which i'm getting is:

Stud_no    doj          last_sunday
001        21-01-2019   30-06-2019
001        22-01-2019   30-06-2019
001        23-01-2019   30-06-2019
001        24-01-2019   30-06-2019
001        25-01-2019   30-06-2019
001        26-01-2019   30-06-2019

The expected output is:

Stud_no   last_sunday
001       27-01-2019
001       24-02-2019
001       31-03-2019
001       26-04-2019
001       26-05-2019
001       30-06-2019
001       28-07-2019
001       25-08-2019

Can someone help me here?

Sara
  • 312
  • 6
  • 15
  • we come to know your expected output. Could you please show us your actual input also. Just in case would be easy to understand. – vikrant rana Sep 03 '19 at 18:11
  • @vikrantrana i have added the input as well as output which i'm getting – Sara Sep 04 '19 at 04:58
  • It's little unclear here. we want your actual input and based on that your expectation. Also I was not able to relate your last_sunday calculation with respect to doj. Could you also explain that part a bit. I mean the logic behind this calculation. Thanks – vikrant rana Sep 04 '19 at 05:12
  • Dates is not in the proper format. It should be yyyy-MM-dd. Convert to the proper format first – leftjoin Dec 30 '19 at 15:55

0 Answers0