May 13, 2010 at 8:33 am
I am trying to find another way to handle my issue through SQL vs Crystal Reports.
I have a Crystal Report that currently has a subreport to display by Date/Time what activity or rental event is happening in each room
The common table before each is the booking table.
The report for the Activities in each room displays
Activity Title
Date/Time
Room
The subreport for Rental event in each room displays
Rental Event
Date/Time
Room
I need to create one report sorted by Date/Time
The report for the Activities links the Booking table to Course_book table via booking_id.
The course_booking table is linked to Course table via course_id. The Course table is linked to the Activity table via activity_id. The Booking table is also linked to the Facility table via facility_id.
All Joins are LOJ. The activity title is from activity table. The room is from the facility table
The report for the rental events links the booking table to the rental_booking table via booking_id.
The Rental_booking table is linked to the Function table via the function_id. The Rental_booking table is linked to the Rental table via rental_id. The Booking table is also linked to the facility table via facility_id. All joints are LOJ. The Rental event name is from the Rental table
The facility table is used in the selection criteria to exclude certain facilities from the list.
Now for my question: I need to sort the data by Date/Time vs. activity then rentals. I am not sure how to write the query in SQL using SQL SERVER 2005 or 2008. Would this handled thru a subquery?
If so, can someone provide assistance on how to write the query. Thanks in advance
May 13, 2010 at 8:56 am
If you could post some table structures, sample data, and expected output that would help us
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
May 14, 2010 at 6:23 am
You can sort data by ORDER BY clause.
So you can order Activity, OtherFields, then DateTimeField as
ORDER BY Activity, OtherFields, DateTimeField
But in SQL2005 there are new ranking functions like
ROW_NUMBER() OVER (PARTITION BY Activity, OtherFields ORDER BY DateTimeField desc)
this select will number records from starting 1 in each activity, ... column combination.
Please check SQL - SELECT TOP n or SELECT TOP Random n Rows From a Table For Each Category or Group
Perhaps this is what you are asking for.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply