table includes times for startup and end of operation as datetime field
related to daily shift operations:
dateid date starttime endtime
458 2006-12-29 22:00 23:15
458 2006-12-29 00:15 01:30
459 2006-12-30 20:00 21:10
459 2006-12-30 22:15 23:35
459 2006-12-30 23:30 00:40
459 2006-12-30 01:50 02:30
records are inserted for a date related to begining of the shift, although
some operations are performed also past the midnight (actualy next day, ex:
2006-12-31), but belongs to same shift (group)
Now I need to build a function that corrects (updates) the date of every
operation recorded after midnight to a date+1 value, so all records related
to same groups (458, 459, etc) that starts after midnight has correct date.
The procedure has to update already exiting table.
Any solution?
GreyI'm a bit confused. You mention that it uses a datetime data type,
but the data shows appears to be a column with a date and no time, and
two columns with time but no date. Datetime always has both.
Roy Harvey
Beacon Falls, CT
On Thu, 4 Jan 2007 00:26:39 +0100, "Grey" <null@.null.nullwrote:
Quote:
Originally Posted by
>I have following problem:
>
>table includes times for startup and end of operation as datetime field
>related to daily shift operations:
>
>dateid date starttime endtime
>
>458 2006-12-29 22:00 23:15
>458 2006-12-29 00:15 01:30
>459 2006-12-30 20:00 21:10
>459 2006-12-30 22:15 23:35
>459 2006-12-30 23:30 00:40
>459 2006-12-30 01:50 02:30
>
>records are inserted for a date related to begining of the shift, although
>some operations are performed also past the midnight (actualy next day, ex:
>2006-12-31), but belongs to same shift (group)
>
>Now I need to build a function that corrects (updates) the date of every
>operation recorded after midnight to a date+1 value, so all records related
>to same groups (458, 459, etc) that starts after midnight has correct date.
>
>The procedure has to update already exiting table.
>
>Any solution?
>Grey
>
news:enhe3f$ura$1@.news.dialog.net.pl...
Quote:
Originally Posted by
>I have following problem:
>
table includes times for startup and end of operation as datetime field
related to daily shift operations:
>
dateid date starttime endtime
>
458 2006-12-29 22:00 23:15
458 2006-12-29 00:15 01:30
459 2006-12-30 20:00 21:10
459 2006-12-30 22:15 23:35
459 2006-12-30 23:30 00:40
459 2006-12-30 01:50 02:30
>
records are inserted for a date related to begining of the shift, although
some operations are performed also past the midnight (actualy next day,
ex: 2006-12-31), but belongs to same shift (group)
>
Now I need to build a function that corrects (updates) the date of every
operation recorded after midnight to a date+1 value, so all records
related to same groups (458, 459, etc) that starts after midnight has
correct date.
>
The procedure has to update already exiting table.
Assuming no operation will exceed 24 hours it might look something like
this:
SELECT dateid,
date as startdate,
starttime,
CASE
WHEN endtime starttime
THEN convert(datetime,date)
ELSE convert(datetime,date)+ 1
END AS EndDate,
endtime
FROM Table1
Quote:
Originally Posted by
>
Any solution?
Grey
>
>
Quote:
Originally Posted by
I'm a bit confused. You mention that it uses a datetime data type,
but the data shows appears to be a column with a date and no time, and
two columns with time but no date. Datetime always has both.
>
Roy Harvey
Beacon Falls, CT
The fields are presented for simplicity. they have both date and time, but
the front end adds only time, so the datepart of the filed is disregarded.
Any way it contains a wrong value for operations past midnight.
Grey|||
Quote:
Originally Posted by
Assuming no operation will exceed 24 hours it might look something like
this:
>
SELECT dateid,
date as startdate,
starttime,
CASE
WHEN endtime starttime
THEN convert(datetime,date)
ELSE convert(datetime,date)+ 1
END AS EndDate,
endtime
FROM Table1
Thats too easy. Table contains values from serveral hundreds of days. each
day has an id dateid like 458, 459, etc.
I have to go trough all table finding times past midninght for given group
and change the date to correct one.
Grey
Quote:
Originally Posted by
>
>
>
Quote:
Originally Posted by
>>
>Any solution?
>Grey
>>
>>
>
>|||OK, let me see if I understand this. Have I identified the row that
need fixing correctly? I chose the one row that ended before it
started.
Quote:
Originally Posted by
>dateid date starttime endtime
>
>458 2006-12-29 22:00 23:15
>458 2006-12-29 00:15 01:30
>459 2006-12-30 20:00 21:10
>459 2006-12-30 22:15 23:35
>459 2006-12-30 23:30 00:40 --Problem?
>459 2006-12-30 01:50 02:30
If that is not correct, please mark which ones have the problem and
what rule was made it so.
Roy Harvey
Beacon Falls, CT
On Thu, 4 Jan 2007 14:47:28 +0100, "Grey" <null@.null.nullwrote:
Quote:
Originally Posted by
>
Quote:
Originally Posted by
>I'm a bit confused. You mention that it uses a datetime data type,
>but the data shows appears to be a column with a date and no time, and
>two columns with time but no date. Datetime always has both.
>>
>Roy Harvey
>Beacon Falls, CT
>
>The fields are presented for simplicity. they have both date and time, but
>the front end adds only time, so the datepart of the filed is disregarded.
>Any way it contains a wrong value for operations past midnight.
>
>Grey
>|||
Quote:
Originally Posted by
Quote:
Originally Posted by
>>dateid date starttime endtime
>>
>>458 2006-12-29 22:00 23:15
>>458 2006-12-29 00:15 01:30 --Problem?
Quote:
Originally Posted by
Quote:
Originally Posted by
>>459 2006-12-30 20:00 21:10
>>459 2006-12-30 22:15 23:35
>>459 2006-12-30 23:30 00:40
>>459 2006-12-30 01:50 02:30 --Problem?
>>459 2006-12-30 03:30 04:30 --Problem?
The start time is relevant - marked records should have a date + 1
Grey|||Something like this might do what you want.
First the simple way, with two seperate UPDATEs, one for each column:
UPDATE Operations
SET starttime = DATEADD(day,1,starttime)
WHERE datepart(hour,starttime) < 12
UPDATE Operations
SET endtime = DATEADD(day,1,endtime)
WHERE datepart(hour,endtime) < 12
Note that I chose hour 12 as an arbitrary point in the day such that
times before that are "after midnight" and times after that are
"before midnight". Choose whatever time you prefer.
Also note that this "fixes" (or destroys, you tell me) the second date
in the sample row:
Quote:
Originally Posted by
Quote:
Originally Posted by
Quote:
Originally Posted by
>>>459 2006-12-30 23:30 00:40
This was not designated as a problem row, but I thought the second
date would be. Please clarify if this second date should not be
changed.
Anyway, the two-UPDATE approach is innefficient, as most of the
changes happen to both columns on the same row. So we combine them,
but it becomes rather more complicated.
UPDATE Operations
SET starttime = CASE WHEN datepart(hour,starttime) < 12
THEN DATEADD(day,1,starttime)
ELSE starttime
END,
endtime = CASE WHEN datepart(hour,endtime) < 12
THEN DATEADD(day,1,endtime)
ELSE starttime
END
WHERE datepart(hour,starttime) < 12
OR datepart(hour,endtime) < 12
Hope that helps.
Roy Harvey
Beacon Falls, CT
On Thu, 4 Jan 2007 16:17:13 +0100, "Grey" <null@.null.nullwrote:
Quote:
Originally Posted by
>
Quote:
Originally Posted by
Quote:
Originally Posted by
>>>dateid date starttime endtime
>>>
>>>458 2006-12-29 22:00 23:15
>>>458 2006-12-29 00:15 01:30 --Problem?
>
Quote:
Originally Posted by
Quote:
Originally Posted by
>>>459 2006-12-30 20:00 21:10
>>>459 2006-12-30 22:15 23:35
>>>459 2006-12-30 23:30 00:40
>>>459 2006-12-30 01:50 02:30 --Problem?
>>>459 2006-12-30 03:30 04:30 --Problem?
>
>The start time is relevant - marked records should have a date + 1
>
>Grey
>|||OK, now I have solved the problem by an approach I found at some other post
presented by Joe Celko:
CREATE TABLE Foobar
(id INTEGER NOT NULL PRIMARY KEY,
name CHAR(10) NOT NULL);
INSERT INTO Foobar VALUES (12, 'Johnson');
INSERT INTO Foobar VALUES (57, 'Nelson');
INSERT INTO Foobar VALUES (11, 'Roberts');
INSERT INTO Foobar VALUES (68, 'Smith');
SELECT F1.id, F1.name,
(SELECT MAX (id)
FROM Foobar AS F2
WHERE F2.id < F1.id) AS prev_id,
(SELECT MIN(id)
FROM Foobar AS F3
WHERE F3.id F1.id) AS next_id
FROM Foobar AS F1;
These subquery expressions are the LUB (least upper
bound) and the GLB (greatest lower bound).
Thats finds the lower records. Than a simply comparation like this:
SELECT CASE WHEN f1.id< (select id from foobar WHERE id =
dbo.get_prev_id(F1.id)) THEN date+1 ELSE date END AS date_past_midnight from
foobar as f1
Function get_prev_id is like:
SELECT (SELECT MAX (id) FROM foobar AS F2 WHERE F2.id < F1.id) AS prev_id
FROM foobar AS F1
where F1.id = @.id <-- (argument passed to function)
Thanks for all help
GREY
No comments:
Post a Comment