LAG function with two different partitions

I have a table with multiple calendars inside it and I need to get the previous business day for each row in the table.

Calendar ID     Date    Business Day    Previous Business Day
AC1         24/12/2030      Y                   -
AC1         25/12/2030      N               24/12/2030
AC1         26/12/2030      N               24/12/2030
AC1         27/12/2030      Y               24/12/2030
AC1         28/12/2030      N               27/12/2030
AC1         29/12/2030      N               27/12/2030
AC1         30/12/2030      Y               27/12/2030
AC1         31/12/2030      Y               30/12/2030
AC2         01/01/2010      Y                   -
AC2         02/01/2010      N               01/01/2010
AC2         03/01/2010      N               01/01/2010
AC2         04/01/2010      Y               01/01/2010
AC2         05/01/2010      Y               04/01/2010

I’ve been trying to use the LAG function to achieve this but I’m unable to get the correct return for every item. I’ve ended up splitting it into two queries. When the day is a business day I can use:

CASE WHEN BUSINESS_DAY = 'Y' THEN LAG(CALENDAR_DATE,1,null) OVER(PARTITION BY CALENDAR_ID Order By CALENDAR_ID asc, CALENDAR_DATE asc) FROM CALENDAR Where BUSINESS_DAY = 'Y' 

When its a Non-Business day I’ve had to put it into a table function and cross apply (there are millions of rows and this is very slow). Is there a better way?

Answer

This is NOT a gaps-and-islands problem. This is merely a cumulative max problem:

select t.*,
       max(case when businessday = 'Y' then date end) over
           (partition by calendarid
            order by date
            rows between unbounded preceding and 1 preceding
           ) as prev_businessday
from t;

EDIT:

The equivalent logic for the next business day is:

       min(case when businessday = 'Y' then date end) over
           (partition by calendarid
            order by date
            rows between 1 following and unbounded following
           ) as next_businessday

You can also reverse the sort:

       min(case when businessday = 'Y' then date end) over
           (partition by calendarid
            order by date desc
            rows between unbounded preceding and 1 preceding
           ) as next_businessday

Personally, I find this version a bit harder to visualize.