Teradata SQL -用于显示里程碑的条件、时间和逻辑
Teradata SQL -用于显示里程碑的条件、时间和逻辑
提问于 2019-09-04 17:00:47
我有一个有三列的表，想用下面的逻辑创建'milestones‘列。注意，里程碑是期望的结果。
规则为-状态准备就绪，可运行天数。类型A的>= 14处于运行轨道上-运行状态可运行，运行天数为B的>= 30运行正常。
STATUS DAYS TO GO TYPE MILESTONE
READY 13 A ON TRACK
OPEN 15 A ON TRACK
READY 15 A ON TRACK
OPEN 13 A LATE
READY 28 B ON TRACK
OPEN 31 B ON TRACK
OPEN 29 B LATE
OPEN 33 B ON TRACK我的问题是，当不拿起正确的情况下，然后超过30，并准备就绪。
CASE
WHEN TYPE = 'A' AND STATUS = 'READY' AND DAYS TO GO >= 14 THEN 'ON TRACK'
WHEN TYPE = 'B' AND STATUS = 'READY' AND DAYS TO GO >= 30 THEN 'ON TRACK
ELSE 'LATE' END回答 1
Stack Overflow用户
发布于 2019-09-04 17:32:52
我想多亏了@Tim Biegeleisen，我才能回答我自己的问题。看起来我需要将所有规则都包含到CASE WHEN中。
所以我的数据是(我转移到了mySQL，因为这是最简单的事情)
CREATE TABLE tableA
(
STATUS VARCHAR (5),
DAYS int (2),
TYPE VARCHAR (2)) ;
insert into tableA values
("READY",13,"A"),
("OPEN",15,"A"),
("READY",15,"A"),
("OPEN",13,"A"),
("READY",28,"B"),
("OPEN",31,"B"),
("OPEN",29,"B"),
("OPEN",33,"B");我的选择是：
SELECT t.STATUS,t.DAYS,t.TYPE,
CASE
WHEN (t.TYPE = "A" AND t.DAYS >= 14 AND t.STATUS = "READY") THEN "ON TRACK"
WHEN (t.TYPE = "A" AND t.DAYS <= 14 AND t.STATUS = "READY") THEN "ON TRACK"
WHEN (t.TYPE = "A" AND t.DAYS >= 14 AND t.STATUS <> "READY") THEN "ON TRACK"
WHEN (t.TYPE = "B" AND t.DAYS >= 30 AND t.STATUS = "READY") THEN "ON TRACK"
WHEN (t.TYPE = "B" AND t.DAYS <= 30 AND t.STATUS = "READY") THEN "ON TRACK"
WHEN (t.TYPE = "B" AND t.DAYS >= 30 AND t.STATUS <> "READY") THEN "ON TRACK"
ELSE "LATE" END AS "MILESTONE"
FROM tableA t;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/57784984
复制相关文章
相似问题
腾讯云开发者
