Need help with the Query - 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

General SQL Server Forums

New to SQL Server Programming

Need help with the Query

Author

Topic

rowter
Yak Posting Veteran

76 Posts

Posted - 2011-12-19 : 16:25:28

Hi All,

We have a table with columns 'ActivityActivityTime', 'Type', vehicle_ID
We need to get reveneueTime and totalTime for each Vehicle_ID
RevenueTime will be max(ActivityTime ) - min(ActivityTime ) where type <>'LN' Group by Vehicle_ID
totalTime will be max(ActivityTime ) - min(ActivityTime ) Group by Vehicle_ID

Now, using a single query can we get all of this information?
If not is ther any other way?

thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-19 : 17:29:01

You can make use of the CASE expression, like this:

SELECT
   MAX(CASE WHEN [type] <> 'LN' THEN ActivityTime END) -
      MIN(CASE WHEN [type] <> 'LN' THEN ActivityTime END) AS RevenuTime,
   MAX(ActivityTime)-MIN(ActivityTime) AS TotalTime
FROM
   YourTable
GROUP BY
   Vehicle_Id;

rowter
Yak Posting Veteran

76 Posts

Posted - 2011-12-20 : 10:49:17

Thanks a lot Sunitabeck...

I found the answer here too ...
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=169307

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources