February 2, 2006 at 8:00 pm
I need to generate a report that shows the hours machines are used by shift, where the data available is only a meter reading at the start of each shift. Therefore I need to compare the values in one record with the next record for each machine. The data looks something like this:
Date Shift MachineID MeterReading
Day 1 1 Machine1 100
Day 1 1 Machine2 250
Day 1 1 Machine3 400
Day 1 2 Machine1 105
Day 1 2 Machine2 257
Day 1 3 Machine1 112
Day 2 1 Machine1 119
Day 2 1 Machine2 263
Day 2 1 Machine3 406
etc.
Sometimes readings are not taken, so there are nulls in the data. However from the above data a report for the first day would be generated from a dataset that would look like the following:
Date Shift MachineID Start End Hours
Day 1 1 Machine1 100 105 5
Day 1 2 Machine1 105 112 7
Day 1 3 Machine1 112 119 7
Day 1 1 Machine2 250 257 7
Day 1 2 Machine2 257 263 6
Day 1 1 Machine3 400 406 6
Can someone please advise how to write a query that compares the values in one record with those of the next record for the same machine in order to get a dataset as above?
February 2, 2006 at 9:05 pm
Hi Mark Finnie,
This is correlated subquery is guaranteed to NOT be the most efficient way to do this. However, it will get you there till the next respondent proposes a better solution 😉 Just copy and paste the script and you can check the results.
SET nocount ON
DECLARE @usage TABLE (
pkint INT IDENTITY( 1 , 1 ) NOT NULL PRIMARY KEY,
daynum INT NOT NULL,
shift INT NULL,
machineid VARCHAR(10) NOT NULL,
meterreading INT NULL)
INSERT @usage VALUES(1, 1, 'machine1', 100)
INSERT @usage VALUES(1, 1, 'machine2', 250)
INSERT @usage VALUES(1, 1, 'machine3', 400)
INSERT @usage VALUES(1, 2, 'machine1', 105)
INSERT @usage VALUES(1, 2, 'machine2', 257)
INSERT @usage VALUES(1, 3, 'machine1', 112)
INSERT @usage VALUES(2, 1, 'machine1', 119)
INSERT @usage VALUES(2, 1, 'machine2', 263)
INSERT @usage VALUES(2, 1, 'machine3', 406)
SELECT u1.daynum,
u1.shift,
u1.machineid,
u1.meterreading AS beginreading,
(SELECT Min(meterreading)
FROM @usage
WHERE machineid = u1.machineid
AND meterreading > u1.meterreading) AS endreading,
(SELECT Min(meterreading)
FROM @usage
WHERE machineid = u1.machineid
AND meterreading > u1.meterreading) - u1.meterreading AS USAGE
FROM @usage AS u1
WHERE (SELECT Min(meterreading)
FROM @usage
WHERE machineid = u1.machineid
AND meterreading > u1.meterreading) - u1.meterreading IS NOT NULL
ORDER BY machineid,
daynum,
shift
February 5, 2006 at 8:52 pm
Try this. Tested only with sample data provided. Should test with more samples to confirm.
create table #usage
(
pkint INT IDENTITY( 1 , 1 ) NOT NULL PRIMARY KEY,
daynum INT NOT NULL,
shift INT NULL,
machineid VARCHAR(10) NOT NULL,
meterreading INT NULL
)
INSERT #usage VALUES(1, 1, 'machine1', 100)
INSERT #usage VALUES(1, 1, 'machine2', 250)
INSERT #usage VALUES(1, 1, 'machine3', 400)
INSERT #usage VALUES(1, 2, 'machine1', 105)
INSERT #usage VALUES(1, 2, 'machine2', 257)
INSERT #usage VALUES(1, 3, 'machine1', 112)
INSERT #usage VALUES(2, 1, 'machine1', 119)
INSERT #usage VALUES(2, 1, 'machine2', 263)
INSERT #usage VALUES(2, 1, 'machine3', 406)
select s.daynum, s.shift, s.machineid,
s.meterreading as [Start], isnull(e.meterreading, n.meterreading) as [End],
isnull(e.meterreading, n.meterreading) - s.meterreading as [Hours]
from#usage s left join #usage e
ons.machineid= e.machineid
ands.daynum= e.daynum
ands.shift= (e.shift - 1)
left join #usage n
ons.machineid= n.machineid
ands.daynum= (n.daynum - 1)
andn.shift= 1
wheren.meterreadingis not null
order by s.daynum, s.machineid, s.shift
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply