how can i show nearest matching record to a current time? 1

[chartpop]

ok, in my database i have StartTime | EndTime | DayDay
start time is in the format 22:30
end time is in the same format i.e 00:30
dayday is the numerical equiv of day of the week i.e sunday = 1, monday = 2, etc.

What i want to display is the nearest matching recording where StartTime >= FormatDateTime(NOW, 4)

I want it to display the record with the nearest start time that has started but not yet ended.

this is the nearest i got...

WHERE StartTime > '" + FormatDateTime(NOW, 4) + "' AND (DayDay = " + Replace(rsSchedule__varDayDay, "'", "''") + ")"

how could i do something like this

WHERE StartTime = FormatDateTime(NOW, 4) OR FormatDateTime(NOW, 4) BETWEEN StartTime AND EndTime

i know the above code is not correct, but it should help you understand what im trying to do.

 

[pkailas]

why not just return the TOP 1 record ordered by StartTime DESC?

SELECT TOP 1 * FROM [TABLE} ORDER BY StartTime DESC

if you have many thing running at the same time and EndTime is null until completetion you could try

SELECT TOP 1 * FROM [TABLE] WHERE EndTime is Null ORDER BY StartTime DESC

 

[chartpop]

Ive used what you have written to combine with the need for the records (which may not be unique) to limit to the current day

"SELECT TOP 1 * FROM schedule WHERE StartTime <> '" + FormatDateTime(NOW, 4) + "' AND (DayDay = " + Replace(rsSchedule__varDayDay, "'", "''") + ")  ORDER BY StartTime DESC"

Thanks for the post, can you see anything wrong with the code? It seems to be working.

 

[pkailas]

doesn't your query not include anything that happened to start today? You are just interested in the last thing that ran but didn't finish, right?

 

[chartpop]

I need to display
i.e the current band playing on stage.

the bands all have a start and end time in the schedule
I have a column for choosing the day they are on stage, so I needed to find the band on stage equal to or greater than the current time but before it has ended.

If i dont include the day then it ends up showing me what would be on at this current time on sunday... but i need it to show me whats on now, this current day.

 

[chartpop]

my next problem would be how to also display one underneath that said "playing next"....

 

[pkailas]

Gotcha
How about this?

"SELECT TOP 1 * FROM schedule WHERE StartTime <= '" formatdatetime(now, 4) & "' AND EndTime > '" & formatdatetime(now, 4) & "' AND (DayDay = " & Replace(rsSchedule__varDayDay, "'", "''") &  ")  ORDER BY StartTime DESC"

 

[chartpop]

I cant get your last code to work.

Doesnt the AND statement need to be in brackets?
I always get problems when I try and use more than one AND statement.

 

[pkailas]

I'm sorry for the typo... try this.

"SELECT TOP 1 * FROM schedule WHERE StartTime <= '" & formatdatetime(now, 4) & "' AND EndTime > '" & formatdatetime(now, 4) & "' AND (DayDay = " & Replace(rsSchedule__varDayDay, "'", "''") &  ")  ORDER BY StartTime DESC"

 

[chartpop]

i get this error when i try run that.

Microsoft OLE DB Provider for ODBC Drivers error '80040e07' 

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

the only code that works so far is the one with the single AND

 

[pkailas]

could you post the code you're using along with this? that way I can show you how to put some debug stuff in to see what's going on. The problem is with data types and quotes.. I need to see what's going on.

 

[chartpop]

This currently works... unless theres something im missing, ive made the day static (varDayDayToday) and varDayDay is now only used for the main full day display of the schedule, ive made the date for that todays date as default, but it picks up the querysting ?day=1 ?day=2 etc so that i can navigate the different days(i know thats not part of the original question but its part of the code displayed here... ive stopped before i got to the html stuff)

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/myconn.asp" -->
<%
Dim rsSchedule__varDayDay
rsSchedule__varDayDay = Weekday(Date)
rsSchedule__varDayDayToday = Weekday(Date)

If (Request("day")  <> "") Then 
  rsSchedule__varDayDay = Request("day") 
  else
End If
%>
<%
Dim rsShedule__varDayDay
rsShedule__varDayDay = Weekday(Date)
if Request.QueryString("day") <> "" then rsShedule__varDayDay = Request.QueryString("day")
%>
<%
Dim rsSchedule
Dim rsSchedule_numRows

Set rsSchedule = Server.CreateObject("ADODB.Recordset")
rsSchedule.ActiveConnection = MM_myconn_STRING
rsSchedule.Source = "SELECT *  FROM schedule  WHERE DayDay = " + Replace(rsSchedule__varDayDay, "'", "''") + "  ORDER BY StartTime"
rsSchedule.CursorType = 0
rsSchedule.CursorLocation = 2
rsSchedule.LockType = 1
rsSchedule.Open()
rsSchedule_numRows = 0
%>
<%
Dim rsAir
Dim rsAir_numRows
Set rsAir = Server.CreateObject("ADODB.Recordset")
rsAir.ActiveConnection = MM_myconn_STRING
rsAir.Source = "SELECT TOP 1 * FROM schedule WHERE StartTime <> '" + FormatDateTime(NOW, 4) + "' AND (DayDay = " + Replace(rsSchedule__varDayDayToday, "'", "''") + ")  ORDER BY StartTime DESC"
rsAir.CursorType = 0
rsAir.CursorLocation = 2
rsAir.LockType = 1
rsAir.Open()
rsAir_numRows = 0
%>

<%
Dim HLooper1__numRows
HLooper1__numRows = 8
Dim HLooper1__index
HLooper1__index = 0
rs_numRows = rs_numRows + HLooper1__numRows
%>

 

[pkailas]

try this..

rsAir.source = "SELECT TOP 1 * FROM schedule WHERE StartTime <= '" & formatdatetime(now, 4) & "' AND EndTime > '" & formatdatetime(now, 4) & "' AND (DayDay = " & Replace(rsSchedule__varDayDay, "'", "''") &  ")  ORDER BY StartTime DESC"

response.write rsAir.source
response.end

what get's displyed?

 

[chartpop]

gives me

SELECT TOP 1 * FROM schedule WHERE StartTime <= '01:17' AND EndTime > '01:17' AND (DayDay = 6) ORDER BY StartTime DESC

 

[pkailas]

what are the data types for StartTime, EndTime and DayDay?

only use ' ' if they datatypes are NOT integers

what if you cut and paste that into Query Analyzer?

Other than that, the query looks fine.

 

[chartpop]

datatype of starttime and endtime are text
day is numeric

if i use the sql analyzer it returns no records. unless i change

DayDay = 6) ORDER BY StartTime DESC

to

DayDay <> 6) ORDER BY StartTime DESC

then i get one record from thursday showing up.

 

[pkailas]

Ok, that means that the query works at least

When you use you don't get any returned results? Is this not what you expected? Do you have data in the database that is for DayDay 6?

SELECT TOP 1 * FROM schedule WHERE StartTime <= '01:17' AND EndTime > '01:17' AND (DayDay = 6) ORDER BY StartTime DESC

 

[chartpop]

yes, i have stuff in for every day, no gaps, rolling 7days.

 

[pkailas]

so there is a record in schedule where DayDay is 6 and the StartTime is befor 01:17 and the EndTime is after 01:17?

Just making sure so we aren't chasing our tails.

 

[chartpop]

it displays day6 time 00:03 - 06:00 day7 so yes...

but only when i use the <>