Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Why different result from theese 2 queries

Author  Topic 

magmo
Aged Yak Warrior

558 Posts

Posted - 2014-07-06 : 06:29:33
Hi

I have 2 queries that give 2 different result and I dont know why, could someone please tell me why Query 2 give me a different result?



Query 1, this give me Qty = 55

SELECT SUM(NumberOfCards) AS Qty, CardFormat
FROM dbo.tbl_Cards
WHERE (DateAdded BETWEEN CONVERT(DATETIME, '2014-07-06 11:50:00', 102) AND CONVERT(DATETIME, '2014-07-06 12:05:00', 102)) AND (IsFetched = 1) AND (Duplicate = 0)
GROUP BY CardFormat


Query 2, this give me Qty 56 (QtyPaidCards + QtyFreeCards)

SELECT CardFormat,

CONVERT(VarChar, DATEADD(dd,DATEDIFF(dd,0,DateAdded),0),102) AS Dag,

ISNULL(SUM(CASE WHEN NULLIF(TransactionID, '0') IS NOT NULL THEN NumberOfCards END), 0)AS QtyPaidCards,
ISNULL(SUM(CASE WHEN TransactionID IN ('0', '') THEN NumberOfCards END), 0) AS QtyFreeCards
FROM tbl_Cards
WHERE IsFetched = 1 AND Duplicate = 0
AND (DateAdded BETWEEN CONVERT(DATETIME, '2014-07-06 11:50:00', 102) AND CONVERT(DATETIME, '2014-07-06 12:05:00', 102))
AND CardFormat = 'A5'

GROUP BY CardFormat, DATEADD(dd,DATEDIFF(dd,0,DateAdded),0)
ORDER BY DATEADD(dd,DATEDIFF(dd,0,DateAdded),0), CardFormat

magmo
Aged Yak Warrior

558 Posts

Posted - 2014-07-06 : 08:59:58
I found the cause, sometimes TransactionID was empty value and that was not handled...
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-07-06 : 09:58:46
SUM(CASE WHEN TransactionID > '0' THEN NumberOfCards ELSE 0 END) AS QtyPaidCards,
SUM(CASE WHEN TransactionID > '0' THEN 0 ELSE NumberOfCards END) AS QtyFreeCards

Takes care of empty strings, NULL and zero.


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

magmo
Aged Yak Warrior

558 Posts

Posted - 2014-07-06 : 14:12:01
Thanks

It works, but I'm not really sure how this part works...


SUM(CASE WHEN TransactionID > '0' THEN 0 ELSE NumberOfCards END) AS QtyFreeCards
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-07-06 : 15:56:24
Empty space ('') is not greater than ('0') when comparing string.
Also ('0') itself is not greater than another zero ('0').
And NULLs cannot be compared to anything except IS NULL.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
   

- Advertisement -