3a2c15ce-9604-430a-9e06-f2c566818830

Assigned To

Center Contact

Title

Status

Data Import

Entered By

Center Contact Phone/Email Etc.

Date Due

Date Completed

Notes

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;

6f33fc0d-da7d-4628-8b33-f5b3b298678d

Cancel