Trouble with query

gamerPotatoe

Hi there I habve a problem with a query on two relations I have two relations here: RESERVED_LEGS PNR|FLNO|FLDATE|RSRVDATE And Tickets_Purchased TKTNO|CLASS|DATEPURCHASE|PRICE|LEGID|PNR|WLR this is a simple flight reservation scenario. -When a passenger buys a ticket an entry is made in the tickets purchased relation - and if he makes a booking on that relation the ticket number is associated with an autogenerated PNR value (other wise null if no reservation is made on ticket) and updated in the tickets_PURCHASED relation - AND LIKEWISE an entry with the correposnding PNR is made in the RESERVED_LEGS TABLE where details of the flight number and date are added. There are 2 classes of tickets here namely -first -Third The problem is that I have to make a query which shows the number of resercvations made (in this case entries in the Reserved_Legs table) on the basis of class of tickets upon which reservations were made as well as broke up into months i.e. ------------------------------------------------ |Class |Jan|Feb|March|April|May|June|July|Aug..... ------------------------------------------------ ------------------------------------------------ |First |130|500|320 |112 |120|100 |145 |145| ------------------------------------------------ |Third |402|180|110 |510 |410|110 |128 |162| ------------------------------------------------ The thing is that I'm not sure how this can be accomplished by an SQL query, I've been hacking at it for a while and still have no luck. I tried some queries but the thing is that at anytime there is no guarantee that there will be a ticket of all teh classes booked in all the months. What would be the best way to do this. Any SQL that can create the above relation or is any PL required. I'm doing this for a project and I'm using SQL server 2000 :) and would like any suggestions.

Mike Dimmick

Ah, the old cross-tab problem. Quite tricky in SQL Server 2000. One technique that I've had success with is to define a temporary table containing all the months of interest:

DECLARE @months TABLE
(
MonthNum int NOT NULL
)

INSERT INTO @months
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9 UNION ALL
SELECT 10 UNION ALL
SELECT 11 UNION ALL
SELECT 12

You also need a table for categories:

DECLARE @classes TABLE
(
ClassName varchar(5) NOT NULL
)

INSERT INTO @classes
SELECT 'First' UNION ALL
SELECT 'Third'

You can then join these tables onto your data to get an output that looks something like this:

Class | Month | Reservations

First | Jan | 130
First | Feb | 500
First | Mar | 320
First | Apr | 112
...
First | Dec | count
Third | Jan | count
Third | Feb | count
...
Third | Dec | count

You'll then need to juggle the results into the appropriate output format in the client application. To give you a bit of a start, you might count the number of tickets issued in each month like so:

SELECT
ClassName AS Class,
DATENAME(month, DATEADD(m, MonthNum - 1, '20040101')) AS Month,
COUNT(TKTNO) AS [Count]
FROM
Tickets_Purchased TP
RIGHT JOIN
(
SELECT ClassName, MonthNum
FROM @classes CROSS JOIN @months
) Crit
ON TP.CLASS = Crit.ClassName AND MONTH(TP.DATEPURCHASE) = Crit.MonthNum
GROUP BY
ClassName, MonthNum
ORDER BY
ClassName, MonthNum

I'm sure you can work out the rest from here.

Edbert P

This is quite complex, but I'm gonna give it a try :) You can probably do this in two ways (without using a temporary table, that is). 1. If you're fine with the result being shown vertically: SELECT CLASS, DATENAME([month], RSRVDATE) AS Month, COUNT(*) AS Total FROM RESERVED_LEGS INNER JOIN Tickets_Purchased ON RESERVED_LEGS.PNR = Tickets_Purchased.TKTNO GROUP BY CLASS, MONTH(RSRVDATE) ORDER BY CLASS, MONTH(RSRVDATE) This should return something like: CLASS Month Total First January 100 First February 150 ... 2. If you want the result to be shown horizontally (this is more difficult): SELECT CLASS, (SELECT COUNT(*) FROM RESERVED_LEGS INNER JOIN Tickets_Purchased AS TP ON RESERVED_LEGS.PNR = TP.TKTNO WHERE MONTH(RSRVDATE) = 1) AND TP.CLASS = TicketsPurchased.CLASS) AS Jan, (SELECT COUNT(*) FROM RESERVED_LEGS INNER JOIN Tickets_Purchased AS TP ON RESERVED_LEGS.PNR = TP.TKTNO WHERE MONTH(RSRVDATE) = 2) AND TP.CLASS = TicketsPurchased.CLASS) AS Feb, (SELECT COUNT(*) FROM RESERVED_LEGS INNER JOIN Tickets_Purchased AS TP ON RESERVED_LEGS.PNR = TP.TKTNO WHERE MONTH(RSRVDATE) = 3) AND TP.CLASS = TicketsPurchased.CLASS) AS Mar, ... ... ... FROM Tickets_Purchased GROUP BY CLASS ORDER BY CLASS You can always use the query from #1 and format it to present it the way you want it to be, as I think it will be the most efficient query from both. I hope it helps ;) PS: You might want to tweak the SQL queries a bit to make it work :) PPS: Maybe there is a more efficient way to do #2. Anyone? Edbert P. Sydney, Australia.