May 1, 2012 at 7:58 am
Hello,
I have the below query where I would like to return the highest value for a booking
SELECT MAX(dbo.BookedFinance.GrossProfit) AS GP,
dbo.BookedFinance.FinanceID
FROM dbo.BookedExtrasList INNER JOIN
dbo.BookingTransaction ON dbo.BookedExtrasList.BookingTransactionID = dbo.BookingTransaction.BookingTransactionID INNER JOIN
dbo.BookedFinance ON dbo.BookedExtrasList.PackageFinanceID = dbo.BookedFinance.FinanceID OR
dbo.BookedExtrasList.NonPackageFinanceID = dbo.BookedFinance.FinanceID
WHERE (dbo.BookingTransaction.BookingRef = 'G538915') AND (dbo.BookedExtrasList.InvoiceCode = 'TU')
GROUP BYdbo.BookingTransaction.BookingTransactionID,
dbo.BookedFinance.FinanceID
That brings up this result
GP FinanceID
--------------------------------------- -----------
1897.00000 90404574
635.20000 90404575
0.00000 90411344
0.00000 90411345
0.00000 90725824
0.00000 90725825
0.00000 90734068
0.00000 90734069
0.00000 90835485
0.00000 90835486
0.00000 90841311
0.00000 90841312
I only want to return the the value £1897.00 with it's corresponding FinanceID and I'm sure sure how I go about this.
The tables are structured as follows
CREATE TABLE Bookingtransaction
(
Bookingtransactionid int,
Bookingref varchar(10),
bookingdate datetime
)
CREATE TABLE BookedExtraslist
(
BookedExtrasListID int,
BookingTransactionID int,
InvoiceCodevarchar (20),
Descriptionvarchar (100),
SupplierCode varchar (100),
CreatedDatedatetime,
PackageFinanceID int,
NonPackageFinanceID int,
Pax int
)
CREATE TABLE BookedFinance
(
FinanceID int,
BookingTransactionID int,
ComponentID smallint,
IsPackage bit,
GrossTurnover decimal (15,5),
GrossProfit decimal (15,5),
DropNett decimal (15,5),
HiddenDropNett decimal (15,5),
AgentCommission decimal (15,5),
Incentive decimal(15,5),
Overrides decimal (15,5),
CancellationCharges decimal(15,5),
AdministrationCharges decimal (15,5),
APC decimal (15,5),
AFI decimal (15,5),
ExchangeID smallint,
ExchangeRate decimal(15,5),
CostOfSale decimal(15,5),
NettTurnover decimal(15,5),
GrossProfitPercentage decimal(15,5),
LocalNett decimal(15,5),
Pax smallint,
TicketedPax smallint,
ParentID int,
NumberOf int,
IsCancelled bit,
Currency char,
CurrencyRate decimal(15,5),
CurrencyCost decimal(15,5)
)
Any ideas?
TIA
May 1, 2012 at 8:05 am
couple of ways
SELECT TOP 1 MAX(dbo.BookedFinance.GrossProfit) AS GP,
dbo.BookedFinance.FinanceID
FROM dbo.BookedExtrasList INNER JOIN
dbo.BookingTransaction ON dbo.BookedExtrasList.BookingTransactionID = dbo.BookingTransaction.BookingTransactionID INNER JOIN
dbo.BookedFinance ON dbo.BookedExtrasList.PackageFinanceID = dbo.BookedFinance.FinanceID OR
dbo.BookedExtrasList.NonPackageFinanceID = dbo.BookedFinance.FinanceID
WHERE (dbo.BookingTransaction.BookingRef = 'G538915') AND (dbo.BookedExtrasList.InvoiceCode = 'TU')
GROUP BYdbo.BookingTransaction.BookingTransactionID,
dbo.BookedFinance.FinanceID
WITH CTE AS
(
SELECT ROW_NUMBER() OVER ( MAX(dbo.BookedFinance.GrossProfit) DESC) AS RowNum,
MAX(dbo.BookedFinance.GrossProfit) AS GP,
dbo.BookedFinance.FinanceID
FROM dbo.BookedExtrasList INNER JOIN
dbo.BookingTransaction ON dbo.BookedExtrasList.BookingTransactionID = dbo.BookingTransaction.BookingTransactionID INNER JOIN
dbo.BookedFinance ON dbo.BookedExtrasList.PackageFinanceID = dbo.BookedFinance.FinanceID OR
dbo.BookedExtrasList.NonPackageFinanceID = dbo.BookedFinance.FinanceID
WHERE (dbo.BookingTransaction.BookingRef = 'G538915') AND (dbo.BookedExtrasList.InvoiceCode = 'TU')
GROUP BYdbo.BookingTransaction.BookingTransactionID,
dbo.BookedFinance.FinanceID
)
SELECT GP, FinanceID FROM CTE WHERE RowNum = 1
May 1, 2012 at 8:08 am
Once again you show me the way with very simple stuff.
Thanks you kindly Sir!
May 1, 2012 at 8:37 am
anthony.green (5/1/2012)
couple of ways
SELECT TOP 1 MAX(dbo.BookedFinance.GrossProfit) AS GP,
dbo.BookedFinance.FinanceID
FROM dbo.BookedExtrasList INNER JOIN
dbo.BookingTransaction ON dbo.BookedExtrasList.BookingTransactionID = dbo.BookingTransaction.BookingTransactionID INNER JOIN
dbo.BookedFinance ON dbo.BookedExtrasList.PackageFinanceID = dbo.BookedFinance.FinanceID OR
dbo.BookedExtrasList.NonPackageFinanceID = dbo.BookedFinance.FinanceID
WHERE (dbo.BookingTransaction.BookingRef = 'G538915') AND (dbo.BookedExtrasList.InvoiceCode = 'TU')
GROUP BYdbo.BookingTransaction.BookingTransactionID,
dbo.BookedFinance.FinanceID
If you use this approach make sure you include an ORDER BY
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 1, 2012 at 8:51 am
Sean Lange (5/1/2012)
anthony.green (5/1/2012)
couple of ways
SELECT TOP 1 MAX(dbo.BookedFinance.GrossProfit) AS GP,
dbo.BookedFinance.FinanceID
FROM dbo.BookedExtrasList INNER JOIN
dbo.BookingTransaction ON dbo.BookedExtrasList.BookingTransactionID = dbo.BookingTransaction.BookingTransactionID INNER JOIN
dbo.BookedFinance ON dbo.BookedExtrasList.PackageFinanceID = dbo.BookedFinance.FinanceID OR
dbo.BookedExtrasList.NonPackageFinanceID = dbo.BookedFinance.FinanceID
WHERE (dbo.BookingTransaction.BookingRef = 'G538915') AND (dbo.BookedExtrasList.InvoiceCode = 'TU')
GROUP BYdbo.BookingTransaction.BookingTransactionID,
dbo.BookedFinance.FinanceID
If you use this approach make sure you include an ORDER BY
Doh.... forgot about that, result set is non-deterministic so have to force it in the correct order
May 1, 2012 at 10:01 am
Based on the above data I'm trying to insert that query as a subquery, I thought I was going about it the right way with this:
SELECTdbo.BookingTransaction.BookingRef
, (
SELECT TOP 1
MAX(dbo.Bookedfinance.GrossProfit), dbo.bookedextraslist.description
FROMdbo.BookedFinance
INNER JOIN dbo.BookedExtrasList ON dbo.Bookedfinance.FinanceID = dbo.BookedExtrasList.PackageFinanceID
GROUP BY
dbo.BookedExtrasList.Description
) AS Description
, dbo.Bookings.CancelledDate
, dbo.BookedFlightLegs.DepDate
, dbo.BookedFlightLegs.AirlineCode
, dbo.BookedFlightLegs.FlightNumber
, dbo.BookedFlightLegs.DepAirportID
, dbo.BookedFlightLegs.ArrAirportID
, dbo.BookedFlightLegs.ArrDate
, dbo.BookedExtrasList.SupplierCode
FROM dbo.BookedExtrasList INNER JOIN
dbo.BookingTransaction WITH (NOLOCK) ON dbo.BookedExtrasList.BookingTransactionID = dbo.BookingTransaction.BookingTransactionID INNER JOIN
dbo.Bookings WITH (NOLOCK) ON dbo.BookingTransaction.BookingRef = dbo.Bookings.BookingRef INNER JOIN
dbo.BookedFlightLegs WITH (NOLOCK) ON dbo.BookingTransaction.BookingTransactionID = dbo.BookedFlightLegs.BookingTransactionID
WHERE
(dbo.BookedExtrasList.InvoiceCode = 'TU')
AND (dbo.BookingTransaction.TransactionDate BETWEEN @sDate and @eDate)
AND (dbo.BookedFlightLegs.AirlineCode <> 'BSP')
AND (dbo.BookingTransaction.Pax <> 0)
GROUP BYdbo.BookingTransaction.BookingRef
, dbo.BookedExtrasList.Description
, dbo.Bookings.CancelledDate
, dbo.BookedFlightLegs.DepDate
, dbo.BookedFlightLegs.FlightNumber
, dbo.BookedFlightLegs.AirlineCode
, dbo.BookedFlightLegs.DepAirportID
, dbo.BookedFlightLegs.ArrAirportID
, dbo.BookedFlightLegs.ArrDate
, dbo.BookedExtrasList.SupplierCode
Alas, I was wrong as after a little research you can only have one output column in a subquery, but that's about it, the rest of the documentation I was mangled my brain - I think i've had too much coffee.
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS
My aim is to return the column Description where the finance table contains the largest value, any ideas?
May 1, 2012 at 10:11 am
Something like this?
SELECTdbo.BookingTransaction.BookingRef
, x.MaxDescription
, dbo.Bookings.CancelledDate
, dbo.BookedFlightLegs.DepDate
, dbo.BookedFlightLegs.AirlineCode
, dbo.BookedFlightLegs.FlightNumber
, dbo.BookedFlightLegs.DepAirportID
, dbo.BookedFlightLegs.ArrAirportID
, dbo.BookedFlightLegs.ArrDate
, dbo.BookedExtrasList.SupplierCode
FROM dbo.BookedExtrasList INNER JOIN
dbo.BookingTransaction WITH (NOLOCK) ON dbo.BookedExtrasList.BookingTransactionID = dbo.BookingTransaction.BookingTransactionID INNER JOIN
dbo.Bookings WITH (NOLOCK) ON dbo.BookingTransaction.BookingRef = dbo.Bookings.BookingRef INNER JOIN
dbo.BookedFlightLegs WITH (NOLOCK) ON dbo.BookingTransaction.BookingTransactionID = dbo.BookedFlightLegs.BookingTransactionID
join
(
SELECT TOP 1 MAX(dbo.Bookedfinance.GrossProfit) as MaxGrossProfit, dbo.bookedextraslist.description as MaxDescription, BookedExtrasList.PackageFinanceID
FROM dbo.BookedFinance
INNER JOIN dbo.BookedExtrasList ON dbo.Bookedfinance.FinanceID = dbo.BookedExtrasList.PackageFinanceID
GROUP BY
dbo.BookedExtrasList.Description, BookedExtrasList.PackageFinanceID
) x on dbo.Bookedfinance.FinanceID = dbo.BookedExtrasList.PackageFinanceID
WHERE
(dbo.BookedExtrasList.InvoiceCode = 'TU')
AND (dbo.BookingTransaction.TransactionDate BETWEEN @sDate and @eDate)
AND (dbo.BookedFlightLegs.AirlineCode <> 'BSP')
AND (dbo.BookingTransaction.Pax <> 0)
GROUP BYdbo.BookingTransaction.BookingRef
, dbo.BookedExtrasList.Description
, dbo.Bookings.CancelledDate
, dbo.BookedFlightLegs.DepDate
, dbo.BookedFlightLegs.FlightNumber
, dbo.BookedFlightLegs.AirlineCode
, dbo.BookedFlightLegs.DepAirportID
, dbo.BookedFlightLegs.ArrAirportID
, dbo.BookedFlightLegs.ArrDate
, dbo.BookedExtrasList.SupplierCode
As a another question, why all the nolock hints? Do you know what they do and what they can introduce?
http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 1, 2012 at 10:29 am
Thanks, I'll give it a try in the morning, just off home now.
I'm pretty new to T-SQL, having previously only used Access for SQL but my understanding of the nolock hint was the query will run regardless of anything else happening on the DB at that time, but it may also result in slower performance if others are querying the DB- is that correct?
I've only recently looked it up as I'm in a new role and a lot of the existing stored procedures use the nolock hint. I've gone along with using it myself as this is a QA server and other developers are hammering it too.
Do you advise against nolock?
May 1, 2012 at 10:31 am
Qutip (5/1/2012)
Do you advise against nolock?
Strongly. It is not a go faster switch. Rather it is a 'I'm happy with slightly incorrect results' switch.
See - http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 1, 2012 at 10:33 am
I would definitely advise against nolock. Read the article I referred to, it explains it far better than I could. You can end up with missing and/or duplicate data along with a host of other issues.
--EDIT--
I was slow in typing my response. Thanks for the link Gail, I have not seen that one before.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 2, 2012 at 3:58 am
Thanks, I've read through both of those links and will certainly tread carefully in regards to table hints.
I think I've possibly been going about my task the wrong way.
I'll use the below data to try and explain what result I'm trying to get.
Here's one result from the main query:
BookingRef Description DepDate AirlineCode FlightNumber ArrDate
---------- ---------------------------------------------------------------------------------------------------- ----------------------- ----------- ------------ -----------------------
G538915 18 Day New Zealand Advent 2012-11-19 00:00:00.000 BA 0009 2012-11-20 14:20:00.000
G538915 3 Day Red Centre Sights & 2012-11-19 00:00:00.000 BA 0009 2013-01-17 05:25:00.000
Here's the data I want to use to ensure I get only return one value per bookingref:
BookingRef Description GrossTurnover
-------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------
G538915 3 Day Red Centre Sights & 1588.00000
G538915 18 Day New Zealand Advent 8090.00000
Based on the second query I want the first query to return only one result based on the MAX value of gross turnover.
I woke this morning full of joy thinking I had nailed based on your suggestions yesterday and I figured out I'm barking up the wrong tree.
TIA
May 2, 2012 at 7:24 am
Not totally sure I get what your question is at this point. Can you try to explain it clearly?
I might be able to nudge you to an answer but I have a feeling we are close to the point where I am going to need ddl, sample data and desired output.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 2, 2012 at 10:38 am
Ultimately my aim was to only select the row that is joined to the highest value..
I think I'm on the right track with this with the text in bold doing selecting the correct row, I think - still need to do a bit more testing and maybe a bit more shuffling about about as it took 8 mins to return 99 rows :doze:
DECLARE @sDate DATETIME
, @eDate DATETIME
SET @sDate = CONVERT(DATETIME, '01/02/2012', 103)
SET @eDate = CONVERT(DATETIME, '02/02/2012', 103)
SELECTDISTINCTdbo.BookingTransaction.BookingRef
, dbo.BookedExtrasList.Description
, dbo.Bookings.CancelledDate
, dbo.BookedFlightLegs.DepDate
, dbo.BookedFlightLegs.AirlineCode
, dbo.BookedFlightLegs.FlightNumber
, dbo.BookedFlightLegs.DepAirportID
, dbo.BookedFlightLegs.ArrAirportID
, dbo.BookedFlightLegs.ArrDate
, dbo.BookedExtrasList.SupplierCode
FROM dbo.BookedExtrasList INNER JOIN
dbo.BookingTransaction ON dbo.BookedExtrasList.BookingTransactionID = dbo.BookingTransaction.BookingTransactionID INNER JOIN
dbo.Bookings ON dbo.BookingTransaction.BookingRef = dbo.Bookings.BookingRef INNER JOIN
dbo.BookedFlightLegs ON dbo.BookingTransaction.BookingTransactionID = dbo.BookedFlightLegs.BookingTransactionID
INNER JOIN
(SELECT
MAXI.grossprofit,
MAXI.BookingRef,
MAXI.FinanceID
FROM
(
SELECT
bookedfinance.grossprofit,
BookingTransaction.BookingRef,
bookedfinance.FinanceID, RANK() OVER(PARTITION BY Bookingref ORDER BY bookedfinance.GrossProfit DESC) AS RowNum
FROM
dbo.BookedExtrasList INNER JOIN
dbo.BookingTransaction ON dbo.BookedExtrasList.BookingTransactionID = dbo.BookingTransaction.BookingTransactionID INNER JOIN
dbo.BookedFinance ON dbo.BookedExtrasList.PackageFinanceID = dbo.BookedFinance.FinanceID
--WHERE BookingRef = 'G538915'
WHERE (dbo.BookingTransaction.TransactionDate BETWEEN @sDate and @eDate)
GROUP BY dbo.BookingTransaction.BookingRef,
dbo.BookedFinance.GrossProfit,
dbo.BookedFinance.FinanceID
) AS MAXI
WHERE RowNum = 1)
AS fin ON Bookedextraslist.PackageFinanceID = fin.FinanceID --*/
WHERE
(dbo.BookedExtrasList.InvoiceCode = 'TU')
AND (dbo.BookingTransaction.TransactionDate BETWEEN @sDate and @eDate)
AND (dbo.BookedFlightLegs.AirlineCode <> 'BSP')
AND (dbo.BookingTransaction.Pax <> 0)
AND (dbo.Bookings.CancelledDate IS NULL)
GROUP BYdbo.BookingTransaction.BookingRef
, dbo.BookedExtrasList.Description
, dbo.Bookings.CancelledDate
, dbo.BookedFlightLegs.DepDate
, dbo.BookedFlightLegs.FlightNumber
, dbo.BookedFlightLegs.AirlineCode
, dbo.BookedFlightLegs.DepAirportID
, dbo.BookedFlightLegs.ArrAirportID
, dbo.BookedFlightLegs.ArrDate
, dbo.BookedExtrasList.SupplierCode
That took me all day :-/
May 2, 2012 at 10:41 am
Is this at least getting the correct results? We can tweak the performance but totally pointless unless the result set is correct.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 2, 2012 at 11:52 am
I did get the correct result on the one isolated reference. I will be testing again in the morning on a larger scale.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply