I have a quetion for one of the guru's on here...
You've never failed me yet so I'm hopeful you might help me out again.
Here's the scenario... (sorry, copy and paste with courier font for fixed
spacing if it doesn't look right)
Date EquipId Reading
04/01/05 12345 12
04/02/05 12345 10
04/03/05 12345 10
04/04/05 12345 11
04/05/05 12345 13
04/06/05 12345 11
04/07/05 12345 0
04/08/05 12345 0
04/09/05 12345 0
04/10/05 12345 44
04/11/05 12345 13
04/12/05 12345 11
04/01/05 55555 12
04/02/05 55555 10
04/03/05 55555 10
04/04/05 55555 11
04/05/05 55555 13
04/06/05 55555 11
04/07/05 55555 12
04/08/05 55555 11
04/09/05 55555 11
04/10/05 55555 14
04/11/05 55555 13
04/12/05 55555 11
I need to estimate the values that belong in 04/07 thru 04/10 for
EquipID 12345 based on the READING that is in 04/10. I want to put
11 (44/4 days) in each of the Reading fields. This would be only
for consecutive "missed" (or 0) reads. If we missed 1 day, I'd use
the next days reading and divide by 2.
Also, and I'm sure this is a dumb question...
Is there a function that returns the date as shown? "mm/dd/yyyy" I've been
"DATEPART"ing the dates and concatenating and it seems like a lot of
work for something that is probably already written and I just haven't
found.
I look forward to your creative suggestions!
Thanks
Travis Rushton - Systems Analyst
Kootenai Electric Cooperative
Life Is Not A Journey To The Grave With The Intention Of Arriving Safely In
A Handsome And Well Preserved Body. Rather, The Objective Should Be To Skid
In Broadside, Thoroughly Used Up, Totally Worn Out, Loudly Proclaiming:
"Wow... What A Ride!!"Doing this using set-based logic could become a bit tricky. If this is an
one-time deal, use a cursor & get it over with, otherwise here is one
solution:
UPDATE tbl
SET Reading = ( SELECT t3.Reading FROM tbl t3
WHERE t3.EquipId = tbl.EquipId AND t3.Reading <> 0
AND t3.dt = ( SELECT MIN ( t2.dt ) FROM tbl t2
WHERE t2.EquipId = tbl.EquipId
AND t2.dt > tbl.dt
AND t2.Reading <> 0
AND tbl.Reading = 0 ) ) /
DATEDIFF( d, ( SELECT MAX ( t2.dt ) FROM tbl t2
WHERE t2.EquipId = tbl.EquipId
AND t2.dt < tbl.dt AND t2.Reading <>
0 ),
( SELECT MIN ( t2.dt ) FROM tbl t2
WHERE t2.EquipId = tbl.EquipId
AND t2.dt > tbl.dt AND t2.Reading <>
0
AND tbl.Reading = 0 ) )
WHERE Reading = 0 ;
Anith
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment