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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
YiogiConnect With a Mentor Commented:
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
 
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
All Courses

From novice to tech pro — start learning today.