Wiktor Kostrzewski Wiktor Kostrzewski - 7 months ago 9
SQL Question

SQL how to determine hours from date per day and workstation

I have a query which gives me output

select distinct(d.data_name) ,h.workstation_name, h.type_id, convert(varchar(19),(h.insert_date),120) as start
from data d, process_data_history h
where h.data_id = d.data_id
and h.type_id between '500' and '5001'
and h.workstation_name like 'PL1W7D-105120'
and CONVERT(VARCHAR(25), h.insert_date, 126) LIKE '2016-04%'
order by d.data_name, start ;


And output looks like this

data_name workstation_name type_id start
WE228JP_2016_04_05__10_03_03 PL1W7D-105120 500 2016-04-05 12:45:38
WE228JP_2016_04_05__10_03_03 PL1W7D-105120 501 2016-04-05 12:45:38
WE228JP_2016_04_05__10_03_03 PL1W7D-105120 600 2016-04-05 12:45:39
WE228JP_2016_04_05__10_03_03 PL1W7D-105120 601 2016-04-05 12:45:39
WE228JP_2016_04_05__10_03_03 PL1W7D-105120 4001 2016-04-05 14:06:04
WE228JP_2016_04_05__10_03_03 PL1W7D-105120 4000 2016-04-05 14:05:55
WE228JP_2016_04_05__10_03_03 PL1W7D-105120 4100 2016-04-05 14:06:05
WE228JP_2016_04_05__10_03_03 PL1W7D-105120 4101 2016-04-05 14:10:43
WE228JP_2016_04_05__10_03_03 PL1W7D-105120 5000 2016-04-05 14:10:43
WE228JP_2016_04_05__10_03_03 PL1W7D-105120 5001 2016-04-05 14:10:45
WE228JP_2016_04_06__10_33_03 PL1W7D-105120 500 2016-04-06 14:10:46
WE228JP_2016_04_06__10_33_03 PL1W7D-105120 600 2016-04-06 14:10:47
WE228JP_2016_04_06__10_33_03 PL1W7D-105120 501 2016-04-06 14:10:47
WE228JP_2016_04_06__10_33_03 PL1W7D-105120 601 2016-04-06 14:10:48
WE228JP_2016_04_06__10_33_03 PL1W7D-105120 4000 2016-04-06 15:30:07
WE228JP_2016_04_06__10_33_03 PL1W7D-105120 4001 2016-04-06 15:30:18
WE228JP_2016_04_06__10_33_03 PL1W7D-105120 4100 2016-04-06 15:30:18
WE228JP_2016_04_06__10_33_03 PL1W7D-105120 4101 2016-04-06 15:35:01
WE228JP_2016_04_06__10_33_03 PL1W7D-105120 5000 2016-04-06 15:35:01
WE228JP_2016_04_06__10_33_03 PL1W7D-105120 5001 2016-04-06 15:35:03
WE228JP_2016_04_15__10_03_03 PL1W7D-105122 500 2016-04-05 12:45:38
WE228JP_2016_04_15__10_03_03 PL1W7D-105122 501 2016-04-05 12:45:38
WE228JP_2016_04_15__10_03_03 PL1W7D-105122 600 2016-04-05 12:45:39
WE228JP_2016_04_15__10_03_03 PL1W7D-105120 601 2016-04-05 12:45:39
WE228JP_2016_04_15__10_03_03 PL1W7D-105122 4001 2016-04-05 14:06:04
WE228JP_2016_04_15__10_03_03 PL1W7D-105122 5000 2016-04-05 14:10:43
WE228JP_2016_04_15__10_03_03 PL1W7D-105122 5001 2016-04-05 14:10:45


Also i know that

ID 500 600 ...5000 represents start time of steps (programs)

ID 501 601 ...5001 represents end time of steps (programs)

So i need to know for how long workstation was working per day:

So desired output would be like this (it's example):

data_name workstation_name Hours
2016-04-05 PL1W7D-105120 22 hours
2016-04-06 PL1W7D-105120 21 hours
2016-04-15 PL1W7D-105122 17 hours


Any hint how i could do that? Anything will do ;)

Answer

I think you just want aggregation. Here is one method:

select d.data_name, h.workstation_name, h.type_id,
       datediff(hour, min(insert_date), max(insert_date)) as hours_sortof
from data d join
     process_data_history h 
     on h.data_id = d.data_id
where h.type_id between '500' and '5001' and 
      h.workstation_name like 'PL1W7D-105120'
      h.insert_date >= '2016-04-01' and h.insert_date < '2016-05-01'
group by d.date_name, h.workstation_name,
order by d.data_name, start ;

Notes:

  • Learn to use explicit JOIN syntax. Never use commas in the FROM clause.
  • Do not convert dates to strings for comparison purposes. SQL Server has very nice date/time functionality built into the system.
  • Only use LIKE on string constants. Implicit conversion can be a major source of problems.
  • The hours_sortof is based on how SQL Server handles the differences of times. It counts hour boundaries rather than actual hours. For more refinement, use a smaller increment, such as minutes or seconds.