CODEWITHSUNDEEP
c#sql-server
Lord Relix
Lord Relix

Reputation: 942

Correct way to search between dates

My query must grab customers' statements and certain data between certain dates. For testing purposes, I've created a few test accounts and used the full query.

Now I need to filter between dates. This is my current query:

SELECT 
FechaDeSistema, 
Code, 
CASE 
    WHEN DR_Socio = @SocioNum THEN cast(Cantidad as decimal(19,2)) 
END as Debit, 
CASE 
    WHEN CR_Socio = @SocioNum THEN cast(Cantidad as decimal(19,2)) 
END AS Credit, 
CASE 
    WHEN DR_Socio = @SocioNum THEN cast(BalanceDebito as decimal(19,2)) 
    WHEN CR_Socio = @SocioNum THEN cast(BalanceCredito as decimal(19,2)) 
END AS Balance 
FROM Ledger 
 WHERE (Debito_Cuenta = @Acct) 
 OR    (Credito_Cuenta = @Ncct) 
 AND (FechaDeSistema BETWEEN @Start AND @Final)
 ORDER BY FechaDeSistema DESC";

The parameters for the dates are given by two calendars I have set up. However, when I generate the report, it still shows the whole range of data, never picking between the dates I chose. What am I doing wrong?

Upvotes: 1

Views: 217

Answers (2)

Scott Isaacs
Scott Isaacs

Reputation: 1168

It's probably an order of operations issue on your WEHRE clause.

WHERE something
    OR somethingelse
    AND onemorething

SQL is probably interpreting those differently than you expect. Add parentheses somewhere to clarify what you actually need.

Upvotes: 1

Cam Bruce
Cam Bruce

Reputation: 5689

Change your WHERE clause to this:

 WHERE (Debito_Cuenta = @Acct OR Credito_Cuenta = @Ncct) 
 AND FechaDeSistema BETWEEN @Start AND @Final

Your parenthesis were not correct and was grouping your clauses wrong.

Upvotes: 7

Related Questions