I am very new at SQL and I have been working on my First Query. Now I need your help. I have this report in Excel. I have formula in the AGE column N calculating the AGE. I have been trying to create a SQL query to come up with the same answer. So far I have figured out the columns that I need and pulled all the data to Match Excel. Only thing is I don't know how and where to enter this IF statement so it will calculate the age when i run the query.
我是SQL的新手,我一直致力于我的First Query。现在我需要你的帮助。我在Excel中有这个报告。我在AGE列N中计算AGE的公式。我一直在尝试创建一个SQL查询来提出相同的答案。到目前为止,我已经找到了我需要的列,并将所有数据提取到Match Excel。唯一的问题是我不知道如何以及在何处输入此IF语句,因此它将计算运行查询时的年龄。
Work Order Location MISC Status Actual Finish Finish Time Parent WO Work Type N Reported Date Reported Time Site Gen Age Sub Name Substation Location
1234567899 4074 COMP 11/5/14 3:08:49 PM CM 10/7/14 1:47:42 PM 29.05633102
12348574987 2946 SCHED CM 10/30/14 10:28:03 AM 188.5638542
AGE is a formula, pasted below:
AGE是一个公式,粘贴如下:
=IF ((IF(Status<>"Comp",Today()-Reported date, actual finish-reported date))<0,0, IF(STATUS<>"COMP", TODAY()-REPORTED DATE, ACTUAL FINISH-REPORTED DATE))
SQL Writen Query
SQL写入查询
SELECT WO.WONUM,
LOCOFFDESC.DESCRIPTION AS OFFICE,
WO.STATUS,
WO.LOCATION,
CASE
WHEN ((INSTR(LOCOFF.EXT_LOC_HIERARCHY_PATH, '/', 2, 3)) = 0)
THEN SUBSTR(LOCOFF.EXT_LOC_HIERARCHY_PATH, ((INSTR(LOCOFF.EXT_LOC_HIERARCHY_PATH, '/', 1, 2) + 2)), LENGTH(LOCOFF.EXT_LOC_HIERARCHY_PATH))
WHEN ((INSTR(LOCOFF.EXT_LOC_HIERARCHY_PATH, '/', 2, 3)) IS NOT NULL)
THEN SUBSTR(LOCOFF.EXT_LOC_HIERARCHY_PATH, ((INSTR(LOCOFF.EXT_LOC_HIERARCHY_PATH, '/', 1, 2) + 2)), (INSTR(LOCOFF.EXT_LOC_HIERARCHY_PATH, '/', 2, 3)) - (INSTR(LOCOFF.EXT_LOC_HIERARCHY_PATH, '/', 1, 2) + 2))
END AS "SUBSTATION CASE",
WO.PARENT,
WO.WORKTYPE,
WO.REPORTEDBY,
WO.REPORTDATE,
WO.ACTFINISH,
WO.SITEID
FROM LOCATIONS LOCOFF
RIGHT JOIN MAXPRD.WORKORDER WO
ON WO.LOCATION = LOCOFF.LOCATION
INNER JOIN MAXPRD.LOCANCESTOR LOCOFFANC
ON LOCOFFANC.LOCATION = LOCOFF.LOCATION
INNER JOIN MAXPRD.LOCATIONS LOCOFFDESC
ON LOCOFFANC.ANCESTOR = LOCOFFDESC.LOCATION
INNER JOIN MAXPRD.LOCHIERARCHY LOCOFFHIER
ON LOCOFFANC.ANCESTOR = LOCOFFHIER.LOCATION
WHERE LOCOFF.SITEID = 'SUB'
AND LOCOFFHIER.PARENT = '2000'
GROUP BY WO.WONUM,
LOCOFFDESC.DESCRIPTION,
WO.STATUS,
WO.LOCATION,
LOCOFF.EXT_LOC_HIERARCHY_PATH,
LOCOFF.SITEID,
LOCOFFHIER.PARENT,
WO.PARENT,
WO.WORKTYPE,
WO.REPORTEDBY,
WO.REPORTDATE,
WO.ACTFINISH,
WO.SITEID
1 个解决方案
#1
You place the calculation after the SELECT statement, before the FROM statement, so you have a column filled with calculated values, similar to excel.
您将计算放在SELECT语句之后,在FROM语句之前,因此您有一个填充计算值的列,类似于excel。
We need to know what database system you are working with to get the exact syntax. Can you try after WO.SITEID,
我们需要知道您正在使用哪个数据库系统来获取确切的语法。你可以试试WO.SITEID,
SELECT WO.WONUM,
LOCOFFDESC.DESCRIPTION AS OFFICE,
WO.STATUS,
WO.LOCATION,
CASE
WHEN ((INSTR(LOCOFF.EXT_LOC_HIERARCHY_PATH, '/', 2, 3)) = 0)
THEN SUBSTR(LOCOFF.EXT_LOC_HIERARCHY_PATH, ((INSTR(LOCOFF.EXT_LOC_HIERARCHY_PATH, '/', 1, 2) + 2)), LENGTH(LOCOFF.EXT_LOC_HIERARCHY_PATH))
WHEN ((INSTR(LOCOFF.EXT_LOC_HIERARCHY_PATH, '/', 2, 3)) IS NOT NULL)
THEN SUBSTR(LOCOFF.EXT_LOC_HIERARCHY_PATH, ((INSTR(LOCOFF.EXT_LOC_HIERARCHY_PATH, '/', 1, 2) + 2)), (INSTR(LOCOFF.EXT_LOC_HIERARCHY_PATH, '/', 2, 3)) - (INSTR(LOCOFF.EXT_LOC_HIERARCHY_PATH, '/', 1, 2) + 2))
END AS "SUBSTATION CASE",
WO.PARENT,
WO.WORKTYPE,
WO.REPORTEDBY,
WO.REPORTDATE,
WO.ACTFINISH,
WO.SITEID,
(
CASE
WHEN (
CASE WHEN WO.STATUS != 'Comp'
THEN SYSDATE-WO.REPORTDATE
ELSE WO.ACTFINISH-WO.REPORTDATE
END) < 0
THEN 0
ELSE
CASE WHEN STATUS != 'COMP'
THEN SYSDATE-WO.REPORTDATE
ELSE WO.ACTFINISH-WO.REPORTDATE
END
END) as age
FROM LOCATIONS LOCOFF
RIGHT JOIN MAXPRD.WORKORDER WO
ON WO.LOCATION = LOCOFF.LOCATION
INNER JOIN MAXPRD.LOCANCESTOR LOCOFFANC
ON LOCOFFANC.LOCATION = LOCOFF.LOCATION
INNER JOIN MAXPRD.LOCATIONS LOCOFFDESC
ON LOCOFFANC.ANCESTOR = LOCOFFDESC.LOCATION
INNER JOIN MAXPRD.LOCHIERARCHY LOCOFFHIER
ON LOCOFFANC.ANCESTOR = LOCOFFHIER.LOCATION
WHERE LOCOFF.SITEID = 'SUB'
AND LOCOFFHIER.PARENT = '2000'
GROUP BY WO.WONUM,
LOCOFFDESC.DESCRIPTION,
WO.STATUS,
WO.LOCATION,
LOCOFF.EXT_LOC_HIERARCHY_PATH,
LOCOFF.SITEID,
LOCOFFHIER.PARENT,
WO.PARENT,
WO.WORKTYPE,
WO.REPORTEDBY,
WO.REPORTDATE,
WO.ACTFINISH,
WO.SITEID
Edited for MS SQL server. Edited AGAIN for Oracle :)
编辑MS SQL服务器。为甲骨文编辑AGAIN :)
#1
You place the calculation after the SELECT statement, before the FROM statement, so you have a column filled with calculated values, similar to excel.
您将计算放在SELECT语句之后,在FROM语句之前,因此您有一个填充计算值的列,类似于excel。
We need to know what database system you are working with to get the exact syntax. Can you try after WO.SITEID,
我们需要知道您正在使用哪个数据库系统来获取确切的语法。你可以试试WO.SITEID,
SELECT WO.WONUM,
LOCOFFDESC.DESCRIPTION AS OFFICE,
WO.STATUS,
WO.LOCATION,
CASE
WHEN ((INSTR(LOCOFF.EXT_LOC_HIERARCHY_PATH, '/', 2, 3)) = 0)
THEN SUBSTR(LOCOFF.EXT_LOC_HIERARCHY_PATH, ((INSTR(LOCOFF.EXT_LOC_HIERARCHY_PATH, '/', 1, 2) + 2)), LENGTH(LOCOFF.EXT_LOC_HIERARCHY_PATH))
WHEN ((INSTR(LOCOFF.EXT_LOC_HIERARCHY_PATH, '/', 2, 3)) IS NOT NULL)
THEN SUBSTR(LOCOFF.EXT_LOC_HIERARCHY_PATH, ((INSTR(LOCOFF.EXT_LOC_HIERARCHY_PATH, '/', 1, 2) + 2)), (INSTR(LOCOFF.EXT_LOC_HIERARCHY_PATH, '/', 2, 3)) - (INSTR(LOCOFF.EXT_LOC_HIERARCHY_PATH, '/', 1, 2) + 2))
END AS "SUBSTATION CASE",
WO.PARENT,
WO.WORKTYPE,
WO.REPORTEDBY,
WO.REPORTDATE,
WO.ACTFINISH,
WO.SITEID,
(
CASE
WHEN (
CASE WHEN WO.STATUS != 'Comp'
THEN SYSDATE-WO.REPORTDATE
ELSE WO.ACTFINISH-WO.REPORTDATE
END) < 0
THEN 0
ELSE
CASE WHEN STATUS != 'COMP'
THEN SYSDATE-WO.REPORTDATE
ELSE WO.ACTFINISH-WO.REPORTDATE
END
END) as age
FROM LOCATIONS LOCOFF
RIGHT JOIN MAXPRD.WORKORDER WO
ON WO.LOCATION = LOCOFF.LOCATION
INNER JOIN MAXPRD.LOCANCESTOR LOCOFFANC
ON LOCOFFANC.LOCATION = LOCOFF.LOCATION
INNER JOIN MAXPRD.LOCATIONS LOCOFFDESC
ON LOCOFFANC.ANCESTOR = LOCOFFDESC.LOCATION
INNER JOIN MAXPRD.LOCHIERARCHY LOCOFFHIER
ON LOCOFFANC.ANCESTOR = LOCOFFHIER.LOCATION
WHERE LOCOFF.SITEID = 'SUB'
AND LOCOFFHIER.PARENT = '2000'
GROUP BY WO.WONUM,
LOCOFFDESC.DESCRIPTION,
WO.STATUS,
WO.LOCATION,
LOCOFF.EXT_LOC_HIERARCHY_PATH,
LOCOFF.SITEID,
LOCOFFHIER.PARENT,
WO.PARENT,
WO.WORKTYPE,
WO.REPORTEDBY,
WO.REPORTDATE,
WO.ACTFINISH,
WO.SITEID
Edited for MS SQL server. Edited AGAIN for Oracle :)
编辑MS SQL服务器。为甲骨文编辑AGAIN :)