asked
How to check a value in a column to return only data where exists in all rows?
I'm very confused to what I'm asking, I don't know how to do it: I need to check SumTrxnMon Column where the values in the column are equal to 0, on the basis of TID (*which can be multiple*), but need to return only rows where the data against all the TID are equal to 0, otherwise neglect. **SQL Statement** SELECT A.* FROM tblRPT_Spend A, tblMer_DeployORetrieveTerm B WHERE A.MID = B.MID AND A.TID = B.TID AND B.isDeployORetrieve = 1 AND A.SumTrxnMon = 0 AND YEAR(A.SpendFrom) = 2013 ORDER BY A.TID, A.GeneratedOn **Sample Data** MID, TID, MerchantName, CreationDate, FlatAmount, Percentage, CountTrxnMon, SumTrxnMon, SumCommMon, SpendFrom, SpendTo, GeneratedOn 505604000, 4513, Leisure Holidays - Bin Soughat, 2007-05-02 00:00:00.000, 0, 2.25, 0, 0, 0, 2013-01-01 00:00:00.000, 2013-01-31 00:00:00.000, 2013-02-01 00:00:00.000, NULL 505604000, 4513, Leisure Holidays - Bin Soughat, 2007-05-02 00:00:00.000, 0 2.25, 0, 0, 0, 2013-02-01 00:00:00.000, 2013-02-28 00:00:00.000, 2013-03-01 00:00:00.000, NULL 505604000, 4513, Leisure Holidays - Bin Soughat, 2007-05-02 00:00:00.000, 0, 2.25, 0, 123456, 0, 2013-03-01 00:00:00.000, 2013-03-31, 00:00:00.000, 2013-04-01 00:00:00.000, NULL
I'm confused. So you want to exclude from the results any value for SumTrxnMon that equals 0 or in a range from 0 to 10000? Then, logically, you want SumTrxnMon > 10000 and we're done. Is that right?
This site runs off of votes. Please mark all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that answer.
If I understand what you're saying, the WHERE clause with an equals criteria should do it. SELECT... FROM... WHERE TID = 0 AND.... That should absolutely filter so you only get that value. Even if that column exists in both tables, you only need to reference one of them because of the JOIN. And speaking of the JOIN, you're using ANSI89 syntax there. You should instead do this: SELECT... FROM tblRPT_Spend rs JOIN tblMer_DeployORetrieveTerm mdrt ON rs.TID = mdrt.TID WHERE... Especially when you get to OUTER JOIN in SQL Server 2012 you can no longer use the *= =* mechanisms of ANSI89. Oh, and I changed the aliases because 'a' and 'b' aliases are a really nasty paradigm that just leads to confusion as you get more and more tables.
Hi Grant Fritchey I cannot restrict the answer to TID, because I need to check the complete data, and regarding to the 2nd option I'm just joining the 2 tables to check some active status on TID, kindly check the Sample data I've posted, on the last row, the SumTrxMon value is changing. I need that if a row with the same TID contains value in column SumTrxnMon other than 0 or in range like 0 to 10000, the complete result having the specific TID (in my case of Sample Data **4513**) should not appear in the results.
Hi @eligiable , if I understand correctly, you want to retrieve all records that have the same TID and values greater than 10000. If so, just do what @Grant Frichey did in the first "where" clause, but this time use not equal "!=" to eliminate everything that is less than 10000.
Or just greater than. That way you avoid the statistics negating effects of the NOT in the not equals comparison.
I need that The Statement should check every row returned SUMTrxnMon BETWEEN 0 to n ... and if there is any value based on TID > than n Number the whole data against the TID needs to be discarded. I've figured out something, but no worth, check the last WHERE Clause: SELECT A.OutletID, A.MerchantID, A.MerchantName, A.MID, A.TID, B.Amount, A.CreationDate, A.Status, A.CountTrxnMon, A.SumTrxnMon, A.UserID, A.FullName, A.Month FROM ( SELECT B.OutletID, D.MerchantID, D.MerchantName, A.MID, A.TID, A.CreationDate, CASE WHEN B.isDeployORetrieve = 1 THEN 'Active' WHEN B.isDeployORetrieve = 2 THEN 'Retrieve' ELSE 'N/A' END AS Status, ISNULL(A.CountTrxnMon,0) AS CountTrxnMon, ISNULL(A.SumTrxnMon,0) AS SumTrxnMon, E.UserID, E.FullName, DATENAME(MONTH, A.SpendFrom) AS Month FROM dbo.tblRPT_Spend AS A INNER JOIN dbo.tblMer_DeployORetrieveTerm AS B ON A.MID = B.MID AND A.TID = B.TID INNER JOIN dbo.tblMer_Outlet AS C ON B.OutletID = C.OutletID LEFT OUTER JOIN dbo.tblGen_Merchants AS D ON C.MerchantID = D.MerchantID LEFT OUTER JOIN dbo.tblGen_Users AS E ON C.SignedByUserID = E.UserID WHERE (A.SpendFrom >= '2013-01-01') AND (A.SpendTo <= '2013-12-31')) AS A INNER JOIN ( SELECT OutletID, MID, TID, SUM(Amount) AS Amount FROM tblMer_OutletRental GROUP BY OutletID, MID, TID) AS B ON A.OutletID = B.OutletID AND A.MID = B.MID AND A.TID = B.TID WHERE 1=1 AND A.TID IN (SELECT TID FROM tblRPT_Spend WHERE SumTrxnMon BETWEEN 0 and 10000) AND A.TID NOT IN (SELECT TID FROM tblRPT_Spend WHERE SumTrxnMon NOT BETWEEN 0 AND 10000)
I need that The Statement should check every row returned SUMTrxnMon BETWEEN 0 to n ... and if there is any value based on TID > than n Number the whole data against the TID needs to be discarded. I've figured out something, but no worth, check the last WHERE Clause: SELECT A.OutletID, A.MerchantID, A.MerchantName, A.MID, A.TID, B.Amount, A.CreationDate, A.Status, A.CountTrxnMon, A.SumTrxnMon, A.UserID, A.FullName, A.Month FROM ( SELECT B.OutletID, D.MerchantID, D.MerchantName, A.MID, A.TID, A.CreationDate, CASE WHEN B.isDeployORetrieve = 1 THEN 'Active' WHEN B.isDeployORetrieve = 2 THEN 'Retrieve' ELSE 'N/A' END AS Status, ISNULL(A.CountTrxnMon,0) AS CountTrxnMon, ISNULL(A.SumTrxnMon,0) AS SumTrxnMon, E.UserID, E.FullName, DATENAME(MONTH, A.SpendFrom) AS Month FROM dbo.tblRPT_Spend AS A INNER JOIN dbo.tblMer_DeployORetrieveTerm AS B ON A.MID = B.MID AND A.TID = B.TID INNER JOIN dbo.tblMer_Outlet AS C ON B.OutletID = C.OutletID LEFT OUTER JOIN dbo.tblGen_Merchants AS D ON C.MerchantID = D.MerchantID LEFT OUTER JOIN dbo.tblGen_Users AS E ON C.SignedByUserID = E.UserID WHERE (A.SpendFrom >= '2013-01-01') AND (A.SpendTo <= '2013-12-31')) AS A INNER JOIN ( SELECT OutletID, MID, TID, SUM(Amount) AS Amount FROM tblMer_OutletRental GROUP BY OutletID, MID, TID) AS B ON A.OutletID = B.OutletID AND A.MID = B.MID AND A.TID = B.TID WHERE 1=1 AND A.TID IN (SELECT TID FROM tblRPT_Spend WHERE SumTrxnMon BETWEEN 0 and 10000) AND A.TID NOT IN (SELECT TID FROM tblRPT_Spend WHERE SumTrxnMon NOT BETWEEN 0 AND 10000)
14 People are following this question.
