Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- (SELECT CIT.citation_id,PD.*,A.agency_name,AP.*,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
- 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.*,A.agency_name,P.*,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
- 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=3 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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement