SQL syntax

[Legacy 98055]

<font color="#202020" size="3" face="Courier New"><pre>Table name = MOVES_ALL



REQTRL PIKTRL MOVTRL


1/1/02 5:58 PM 1/1/02 7:19 PM 1/1/02 7:32 PM

1/1/02 7:31 PM 1/1/02 7:42 PM 1/1/02 7:43 PM

1/1/02 7:35 PM 1/1/02 7:52 PM 1/1/02 7:52 PM

1/1/02 7:51 PM 1/1/02 8:11 PM 1/1/02 8:11 PM

1/1/02 7:57 PM 1/1/02 8:01 PM 1/1/02 8:03 PM

1/1/02 7:57 PM 1/1/02 8:01 PM 1/1/02 8:08 PM

1/1/02 7:58 PM 1/1/02 8:13 PM 1/1/02 8:16 PM

1/1/02 8:19 PM 1/1/02 9:16 PM 1/1/02 9:17 PM

</pre></font>

Hello helpers! I appreciate it.
The above are three fields from the further above mentioned table which have to do with my query. I am very new to SQL and have found it fairy easy to do simple queries of (from date) (to date) but I have run into a small roadblock. The three fields here are the only fields in this table which offer date and time information. The first column of dates is when a the assignment of the task takes place, the second is when it begins, the third is when it is finished. I need to query any of the above for given shifts worked without having to supply the datevalue but only the time value and/or the day of the week. A literal examples:

Return all records which occured between 6 AM to 6PM, on Monday to Wednesday, in the given date range.

What is the syntax if I wanted to query for, say,

01/01/02 00:00 AM to 12/31/02 23:59 PM for all records which occured from any Wednesday within the date range above and the timeframe from Wednesday 6PM to Thursday 6AM ?

The database is still in design and I could easily break up the date fields into three separate fields such as:

03/20/03 15:00 PM could be broken up into these three separate fields:

<font color="#202020" size="3" face="Courier New"><pre>03/20/2003 15:00 PM Thursday</font></pre>

I am willing to do that if you think it would be more efficient but I am betting that there is a way to query with the information structured as is?

That is my question.

Thanks,
Tom

Using VB with ADO 2.6 on a Microsoft Database (mdb)


BTW
<font color="#202020" size="10" face="Courier New">SUPERBONUS</font> question here!
Is it possible to get the average in minutes from field REQTRL to PIKTRL for any given query using SQL?

Thanks again!

 

[kkknie]

The key here is to select different parts of the date fields.

Your Example:
01/01/02 00:00 AM to 12/31/02 23:59 PM for all records which occured from any Wednesday within the date range above and the timeframe from Wednesday 6PM to Thursday 6AM ?

My thoughts:
Select * from MOVES_ALL
Where REQTRL >= '01/01/02 00:00 AM'
and REQTRL < '01/01/03 00:00 AM'
and ((HOUR(REQTRL) >=6 and WEEKDAY(REQTRL)=4)
OR (HOUR(REQTRL) <6 and WEEKDAY(REQTRL)=5))

The HOUR function returns the hour of the day from 0-23.
the WEEKDAY function returns the day of the week from 1-7 (with Sunday as 1)
Note also the use of parenthesis. If you get them wrong, you'll return the wrong data.

I think using the hour and weekday functions will put you on the right track. From there, it's just a matter of getting the right where clause.

As for the BONUS question, I think it's quite simple.

Select AVG((PIKTRL-REQTRL)*24.0*60.0) from MOVES_ALL
Where {Insert your Where clause here}

The *24.0*60.0 handles the fact that subtracting two date variables yields a number in days. This converts to minutes

Hope this helps,

K

 

[Legacy 98055]

Your not kidding it helps! Fantanstic!
I am very impressed and did not know it would be that easy. SQL here I come. I must learn this language. The programs I wrote about a year ago all read from daily text files and tested for conditions. No problem when I only had several thousand lines to read but it's been crawling along lately and I need to get into the real world and learn how to work with databases. The syntax you gave me grabs my query for the whole year in under 1 second as opposed to about 23 seconds using the old stuff. I searched the web and did see some SQL functions but did not see the Hour or Weekday functions. I am trying to get this project done this weekend and may have several other questions if you don't mind checking back if you have time. Thanks a bunch!

Tom

BTW
If you could recommend a web site which offers tutorials or reference beyond something superficial, please reply with the links.
Thanks again...

 

[blueshark]

Here's my list of bookmarks:


<!DOCTYPE NETSCAPE-Bookmark-file-1>
<!-- This is an automatically generated file.
It will be read and overwritten.
Do Not Edit! -->
<TITLE>Bookmarks</TITLE>
<H1>Bookmarks</H1>
<DL>


<DT>Welcome to WinSite! The place for shareware, demoware, trialware, tips, and utilities for your computer
<DT>The Access Web - Welcome
<DT>Welcome to MATA Technologies.com. Providing cusomized software solutions
<DT>CodeArchive THE SOURCE CODE Site, Download Source code for Visual Basic, Java and Tcl-Tk programs
<DT>Smart Access Home
<DT>Access - UtterAccess.com
<DT>Viewing list of forums - Forums powered by UBBThreads™
<DT>Links
<DT>Microsoft Access Files
<DT>MS Access shareware and freeware tools from Peter's Software
<DT>MS Access - Mr.Access - MS Access Developer and User Group
<DT>The Access Web Resources Index
<DT>Ms Access Freak - Main Page
<DT>Contests, Prizes, and Favorites from ProgrammingMSAccess.Com
<DT>Home
<DT>myLittleTools.net web-based applications for ASP developers
<DT>Hugo's Access home-page
<DT>access tips main page
<DT>Code Examples
<DT>Chicago Access Users Group Homepage
<DT>Trevors MS Access FAQ
<DT>Microsoft Access Database Design Help-Examples-Samples-Tips-Techniques
<DT>Microsoft Access Help Centre Click here for advice, tips, support, programmers, developers and more!
<DT>Free Utilities! - Trigeminal Software, Inc. (English)
<DT>Alex Home Page - Software and Tips
<DT>Microsoft technical mail list discussion
<DT>Saicom Systems Home - Online Software Consultants. Custom Software development,Systems Analysis,Database Design.
<DT>Microsoft Access Tips
<DT>Pierce Business Systems
<DT>ASP Free.com ASP.NET Homepage
<DT>Conquer Access RunCommand Constants
<DT>Google Search comp.database.ms-access
<DT>Roger's Access Library
<DT>ACC2000 How to Install Access 97 & Access 2000 on Same Computer
<DT>Goldcode Ltd Home Page
<DT>Baarns Consulting Developers Only Home Page
<DT>The Netfolk MS Access Answers Page
<DT>Zada Solutions - Free Developer Add-Ins
<DT>Microsoft Access
<DT>Lebans News Page
<DT>software
<DT>Applecore pages on MS Access
<DT>Access Coach eHome
<DT>MS Access Developers Site - MS Access Wizard Tools
<DT>Files
<DT>Access Rants
<DT>Universal Software Agency Experts for MS ACCESS
<DT>MS-Access Shareware CD-ROM from EMS Professional Shareware
</DL>

 

[Dugantrain]

YEAH!

Yes, Blueshark! This is exactly what I've been needing; a list of bookmarks. Thank you!

 

[Legacy 98055]

Thankyou very much blueshark!
Tom