• Replies 4 replies
  • Subscribers 338 subscribers
  • Views 42 views
  • Users 0 members are here
Related

SQL Help - Update age based on current date

Jason Combs (Past Member)
$organization

Hello group,

I am working on a stored procedure to update our students ages based upon the current date. I wrote the SQL below and it looks to be a good start but I need help with looping through the data. 

Can you assist?

Declare @BDate datetime2

Declare @CDate datetime2

Declare @CAge int

Declare @age int

 

--set existing birthday from Table

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

 

--set current date

Set @CDate = GetDate()

 

--calculate age

Set @CAge = CASE

WHEN dateadd(year, datediff (year, @BDate, @CDate), @BDate) > @CDate

THEN datediff (year, @BDate, @CDate) - 1

ELSE datediff (year, @BDate, @CDate)

end

 

--set existing age from table

Set @age = (select age from LT_EDU_REGISTRATION where id_key = 5) --NEED TO LOOP

 

--check if age is different or null

IF @Age <> @CAge or

@Age is null

 

--update with current age

UPDATE LT_EDU_REGISTRATION

SET age = @CAge

ELSE

end

 

Parents
  • $organization

    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! Feel free to email me too.

     

    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



    [edited by: Travis Armbuster at 11:32 AM (GMT -6) on 18 Feb 2015]
Reply
  • $organization

    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! Feel free to email me too.

     

    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



    [edited by: Travis Armbuster at 11:32 AM (GMT -6) on 18 Feb 2015]
Children
No Data