?
Solved

Selecting records according to date

Posted on 2007-03-22
6
Medium Priority
?
283 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 500 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 500 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses how to implement server side field validation and display customized error messages to the client.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses

771 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