"Pelayanan Vs Keuangan"

declare @bulan1 as int
declare @bulan2 as int
declare @type as char(1) --1 sosial or 2 komersial
declare @tahun as int
set @bulan1 =1
set @bulan2 =1
set @type = 1
set @tahun = 2007

-- cek kempel keuangan dan pelayanan
select d.kdakun, keu, sum(bystjregz)bystjregzz, sum(bystjsjp) bystjsjp, (sum(bystjsjp)- keu) seilsihsjp, (sum(bystjregz)- keu) selisihreg from
(select a.kdakun,sum(bystjreg) bystjregz, sum(bystjsjp) bystjsjp from
(select '7010100' + @type kdakun,'1' jenpelsjp
union
select '7010200' + @type kdakun,'2' jenpelsjp
union
select '7010300' + @type kdakun,'3' jenpelsjp
union
select '7010400' + @type kdakun,'4' jenpelsjp
union
select '7010100' + @type kdakun,'5' jenpelsjp
union
select '7010200' + @type kdakun,'6' jenpelsjp
union
select '7010300' + @type kdakun,'7' jenpelsjp
union
select '7010400' + @type kdakun,'8' jenpelsjp)
a inner join
(select b.jnspelsjp,a.noreg,bystjreg,tglstjkeu,
jnspstsjp,flagreg,sum(bystjsjp)bystjsjp
from datreg a left join datsjp b on a.noreg = b.noreg
group by b.jnspelsjp,a.noreg,bystjreg,tglstjkeu,jnspstsjp,flagreg)
b on a.jenpelsjp = b.jnspelsjp
where
month(tglstjkeu) between @bulan1 and @bulan2
and jnspstsjp = @type-1 and b.noreg is not null and year(tglstjkeu)=@tahun
and flagreg = '6'group by a.kdakun
union
select '7010100'+@type kdakun,IsNull(Sum(A.KapPerBln), 0) bystjreg,IsNull(Sum(A.KapPerBln), 0) bystjsjp
From DatKapBln A Where Month(A.KapBlnThn) between @bulan1 and @bulan2
and Year(A.KapBlnThn)=@tahun and A.KapFlagKeu='6' and A.KapFlagPst= @type-1)
d inner join
(select kdakun, sum(totald-totalk) keu from datjurnal
where month(tgl) between @bulan1 and @bulan2 and year(tgl) = @tahun
group by kdakun) e on d.kdakun = e.kdakun
group by d.kdakun,keu
order by d.kdakun

NB :
Biasa dapet dari suhu

-----------------------------------------------------------------------------

--nech antara SJP ma register (oleh-oleh pas pelatian monevdal)--

SELECT b.KCPelReg AS KC, e.NmTKP, c.NMJNSPST AS [JNS PESERTA], g.NMJNSPPK,
f.NMPPK AS [NAMA PPK], b.NoReg AS [NO REGISTER],
b.ByStjReg AS [BIAYA PD KEMPEL/REG],
SUM(CEILING(a.ByVerSJP)) AS [BIAYA PD INDIVIDU RECO],
CEILING(SUM(a.ByVerSJP) - b.ByStjReg)AS SELISIH
FROM DatReg b INNER JOIN
DatSJP a ON b.NoReg = a.NoReg INNER JOIN
REFJNSKPST c ON a.JnsPstSJP = c.KDJNSPST INNER JOIN
RegRp d ON b.NoReg = d.NoReg INNER JOIN
RefTKP e ON d.TKPReg = e.KdTKP INNER JOIN
REFPPK f ON b.PPKKlaimReg = f.KDPPK INNER JOIN
REFJNSPPK g ON f.JNSPPK = g.KDJNSPPK
WHERE (YEAR(b.TglStjKeu) = 2006)
GROUP BY b.NoReg, b.ByStjReg, b.KCPelReg, b.PPKKlaimReg, c.NMJNSPST, f.NMPPK, f.JNSPPK, e.NmTKP, d.TKPReg, a.JnsPstSJP, f.JNSPPK,
g.NMJNSPPK
HAVING (CEILING(SUM(a.ByVerSJP) - b.ByStjReg) < - 1)
AND (d.TKPReg <> '10')
ORDER BY a.JnsPstSJP, f.JNSPPK, d.TKPReg,
CEILING(SUM(a.ByVerSJP) - b.ByStjReg) DESC

----------------------------------------------------------------------
Korsim Mataram punya gawe
-- untuk menampilkan jenis peserta yang berbeda antara SJP vs register
select distinct b.jnspstsjp, a.*
from datreg a inner join datsjp b on (a.noreg=b.noreg)
where b.jnspstsjp<>a.jnspstreg

-- mengupdate jenis peserta yang salah pada SJP
update datsjp set jnspstsjp=b.jnspstreg
from datsjp a inner join datreg b on (a.noreg=b.noreg)
where a.jnspstsjp<>b.jnspstreg
----------------------------------------------------------------------

1 komentar:

Anonymous said...

bukan script aku tuh mas, Yoez kasi ke aku, ntar dikirain ngaku2 lg :P

"Coment ye...satu batang coklat menantimu :D"

Berikan sedikit waktu ye...tuk sekedar menorehkan goresan tangan mungilmu.,tapi inget jangan asal ngomonk.,
"gw gampar loe..."
wakakakakakaka...............

Recent Comments