Calculating Numerator and Denominator
-
Good evening,
I have a rather peculiar issue while trying to calculate Numerator and Denominator for a Final Temporary Table I have built (using a couple of Intermediary Temporary Tables along the way).
ATTEMPT 1
The following two lines were my initial attempt (on the Final Temp):
Cast(SUM(CASE When <Conditions> Then 1 Else 0 End) as varchar) AS 'Numerator',
Count(*) AS 'Denominator'
While the Numerator was spot-on, the Denominator contained woefully small figures.
ATTEMPT 2
So next, I calculated the denominator creating an additional LEFT JOIN directly on the Source table in the database (which is what Final Temp is actually based on anyway). There is no filtering going on in the intermediary tables from the first to Final Temp – just a series of LEFT JOINs with other Source tables.
LEFT JOIN (Select….Count(*) As 'Denominator' from <Source Table>)
This time the Count(*) from this subquery provided a vastly larger – and also incorrect – Denominator:
ATTEMPT 3
I finally tried an additional LEFT JOIN of the Final Temp with itself e.g.
LEFT JOIN (Select….Count(*) As 'Denominator' from <Final Temp >)
This time, Both Numerator and Denominator were vastly inflated!
The correct Numerator should be 40-something (which is achieved in both Attempts 1 & 2 during the full run of the script), but the Denominator has been incorrect in all three attempts and needs to be around 1700. In fact, I do achieve around 1700 when I independently run either of the two subqueries comprising both additional LEFT JOINS of Attempts 2 & 3 in a separate scripts:
Select….Count(*) As 'Denominator' from <Source Table>
Select….Count(*) As 'Denominator' from <Temporary Table >
I would appreciate insight in to what is happening.
-
It's always nice to provide usable object models (ddl) with sample data and the actual queries, isolation levels, ....
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
There is no way for us to help you with the minimal information that you have given. There is likely a problem with your JOIN conditions resulting in too few/many rows being returned, but you haven't provided any join conditions.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply