Using Case - 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 2008 Forums

Transact-SQL (2008)

Using Case

Author

Topic

sonjan
Starting Member

22 Posts

Posted - 2012-05-21 : 18:34:31

Hi
I would like to exclude negated transactions for data entry fuel transactions on vehicles eg litres = 800l, then -800l. This is for the same vehicle, odometer reading and transaction date. I used the following:

case when fueltrans.odometer > 1 and fueltrans.litres > 1 -abs(fueltrans.litres) else fueltrans.odometer end as [Odometer].

Obviously this didn't work. Appreciate any advice.
Thanks
Sonja

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-21 : 18:43:27

didnt get you. what do you mean by it doesnt work?
can you explain with data sample?

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

sonjan
Starting Member

22 Posts

Posted - 2012-05-21 : 19:22:40

The results displayed:

Asset No Litres Odometer Date FuelCapacity
379861 537.41 815 20/12/2010 70
379861 -537.41 815 20/12/2010 70
379861 53.74 815 20/12/2010 70

I only want the third transaction to display as the second transaction negates the first transaction due to data entry error.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-21 : 19:31:27

[code]
SELECT *
FROM Table t
WHERE NOT EXISTS (SELECT 1
FROM table
WHERE AssetNo = t.AssetNo
AND [Date] = t.[Date]
AND Odometer = t.Odometer
AND FuelCapacity = t.FuelCapacity
AND Litres = -1 * t.Litres)
[/code]

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

sonjan
Starting Member

22 Posts

Posted - 2012-05-21 : 23:16:13

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-21 : 23:19:12

wc

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources