I have two tables: one with downtime and the other with productive time.
I want to have a table like this
But I am getting this
In the result, I am getting twice the downtime of the sum for the report 04102021-1, but as can be seen in the second picture, the value is present only once.
The script I am using is the following:
SELECT WAJ.REPORTCODE,--BASIC_REPORT_TABLE.TECHNICIAN,BASIC_REPORT_TABLE.JOBREPORTCODE, SUM(CASE WHEN DATEDIFF(SECOND,WAJ.TIMESTARTED,WAJ.TIMEFINISHED)<0 THEN (86400+DATEDIFF(SECOND,WAJ.TIMESTARTED,WAJ.TIMEFINISHED))/3600.0 ELSE DATEDIFF(SECOND,WAJ.TIMESTARTED,WAJ.TIMEFINISHED) /3600.0 END) AS PRODUCTION_TIME, SUM(CASE WHEN DATEDIFF(SECOND,WAS.TIMESTARTED,WAS.TIMEFINISHED)<0 THEN (86400+DATEDIFF(SECOND,WAS.TIMESTARTED,WAS.TIMEFINISHED))/3600.0 ELSE DATEDIFF(SECOND,WAS.TIMESTARTED,WAS.TIMEFINISHED) /3600.0 END) AS DOWNTIME FROM WORK_AT_JOB WAJ,WAITING_AT_SITE WAS WHERE (WAJ.REPORTCODE=WAS.REPORTCODE AND WAJ.REPORTCODE LIKE '04102021%') GROUP BY WAJ.REPORTCODE
After the @xQbert comment, I tried this:
SELECT WAS.REPORTCODE, SUM(CASE WHEN DATEDIFF(SECOND,WAJ.TIMESTARTED,WAJ.TIMEFINISHED)<0 THEN (86400+DATEDIFF(SECOND,WAJ.TIMESTARTED,WAJ.TIMEFINISHED))/3600.0 ELSE DATEDIFF(SECOND,WAJ.TIMESTARTED,WAJ.TIMEFINISHED) /3600.0 END) AS PRODUCTION_TIME, SUM(CASE WHEN DATEDIFF(SECOND,WAS.TIMESTARTED,WAS.TIMEFINISHED)<0 THEN (86400+DATEDIFF(SECOND,WAS.TIMESTARTED,WAS.TIMEFINISHED))/3600.0 ELSE DATEDIFF(SECOND,WAS.TIMESTARTED,WAS.TIMEFINISHED) /3600.0 END) AS DOWNTIME FROM WAITING_AT_SITE WAS JOIN WORK_AT_JOB WAJ ON (WAJ.REPORTCODE=WAS.REPORTCODE AND WAS.REPORTCODE LIKE '04102021%') GROUP BY WAS.REPORTCODE
But I got the same result.
May you give some advice to get the result I want?
Thanks in advance
Advertisement
Answer
You could use conditional aggregation for this, but the easiest, and probably most performant, way to do this is to pre-aggregate the results before you join.
SELECT waj.REPORTCODE waj.PRODUCTION_TIME, was.DOWNTIME FROM ( SELECT waj.REPORTCODE, SUM(CASE WHEN v.diff < 0 THEN 86400 + v.diff ELSE v.diff END / 3600.0) AS PRODUCTION_TIME FROM WORK_AT_JOB waj CROSS APPLY (VALUES( DATEDIFF(SECOND, waj.TIMESTARTED, waj.TIMEFINISHED) )) v(diff) WHERE waj.REPORTCODE LIKE '04102021%' GROUP BY waj.REPORTCODE ) waj JOIN ( SELECT was.REPORTCODE, SUM(CASE WHEN v.diff < 0 THEN 86400 + v.diff ELSE v.diff END / 3600.0) AS PRODUCTION_TIME FROM WAITING_AT_SITE was CROSS APPLY (VALUES( DATEDIFF(SECOND, was.TIMESTARTED, was.TIMEFINISHED) )) v(diff) WHERE was.REPORTCODE LIKE '04102021%' GROUP BY was.REPORTCODE ) was ON waj.REPORTCODE = was.REPORTCODE;
Note the use of CROSS APPLY (VALUES
to avoid code repetition.