(SELECT CIT.citation_id,PD.system_id,PD.pumpdate,PD.gallonspercycle as maint_gallonspercycle,PD.gallonsperminute as maint_gallonsperminute,PD.dailygallonlimit as maint_dailygallonlimit,PD.pumpnumber,PD.totalcycles,PD.totalseconds,PD.totalrows,A.agency_name,AP.permit_num,Ap.gallonsperminute as agc_gallonsperminute,Ap.gallonspercycle as agc_gallonspercycle,AP.dailygallonlimit as agc_dailygallonlimit,CAST((CAST(ISNULL(A.grace_period,5) AS FLOAT)/100) AS FLOAT)+1 AS theAmount,
p.permit_num,C.*, O.*,R.service_rep_name,L.*,S.system_name,M.manufacturer_name,

Case when ((AP.gallonsperminute IS not null and totalseconds * (AP.gallonsperminute/60) > AP.dailygallonlimit * CAST((CAST(ISNULL(A.grace_period,5) AS FLOAT)/100) AS FLOAT)+1) or (AP.gallonspercycle IS not null and totalcycles>AP.dailygallonlimit/AP.gallonspercycle * CAST((CAST(ISNULL(A.grace_period,5) AS FLOAT)/100) AS FLOAT)+1)) then 3 else 0 end As violation_type,
Case when (AP.gallonsperminute IS not null and totalseconds * (AP.gallonsperminute/60) > AP.dailygallonlimit * CAST((CAST(ISNULL(A.grace_period,5) AS FLOAT)/100) AS FLOAT)+1) then totalseconds * (AP.gallonsperminute/60) - AP.dailygallonlimit * (1-CAST((CAST(ISNULL(A.grace_period,5) AS FLOAT)/100) AS FLOAT)) else (totalcycles*AP.gallonspercycle) - AP.dailygallonlimit* (1-CAST((CAST(ISNULL(A.grace_period,5) AS FLOAT)/100) AS FLOAT)) end as gallons_over
FROM
PumpDays PD, locations L
      LEFT outer join Owners O ON O.owner_id=L.owner_id,
systems S
      LEFT outer join manufacturers M ON M.manufacturer_id=S.manufacturer_id
      LEFT outer join service_contracts c ON C.system_id=S.system_id and c.expire_notice_ind='N' and C.renewed_ind='N'
      LEFT outer join service_reps R ON C.service_rep_id=R.service_rep_id,
system_permits sp, permits p
      LEFT outer join citations cit ON cit.permit_num = p.permit_num,
permits AP, Agencies A
WHERE p.permit_num = AP.permit_num and p.active=1 and AP.active=1 and AP.permit_id <> P.permit_id and PD.assoc_type=4 and
A.agency_id = AP.agency_id and S.location_id=L.location_id and L.assoc_type=4 and L.assoc_id = 15008 AND
p.permit_num = PD.permit_num and sp.system_id = s.system_id and p.permit_id = sp.permit_id and
(
(AP.gallonsperminute IS not null and totalseconds * (AP.gallonsperminute/60) > AP.dailygallonlimit * CAST((CAST(ISNULL(A.grace_period,5) AS FLOAT)/100) AS FLOAT)+1) or
(AP.gallonspercycle IS not null and totalcycles>AP.dailygallonlimit/AP.gallonspercycle * CAST((CAST(ISNULL(A.grace_period,5) AS FLOAT)/100) AS FLOAT)+1)))
UNION ALL
(SELECT CIT.citation_id,PD.system_id,PD.pumpdate,PD.gallonspercycle as maint_gallonspercycle,PD.gallonsperminute as maint_gallonsperminute,PD.dailygallonlimit as maint_dailygallonlimit,PD.pumpnumber,PD.totalcycles,PD.totalseconds,PD.totalrows,A.agency_name,P.permit_num,p.gallonsperminute as agc_gallonsperminute,p.gallonspercycle as agc_gallonspercycle,P.dailygallonlimit as agc_dailygallonlimit,0 AS theAmount,p.permit_num,C.*, O.*,R.service_rep_name,L.*,S.system_name,M.manufacturer_name,
Case when ((PD.gallonsperminute IS not null and totalseconds * (PD.gallonsperminute/60) > PD.dailygallonlimit) or (PD.gallonspercycle IS not null and totalcycles>PD.dailygallonlimit/PD.gallonspercycle)) then 4 else 0 end as violation_type,
Case when (PD.gallonsperminute IS not null and totalseconds * (PD.gallonsperminute/60) > PD.dailygallonlimit) then totalseconds * (PD.gallonsperminute/60) - PD.dailygallonlimit else (totalcycles*PD.gallonspercycle) - PD.dailygallonlimit end as gallons_over
FROM PumpDays PD,
locations L
      LEFT outer join Owners O ON O.owner_id=L.owner_id,
systems S
      LEFT outer join manufacturers M ON M.manufacturer_id=S.manufacturer_id
      LEFT outer join service_contracts c ON C.system_id=S.system_id and c.expire_notice_ind='N' and C.renewed_ind='N'
      LEFT outer join service_reps R ON C.service_rep_id=R.service_rep_id,
system_permits sp, permits p
      LEFT outer join citations cit ON cit.permit_num = p.permit_num,
agencies A
WHERE S.location_id=L.location_id and L.assoc_type=4 and L.assoc_id = 15008 AND PD.assoc_type=4 and  p.permit_num = PD.permit_num and sp.system_id = s.system_id and
p.permit_id = sp.permit_id and p.agency_id = A.agency_id and
(
(PD.gallonsperminute IS not null and totalseconds * (PD.gallonsperminute/60) > PD.dailygallonlimit)
or
(PD.gallonspercycle IS not null and totalcycles>PD.dailygallonlimit/PD.gallonspercycle)
))
ORDER BY R.service_rep_name ASC