Return a minimum of '1'? - 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)

Return a minimum of '1'?

Author

Topic

sbt1
Yak Posting Veteran

89 Posts

Posted - 2005-02-25 : 15:30:22

I have a query that returns a sum divided by 8 (which converts hours into days). Works fine, except if the hours are < 8 it rounds down thus returning 0 instead of '1'. What I want to do is have it always return a value of at least '1'.

SELECT
TaskDates.Resource,
TaskDates.Type,
Sum(TaskDates.Duration) AS TotalHRS,
Sum(TaskDates.Duration)/8 AS TotalDAYS
FROM TaskDates INNER JOIN Tasks ON Tasks.TaskID=TaskDates.TaskID

I tried using a MAX function:
Max(Sum(TaskDates.Duration)/8,1) AS TotalDAYS

but that didn't work (query blew up).

Ideas?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-25 : 15:38:04

[code]
SELECT
TaskDates.Resource,
TaskDates.Type,
Sum(TaskDates.Duration) AS TotalHRS,
isnull(nullif(Sum(TaskDates.Duration)/8,0),1) AS TotalDAYS
FROM TaskDates INNER JOIN Tasks ON Tasks.TaskID=TaskDates.TaskID
[/code]

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources