January 10, 2013 at 10:16 am
Hi I have a MySQL query below.
I needed to modify it so it returned only results from a set date/time, or even better from the current Date/time onwards
Any help on this? I don't look at SQL at all, and have spent time working on the query I have already! Appologies if this is in the wrong forum.
Many Thanks
Select
Resources.DisplayName,
Bookings.StartTime,
Bookings.EndTime,
Users.LoginName
From
Bookings Inner Join
Users On Bookings.BookedBy = Users.UserID And Bookings.BookedFor =
Users.UserID Inner Join
Resources On Bookings.ResourceID = Resources.ResourceID
Where
Resources.DisplayName = 'PCWORK1'
DisplayNameStartTimeEndTimeLoginName
PCWORK109/01/2013 22:00:0009/01/2013 22:15:00user1
PCWORK109/01/2013 22:15:0009/01/2013 22:30:00user2
PCWORK110/01/2013 05:24:5410/01/2013 06:23:06user3
PCWORK110/01/2013 16:00:0010/01/2013 18:00:00user4
January 10, 2013 at 10:26 am
Hi and welcome to SSC!!! This is a sql server forum and not a MySql so the help you get may be somewhat different. The basics are the same though and what you are doing should be the same in either database.
I needed to modify it so it returned only results from a set date/time, or even better from the current Date/time onwards
Not really sure what you mean here. Do you want rows that have a starttime greater than some date?
I will make that assumption. All you have to do is add an additional predicate to your where clause.
and starttime > [some date]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 10, 2013 at 10:36 am
It should be fairly straight forward, this query will return all rows where the Startdate is greater than or equal to the current datetime
Select
Resources.DisplayName,
Bookings.StartTime,
Bookings.EndTime,
Users.LoginName
From
Bookings
Inner Join Users
On Bookings.BookedBy = Users.UserID And Bookings.BookedFor =Users.UserID
Inner Join Resources
On Bookings.ResourceID = Resources.ResourceID
Where
Resources.DisplayName = 'PCWORK1'
and Bookings.StartTime>=GetDate()
This code will actully return 0 rows, as there are no start times that are greater than the current time.
If you wanted you could use a paramater instead of the GetDate(), to filter more dynamically, this should return all rows where the Starttime is >= 2nd Jan 2013 @ 1pm
Delcare @DateParameter DateTime
Set @DateParameter='02-Jan-2013 13:00:00.000'
Select
Resources.DisplayName,
Bookings.StartTime,
Bookings.EndTime,
Users.LoginName
From
Bookings
Inner Join Users
On Bookings.BookedBy = Users.UserID And Bookings.BookedFor =Users.UserID
Inner Join Resources
On Bookings.ResourceID = Resources.ResourceID
Where
Resources.DisplayName = 'PCWORK1'
and StartTime)>=@DateParameter
_________________________________________________________________________
SSC Guide to Posting and Best Practices
January 10, 2013 at 11:28 am
Thanks for your replies (Sean and Jason)
Especially to Jason - 😀
adding and Bookings.EndTime>=GetDate() was what I was looking for!
The data is from a pc booking system for future bookings, and extracting data for the people who do the timetables to say pcs are booked out
It's so easy when you know how!
Thanks again, Sukh
January 11, 2013 at 2:17 am
Glad to help,
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply