0

I have a table as -

test_table(booking_id, booking_description, start_date, end_date)

Sample Data - 

1 | Some booking | 06/30/2013 | 08/01/2013
2 | Some new one | 08/05/2013 | 09/01/2013
3 | Some new two | 09/03/2013 | 09/05/2013

Now I want to generate a monthly xml file from using some java code (No problem in it, I would write), I would be passing the month and year (basically start and end date of the month) to mysql query and I want some table as -

month = 7, year 2013

1 | Some booking | 07/01/2013
1 | Some booking | 07/02/2013
...

Month = 9, year = 2013

2 | Some new one | 09/01/2013
  |              | 09/02/2013
3 | Some new two | 09/03/2013
...

I was looking to use a java loop from start date to end date and query mysql to find out whether this date comes in the date range or not, if it comes I would add the details else I would add blanks. But that is going to be horrible approach (will go for 30 times mysql look ups) and I am considering it as last option.

Is there any other way around with one or two mysql query and get the data in the format.

EDIT:

month = 7, year = 2013

Select * 
from booking_details 
where  month(start_date) <= 7 and year(start_date) <= 2013 and 
month(end_date) >= 7 and year(end_date) >= 2013

I developed this query but still not sure would it over all the possible scenarios.

codeomnitrix
  • 4,179
  • 19
  • 66
  • 102
  • which column do you want to check against the month/year you're passing in, start_date or end_date, or both? – BlackICE Jul 10 '13 at 07:05
  • the data should be fetched only if the date lies between some date range in the table, else no data would be selected for the date. – codeomnitrix Jul 10 '13 at 07:19
  • you want to check against 2 date ranges? so if you pass in 7/2013 you want to see if start date or end date are between 7/1/2013 and 7/31/2013? – BlackICE Jul 10 '13 at 07:30
  • yes, I mean to say, if i pass 7/2013 it should fetch all the bookings for this month by checking start & end dates of all the bookings in table. – codeomnitrix Jul 10 '13 at 07:34

1 Answers1

0

Based on my understanding of the question you want something like this:

declare @date datetime
Select booking_id, booking_description, start_date --you don't indicate which date field you want in the results
from test_table
where (start_date between @date and date_add(@date, INTERVAL 1 MONTH))
or (end_date between @date and date_add(@date, INTERVAL 1 MONTH))

SQL is probably not exact, I know TSQL not MySQL but this should be close.

BlackICE
  • 8,816
  • 3
  • 53
  • 91
  • Thanks BlackICE, you see, it will skip the scenario if the start date is of last month and the end date is of next month. eg. start_date = 23 jun 2013 and end_date = 4 Aug 2013. And now if I am looking data for july 2013, the query will fail. – codeomnitrix Jul 10 '13 at 07:52
  • ah, ok, that complicates things, I'll think on it. – BlackICE Jul 10 '13 at 07:55
  • hey I have developed som query in my question edit, can you cross verify it, would it work. I am not able to find out all the scenarios. – codeomnitrix Jul 10 '13 at 07:56