is it possible to get ...

  • Thread starter Thread starter haiwen
  • Start date Start date
H

haiwen

Hello, everyone:

I have three tables like below:
class
{classID,int;
cbtHour, int;
ojtHour, int;
}
schedule
{
scheduleID, int;
classID, int;
}
date
{
scheduleID,int;
startTime, DateTime;
endTime, DateTime;
}
the requirement is I need to set up schedule's date so
that their total hour equal to the class's
cbtHour+ojtHour. that means finish one schedule set up for
the class.

to think the SUM or average aggregate operation cannot
take a datetime data type as an argument, but I still
wonder is it possible to get those scheduleID which has
met the requirement in SQL query.

Could anyone help? I do appreciate it.

haiwen
 
Hi Haiwen,

Based on my understanding, you need to get the scheduleIDs whose time span
equals to the sum of hours in class table. And the main point is that you
need to know how to compare time span with int. I think we can count the
time span of the schedule and compare with the sum of hours. I've written a
short code snippet to demonstrate this:

declare @endTime as datetime
set @endTime = '4/5/98 9:00 PM'

declare @startTime as datetime
set @startTime = '4/5/98 3:00 PM'

declare @TimeSpan as datetime
set @TimeSpan = @endTime-@startTime

SELECT cast(@TimeSpan as float)*24

The code above cast the time span to float type. You can also cast the sum
of hours to float easily, so that you can compare there two values.

Does this answer your question? If anything is unclear, please feel free to
reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

--------------------
| Content-Class: urn:content-classes:message
| From: "haiwen" <[email protected]>
| Sender: "haiwen" <[email protected]>
| Subject: is it possible to get ...
| Date: Fri, 24 Oct 2003 13:42:58 -0700
| Lines: 32
| Message-ID: <[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Thread-Index: AcOab2ju8masbzE4TmmPdgFPXjtj7A==
| Newsgroups: microsoft.public.dotnet.framework.adonet
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.adonet:64512
| NNTP-Posting-Host: TK2MSFTNGXA09 10.40.1.161
| X-Tomcat-NG: microsoft.public.dotnet.framework.adonet
|
| Hello, everyone:
|
| I have three tables like below:
| class
| {classID,int;
| cbtHour, int;
| ojtHour, int;
| }
| schedule
| {
| scheduleID, int;
| classID, int;
| }
| date
| {
| scheduleID,int;
| startTime, DateTime;
| endTime, DateTime;
| }
| the requirement is I need to set up schedule's date so
| that their total hour equal to the class's
| cbtHour+ojtHour. that means finish one schedule set up for
| the class.
|
| to think the SUM or average aggregate operation cannot
| take a datetime data type as an argument, but I still
| wonder is it possible to get those scheduleID which has
| met the requirement in SQL query.
|
| Could anyone help? I do appreciate it.
|
| haiwen
|
 
Back
Top