Query Help
-
I have been look at this all day and need some other eyes, when I run the query below I am getting three lines when my goal is one total and cannot find a way to remove the ones I do not need, which are the nulls. It does give me the total 5979.30 but the other two lines are not needed.
It's the case stmt for the amount that is giving me the issue. I have tried putting it the case below as a nested case and it doesn't work either, the query I am writing is much bigger and this is the only issue I am having
Nested case stmt
(case when gn.glacctid = 25 then t.amex end ) +
(case when gn.acctid = 26 then t.vmc end) +
case when gn.acctid = 22 the t.wright end) as AmountQuery
Select distinct
t.GLStoreNum,
case when gn.GLAcctID = 25 then g.GLAcctNum
when gn.GLAcctID = 26 then g.GLAcctNum
when gn.GLAcctID = 22 then t.Wright end as accoutNum,case when gn.GLAcctID in (25,26,22) then t.Amex_ + t.VMC_ + t.Wright end as amount
from tblGLAccountNums gn left join
tblGLAccounts g on gn.GLAcctID = g.GLAcctID inner join
#tmpX1 t on g.GLStoreNum = t.GLStoreNum
MCSE SQL Server 2012\2014\2016
-
What exactly is the problem? Your query is returning rows that have not a 25 in GLAcctId so that field is NULL. Looking at case statement posted not the one in the query those don't have default values and conditions are mutually exclusive so yeah that will always return NULL.
Did you mean for it to be,
(case when gn.glacctid = 25 then t.amex WHEN gn.acctid = 26 then t.vmc WHEN gn.acctid = 22 THEN t.wright end) as Amount -
Your statement:
Nested case stmt
(case when gn.glacctid = 25 then t.amex end ) +
(case when gn.acctid = 26 then t.vmc end) +
case when gn.acctid = 22 the t.wright end) as AmountIs not the same logic you show in your SQL for calculating Amount..
I don't think you are taking into account what happens when gn.GLAcctID is not 25 or 26, thus the NULL. And what if one of your amounts are NULL, then you get your second result I'm assuming.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret. -
updating original query and post
MCSE SQL Server 2012\2014\2016
- below86 - Friday, October 13, 2017 1:44 PM
when I use the nested stmt I get;
MCSE SQL Server 2012\2014\2016
- ZZartin - Friday, October 13, 2017 1:31 PM
I updated my original post, it's been a long day and I didn't write it very clearly. my apologies.
MCSE SQL Server 2012\2014\2016
-
Apply some formatting and thing become easier to see...
SELECT DISTINCT
t.GLStoreNum,
accoutNum = CASE
WHEN gn.GLAcctID = 25 THEN g.GLAcctNum
WHEN gn.GLAcctID = 26 THEN g.GLAcctNum
WHEN gn.GLAcctID = 22 THEN t.Wright
END,
amount = CASE WHEN gn.GLAcctID IN (25, 26, 22) THEN t.Amex_ + t.VMC_ + t.Wright END
FROM
tblGLAccountNums gn
LEFT JOIN tblGLAccounts g
ON gn.GLAcctID = g.GLAcctID
INNER JOIN #tmpX1 t
ON g.GLStoreNum = t.GLStoreNum;The 1st thing that jumps out ... You have a LEFT JOIN from tblGLAccountNums to tblGLAccounts, but then you have an INNER JOIN from tblGLAccounts to #tmpX1 ... Effectively turning the LEFT JOIN into an INNER JOIN. So, if you're not seeing the number of rows you're expecting, that's your culprit.
- lkennedy76 - Friday, October 13, 2017 1:14 PMI have been look at this all day and need some other eyes, when I run the query below I am getting three lines when my goal is one total and cannot find a way to remove the ones I do not need, which are the nulls. It does give me the total 5979.30 but the other two lines are not needed.
It's the case stmt for the amount that is giving me the issue. I have tried putting it the case below as a nested case and it doesn't work either, the query I am writing is much bigger and this is the only issue I am having
Nested case stmt
(case when gn.glacctid = 25 then t.amex end ) +
(case when gn.acctid = 26 then t.vmc end) +
case when gn.acctid = 22 the t.wright end) as AmountQuery
Select distinct
t.GLStoreNum,
case when gn.GLAcctID = 25 then g.GLAcctNum
when gn.GLAcctID = 26 then g.GLAcctNum
when gn.GLAcctID = 22 then t.Wright end as accoutNum,case when gn.GLAcctID in (25,26,22) then t.Amex_ + t.VMC_ + t.Wright end as amount
from tblGLAccountNums gn left join
tblGLAccounts g on gn.GLAcctID = g.GLAcctID inner join
#tmpX1 t on g.GLStoreNum = t.GLStoreNum
I m trying to guess this, hope this gives u an idea where to make the change
for 2nd row the value of amount is 5279.299 which states that none of the "t.Amex_ , t.VMC_ , t.Wright" is NULL. So it means that CASE stmt for accountnum matched either
when gn.GLAcctID = 25 then g.GLAcctNum
when gn.GLAcctID = 26 then g.GLAcctNum
So, considering that for gn.GLAcctID = 25 oR gn.GLAcctID = 26 the g.GLAcctNum IS NULL, means that
the LEFT JOIN (tblGLAccountNums gn left jointblGLAccounts g on gn.GLAcctID = g.GLAcctID) for gn.GLAcctID = 25,26 there is no matching g.GLAcctID = 25,26 hence causing g.GLAcctnum to be NULL.
Try to shift the temp table to the Left most table, coz the join is eventually converting into INNER and fetching resords from temp only.
First solve the problem then write the code !
- Jason A. Long - Saturday, October 14, 2017 7:34 PM
the join was definitely part of the issue, I didn't even see that, thank you. I guess that's what happens when you stare at code for to long. I put in left joins and the data comes across now but it puts in three rows one for each GLacctID when I only need one row all three totaled up into one total.
MCSE SQL Server 2012\2014\2016
-
I'm not sure why I didn't spot this yesterday, but the fact that, in the SELECT list, you're getting [GLStoreNum] from #tmpX1... and... It wasn't returning any nulls.
That's a big clue!... (I'm not 100% sure about what you're going after, but I suspect the following).1) you've written the query backwards. #tmpX1 should be the leftmost table and tblGLAccountNums should be the rightmost table.
2) you don't need or want LEFT JOINs.
3) #tmpX1 rows with a [GLStoreNum] value of '001'.
Here is an alternate version of your query now that I've had a better look at it... Keep in mind this is based mostly on what I "suspect" you're trying to do. Note: I added all of the join columns to the where clause to make it easier to visualize the actual relationships at the actual joins. (Tip-O'-the-Day: this makes it MUCH easier to spot the pain points when you're getting unexpected results.)SELECT --DISTINCT shouldn't need distinct at this point.
t.GLStoreNum,
gn.GLAcctID, -- for dev info only. remove or
g.GLAcctID, -- comment out before deployment.
g.GLStoreNum, --
accoutNum = CASE -- (what I suspect is correct... tblGLAccounts is the test and tblGLAccountNums provides the "true" value)
WHEN g.GLAcctID = 25 THEN gn.GLAcctNum
WHEN g.GLAcctID = 26 THEN gn.GLAcctNum
WHEN g.GLAcctID = 22 THEN t.Wright
END,
--accoutNum = CASE -- (original version)
-- WHEN gn.GLAcctID = 25 THEN g.GLAcctNum
-- WHEN gn.GLAcctID = 26 THEN g.GLAcctNum
-- WHEN gn.GLAcctID = 22 THEN t.Wright
--END,
amount = CASE -- changed from gn to g here too...
WHEN g.GLAcctID IN (25, 26, 22)
THEN t.Amex_ + t.VMC_ + t.Wright
END
FROM
#tmpX1 t
JOIN tblGLAccounts g -- toggle between left & inner to gauge the output (
ON t.GLStoreNum = g.GLStoreNum
LEFT JOIN tblGLAccountNums gn -- toggle between left & inner to gauge the output
ON g.GLAcctID = gn.GLAcctID -- (left join will be necessary for the 3rd "when"
AND gn.GLAcctID IN (25, 26); -- condition of accoutNum to have the possibility of being true true.HTH,
Jaon
- Jason A. Long - Sunday, October 15, 2017 1:41 PM
found the issue, group by
the joins where part of the issue as well.
thank you all for your helpMCSE SQL Server 2012\2014\2016
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply