SQL Server Divide by zero

Aloha, I am getting a divide by zero error in the following

SELECT     PCNS_Staff_NO, sum(PCNS_STAFF_SR_ALLOCATION_TOTAL_HOURS)/
           (sum(DateDiff(dd,PCNS_STAFF_SR_ALLOCATION_START_DATE,PCNS_STAFF_SR_ALLOCATION_END_DATE)))
FROM         PCNS_STAFF_SR_ALLOCATION
Group by PCNS_Staff_NO, PCNS_STAFF_SR_ALLOCATION.PCNS_STAFF_SR_ALLOCATION_END_DATE
having PCNS_STAFF_SR_ALLOCATION_END_DATE > '2007/01/01'
order by PCNS_STAFF_NO

Any ideas?
MittensonmauiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

YiogiCommented:
Yes you have only one division so it means your sum there is 0. I'd suggest the following
SELECT     PCNS_Staff_NO, sum(PCNS_STAFF_SR_ALLOCATION_TOTAL_HOURS)/
           IsNull(NullIf(sum(DateDiff(dd,PCNS_STAFF_SR_ALLOCATION_START_DATE,PCNS_STAFF_SR_ALLOCATION_END_DATE))), 0), 1)
FROM         PCNS_STAFF_SR_ALLOCATION
Group by PCNS_Staff_NO, PCNS_STAFF_SR_ALLOCATION.PCNS_STAFF_SR_ALLOCATION_END_DATE
having PCNS_STAFF_SR_ALLOCATION_END_DATE > '2007/01/01'
order by PCNS_STAFF_NO

This will make 0 divisions = 1 divisions. Alternatively you can skip the isnull and then have nulls returned
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MittensonmauiAuthor Commented:
worked great, I need to group them all as one record but can't because I have the end dtae field in the having clause I have treid a few different things but can't get it to work.
0
YiogiCommented:
I assume you want to group by PCNS_Staff_NO. I'd suggest taking your records having PCNS_STAFF_SR_ALLOCATION_END_DATE > '2007/01/01' in a temporary table and then do your select on that one. Then you wouldn't need to have the column in your having clause.

That should be another question but here is the answer to it:
CREATE @TempTable TABLE (StaffNo BigInt, TotalHours Decimal(18,2), StartDate DateTime, EndDate DateTime)
INSERT INTO @TempTable
            SELECT PCNS_Staff_NO,
            PCNS_STAFF_SR_ALLOCATION_TOTAL_HOURS,
            PCNS_STAFF_SR_ALLOCATION_START_DATE,
            PCNS_STAFF_SR_ALLOCATION_END_DATE
            WHERE PCNS_STAFF_SR_ALLOCATION_END_DATE > '20070101'

SELECT  StaffNo,
              sum(TotalHours )/IsNull(NullIf(sum(DateDiff(dd, StartDate, EndDate))), 0), 1)
              FROM @TempTable
              Group by StaffNo
              Order by StaffNo

If the variable table doesn't work you can try an actual temp table # but remember to drop it at the end. I usually prefer variable tables for small recordsets though, but I don't know exactly what Sql engine you are using. I don't know if they work outside MS SQL Server
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.