SQL Query

[Roey]

I need a SQL query to return the earliest dates and times for each distinct school from the following database table:

 

SchoolID Date Time

_______ ____ ____

 

3 3/3/04 8.45

4 4/5/04 14.30

3 2/1/04 12.30

3 6/6/04 7.45

4 6/7/04 8.00

 

 

Result should be

 

SchoolID 3 2/1/04 12.30

SchoolID 4 4/5/04 14.30

 

 

Thanks in advance

[Nerseus]

The following will get it for you. I assume Date is one field and you can split it into the Date/Time? If not, let me know.

 

SELECT SchoolID, MIN(Date) AS [Date]

FROM School

GROUP BY SchoolID

[Roey]

Sadly the date time is not one field it is three......

 

Date

Hour

Minutes

[Joe Mamma]

Sadly the date time is not one field it is three......

 

Date

Hour

Minutes

Shoot the DB designer. . .redefine the DB. . . NOW!!!!

Then shoot the DB designer, again, for GP :)

[Joe Mamma]

Sadly the date time is not one field it is three......

 

Date

Hour

Minutes

what flavor database?

 

is the date a string???

if so . . . shoot the db developer one more time!!! :)

 

time h.mm???

 

1.12

2.01 ???

 

2.1 = 2.10????

[Joe Mamma]

1. Dont name your Fields 'date' and 'Time' (I called them aTime and aDate and I assumed the date was String formatted mm/dd/yyyy as I am an ugly american)

2. This query could take an awful long time on alot of data

3. Kick the DB designer for good measure. then kick his mom!!!

 

SELECT SchoolID, 
 min
 (
		 cast
		 (	 adate + ' ' + 
				 Replace
				 ( 
						Cast(aTime AS varchar(5) ),
						'.' ,
						':'
				 ) 
				 as datetime
		 )
 ) 
 as MinDateTime
FROM TEST
GROUP BY SchoolID

 

oh yeah. . . Make sure your globalization settings on the sql server have ':' set as the Hour minute seperator. . .

 

Lastly. . . Slap his sister if she is close by

Edited July 31, 2004 by Joe Mamma

[Joe Mamma]

oh I just saw the hours and minutes are different fields. . .

 

ints????

[Joe Mamma]

Fields are SchoolID, aDate, Hr, and M

SELECT SchoolID, 
min
(
	cast
	(	 
		adate + ' ' + 
			Cast(hr AS varchar(2) ) +':'+
			Cast(m AS varchar(2) )
		as datetime
	)
) 
as MinDateTime
FROM TEST
GROUP BY SchoolID

 

Still holds true about the Hour:minute delimiter.

 

Is that DB designer still breathing????

[Roey]

Thanks for all of your help Joe Mamma. Can you (or anyone else for that matter) give me some pointers on a better way of designing the table, as I'd rather change it than shoot myself..... I am trying to store multiple visits to schools in the table by date / hour / minute. The only reason that I am trying to find the first scheduled date is that there is a requirement for it on a report that I am doing in Crystal.

[Joe Mamma]

3 questions. . .

 

1. what db are you using?

 

2. Are you needing to select the data by earliest hour and or miniute without regard to the date?

 

3. Do you only need to split the date/time for display purposes?

 

Pointer

use the datetime field type - never store dates as strings.

[Roey]

Hi Joe

 

1) I am using an Access database

2) The earliest date and time need to be selected e.g 1-Aug-2004 at 8.00am

3) The date and time are split up for display purposes only.

 

I am exploring option 3 and seeing if there is a way to concatinate the date and time fields together so that they can be stores as one....

[Joe Mamma]

Hi Joe

 

1) I am using an Access database

2) The earliest date and time need to be selected e.g 1-Aug-2004 at 8.00am

3) The date and time are split up for display purposes only.

 

I am exploring option 3 and seeing if there is a way to concatinate the date and time fields together so that they can be stores as one....

great!

 

store the date/time in one column of type Date/Time.

Use a datetimepicker on your forms to manipulate.

Set the properties:

 

Format = "Custom"

 

CustomFormat set to one of the following:

"M/d/yy h:mm:ss tt" for 1/1/04 8:04:03 pm

"MM/dd/yy h:mm TT" for 01/01/04 8:04 PM

"d/M/yyyy H:mm" for 31/12/2004 20:04

 

 

if you need to write specific parts:

 

get the data as a DateTime object, use the Date property for the date, TimeOfDay for the time,

use the ToString() method on either to get as a string.

 

or use string formatting:

 

String.Format("Date is {0: M/d/yyyy} Time is {0:h:m:s tt}", DateTime.Now) yields

 

Date is 8/2/2004 Time is 2:29:36 PM

 

String.Format("{0: M/d/yyyy}", DateTime.Now) yields

8/2/2004

 

String.Format("{Time is {0:h:m:s tt}", DateTime.Now) yields

2:29:36 PM

 

remember this, a point in time is one piece of data, treat it that way.

 

if so, Nerseus' response

SELECT SchoolID, MIN(Date) AS [Date]

FROM School

GROUP BY SchoolID

 

works, though I would rename the Date Column to something other than "date", possibly EventDate as date is a keyword:

 

SELECT SchoolID, MIN(EventDate) AS [MinEventDate]

FROM School

GROUP BY SchoolID

 

and buy that DB designer a cup of coffee, he's had a rough day :)

[Roey]

Thanks Joe, really appreciatte all of the help you've given me with this one. Hopefully I can return the favor one day.

[Jay1b]

http://www.sqlcourse.com does some great tutorials on SQL. It even gives u a place where you can test ur scripts urself - I would strongly recommend anyone doing them.

 

http://www.sqlcourse2.com for the more advanced stuff.