Accepted Solutions (0)
Answers (1)
I do not know how to do this natively in WebI although it likely is. Perhaps my solution using free-hand SQL employing MIN() and MAX() functions with the OVER clause will work for you or spark another idea.
The functional SQL Server SQL code can be seen here.
I have repeated the code below with the exception of uncommenting the line prompting for a date and commenting the line with the hard-coded date.
DECLARE @StartDate DATE;
DECLARE @EndDate DATE;
DECLARE @WeekOffset INT;
DECLARE @WeeksInYear INT;
SET @StartDate = @Prompt('Enter Starting Date:','D',,Mono,Free,Not_Persistent,,User:0)
--SET @StartDate = '2024-04-28';
SET @EndDate = DATEADD (DAY, -1, DATEADD (WEEK, 20, @StartDate));
SET @WeekOffset = DATEPART (WEEK, @StartDate) - 1;
-- @WeeksInYear is to account for some years having more weeks than most (e.g., 2028)
SET @WeeksInYear = DATEPART (WEEK, DATEFROMPARTS (YEAR (@StartDate), 12, 31));
--SELECT
-- @StartDate AS [StartDate]
-- , @EndDate AS [EndDate]
-- , @WeekOffset AS [WeekOffset]
-- , @WeeksInYear AS [WeeksInYear];
WITH dates_cte (CalendarDate) AS
(
SELECT @StartDate AS [CalendarDate] -- Put the start date here
UNION ALL
SELECT DATEADD (DAY, 1, CalendarDate)
FROM dates_cte
WHERE CalendarDate < @EndDate -- Put the end date here
)
SELECT DISTINCT
IIF(DATEPART (WEEK, d.CalendarDate) - @WeekOffset < 0
, (@WeeksInYear - 1) + (DATEPART (WEEK, d.CalendarDate) - @WeekOffset)
, DATEPART (WEEK, d.CalendarDate) - @WeekOffset) AS [WeekNumber] -- necessary to span the end of the year
, MIN (d.CalendarDate) OVER (PARTITION BY
DATEPART (MONTH, d.CalendarDate)
, DATEPART (WEEK, d.CalendarDate)
) AS [FirstDayOfWeek]
, MAX (d.CalendarDate) OVER (PARTITION BY
DATEPART (MONTH, d.CalendarDate)
, DATEPART (WEEK, d.CalendarDate)
) AS [LastDayOfWeek]
FROM dates_cte d
ORDER BY WeekNumber
, FirstDayOfWeek
OPTION (MAXRECURSION 32767);
The dates_cte CTE generates all of the dates between your Start Date and End Date (Start Date + 20 weeks - 1 day). If you have a Calendar table you could use that with the same date range instead of the CTE. The MIN() and MAX() functions find the first and last day of each unique combination of Month and Week yielding this...
If you use a database other than SQL Server my code will likely need to be adjusted. If you are not able to do that you will need to wait for the native WebI solution.
In any case, please do your own thorough testing with a variety of dates...
- All dates within one year
- Dates that span more than one year
- Dates that include a year with more weeks than most. Seems like that shouldn't happen, but it does in SQL Server (e.g., compare the week number returned by the DATEPART(WEEK, '2024-12-31') to that of DATEPART(WEEK, '2024-12-31'))
Hope this works for you or gets you going down the right path. I may try giving this a try natively in WebI with some calculation contexts when I have more time.
Noel
| User | Count |
|---|---|
| 72 | |
| 8 | |
| 8 | |
| 7 | |
| 6 | |
| 6 | |
| 5 | |
| 5 | |
| 5 | |
| 4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.