Solved

Selecting records according to date

Posted on 2007-03-22
6
277 Views
Last Modified: 2013-12-13
Hi there,

I'm having trouble selecting events from my database appropriate to a specific month and year of the user's choice.

Suppose for the following examples, the user wishes to view all records where the event is during, or overlaps into, April 2007.

The following query produces results:

SELECT tblevents.*, DATE_FORMAT(tblevents.event_expires,'%d.%m.%Y') as event_expires, DATE_FORMAT(tblevents.event_to,'%d.%m.%Y') as event_to, DATE_FORMAT(tblevents.event_from,'%d.%m.%Y') as event_from FROM tblevents WHERE (tblevents.event_from BETWEEN '2007/04/01' AND '2007/04/30') OR (tblevents.event_to BETWEEN '2007/04/01' AND '2007/04/30') ORDER BY event_from ASC

However, this version does not:

SELECT tblevents.*, DATE_FORMAT(tblevents.event_expires,'%d.%m.%Y') as event_expires, DATE_FORMAT(tblevents.event_to,'%d.%m.%Y') as event_to, DATE_FORMAT(tblevents.event_from,'%d.%m.%Y') as event_from FROM tblevents WHERE ('2007/04/01' BETWEEN tblevents.event_from AND tblevents.event_to) OR ('2007/04/30' BETWEEN tblevents.event_from AND tblevents.event_to) ORDER BY event_from ASC


The first version works largely, but if an event spans from say January - June, it won't return such rows because the to and from date column values fall outside the current month. I figured I could fix this using the 2nd query shown about, but that doesn't seem to want to return any rows at all. I guess the BETWEEN syntax doesn't take date columns as the arguments in that way? Why, I can't understand though.

Can someone please tell me how to fix this? I know I could adjust my system to record an event_id against a date in a new table, but I doubt there's any need for that and some nice SQL should yield the results I require...can it?

Hope you can help!

Many thanks.
0
Comment
Question by:m175400
6 Comments
 
LVL 11

Expert Comment

by:Louis01
ID: 18771882
This one below should work unless your event can start before April and end after April.
If so, you need to do a cross join to a calendar containing an entry for every day.

SELECT tblevents.*, DATE_FORMAT(tblevents.event_expires,'%d.%m.%Y') as event_expires,
DATE_FORMAT(tblevents.event_to,'%d.%m.%Y') as event_to,
DATE_FORMAT(tblevents.event_from,'%d.%m.%Y') as event_from
FROM tblevents
WHERE (year(tblevents.event_from) = 2007 or year(tblevents.event_to) = 2007)
AND (month(tblevents.event_from) = 4 or year(tblevents.event_to) = 4)
ORDER BY event_from ASC


Cross join to a calendar containing an entry for every day - Example

SELECT tblevents.*, DATE_FORMAT(tblevents.event_expires,'%d.%m.%Y') as event_expires,
DATE_FORMAT(tblevents.event_to,'%d.%m.%Y') as event_to,
DATE_FORMAT(tblevents.event_from,'%d.%m.%Y') as event_from
FROM tblevents cross join (select caldate from mycalendar where mycalendar.caldate BETWEEN '2007/04/01' AND '2007/04/30') mycalendar
WHERE mycalendar.caldate between tblevents.event_from and tblevents.event_to
0
 
LVL 1

Author Comment

by:m175400
ID: 18771938
Sorry, so how does mycalendar table work?

Trying to visualise this. Do I just need one column with every date day by day for like 30yrs over which the site could be used? Or do I need an entry for each day the event is on e.g.

tblcalendar
calendar_id
calendar_date
event_id

?
0
 
LVL 11

Accepted Solution

by:
Louis01 earned 125 total points
ID: 18772236
You can use a table as you suggested.
The following is an example of how to return all the days from the current date to the end of the month (in MS SQL SERVER though -you will need to change it to MySql)


Use it instead of the [(select caldate from mycalendar where mycalendar.caldate BETWEEN '2007/04/01' AND '2007/04/30') mycalendar] part

SELECT dateadd(dd,0, datediff(dd,0,DATEADD(dd, seq.id, getdate()))) calDate
FROM (SELECT (a1.id + a0.id) id
        FROM (SELECT 0 id UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
                  SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
        ) a0      CROSS JOIN (SELECT 0 id UNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL SELECT 30
        ) a1) seq
WHERE month(DATEADD(dd, seq.id, getdate())) = month(getdate())
0
 
LVL 2

Assisted Solution

by:ckyteh
ckyteh earned 125 total points
ID: 19055552
Hi,

If your query is generally working except in the case where the event spans the entire month, then perhaps add another condition to check for that. This can be done by checking if the "from" date is before the start of the month AND the "to" date is after the end of the month. For example, add:

OR (tblevents.event_from < '2007/04/01' AND tblevents.event_to > '2007/04/30')

So you would end up with:

SELECT tblevents.*, DATE_FORMAT(tblevents.event_expires,'%d.%m.%Y') as event_expires, DATE_FORMAT(tblevents.event_to,'%d.%m.%Y') as event_to, DATE_FORMAT(tblevents.event_from,'%d.%m.%Y') as event_from FROM tblevents WHERE (tblevents.event_from BETWEEN '2007/04/01' AND '2007/04/30') OR (tblevents.event_to BETWEEN '2007/04/01' AND '2007/04/30') OR (tblevents.event_from < '2007/04/01' AND tblevents.event_to > '2007/04/30')  ORDER BY event_from ASC
0
 
LVL 1

Expert Comment

by:Computer101
ID: 21207277
Forced accept.

Computer101
EE Admin
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to count occurrences of each item in an array.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now