Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-01-26 : 10:02:53
|
Gary writes "Last week I asked if it was possible to get the results of a query to look like an Excel pivot table. Where I would get results in an X and Y display.
Someone responded and said I should look at using a CASE statment. I have done this and am seeing the data I need, now I just need it to go one step futher and total the results per category.
I work for American Airlines in Dallas and we are trying to display on our intranet web site our departure dependability by the three terminals we operate out of. If a flight does not leave on time it is charged the number of minutes over it's scheduled departure time, and given a delay code for the cause of the delay. Below is the code I am using in my SQL statement and the results.
--------------------------------------------------------------
Select delay1,
"Terminal A" = Case gates.terminal WHEN "A" Then "1" else 0 end,
"Terminal B" = Case gates.terminal WHEN "B" Then "1" else 0 end,
"Terminal C" = Case gates.terminal WHEN "C" Then "1" ELSE 0 END
FROM dbo.Flights INNER JOIN dbo.Gates ON dbo.Flights.DepGate=dbo.Gates.Gate AND dbo.Flights.ActDepCity=dbo.Gates.Station
WHERE datediff(dd,getdate(), actdate) = -1 and Flight <3000 and ActDepCity = 'DFW' and Minutes1 >05
GROUP BY Terminal, delay1
ORDER BY delay1
---------------------------------------------------------------
These are the results.
delay1 Terminal A Terminal B Terminal C
------ ----------- ----------- -----------
01 0 0 1
03 0 1 0
03 0 0 1
09 1 0 0
11 1 0 0
11 0 0 1
12B 1 0 0
16 1 0 0
27B 1 0 0
27B 0 1 0
27B 0 0 1
29A 1 0 0
36 0 0 1
46 1 0 0
46 0 1 0
46 0 0 1
49M 1 0 0
50M 1 0 0
60 1 0 0
63 1 0 0
63 0 1 0
63 0 0 1
66A 0 1 0
66A 0 0 1
72A 1 0 0
72A 0 0 1
75A 0 0 1
75L 1 0 0
90C 0 0 1
91 0 0 1
91T 1 0 0
91T 0 0 1
94C 0 1 0
----------------------------------------------------------
Is there any way of getting the delay code to add up when there are multiples of the same delay. Ie: code 46 took a delay in all three terminals. Can I get it to just display the delay code and the three delays on one line or row?
I have tried the COUNT function right after the SELECT delay1, Ie: COUNT(*) as NumDelays
But it blows the query, and I get the response (Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'Terminal A'.)
I am so close in getting the information I need!! Any help would be greatly appreciated!!
Regards.
Gary Costigan" |
|
|
|
|
|
|
|
COUNT function in a CASE statment