SQL Help - Update age based on current date

It worked perfectly

Thanks for your help.

Jason Combs | Director of Information Technology |jason.combs@strazcenter.org

David A. Straz, Jr. Center for the Performing Arts |

1010 North W.C Macinnes Pl | Tampa, Florida 33602
O: 813.222.1092 F: 813.222.1057

From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Travis Armbuster
Sent: Wednesday, February 18, 2015 11:09 AM
To: Combs, Jason
Subject: Re: [Tessitura Technical Forum] SQL Help - Update age based on current date

This has not bee tested, but I believe it will get you started. As always do this test before just placing it live. Hope it helps!

Travis

--Declare @BDate datetime2
Declare @CDate datetime2
Declare @CAge int
Declare @age int
declare @min int
declare @max int

 
declare @work table(
customer_no int,
birth_dt datetime,
age int)

--collect everyone in work table
insert into @work
select customer_no, birth_date,age from LT_EDU_REGISTRATION where id_key = 5

--set existing birthday from Table
--Set @BDate = (select birth_date from LT_EDU_REGISTRATION where id_key = 5) --NEED TO LOOP


--set current date, @min and @max

set @min = (select MIN(customer_no) from @work)
set @max = (select MAX(customer_no) from @work)
Set @CDate = GetDate()


--start looping
While @max >= @min
begin

--calculate age
Set @CAge = (select CASE
                        WHEN dateadd(year, datediff (year, birth_dt, @CDate), birth_dt) > @CDate
                        THEN datediff (year, birth_dt, @CDate) - 1
                        ELSE datediff (year, birth_dt, @CDate)
                    end
             from @work where customer_no = @min)
--set existing age from table
Set @age = (select age from @work where customer_no = @min) --NEED TO LOOP

--check if age is different or null
IF @Age <> @CAge or
@Age is null
begin
--update with current age

select @age, @CAge as new_birthday --if it looks good remove this line and uncomment the update statement

--UPDATE b
--SET b.age    = @CAge
--from @work a
--join LT_EDU_REGISTRATION b on a.customer = b.customer_no
--where a.customer = @min


end

--increment the min

set @min = (select MIN(customer_no) from @work where customer_no > @min)

end