SQL Help - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Help

Author

Topic

JTProg
Starting Member

24 Posts

Posted - 2006-10-10 : 15:48:04

How can I retrieve a count by individual for adjustment line items that fall between the values of 100 and -100? The last statement in the Where Clause is giving me problems.

Declare @Month int

Set @Month = 8

SELECT dbo.Batch.Department, dbo.Batch.PreparedBy, Count(dbo.Adjustments.BMIBatch)as 'Counter', SUM(dbo.Adjustments.DebitAmount) AS 'Total Debit', SUM(dbo.Adjustments.CreditAmount) AS 'Total Credit'
FROM dbo.Batch INNER JOIN dbo.Adjustments on dbo.Batch.BMIBatch = dbo.Adjustments.BMIBatch
WHERE ((dbo.Batch.Status = '20') OR (dbo.Batch.Status = '1') OR (dbo.Batch.Status = '3') OR (dbo.Batch.Status = '2') OR (dbo.Batch.Status = '6'))
AND (DATEPART(month, dbo.Batch.PreparedDate) = @Month) AND (dbo.Batch.Type = 'Adjustment') AND (((dbo.Adjustments.DebitAmount - dbo.Adjustments.CreditAmount)Between 100 and -100))
GROUP BY dbo.Batch.Department, dbo.Batch.PreparedBy
ORDER BY dbo.Batch.Department, dbo.Batch.PreparedBy

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-10 : 16:02:08

Values for BETWEEN operator ALWAYS need to be ascending.
Try this

SELECT		dbo.Batch.Department,
		dbo.Batch.PreparedBy,
		Count(dbo.Adjustments.BMIBatch) as 'Counter',
		SUM(dbo.Adjustments.DebitAmount) AS 'Total Debit',
		SUM(dbo.Adjustments.CreditAmount) AS 'Total Credit'
FROM		dbo.Batch
INNER JOIN	dbo.Adjustments on dbo.Batch.BMIBatch = dbo.Adjustments.BMIBatch
WHERE		dbo.Batch.Status IN ('20', '1', '3', '2', '6')
		AND DATEPART(month, dbo.Batch.PreparedDate) = @Month
		AND dbo.Batch.Type = 'Adjustment'
		AND dbo.Adjustments.DebitAmount - dbo.Adjustments.CreditAmount BETWEEN -100 AND 100
GROUP BY	dbo.Batch.Department,
		dbo.Batch.PreparedBy
ORDER BY	dbo.Batch.Department,
		dbo.Batch.PreparedBy

Peter Larsson
Helsingborg, Sweden

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-10-10 : 16:04:17

Perhaps you should should us some sample data and the expected result set. With such a brief synopsis and the query, it's hard to know what you want as we don't have intimate knowledge of your environment like you do.

Tara Kizer

JTProg
Starting Member

24 Posts

Posted - 2006-10-10 : 16:18:52

Thanks for helping. The SQL Statement you wrote seems to work just fine.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources