TSQL - 小于或大于日期

2021-09-10 00:00:00 tsql sql-server

我想生成一个查询,显示如果 startdate 小于 4 个月,则给出 5%,如果它大于 4 个月给出 10%.

I would like to produce a query that shows if startdate is less than 4 months old give 5% if its older than 4 months give 10%.

我正在使用 SQLFiddle,但没有得到正确的输出.目前它在所有列上给我 5% 或者如果我替换

I am using SQLFiddle and I not getting the right output. At the moment it’s giving me 5% on all the columns or if I replace

 SET Discount = '' 

 SET Discount = '5%' 

查询成功但不显示数据.

It will query successfully but does not show data.

这是我做的代码...

Declare
 startdate  date,
 finishdate date,
 Discount varchar(4)

UPDATE Persons
SET [Discount] = ' ' 
SELECT *, DATEADD(MM,-4,startdate) AS TADate
FROM Persons 
WHERE Discount = '5%' AND startdate < dateadd(month,-4,getdate()) OR 
Discount = '10%' AND startdate < dateadd(month,4,getdate())

如果有人可以帮助我,请提前致谢.

Thanks in advance if anyone can help me.

推荐答案

update中使用case语句根据date分配折扣

Use case statement in update to allocate discount based on date

像这样尝试update

UPDATE Persons SET [Discount] = case 
when startdate > dateadd(month,-4,getdate()) and startdate <= getdate() then '5%' 
when startdate < dateadd(month,-4,getdate()) Then '10%' 
End
SELECT * FROM Persons 

相关文章