Here is very intresting query,but it is not wirtten in DB2:
input table:
Task Start Finish
Sleep 01-oct-04 19:00 02-Oct-04 07:00
Awake 02-oct-04 07:00 02-Oct-04 20:00
Sleep 02-oct-04 20:00 03-Oct-04 02:00
Need to re****t what I did in a 24 hour period (midnight to midnight),
what would be the best way to obtain the data below:
01-Oct-04 Sleep 5hrs
02-Oct-04 Awake 13hrs
02-Oct-04 Sleep 11hrs
03-Oct-04 Sleep 2hrs
Here is detailed algoritnm expalnation:
To help understand this type of problem, which involves datetime ranges,
it helps to construct a timeline diagram to illustrate the various start
and
finish
scenarios relative to a given date.
taskday nextday
| |
1 <--S====F | |
| |
2 <------S==|=F |
| |
3 <------S==|============|=F
| |
4 |<--S====F |
| |
5 |<--------S==|=F
| |
6 | |<--S====F
| |
In this diagram, the S====F's are the tasks. Each task has start (S) and
finish (F) datetime
values. The taskday line represents time 00:00:00, i.e. midnight, while
nextday is midnight
of the next day. Midnight is what you get when you remove (or "zero out"
if
you prefer)
the time ****tion of a datetime value. There's an arrow pointing from each
start datetime
value to its corresponding date, i.e. midnight value.
So relative to taskday, the diagram shows 6 scenarios which a task can
have,
based on its start and finish datetime values. Remember, we are going to
use
GROUP BY date,
so we want to sum up, when grouping, all tasks relative to each date,
whenever
any part of the elapsed time of the task occurred on that date.
For a given taskday, scenarios 1 and 6 can be ignored, as they will be
included
entirely within previous or next days. In scenarios 2 through 5, then,
some
****tion
of the task elapsed time occurs between taskday and nextday. These
****tions
are shown
in the diagram in bold red.
The following conditions will select only scenarios 2 through 5:
where start < nextday
and finish > taskday
Scenario 6 is excluded because its start is not less than nextday.
Scenario 1 is excluded because its finish is not greater than taskday.
Neat,
eh?
Now to consider grouping by date. We need to have every date for which
even a
****tion of any
task occurs on that date. So we need at least the date of every start
datetime in the data.
Secondly, to cover all possible cases, we also need, somehow, all dates
that
occur
between the start and finish of any task, i.e. cases similar to scenario 3
but which
have multiple dates separating start and finish.
Finally, we need the date corresponding to each finish, to include the
"tail
end" ****tions
of scenarios 3 and 5, i.e. to include these ****tions when they become
scenario 2 when
nextday becomes taskday.
To handle all of the requirements for the different dates that we will
need
to GROUP BY,
we join an integers table to each task, to "generate" each date between
the
date of start
and finish.
select distinct
midnight(start+i) as taskday
from integers
inner
join tasktimes
on midnight(start+i)
between
midnight(start)
and finish
Here, midnight is "pseudo-SQL" and represents a datetime value with the
time
****tion
removed (or zeroed out, if you prefer). We furthermore pretend that we can
simply add an
integer number to a datetime value without a care, to get a new datetime
value which is that
number of days later. Note: the integers table column i must contain
values 0,
1, 2,
and so on. Especially 0.
Now that we can GROUP BY every date of interest, let us move on to the
summing.
In scenarios 2 and 3, we include only the elapsed time from taskday, up to
either
finish or nextday, whichever comes first. In scenarios 4 and 5, we include
only
the elapsed time from start, up to either finish or nextday, whichever
comes
first.
Stated another way, we sum only the ****tion of elapsed time between the
greater of taskday
and start, and the lesser of finish and nextday. You may have to look at
the
diagram for
a while to see it this way.
sum( lesser(finish,nextday)
- greater(taskday,start) )
Here again, lesser and greater are "pseudo-SQL" functions. And again, we
will
pretend
that we can obtain elapsed times in hours simply by subtracting datetime
values without
a care. In practice, the syntax needed to calculate an elapsed time in
hours
will vary wildly
from one database system to the next.
select taskday, task
, sum( datediff(s
, case when taskday
>= start
then taskday
else start end
, case when finish
<= dateadd(d,1,taskday)
then finish
else dateadd(d,1,taskday) end
)
) / 3600.0 as hrs
from ( select distinct
cast(convert(char(10)
,dateadd(d,i,start),120)
as datetime ) as taskday
from integers
inner
join tasktimes
on cast(convert(char(10)
,dateadd(d,i,start),120)
as datetime )
between
cast(convert(char(10)
,start,120)
as datetime )
and finish
) as distinctdates
inner
join tasktimes
on start <= dateadd(d,1,taskday)
and finish > taskday
group by taskday, task
order by taskday, task
Several explanations are in order concerning the specific SQL Server
syntax
used here.
The CASE expressions are standard SQL that SQL Server happens to sup****t.
Other databases may have other constructions (e.g. IIF in Microsoft
Access).
DATEDIFF(datepart, datetime, datetime) and DATEADD(datepart, number,
datetime)
perform date
arithmetic, and dateparts s and d represent seconds and days,
respectively.
We SUM() the elapsed times in seconds, and then divide the total by 3600
to
get hours.
Calculating the difference in hours would also be possible, but requires
care
to ensure
fractional hours are not lost.
CONVERT(CHAR(10),datetime,120) strips off the time ****tion of a datetime
value, and results
in a string, but CAST(expression as DATETIME) converts the string back to
a
datetime.
This is the midnight function mentioned earlier.
Whether any of you other than the person who submitted the original
question
can use this
final solution is not im****tant. What this 3-part explanation
demonstrates,
I hope, is how to attack questions of this type, and the im****tance of
having
comprehensive
test data.
My question is how to replace DATEADD ,DATEDIFF, CONVERT in DB2.
or MAY THERE IS ANOTHER SOLUTION.
Thank's in advance
Leny G.
--
Message posted via http://www.dbmonster.com
|
