Use COALESCE value in calculation

Transact-SQL
1

I'm trying to calculate total hours in shop. One of the "wo_status" completed_date is null. I want the null "completed_date" to equal today and then calculate the total time of all three status's below. I'm close but I can't get the "DATEDIFF" to use the coalesce value as the new complete date for wo_status "C080"

SELECT WS.WO_STATUS,
WS.START_DATE,
WS.COMPLETED_DATE,
COALESCE(WS.COMPLETED_DATE,GETDATE()) AS [COMPLETE DATE],
DATEDIFF(HOUR,WS.START_DATE,WS.COMPLETED_DATE) AS [TIME IN STATUS]

FROM WO_HDR_STATUS WS

WHERE PARENT_DOC_NO = 131131

Results is:
wo_status.....start_date..........................Completed_date...............Complete Date..................Time in Status
K002.............2015-07-13 15:41:04.233....2015-07-15 15:07:12.553...2015-07-15 15:07:12.553...48
K020.............2015-07-15 15:07:12.233....2015-07-17 15:06:15.553...2015-07-17 15:06:15.553...48
C080.............2015-07-17 15:06:16.233....NULL...............................2015-07-20 10:59:56.887...NULL

2

Use the same COALESCE instead of COMPLETED_DATE like this:

SELECT  WS.WO_STATUS ,
        WS.START_DATE ,
        WS.COMPLETED_DATE ,
        COALESCE(WS.COMPLETED_DATE, GETDATE()) AS [COMPLETE DATE] ,
        DATEDIFF(HOUR, WS.START_DATE, COALESCE(WS.COMPLETED_DATE, GETDATE())) AS [TIME IN STATUS]
FROM    WO_HDR_STATUS WS
WHERE   PARENT_DOC_NO = 131131
1 Like
3

Perfect. Thank you.

One more thing. How do I sum all the "Time in Status"' and just give me one line with the "total time"? I have "ws.parent_doc_no" in my select statement but left it out of my previous post to save space. I think I want to "Group by" the parent doc no but I'm getting an error.

Msg 8120, Level 16, State 1, Line 2
Column 'WO_HDR_STATUS.WO_STATUS' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Complete code:
SELECT WS.PARENT_DOC_NO,
WS.WO_STATUS,
WS.START_DATE,
WS.COMPLETED_DATE,
DATEDIFF(HOUR,WS.START_DATE, COALESCE(WS.COMPLETED_DATE,GETDATE())) AS [TIME IN STATUS]

FROM WO_HDR_STATUS WS

WHERE PARENT_DOC_NO = 131131

Group by PARENT_DOC_NO

4

Are you sure you posted the correct code? I don't see any summation in your code.

Regardless, the rule when you want to use SUM (or any aggregate function) is that any column that is in the select list that is outside of an aggregate function (such as SUM) should be listed in the group by code.