question

As the commenters say, it's a tricky one for us to help with without the schema / table structures and some sample data.

However, as a starting point, two things to consider:

(1) Breaking the query down into separate queries, the results of which you then merge together using the UNION ALL command, eg (assuming bookingdate / showdate are stored as date only, with no time part):

select * from bookings where showdate = convert(datetime, convert(varchar(10), getdate(), 120) AND bookingdate = convert(datetime, convert(varchar(10), getdate(), 120)
UNION ALL
select * from bookings where bookingdate = convert(datetime, convert(varchar(10), getdate(), 120) and bookingdate < convert(datetime, convert(varchar(10), getdate(), 120)
UNION ALL
select * from bookings where bookingdate = convert(datetime, convert(varchar(10), getdate(), 120) and showdate > convert(datetime, convert(varchar(10), getdate(), 120)

(2) Alternatively, provide a single query along the lines of:

select * from bookings
where ( 
    (showdate = convert(datetime, convert(varchar(10), getdate(), 120)) AND bookingdate = convert(datetime, convert(varchar(10), getdate(), 120))
OR (bookingdate = convert(datetime, ...

as above

I have done something similar and I used case statements to identify and tag the appropriate rows,

select * ,case 
when datediff(dd,getdate(),bookdate)=0 and 
     datediff (dd,getdate(),eventdate)=0 then 'today' 
when datediff(dd,getdate(),bookdate)=0 and 
          datediff(dd,getdate(),eventdate)>0 then 'future'
when datediff(dd,getdate(),bookdate)=-1 and 
          datediff(dd,getdate(),eventdate)=0 then 'yesterday'
end as 'tag'
from booking

then in your report you can group by tag. just watch out if you have times in the dates and figure out if a day is 24 hours from the booking time or simply the day, and code as required

hi, @ Thomas: if you need schema or table structure please let me know i will provide to help you understand in a better way, but i believe for montly report your query or approach can help me.

@ Daniel: bro i got your point here and will try if it will work but the problem is this is monthtly report, and there are paramenters from and to date, so user will select any date range in it then we have to show with dates.

For Example i will give you some data in the current month example,

Booking Date ShowDate Booking Number Show Time 20th June 21 June 12345 9:45 21 June 23 June 65435 4:00 12 June 12 June 98765 7:00 11 June 27 June 23434 6:00 25 June 27 June 45678 6:00 23 June 27 June 76547 6:00 25 June 25 June 87877 5:00

i hope above example will help you to understand the table structure.

Now in Report we need:

Bookings 4 Today as advance |>>| Booking Done 4 2day 4 2day show |>>| Booking done today or previously for next shows