CODEWITHSUNDEEP
mysqlsqlcasedatediff
Ray
Ray

Reputation: 103

Use Case in Datediff statement

Could you please help me with the below query: Consider these 2 tables for Car repair company:

Old_Value   New_Value   Created
0           1           2016/09/14
1           2           2016/09/15
2           3           2016/09/19

Value   Description
0       Not Diagnosed Yet
1       In Queue
2       In Progress
3       Fixed

I'm trying to calculate the Handle Time, which is the date difference between the "In Progress" and "Fixed" status:

Select 
datediff(day, (case when Old_Value='1' and New_Value='2' then Created else Null end), (case when Old_Value='2' and New_Value='3' then Created else Null end))
as Handle_Time
from tb1

All I'm getting is an error msg. Any help please, thank you.

Upvotes: 1

Views: 1638

Answers (2)

Esty
Esty

Reputation: 1912

Not clear what you want but try this it will return 4.

Select TOP 1
datediff(DAY, (SELECT CREATED FROM T1 WHERE New_Value = 2), (SELECT CREATED FROM T1 WHERE New_Value = 3)) as handle_time
from t1

AND I have just run your query but found no error. It just returned all 3 rows as NULL.

CREATE TABLE T1(Old_Value int, New_Value int, Created DATE)
CREATE TABLE T2(Value int, Description varchar(50))

INSERT INTO T1 VALUES
(0, 1, '2016/09/14'),
(1, 2, '2016/09/15'),
(2, 3, '2016/09/19')

INSERT INTO T2 VALUES
(0, 'Not Diagnosed Yet'),
(1, 'In Queue'),
(2, 'In Progress'),
(3, 'Fixed')

Select 
datediff(day, (case when Old_Value='1' and New_Value='2' then Created else Null end), (case when Old_Value='2' and New_Value='3' then Created else Null end))
as Handle_Time
from T1

DROP TABLE T1
DROP TABLE T2

Result:

Handle_Time
NULL
NULL
NULL

Now just run following query

Select 
(case when Old_Value='1' and New_Value='2' then Created else Null end) AS param1, 
(case when Old_Value='2' and New_Value='3' then Created else Null end) AS param2,
datediff(day, (case when Old_Value='1' and New_Value='2' then Created else Null end), (case when Old_Value='2' and New_Value='3' then Created else Null end))
as Handle_Time
from T1

Result:

param1      param2      Handle_Time
NULL        NULL        NULL
2016-09-15  NULL        NULL
NULL        2016-09-19  NULL

So in datediff one of your parameters are always null which returns null.

SELECT DateDiff(DAY, NULL, NULL) --return NULL
SELECT DateDiff(DAY, GETDATE(), NULL) --return NULL
SELECT DateDiff(DAY, NULL, GETDATE()) --return NULL

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270553

In MySQL, you don't use day. I would expect a query such as this:

Select repair_id,
       datediff(max(case when old_value = 1 and new_value = 2 then created end),
                max(case when old_value = 2 and new_value = 3 then created end)
               ) as handle_time
from tb1
group by repair_id;

I've invented the column repair_id, because it makes sense to me. If the table only has one repair in it, you can leave it out of the query.

Upvotes: 4

Related Questions