How to find a lower date

[Grey]

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

[Roy Harvey]

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

On Thu, 4 Jan 2007 00:26:39 +0100, "Grey" <nu**@null.nullwrote:

>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

[Russ Rose]

"Grey" <nu**@null.nullwrote in message
news:en**********@news.dialog.net.pl...

>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

>
Any solution?
Grey

[Grey]

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

[Grey]

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

>

>>
Any solution?
Grey

[Roy Harvey]

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.

>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" <nu**@null.nullwrote:

>

>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

[Grey]

>>dateid date starttime endtime

458 2006-12-29 22:00 23:15
458 2006-12-29 00:15 01:30 --Problem?

>>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

[Roy Harvey]

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:

>>>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" <nu**@null.nullwrote:

>

>>>dateid date starttime endtime

458 2006-12-29 22:00 23:15
458 2006-12-29 00:15 01:30 --Problem?

>>>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

[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