Add everyone in Group (ID 6) to each Friday Meal (Template ID 3) from 05/13/2022 -> 12/31/2025 for anyone that is not already signed up for at least one meal on the day.


Bills Script is below

use WEB_UT_KANECOUNTYWEB


-- adds 1 meal for each person in a specific group to whatever weekday indicated only if there are no meals already scheduled for that day for that person


declare @seniors_personid int
declare @start datetime
declare @end datetime
Declare @mealid int
declare @theDay datetime
declare @groupID int
declare @weekday varchar(max)

set @groupID = 6

set @mealid = 3 -- this is the [Meal_ID] of the master template

set @weekday = 'Friday'

set @start = '05/13/2022'

set @end = '12/31/2025'





DECLARE my_cursor CURSOR FOR

SELECT [GroupMembership_SeniorID]
FROM [dbo].[Group_Memberships]
where [GroupMembership_ID] = @groupID

OPEN my_cursor;

FETCH NEXT FROM my_cursor
INTO @seniors_personid

WHILE @@FETCH_STATUS = 0
BEGIN



DECLARE my_cursor2 CURSOR FOR
SELECT Meal_Date FROM dbo.Meals_Available where Meal_Date between @start and @end and DATENAME(WEEKDAY, Meal_Date) = @weekday

OPEN my_cursor2;
FETCH NEXT FROM my_cursor2
INTO @theDay

WHILE @@FETCH_STATUS = 0
BEGIN

IF not exists (

SELECT Meals_Served_SeniorID,Meals_Served_MealID,Meals_Served_Date

FROM dbo.Meals_Served where Meals_Served_SeniorID = @seniors_personid and Meals_Served_Date = @theDay)

BEGIN

INSERT INTO [dbo].[Meals_Served] ([Meals_Served_SeniorID],[Meals_Served_MealID],[Meals_Served_Date],[Meals_Served_Delivered])
SELECT @seniors_personid,[Meal_ID],[Meal_Date],1 FROM [dbo].[Meals_Available] where [Meal_Date] = @theDay

END


FETCH NEXT FROM my_cursor2
INTO @theDay
END


CLOSE my_cursor2;
DEALLOCATE my_cursor2;



FETCH NEXT FROM my_cursor
INTO @seniors_personid
END

CLOSE my_cursor;
DEALLOCATE my_cursor;