| Author |
 Topic  |
|
|
Frosty615
Starting Member
13 Posts |
 Posted - 01/16/2012 : 05:57:59
|
Hi All,
I Have a query
[Code]
ALTER PROCEDURE [dbo].[sp_GetPersonalFlyingHoursLogBook] (@StaffID AS INT, @StartDate AS DATE, @EndDate AS DATE) AS
SELECT AircraftNumber, Duty, ClaimedBy,
DutyDate, TraineeName, pd.Surname, pd.[First], pd1.Surname AS Surname1, pd1.[First] AS First1,
CommanderName, TraineeName,
SUM(DATEDIFF(SECOND,Takeoff,Land))/3600 AS DurationHours,
(SUM(DATEDIFF(SECOND,TakeOff,Land))% 3600)/60 AS DurationMinutes,
COUNT(DUTY) AS NumberOfFlights
FROM eLogSheets els
LEFT OUTER JOIN
PersonalDetails pd
ON
pd.PDStaffID = els.AircraftCommander
LEFT OUTER JOIN
PersonalDetails pd1
ON
pd1.PDStaffID = els.Trainee
WHERE (pd.PDStaffID = @StaffID OR pd1.PDStaffID = @StaffID) AND DutyDate BETWEEN @StartDate AND @EndDate
GROUP BY
AircraftNumber, Duty, ClaimedBy,
DutyDate, TraineeName, pd.Surname, pd.[First], pd1.Surname, pd1.[First],
CommanderName, TraineeName
ORDER BY Dutydate
[/Code]
I need the resulting set of data sorted by the Column TakeOff which is held in the elogSheets table.
Problem is I cannot have TakeOff in the select list or in the GROUP BY clause and it really needs to be ordered by this column.
Is there anyway I can get around this? |
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 01/16/2012 : 06:50:11
|
Please think about what GROUP BY means and then read your own post.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47189 Posts |
Posted - 01/16/2012 : 06:50:52
|
didnt understand the significance of including TakeOff in orderby if you're not grouping by it. there will be obviously more than one value of TakeOff in each group so in such case how do you determine which value needs to be used for sorting?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Frosty615
Starting Member
13 Posts |
Posted - 01/16/2012 : 07:51:36
|
Ok, perhaps I should explain a little more about the results that are returned.....
As an example
If I don't fly for 31 days then I have to do a check flight with one of our instructors to ensure I am safe enough to fly.
I do this and then fly with others and for these purposes this procedure is returning the correct results
However when I go to enter these into my flying log book (for which we do not need the take off/landing times but can be grouped but all the bits in the group by clause) the result set may not be in the correct order, so the initial check flight doesn't appear in the list as the first result.
I can't include the TakeOff time in current procedure as the results that are returned do not then add the duration or the number of flights i.e they will just return each individual flight.
I hope this helps? |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 01/16/2012 : 08:10:02
|
As Fred and Visakh pointed out, all the columns have to be either listed in the group by clause, or has to be within an aggregate function. I didn't quite understand the business logic you are trying to implement, but would ordering by the min value of TakeOff help? I am thinking this:ORDER BY
MIN(els.TakeOff ),
Dutydate Or may be it is the MAX(els.TakeOff) that you need to sort by? |
|
|
|
Frosty615
Starting Member
13 Posts |
Posted - 01/16/2012 : 08:22:36
|
PERFECT! That seems to have worked as I wanted it too, will test on a copy of our live data and hopefully that will return the results as I'd like them.
Simple, but had me confused! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47189 Posts |
Posted - 01/16/2012 : 10:07:31
|
Hopefully that works for you!
If not, please post typical data and show us how TakeOff values exists for a group and then explain how you want sort using it
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Frosty615
Starting Member
13 Posts |
Posted - 01/16/2012 : 11:00:00
|
I've had a look and it seems to be working just fine.
If it's not working as it should I will post again but that may take a couple of weeks to get some proper live data flowing through the system.
Thanks to all for your help. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47189 Posts |
Posted - 01/16/2012 : 11:03:16
|
no probs
you're welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Order by query/aggregate functions
