Required something reusable and could not find a SQL equivalent of the PL/SQL NEXT_DAY function.
This function returns the week end date for the specified interval.
Defined week as ending on Sunday and interval as weeks, this can be changed as needed, just more relevant to this particular Business Intelligence team.
In actual use would be aggregating several daily financial transactions to a week ending date, and limiting data for instance for the past 26 or 52 or 104 weeks, or similarly trying to capture if any data exists for future weekly intervals. Some of the reports or data extraction tools are also scheduled to execute weekly or monthly. For example the weekly promotional sales report for past and upcoming promotions executes every Wednesday, but may also be pulled earlier or later in the week if needed. Can also use the function to list Sunday the date for every week in a given time period.
Some simple examples of how to pull past or future week ending Sunday dates are listed below.
Examples:
--Week ending Sunday three weeks from today
SELECT dbo.fn_LastSunday(3) as LastSunday, Convert(nchar(8),getdate(),3) as Today
--Week ending Sunday three weeks ago
SELECT dbo.fn_LastSunday(-3) as LastSunday, Convert(nchar(8),getdate(),3)as Today
--Last week's date ending on Sunday
SELECT dbo.fn_LastSunday(0) as LastSunday, Convert(nchar(8),getdate(),3) as Today
--Current week's date ending this coming Sunday
SELECT dbo.fn_LastSunday(1) as LastSunday, Convert(nchar(8),getdate(),3) as Today
