question

Faisal avatar image
Faisal asked

Need Help Regarding SQL Query

Hi,

i have to make one report for one of event management system, the report is quit complex. the requirement is, we need all the bookings done today for today show, and all the bookings previously done for today's shows, and all the bookings we are doing today for advance shows times.

With Regards

queryreporting
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image Magnus Ahlkvist commented ·
Please update your question with information about your table structure, otherwise it's really hard to help.
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Best of all would be to show what you tried too. We can't see what you can see, so at this point I'm just guessing.
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered

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

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Daniel Ross avatar image
Daniel Ross answered

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

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Faisal avatar image
Faisal answered

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

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.