Microsoft Report Builder TSQL query issue

I have written the following TSQL query and am running it within Microsoft Report Builder. My issue is one portion is not working correctly. Any information would be very helpful. Thanks.

  • Note - (SELECT ProcessDate FROM ufnARCUGetLatestProcessDate()) returns the date the query is being run on. It's one of our standard functions.

This is the part I am having issues with. For some reason it is not finding loans that do have a NULL close date. For example the account I am testing on has an open loan yet the value it is returning is 0 when I should receive 1. Does anyone see what I could be missing?

    (SELECT COUNT(*) -- Check if there are no open loans
     FROM arcu.vwARCULoan AS L
     WHERE A.ACCOUNTNUMBER=L.AccountNumber AND
     L.ProcessDate = (SELECT ProcessDate FROM ufnARCUGetLatestProcessDate()) AND
     L.LoanCloseDate IS NOT NULL)=0 AND -- END Check if there are no open loans

Below is the whole query.

SELECT
   A.ACCOUNTNUMBER AS 'Account Number'
  ,A.AccountBranch AS 'Branch Number'
  ,A.AccountOpenDate AS 'Account Open Date'
  ,A.AccountActivityDate AS 'Last Activity Date'
  ,N.NameFirst+' '+N.NameLast AS 'Member Name'
FROM
  arcu.vwARCUAccount AS A
LEFT OUTER JOIN arcu.vwARCUName AS N
ON A.ACCOUNTNUMBER = N.AccountNumber
WHERE
  A.ProcessDate = (SELECT ProcessDate FROM ufnARCUGetLatestProcessDate()) AND
  A.AccountCloseDate IS NULL AND
  A.AccountOpenDate>DATEADD(DAY,-30,A.AccountOpenDate) AND
  N.NameType=0 AND
  N.ProcessDate = (SELECT ProcessDate FROM ufnARCUGetLatestProcessDate()) AND
    (SELECT SUM(S.ShareBalance) -- Check if all shares are zero
     FROM arcu.vwARCUShare AS S
     WHERE A.ACCOUNTNUMBER=S.AccountNumber AND
     S.ProcessDate = (SELECT ProcessDate FROM ufnARCUGetLatestProcessDate()) AND
     A.ProcessDate = (SELECT ProcessDate FROM ufnARCUGetLatestProcessDate()))=0 AND -- END Check if all shares are zero
    (SELECT COUNT(*) -- Check if there are no open loans
     FROM arcu.vwARCULoan AS L
     WHERE A.ACCOUNTNUMBER=L.AccountNumber AND
     L.ProcessDate = (SELECT ProcessDate FROM ufnARCUGetLatestProcessDate()) AND
     A.ProcessDate = (SELECT ProcessDate FROM ufnARCUGetLatestProcessDate()) AND
     L.LoanCloseDate IS NOT NULL)=0 AND -- END Check if there are no open loans
    (SELECT COUNT(*) --Check if tracking records exist 
     FROM arcu.vwARCULoanTracking AS T
     WHERE A.ACCOUNTNUMBER=T.AccountNumber AND 
     T.ProcessDate = (SELECT ProcessDate FROM ufnARCUGetLatestProcessDate()) AND 
     A.ProcessDate = (SELECT ProcessDate FROM ufnARCUGetLatestProcessDate()) AND
     T.LoanTrackingType IN(@TRACKINGTYPES) )=0 AND --END Check if tracking records exist 
    (SELECT COUNT(*)
     FROM arcu.vwARCUInventory AS I
     WHERE A.ACCOUNTNUMBER=I.InventoryAccountNumber AND
     I.ProcessDate = (SELECT ProcessDate FROM ufnARCUGetLatestProcessDate()) AND
     A.ProcessDate = (SELECT ProcessDate FROM ufnARCUGetLatestProcessDate()) AND
     I.InventoryStatus=2 AND 
     I.InventoryType IN (@INVENTORYTYPE))=0 
ORDER BY A.ACCOUNTNUMBER

Answers


I'm assuming that if a loan is still "open" then L.LoanCloseDate will be null.

In that case the problem with your query is

 L.LoanCloseDate IS NOT NULL

This criteria means that you are only counting closed loans, i.e. loans that have a value in LoanClosedDate. What you appear to need is the inverse of that, i.e. open loans. So you probably just need to change this criteria to

 L.LoanCloseDate IS NULL

so that you are counting open loans.


Need Your Help

Load user control from another user control in a RadTabStrip

telerik user control

I am using Telerik Rad tabstrip control in which I am loading two user controls A, B. Now from User Control A , I want to load or call or redirect to User Control B with some query string parameter...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.