December 31, 2013 at 2:52 am
Here's the very first query from your very first post, reformatted and with table aliases applied for readability:
SELECT
SUM(Amount=ABS((
SELECT Top 1 Amount + VATAmount
FROM FAAccKitty k1 WITH(NOLOCK)
Where k1.PlaceID = w.PlaceID
AND k1.TransType = 'PZ'
)))
FROM FAAccWinners w
INNER JOIN Reservations r WITH(NOLOCK)
ON r.PlaceID = w.PlaceID
INNER JOIN Visitor v WITH(NOLOCK)
ON r.EMail = v.EMail
WHERE w.Comments_Approved = 'Yes'
AND v.VisitorID = 274
AND w.PlaceID IN (
SELECT PlaceID
FROM FAAccKitty k2 WITH(NOLOCK)
Where k2.PlaceID = w.PlaceID
AND k2.TransType ='PZ'
AND Year(k2.TransDate) > 1900
AND k2.Approved = 'Yes'
)
AND w.PlaceID NOT IN (
SELECT PlaceID
FROM FAAccKitty k3 WITH(NOLOCK)
Where k3.PlaceID = w.PlaceID
AND k3.TransType = 'PC'
)
There are several reasons why this is unlikely to return the result you are expecting to see. For instance, for each row returned by the main query, the subquery in the SELECT will be run. It has no ORDER BY to correspond to TOP so you don't know which row will be returned - and even if you know now, it could change with the addition or deletion of data from the table, or even by the addition of an index on a seemingly unrelated set of columns. The sum of a bunch of numbers you're not sure about is one number that you're fairly certain is unlikely to be correct.
You - and we - need to know more about your data. Sample scripts would help enormously (see "please read this" in my sig below), but there are shortcuts which might work to begin with.
Try this query:
-- test query 1
SELECT
w.PlaceID
FROM FAAccWinners w
INNER JOIN Reservations r WITH(NOLOCK)
ON r.PlaceID = w.PlaceID
INNER JOIN Visitor v WITH(NOLOCK)
ON r.EMail = v.EMail
WHERE w.Comments_Approved = 'Yes'
AND v.VisitorID = 274
It will return all of the PlaceID values in table FAAccWinners associated with VisitorID = 274. How many rows are returned? Is there only one value of PlaceID or several?
Then run this query:
-- test query 2
SELECT
k1.*
FROM FAAccWinners w
INNER JOIN Reservations r WITH(NOLOCK)
ON r.PlaceID = w.PlaceID
INNER JOIN Visitor v WITH(NOLOCK)
ON r.EMail = v.EMail
INNER JOIN FAAccKitty k1 WITH(NOLOCK)
ON k1.PlaceID = w.PlaceID
WHERE w.Comments_Approved = 'Yes'
AND v.VisitorID = 274
Now this query will tell you a ton of stuff:
How many rows in FAAccKitty per PlaceID
The distribution of the filter columns (TransType, TransDate, Approved) in FAAccKitty
Actual values of Amount and VATAmount.
You should be able to identify the rows you want to include in your aggregate, and sets (of common PlaceID) that you want to exclude.
You can derive from this the exact result you should obtain from any given value of VisitorID - which means you have numbers to match to whilst composing and testing your query.
Give it a whirl, see how you get on. It would be interesting for us to see the output of test query 2.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 31, 2013 at 3:07 am
Thank you I have got it working now with the CTE the ABS() function was missing.
I will however try to implement your other suggestions as they seem quite interesting
Thank you again
December 31, 2013 at 3:37 am
Can you post your solution please? It's closure for your thread, offers closure for those who have helped you, and may help others who stumble upon this thread whilst seeking answers to a similar problem.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 31, 2013 at 3:47 am
This is what worked for me. Thank you very much.
;WITH CTE AS
(SELECT Top 1 Amount+VATAmount AS TOTAL
FROM FAAccKitty WITH(NOLOCK)
inner JOIN FAAccWinners on FAAccKitty.PlaceID=FAAccWinners.PlaceID
INNER JOIN Reservations WITH(NOLOCK) ON (Reservations.PlaceID = FAAccWinners.PlaceID)
INNER JOIN Visitor WITH(NOLOCK) ON (Reservations.EMail = Visitor.EMail)
WHERE FAAccWinners.Comments_Approved='Yes'
AND FAAccWinners.PlaceID IN (SELECT PlaceID FROM FAAccKitty WITH(NOLOCK) Where FAAccKitty.PlaceID=FAAccWinners.PlaceID
AND FAAccKitty.TransType='PZ' AND Year(TransDate)>1900 AND FAAccKitty.Approved='Yes') AND FAAccWinners.PlaceID NOT IN
(SELECT PlaceID FROM FAAccKitty WITH(NOLOCK) Where FAAccKitty.PlaceID=FAAccWinners.PlaceID AND TransType='PC')
AND Visitor.VisitorID= 274
AND FAAccKitty.TransType='PZ'
)
SELECT SUM(ABS(TOTAL))
FROM CTE
December 31, 2013 at 4:13 am
You're welcome, thanks for posting the final query. If it works for you and generates the results you want, then that's good and you're probably done - but I'd recommend a little caution with it, if not some analysis and a bit of a rewrite. Coding by trial and error very often goes horribly wrong.
-- Since the CTE can only return one row, this query is exactly the same;
SELECT Top 1
ABS(Amount + VATAmount) AS TOTAL
FROM FAAccKitty k WITH(NOLOCK)
INNER JOIN FAAccWinners w
on k.PlaceID = w.PlaceID
INNER JOIN Reservations r WITH(NOLOCK)
ON r.PlaceID = w.PlaceID
INNER JOIN Visitor v WITH(NOLOCK)
ON r.EMail = v.EMail
WHERE w.Comments_Approved = 'Yes'
AND w.PlaceID IN (
SELECT PlaceID
FROM FAAccKitty WITH(NOLOCK)
Where FAAccKitty.PlaceID = w.PlaceID
AND FAAccKitty.TransType = 'PZ'
AND Year(TransDate) > 1900
AND FAAccKitty.Approved = 'Yes'
)
AND w.PlaceID NOT IN (
SELECT PlaceID
FROM FAAccKitty WITH(NOLOCK)
Where FAAccKitty.PlaceID = w.PlaceID
AND TransType='PC'
)
AND v.VisitorID = 274
AND k.TransType = 'PZ'
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 31, 2013 at 4:35 am
That is perfect thank you
The solution needs the SUM() function though for my requirements,
SELECT SUM(ABS(k.Amount + k.VATAmount)) AS TOTAL.
Love this site!
December 31, 2013 at 4:48 am
BlackIceAngel (12/31/2013)
That is perfect thank youThe solution needs the SUM() function though for my requirements,
SELECT SUM(ABS(k.Amount + k.VATAmount)) AS TOTAL.
Love this site!
So long as you are aware that SUM() has no effect whatsoever on a one-row result set and is therefore redundant, and that the result you get could change if someone adds or changes a few rows of data (or adds or changes an index) to any one of those tables - or if someone is in the process of changing some rows in one of those tables - then that's ok. The query has more faults than the centre court at Wimbledon when McEnroe was playing in the finals. It's up to you if you want it to work once, by accident, or always, by design.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 7 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply