SQL Jumlah Tunggakan dan Selesai Petugas Ukur

{sisa}
{
SELECT sum(puexsub.parcamt)
FROM puexsub,puex,expe
WHERE puexsub.emplnip ="010184570"
AND puexsub.expeid = puex.expeid
AND puexsub.opernum = puex.opernum
AND puexsub.expeid = expe.expeid
AND expe.stexcod <> "CA"
AND puex.puexenddat is null
AND year(puex.puexinidat)=2006;
}
{selesai}
{
SELECT sum(puexsub.parcamt)
FROM puexsub, puex, expe
WHERE puexsub.emplnip ="010184570"
AND puexsub.expeid = puex.expeid
AND puexsub.opernum = puex.opernum
AND puexsub.expeid = expe.expeid
AND expe.stexcod <> "CA"
AND puex.puexenddat is not null
AND year(puex.puexinidat)=2006;
}
{total}
SELECT sum(puexsub.parcamt)
FROM puexsub, puex, expe
WHERE puexsub.emplnip ="010184570"
AND puexsub.expeid = puex.expeid
AND puexsub.opernum = puex.opernum
AND puexsub.expeid = expe.expeid
AND expe.stexcod <> "CA"
AND year(puex.puexinidat)=2006;

-------------------------------------------
GROUP BERDASARKAN NIP
-------------------------------------------
{sisa}

SELECT puexsub.emplnip,
empl.emplnam,
sum(puexsub.parcamt)
FROM puexsub,puex,expe,empl
WHERE empl.emplnip=puexsub.emplnip
AND puexsub.expeid = puex.expeid
AND puexsub.opernum = puex.opernum
AND puexsub.expeid = expe.expeid
AND expe.stexcod <> "CA"
AND puex.puexenddat is null
AND year(puex.puexinidat)=2006
GROUP BY 1,2;

{selesai}

SELECT puexsub.emplnip,
empl.emplnam,
sum(puexsub.parcamt)
FROM puexsub,puex,expe,empl
WHERE empl.emplnip=puexsub.emplnip
AND puexsub.expeid = puex.expeid
AND puexsub.opernum = puex.opernum
AND puexsub.expeid = expe.expeid
AND expe.stexcod <> "CA"
AND puex.puexenddat is not null
AND year(puex.puexinidat)=2006
GROUP BY 1,2;

{total}

SELECT puexsub.emplnip,
empl.emplnam,
sum(puexsub.parcamt)
FROM puexsub,puex,expe,empl
WHERE empl.emplnip=puexsub.emplnip
AND puexsub.expeid = puex.expeid
AND puexsub.opernum = puex.opernum
AND puexsub.expeid = expe.expeid
AND expe.stexcod <> "CA"
AND year(puex.puexinidat)=2006
GROUP BY 1,2;