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.
Author |
Topic |
kt
Yak Posting Veteran
88 Posts |
Posted - 2013-08-05 : 11:35:57
|
Hi,This query gave me the results below. I only want the result to with quanity only meaning no 0 from the results. Can you pls show me how?Thanksselect ISNULL((SELECT SUM(del_qty) FROM tbl_delcty WITH(NOLOCK) WHERE RIGHT(saty, CASE WHEN LEN(saty)>=5 THEN LEN(saty)-5 ELSE 0 END) = ct.cty AND ctr = 2 GROUP BY RIGHT(saty,CASE WHEN LEN(saty)>=5 THEN LEN(saty)-5 ELSE 0 END)),0) + ISNULL((SELECT ISNULL(SUM(del_qty),0) FROM tbl_crt WITH(NOLOCK) WHERE cty= ct.cty AND ctr = 2 GROUP BY cty),0) AS devqty FROM tbl_cth ct with (NoLock) WHERE gt = '6'devqty======0002475 |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-08-05 : 11:43:06
|
You can add to the WHERE clause at the end. SELECT ISNULL(( SELECT SUM(del_qty) FROM tbl_delcty WITH ( NOLOCK ) WHERE RIGHT(saty, CASE WHEN LEN(saty) >= 5 THEN LEN(saty) - 5 ELSE 0 END) = ct.cty AND ctr = 2 GROUP BY RIGHT(saty, CASE WHEN LEN(saty) >= 5 THEN LEN(saty) - 5 ELSE 0 END) ), 0) + ISNULL(( SELECT ISNULL(SUM(del_qty), 0) FROM tbl_crt WITH ( NOLOCK ) WHERE cty = ct.cty AND ctr = 2 GROUP BY cty ), 0) AS devqtyFROM tbl_cth ct WITH ( NOLOCK )WHERE gt = '6' AND devqty <> 0 An additional comment: there may be opportunities to improve your code (for better performance, tighter logic, ease of understanding etc.), but I couldn't gleam enough info to suggest something from the query you posted. |
|
|
kt
Yak Posting Veteran
88 Posts |
Posted - 2013-08-05 : 11:54:32
|
alreay tried that but got error for Invalid column name devqty. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-08-05 : 12:06:03
|
quote: Originally posted by kt alreay tried that but got error for Invalid column name devqty.
Of course! silly me!!SELECT * FROM (SELECT ISNULL(( SELECT SUM(del_qty) FROM tbl_delcty WITH ( NOLOCK ) WHERE RIGHT(saty, CASE WHEN LEN(saty) >= 5 THEN LEN(saty) - 5 ELSE 0 END) = ct.cty AND ctr = 2 GROUP BY RIGHT(saty, CASE WHEN LEN(saty) >= 5 THEN LEN(saty) - 5 ELSE 0 END) ), 0) + ISNULL(( SELECT ISNULL(SUM(del_qty), 0) FROM tbl_crt WITH ( NOLOCK ) WHERE cty = ct.cty AND ctr = 2 GROUP BY cty ), 0) AS devqtyFROM tbl_cth ct WITH ( NOLOCK )WHERE gt = '6' )s WHERE devqty <> 0 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-08-05 : 12:19:32
|
Try this oneSELECT ISNULL(f.qty, 0) + ISNULL(s.qty, 0) AS devqtyFROM dbo.tbl_cth AS ct WITH (NOLOCK)LEFT JOIN ( SELECT SUBSTRING(saty, 6, LEN(saty)) AS cty, SUM(del_qty) AS qty FROM dbo.tbl_delcty WITH (NOLOCK) WHERE ctr = 2 GROUP BY SUBSTRING(saty, 6, LEN(saty)) ) AS f ON f.cty = ct.ctyLEFT JOIN ( SELECT cty, SUM(del_qty) AS qty FROM dbo.tbl_crt WITH (NOLOCK) WHERE ctr = 2 GROUP BY cty ) AS s ON s.cty = ct.ctyWHERE ct.gt = '6' AND ISNULL(f.qty, 0) + ISNULL(s.qty, 0) <> 0; Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
kt
Yak Posting Veteran
88 Posts |
Posted - 2013-08-05 : 14:21:03
|
thanks for everyone |
|
|
|
|
|
|
|