SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Where Clause Error
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stahorse
Yak Posting Veteran

85 Posts

Posted - 09/27/2012 :  09:14:44  Show Profile  Reply with Quote
Hi

I have

SELECT

[PolicyId] = MB.POLICY_ID
, [SwitchValue] = ISNULL(SUM(MB.TOTAL_AMOUNT), 0.00)
, [RequestedAmount] = ISNULL(SUM(BF.FEE_AMOUNT), 0.00)
, [AmountPaid] = ISNULL(SUM(BF.GLOBAL_FEE_TAKEN), 0.00)

FROM PR_MONEY_BFN MB

LEFT JOIN PR_BFN_FEES BF WITH (NOLOCK)
ON MB.POLICY_ID = BF.POLICY_ID

WHERE

-- ISNULL(BF.GLOBAL_FEE_TAKEN, 0.00) < ISNULL(BF.FEE_AMOUNT, 0.00)
--AND BF.GLOBAL_FEE_TAKEN < BF.FEE_AMOUNT

I don't get any data when I try to use: "BF.GLOBAL_FEE_TAKEN < BF.FEE_AMOUNT"

but when I use: "ISNULL(BF.GLOBAL_FEE_TAKEN, 0.00) < ISNULL(BF.FEE_AMOUNT, 0.00)" I get data back but with zero values for [AmountPaid]

DonAtWork
Flowing Fount of Yak Knowledge

2161 Posts

Posted - 09/27/2012 :  09:24:25  Show Profile  Reply with Quote
try SUM(ISNULL(BF.GLOBAL_FEE_TAKEN,0.00)) instead.








How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Go to Top of Page

stahorse
Yak Posting Veteran

85 Posts

Posted - 09/27/2012 :  09:29:40  Show Profile  Reply with Quote
I still get the same results, "BF.GLOBAL_FEE_TAKEN < BF.FEE_AMOUNT" doesnt return data at all.
"ISNULL(BF.GLOBAL_FEE_TAKEN, 0.00) < ISNULL(BF.FEE_AMOUNT, 0.00)" returns data but with only zero values for [AmountPaid]
Go to Top of Page

lionofdezert
Aged Yak Warrior

Pakistan
885 Posts

Posted - 09/27/2012 :  09:50:21  Show Profile  Visit lionofdezert's Homepage  Send lionofdezert a Yahoo! Message  Reply with Quote
SELECT

[PolicyId] = MB.POLICY_ID
, [SwitchValue] = ISNULL(SUM(MB.TOTAL_AMOUNT), 0.00)
, [RequestedAmount] = ISNULL(SUM(BF.FEE_AMOUNT), 0.00)
, [AmountPaid] = ISNULL(SUM(BF.GLOBAL_FEE_TAKEN), 0.00)

FROM PR_MONEY_BFN MB

LEFT JOIN PR_BFN_FEES BF WITH (NOLOCK)
ON MB.POLICY_ID = BF.POLICY_ID
AND BF.GLOBAL_FEE_TAKEN < BF.FEE_AMOUNT

--------------------------
http://connectsql.blogspot.com/
Go to Top of Page

stahorse
Yak Posting Veteran

85 Posts

Posted - 09/27/2012 :  09:58:07  Show Profile  Reply with Quote
I'm getting zeros for all MB.POLICY_ID and MB.TOTAL_AMOUNT which is wrong.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/27/2012 :  10:29:45  Show Profile  Reply with Quote
show some sample data from your tables and explain what you want as output from them

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000