[RESOLVED] SQL Statment help (Date)

[Bongo]

Greetings,

i have a DB with Appointments.

datestartand and dateend are fields containing the date from to.
The 2 fields are of the format Timestamp.

I am getting in the Application 2 dates (from - to) and need now to check if an appointment is allready boocked.

So how can this Querry be look like? What ever i try i get the wrong result

strSQL = "SELECT Count(*) FROM [Table] WHERE [datestart] >= " & _
             makeDBDatumSQL(dtEnde) & " and [dateend] <= " &        makeDBDatumSQL(dtStart) & " and lgppk = '" & strPK & "'"

many thanks for your help in advance

[FunkyDexter]

Assuming you're talking about SQLServer, Timestamps are not the same as datetimes. You can't do a comparison against them in the way that you have. To achieve the result you're desrcibing you're going to need datetime fields for start and end date.

[Bongo]

@FunkyDexter

DB = PostGreSQL

the function makeDBDatumSQL returns a correct DB timeformat

SELECT Count(*) FROM [Table] WHERE [datestart] >= " & _
makeDBDatumSQL(dtEnde) & " and [dateend] <= " &
makeDBDatumSQL(dtStart) & " and lgppk = '" & strPK & "'"

only think i want to achive is to find out if there is a free slot in the Schedule based on the given dtStart and dtEnde

[FunkyDexter]

Sorry, I didn't realise you were using PostGre. I've only used it once or twice so my syntax knowledge isn't good but I think the problem is actually with your logic.

You're checking that there's a record that starts after your new record ends and ends before you're new record starts. I imagine you're query returns a count of zero every time because you're checking for a logically impossible state. I think what you want is:-

"SELECT Count(*) " & _
"FROM [Table] " & _
"WHERE ([datestart] <= " & makeDBDatumSQL(dtStart) & " " & _
" AND [dateend] >= " & makeDBDatumSQL(dtStart) & ")" & _
"OR ([dateend] >= " & makeDBDatumSQL(dtEnd) & " " & _
" AND [datestart] =< " & makeDBDatumSQL(dtEnd) & ")" & _
"OR ([datestart] >= " & makeDBDatumSQL(dtStart) & " " & _
" AND [dateend] >= " & makeDBDatumSQL(dtEnd) & ")" & _
"and lgppk = '" & strPK & "'"

You're looking for rows that start before your new record starts but end after it starts (they overlap the beginning of your new record) or end after you new record ends but start before it ends (they overlap the end of your new redcord). Finally, you also want to grab any records that start after your new record starts and end before it finishes (they are wholly contained within the dates of your new record).

I'm not sure but I think you could probably make this more efficient and readable by reversing all the boolean logic and checking for records which don't match them. I'm due to go home though so you'll have to experiment with that yourself.

[Bongo]

@FunkyDexter

many thanks it help but if you should get out a more efficient solution i would be interested.

In the meantime i close the thread as resolved and many thanks for the help.