Is it possible to get range of values

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Is it possible to get range of values

Author

Topic

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2009-12-01 : 02:32:44

I have a table named Accounts

In this table, I have 3 columns named
1. General_Leger_AccountNo that is of type varchar(50)
2. Debit (money)
3. Credit (money)

the data in the General_Leger_AccountNo are usually in the following format:

3000-S1000-30001
3200-S1000-23001
4000-L1000-32001
4100-L1000-32001

I would like to retrieve the sum of (debit - credit) from this table where the 4 leftmost digit of General_Leger_AccountNo are between a range of 3000 - 4000. How do I do that

For example,

Select Sum(Debit - Credit) from Accounts
and (convert(int, LEFT (FGLA1.GLAccountNo , 4)) between
3000 and 4000)

does not seems to return me the data I want. :( It returns me a null. Is there any thing wrong with the above SQL?

kbhere
Yak Posting Veteran

58 Posts

Posted - 2009-12-01 : 03:11:08

WHY you have used "AND" after the table name??

Try this also which similar to your query.. I have used CAST() instead of CONVERT() and removed "AND" after table name and replaced it with "WHERE"..

SELECT SUM(debit - credit) FROM Accounts

WHERE CAST(LEFT(General_Leger_AccountNo) AS INT) BETWEEN 3000 AND 4000;

Balaji.K

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-01 : 07:55:33

quote:
Originally posted by kbhere

WHY you have used "AND" after the table name??

Try this also which similar to your query.. I have used CAST() instead of CONVERT() and removed "AND" after table name and replaced it with "WHERE"..

SELECT SUM(debit - credit) FROM Accounts

WHERE CAST(LEFT(General_Leger_AccountNo) AS INT) BETWEEN 3000 AND 4000;

Balaji.K

It should be

SELECT SUM(debit - credit) FROM Accounts

WHERE CAST(LEFT(General_Leger_AccountNo,4) AS INT) BETWEEN 3000 AND 4000;

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources