英文:
SQL stored procedure to determine age and changing the value of a row in another table depending upon the age
问题
这是前一个问题的继续链接。
所以我有一个名为GenericAttribute的表，其中包含以下值:
| Id | KeyGroup | Key | Value |
|---|---|---|---|
| 28 | Customer | DateOfBirth | 26-01-2000 |
| 29 | Customer | DateOfBirth | 26-01-2020 |
| 30 | Customer | CountryPage.HideStatesBlock | FALSE |
我有另一个名为RoleMapper的表，它根据客户的ID将其映射到其角色ID。GenericAttribute中的Id是从RoleMapper表的CustomerID列派生的外键，如下所示。
| CustomerID | CustomerRoleId |
|---|---|
| 28 | 58 |
| 29 | 27 |
我的意图是创建一个SQL代理作业，其中包含一个存储过程，如果客户今天年龄超过60岁，将RoleMapper表的值更新为24。触发器必须每天激活一次。
我正在使用SQL Server。
我尝试使用基于我以前问题中给出的答案的此查询。
select [id] from [Genericattribute]
where [key] = 'DateOfBirth'
and right(value,5)=format(getdate(),'MM-dd')
虽然我能够得到今天生日的人的答案，但当有多人在同一天过生日时，即使在使用表数据类型后，我也无法继续。
英文:
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.
答案1
得分: 1
尝试使用以下查询安排 SQL Server 代理作业：
-- 旧查询
-- DECLARE @Today Date = GETDATE();
;
WITH CTE AS
(
SELECT *, RIGHT(value, 4) + '-' + SUBSTRING(value, 4, 2) + '-' + LEFT(value, 2) AS [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]));
DECLARE @Today Date = GETDATE();
;
WITH CTE AS
(
SELECT *
FROM GenericAttribute
WHERE [key] = 'DateOfBirth'
)
SELECT *
FROM RoleMapper RoleMapper
JOIN CTE ON CTE.ID = RoleMapper.CustomerID
WHERE DATEDIFF(YEAR, value, @Today) > 60
OR (DATEDIFF(YEAR, value, @Today) = 60 AND MONTH(@Today) >= MONTH(value) AND DAY(@Today) >= DAY(value));
英文:
Try Schedule a SQL server agent job with the below query
--OLD 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]))
DECLARE @Today Date=GETDATE()
;WITH CTE
AS
(
SELECT *
FROM GenericAttribute
WHERE [key] = 'DateOfBirth'
)
SELECT *
FROM RoleMapper RoleMapper
JOIN CTE
ON CTE.ID =RoleMapper.CustomerID
WHERE DATEDIFF(YEAR,value,@Today)>60
OR (DATEDIFF(YEAR,value,@Today)=60 AND MONTH(@Today)>=MONTH(value) AND DAY(@Today)>=DAY(value))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论