Login or Register to Ask a Question and Join Our Community


Programming

SQL: find if a set od dates falls in another set of dates

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Programming SQL: find if a set od dates falls in another set of dates
# 1  
Old 10-16-2010
SQL: find if a set od dates falls in another set of dates
Don't know if it is important: Debian Linux / MySQL 5.1

I have a table:
Code:
media_id  	int(8)
group_id 	int(8)
type_id 	int(8)
expiration 	date 
start 	        date 		
cust_id 	int(8) 
num_runs 	int(8) 
preferred_time 	int(8)
edit_date 	timestamp 		ON UPDATE CURRENT_TIMESTAMP 		
[.....]
id 	int(8)

Scenario: I run a report to show all media that was or will be active between two dates, say 10/01/2010 and 10/17/2010.

Now I can write a statement that will give me what i want but it seems like a bad way to do it.
Code:
SELECT * FROM media_a WHERE media_a.cust_id =3 AND (
( CAST( '2010-10-15' AS DATE ) BETWEEN media_a.start 
AND DATE_ADD( expiration, INTERVAL 1 DAY ) )
OR ( CAST( '2010-10-16' AS DATE ) BETWEEN media_a.start
AND DATE_ADD( expiration, INTERVAL 1 DAY ) )
OR ( CAST( '2010-10-17' AS DATE ) BETWEEN media_a.start 
AND DATE_ADD( expiration, INTERVAL 1 DAY ) )
) ORDER BY media_a.group_id, preferred_time

If someone knows of a cleaner more elegant way to do this and is willing to share, I'd be appreciative.

Thanks

Vertical
Last edited by Scott; 10-16-2010 at 07:48 AM.. Reason: Please use code tags
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

How to find the difference between epoc dates in HH:MM:SS?

How to find the difference between below epoc dates in HH:MM:SS 1557863573 converts to Tuesday May 14, 2019 21:52:53 (pm) in time zone Europe/Amsterdam (CEST) 1557866394 converts to Tuesday May 14, 2019 22:39:54 (pm) in time zone Europe/Amsterdam (CEST) #!/bin/bash set -x A=1557863573... (4 Replies)
Discussion started by: abhaydas
4 Replies

2. Shell Programming and Scripting

Display dates between two dates

Hi All, I have 2 dates in mm/dd format. sdate=10/01 (October 01) edate=10/10 (October 10) I need the dates in between these 2 dates like below. 10/01 10/02 10/03 10/04 10/05 10/06 10/07 10/08 (1 Reply)
Discussion started by: jayadanabalan
1 Replies

3. UNIX for Advanced & Expert Users

How to get the Missing dates between two dates in the table?

Hi Am Using Unix Ksh ... I have a Table called date select * from date ; Date 01/02/2013 06/02/2013 I need the output as Missing Date 01/02/2013 02/02/2013 03/02/2013 04/02/2013 05/02/2013 06/02/2013 (2 Replies)
Discussion started by: Venkatesh1
2 Replies

4. Shell Programming and Scripting

Generating dates between two dates

HI, i have row like this HHH100037440313438961000201001012012073110220002 N in this i have 2 dates in pos 25-32 and 33-40 , so based upon the se two dates , i need to generated records between these two values so in the above record 20100101 and 20120731 need to genearte rows like this... (4 Replies)
Discussion started by: sathishsr
4 Replies

5. UNIX for Dummies Questions & Answers

How to write the dates between 2 dates into a file

Hi All, I am trying to print the dates that falls between 2 date variables into a file. Here is the example. $BUS_DATE =20120616 $SUB_DATE=20120613 Output to file abc.txt should be : 20120613,20120614,120120615,20120616 Can you pls help me accomplish this in LINUX. Thanks... (5 Replies)
Discussion started by: dsfreddie
5 Replies

6. Shell Programming and Scripting

Help with Dates and SQl stmt?

hi All Please explain the below statement in RED?What does this mean? perl /HDS/common/operations/Quality_Team/Nirvana/WEEKLY_OOPS/sql_dump.pl --sql "select * from WEEKLY_REPORT order by test_case, type" --username=ddb_qa --password=ddb_qa123 --sid=pldeldb --output... (1 Reply)
Discussion started by: SVS_2017
1 Replies

7. Emergency UNIX and Linux Support

Replacing dates]] with (dates)]]

Hi guys, For my wiki site I need to fix 1400 pages that use the wrong date format, most pages (not all) use eg. 1988]] I need to change that to (1988)]] The date range goes back to 1400 so I guess I need to do the following ssh into my server, dump mysql database vi .sql dump search... (20 Replies)
Discussion started by: lawstudent
20 Replies

8. Shell Programming and Scripting

Need script to generate all the dates in DDMMYY format between 2 dates

Hello friends, I am looking for a script or method that can display all the dates between any 2 given dates. Input: Date 1 290109 Date 2 010209 Output: 300109 310109 Please help me. Thanks. :):confused: (2 Replies)
Discussion started by: frozensmilz
2 Replies

9. Shell Programming and Scripting

find log file between two dates

Dear All, Please can you help me to crack this query? If the log files for the task above all had a naming convention of myoutput_YearMonthDay.log (i.e. myoutput_20060215) How would you find only those log files created between the 10th and the 20th of each month going back the last 365 days.... (1 Reply)
Discussion started by: justin_mca
1 Replies
Login or Register to Ask a Question