SELECT pAuths.*,pAssessments.F177,pAssessments.F178 FROM #Request.prefix_db_agency#.pAuths
LEFT OUTER JOIN #Request.prefix_db_agency#.pAssessments ON pAuths.Assmt_ID = pAssessments.Assmt_ID
AND pAssessments.Status= 0
WHERE pAuths.Auth_ID = '#pAuth_ID#'
AND pAuths.Status= 0
GROUP BY pAuths.Auth_ID
// Define start and end dates for the range
startDate = strating_date; // Example start date: August 1, 2024
endDate = ending_date; // Example end date: August 31, 2024
FirstWeekStart = createDateTime(year(strating_date), month(strating_date), day(strating_date), 0, 0, 0);
// Initialize variables
currentWeekStart = createDateTime(year(strating_date), month(strating_date), day(strating_date), 0, 0, 0);
currentWeekStart = dateAdd("d", -DayOfWeek(currentWeekStart) + 1, currentWeekStart); // Start of the week (Sunday)
weeks = []; // Array to hold week data
week_no = 1;
// Loop through each week in the date range
while (currentWeekStart LTE endDate)
{
// Calculate end of the week
currentWeekEnd = dateAdd("d", 6, currentWeekStart); // End of the week (Saturday)
if (currentWeekEnd GT endDate) {
currentWeekEnd = endDate; // Adjust end date if it exceeds the range
}
// Collect dates for the current week
weekDates = [];
dateIter = currentWeekStart;
while (dateIter LTE currentWeekEnd) {
arrayAppend(weekDates, dateFormat(dateIter, "yyyy-mm-dd"));
dateIter = dateAdd("d", 1, dateIter);
}
// Store week information in the weeks array
if (week_no == 1)
{
arrayAppend(weeks, {start = FirstWeekStart, end = currentWeekEnd, dates = weekDates});
}
else
{
arrayAppend(weeks, {start = currentWeekStart, end = currentWeekEnd, dates = weekDates});
}
//arrayAppend(weeks, {start = currentWeekStart, end = currentWeekEnd, dates = weekDates});
week_no = week_no + 1;
// Move to the next week
currentWeekStart = dateAdd("d", 7, currentWeekStart);
}
// Output the weeks and their dates
// for (week in weeks) {
// writeOutput("Week from " & dateFormat(week.start, "yyyy-mm-dd") & " to " & dateFormat(week.end, "yyyy-mm-dd") & ":
");
// writeOutput("Dates: " & arrayToList(week.dates, ", ") & "
");
// }
UPDATE #Request.prefix_db_agency#.pAuths_weeks SET
Status = ,
Date_Change = ,
Change_by =
WHERE Auth_ID =
AND Status = 0
INSERT INTO #Request.prefix_db_agency#.pAuths_weeks
(
Auth_ID, Week_No, Week_Start, Week_End,
Total_visits,
Date_Create, Created_by, Date_Change, Change_by
)
VALUES
(
,
,
,
,
,
,
,
,
)