mysql - SQL-Case When 问题

Question

我正在尝试将交易日期分类为时效政策。当 LastDate 在该位置的时间超过 Aging Days 限制策略时，它应该显示为 OverAge 如果不是 Within 指的是当前日期。这是当前表:

+---------+------+----------+-------------+
|LastDate | Part | Location | Aging Days  |
+---------+------+----------+-------------+
12/1/2016   123    VVV          90
8/10/2017   444    RRR          10
8/01/2017   144    PR           21
7/15/2017   12     RRR          10

这里是查询:

select
q.lastdate, 
r.part, r.location,
a.agingpolicy as 'Aging Days'
  from opsintranexcel r (nolock)
left InventoryAging a (nolock) on r.location=a.location
left join (select part,MAX(trandate) as lastdate from opsintran group by 
    part) q on r.part=q.part

这是我要添加的额外列:

+---------+------+----------+------------+---------+
|LastDate | Part | Location | Aging Days |   Age   |
+---------+------+----------+------------+---------+
 12/1/2016   123     VVV          90       Overage
 8/10/2017   444     RRR          10       Within 
 8/01/2017   144     PR           21       Within
 7/15/2017   12      RRR          10       Overage

感谢您的帮助。

Answer 1

我认为下面的代码适合你

SELECT
  q.lastdate, 
  r.part, 
  r.location,
  a.agingpolicy as 'Aging Days'
  'Age' =
     CASE
       WHEN DATEDIFF( day, q.LastDate, GETDATE() ) > a.agingpolicy THEN 'Overage'
       ELSE THEN 'Within'
     END 
FROM opsintranexcel r (nolock)
LEFT JOIN InventoryAging a (nolock) on r.location=a.location
LEFT JOIN (
    SELECT part,MAX(trandate) as lastdate 
    FROM opsintran 
    WHERE trantype='II' and PerPost>='201601' 
    GROUP BY part) q ON r.part=q.part