SQL Server SQL stored procedure to determine age and changing the value of a row in another table depending upon the age

fiei3ece 于 2023-02-18 发布在其他

关注(0)|答案(1)|浏览(120)

This is a continuation of the previous question here .

So I have a table named GenericAttribute which has some values like this:
| Id | KeyGroup | Key | Value |
| ------------ | ------------ | ------------ | ------------ |
| 28 | Customer | DateOfBirth | 26-01-2000 |
| 29 | Customer | DateOfBirth | 26-01-2020 |
| 30 | Customer | CountryPage.HideStatesBlock | FALSE |

I have another table named RoleMapper that maps a customer based on their ID to their role ID. The Id in the GenericAttribute is the Foreign Key which originates from the CustomerID column of the RoleMapper table, below.

CustomerID	CustomerRoleId
28	58
29	27

My intention is to create a SQL agent job with a stored procedure that updates the RoleMapper table value to 24, if a customer's age is more than 60, today. The trigger must be activated once a day.

I am using SQL Server.

I tried using this query based on the answer given in my previous question.

select [id] from [Genericattribute]
where [key] = 'DateOfBirth'
  and right(value,5)=format(getdate(),'MM-dd')

Though I was able to get an answer to whose birthday was today, when more than one people had their birthdays on the same day, I was unable to proceed even after using a table data type.

sql-server

来源：https://stackoverflow.com/questions/75382441/sql-stored-procedure-to-determine-age-and-changing-the-value-of-a-row-in-another

1条答案

按热度按时间

qzwqbdag1#

Try Schedule a SQL server agent job with the below query

DECLARE @Today Date=GETDATE()
        
        ;WITH CTE
        AS
        (   
            SELECT  *,RIGHT(value,4)+'-'+SUBSTRING(value,4,2)+'-'+LEFT(value,2)[Date]
            FROM    GenericAttribute
            WHERE   [key] = 'DateOfBirth'
        )
        UPDATE  RoleMapper
        SET     CustomerRoleId=24 
        FROM    RoleMapper RoleMapper
        JOIN    CTE
            ON  CTE.ID =RoleMapper.CustomerID
        WHERE   DATEDIFF(YEAR,[Date],@Today)>60 
            OR (DATEDIFF(YEAR,[Date],@Today)=60 AND MONTH(@Today)>=MONTH([Date]) AND DAY(@Today)>=DAY([Date]))

赞(0）回复(0）举报 2023-02-18

我来回答

SQL Server SQL stored procedure to determine age and changing the value of a row in another table depending upon the age

1条答案

相关问题

热门标签

最新问答