473,320 Members | 1,814 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Time between the busy_end_time and the next busy_start_time

Dear Friends,

I need the time between the busy_end_time and the next
busy_start_time. oracle has a 'Lead' function which can easily address
this issue.

create table t ( bst date, bet date );

insert into t values ( to_date( '2004-01-15 12:00:00','yyyy-mm-dd
hh24:mi:ss'), to_date( '2004-01-15 13:00:00','yyyy-mm-dd hh24:mi:ss')
);

insert into t values ( to_date( '2004-01-15 14:00:00','yyyy-mm-dd
hh24:mi:ss'), to_date( '2004-01-15 15:00:00','yyyy-mm-dd hh24:mi:ss')
);

insert into t values ( to_date( '2004-01-15 16:00:00','yyyy-mm-dd
hh24:mi:ss'), to_date( '2004-01-15 17:00:00','yyyy-mm-dd hh24:mi:ss')
);

insert into t values ( to_date( '2004-01-15 17:00:00','yyyy-mm-dd
hh24:mi:ss'), to_date( '2004-01-15 18:00:00','yyyy-mm-dd hh24:mi:ss')
);

insert into t values ( to_date( '2004-01-15 18:00:00','yyyy-mm-dd
hh24:mi:ss'), to_date( '2004-01-15 19:00:00','yyyy-mm-dd hh24:mi:ss')
);

insert into t values ( to_date( '2004-01-15 21:00:00','yyyy-mm-dd
hh24:mi:ss'), to_date( '2004-01-15 22:00:00','yyyy-mm-dd hh24:mi:ss')
);
SQL> select * from t;

BST BET
------------------- -------------------

2004-01-15 12:00:00 2004-01-15 13:00:00

2004-01-15 14:00:00 2004-01-15 15:00:00

2004-01-15 16:00:00 2004-01-15 17:00:00

2004-01-15 17:00:00 2004-01-15 18:00:00

2004-01-15 18:00:00 2004-01-15 19:00:00

2004-01-15 21:00:00 2004-01-15 22:00:00
Oracle has the following function to get the following

SQL> select bst current_bst,
bet current_bet,
nvl( lead(bst) over( order by bst ), '2004-01-15 23:59:59' )
next_bst
from t;
CURRENT_BST CURRENT_BET NEXT_BST
------------------- ------------------- -------------------
2004-01-15 12:00:00 2004-01-15 13:00:00 2004-01-15 14:00:00

2004-01-15 14:00:00 2004-01-15 15:00:00 2004-01-15 16:00:00

2004-01-15 16:00:00 2004-01-15 17:00:00 2004-01-15 17:00:00

2004-01-15 17:00:00 2004-01-15 18:00:00 2004-01-15 18:00:00

2004-01-15 18:00:00 2004-01-15 19:00:00 2004-01-15 21:00:00

2004-01-15 21:00:00 2004-01-15 22:00:00 2004-01-15 23:59:59

I am unable to use the 'Lead' funtion at SQL Server. Anybody can shed
some light pls.

Regards
Kumar
Jul 20 '05 #1
1 2788
CREATE TABLE T (bst DATETIME, bet DATETIME, CHECK (bst<bet), PRIMARY KEY
(bst,bet))

INSERT INTO t VALUES ('2004-01-15T12:00:00', '2004-01-15T13:00:00')
INSERT INTO t VALUES ('2004-01-15T14:00:00', '2004-01-15T15:00:00')
INSERT INTO t VALUES ('2004-01-15T16:00:00', '2004-01-15T17:00:00')
INSERT INTO t VALUES ('2004-01-15T17:00:00', '2004-01-15T18:00:00')
INSERT INTO t VALUES ('2004-01-15T18:00:00', '2004-01-15T19:00:00')
INSERT INTO t VALUES ('2004-01-15T21:00:00', '2004-01-15T22:00:00')

SELECT T1.bst AS current_bst, T1.bet AS current_bet,
COALESCE(MIN(T2.bst),'2004-01-15T23:59:59.997') AS next_bst
FROM T AS T1
LEFT JOIN T AS T2
ON T2.bst >= T1.bet
GROUP BY T1.bst, T1.bet

--
David Portas
SQL Server MVP
--
Jul 20 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Kevin Lin | last post by:
Hi, I'm trying to come up with a function using PHP/MySQL (preferably a PHP only solution) to find the next time that matches a particular pattern, where the pattern is a combination of values...
17
by: newbiecpp | last post by:
I have hard time to understand run-time environment. Let assume that I have a program that has a simple variable alpha. When this variable is statically allocated, the compiler can use the...
14
by: George | last post by:
In Time.h there is a structure defined for time settings. I'm building an embedded system that has a Real Time Clock but it's not PC compatible. My question is: I don't some elements of the...
18
by: Max | last post by:
This is a follow-up on my previous thread concerning having the program wait for a certain date and time and then executing some code when it gets there. My question is; can I use the Sleep...
13
by: Yannick | last post by:
Hi, I would like to program a small game in Python, kind of like robocode (http://robocode.sourceforge.net/). Problem is that I would have to share the CPU between all the robots, and thus...
3
by: Ethan Strauss | last post by:
Hi, There have been quite a few discussions of Random not giving random numbers and how to fix that by feeding in a new seed each time, waiting enough time, or calling the Next() method of the...
2
by: Dan | last post by:
Hi, I use the logon control for logging into the application. When logging and checking the option "remember me next time" and then closing the browser without to press any logout button which...
9
by: flanagak | last post by:
All, I was wondering if someone could point me in the right direction to decode the following information. I am working on a tool writen in C and one of the items in the file I am decoding is a...
6
by: mattmao | last post by:
Hi all. There is a challenge question I encountered recently, which says: "In plain English, there are six different ways when you want to tell someone else about the current time: ...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.