Kumpulan Query

kumpulan query-query di mssql server dalam rangka membangun Retail Banking Direktorat Portal, cuman mengingat saja kali aja ntar lupa :d :

select convert(varchar(11),date_curr) as tglhigh,tglymd from fund_highlt where flag in (3,9) order by date_curr desc

select * from fund_highlt where flag in (3,9) and tglymd=’20111220′

select TOP 1 tglymd from fund_highlt order by hid desc

declare @maxhid int set @maxhid = (select max(hid) from fund_highlt) select totdpk,volGiro,volTab,volCASA,vol_depo,vol_flex,volTD,tglymd,flag from fund_highlt
where (hid>@maxhid-5 and hid<=@maxhid) or flag=1 order by tglymd

/*dpk nas */
declare @maxdate int, @lastEOM int
set @maxdate = (select max(datcur_ymd) from fund_viewdpk)
set @lastEOM = (select max(datcur_ymd) from fund_viewdpk where flag=3)
SELECT TOP 6 ‘nas’ as channel,datcur_ymd,sum(totbal) FROM fund_viewdpk where datcur_ymd>=(@maxdate-5) or datcur_ymd=@lastEOM
group by datcur_ymd order by datcur_ymd

/*cab*/
declare @maxdate int, @lastEOM int
set @maxdate = (select max(datcur_ymd) from fund_viewdpk)
set @lastEOM = (select max(datcur_ymd) from fund_viewdpk where flag=3)
SELECT TOP 6 ‘cab’ as channel,datcur_ymd,sum(totbal) as totbal FROM fund_viewdpk inner join fundbran on fund_viewdpk.branch=fundbran.brc
where kode=1 and (datcur_ymd>=(@maxdate-5) or datcur_ymd=@lastEOM) group by datcur_ymd order by datcur_ymd

select top 1 reportdt from fu_reportdt order by reportdt desc

/*performance komposisi dpk nas */
select datcur_ymd,sum(noaSA) as noaSA,sum(volSA) as volSA,sum(noaCA) as noaCA,sum(volCA) as volCA,sum(noaflek) as noaflek,sum(volflek) as volflek,sum(noatd) as noatd,sum(tottd) as tottd,sum(totnoa) as totnoa,sum(totbal) as totbal
from fund_viewdpk where date_curr=’20120128′ group by datcur_ymd

select *
from fund_viewdpk where date_curr=’20120117′ group by datcur_ymd

/**/
select sum(totbal),sum(totnoa),datcur_ymd from fund_viewdpk where date_curr =’20111225′ or date_curr =’20111226′ group by datcur_ymd

select sum(totbal) totbal,sum(totnoa) totnoa,sum(tottd) tottd,sum(totcasa) totcasa,datcur_ymd,branch from fund_viewdpk
where branch=’10002′ and (date_curr =’20111227′ or date_curr =’20111226′) group by datcur_ymd,branch

/* cabang */
select totbal,totnoa,balprev,noaprev,tottd,tottdprev,totcasa,totcasaprev from
(select kode,sum(totbal) as totbal,sum(totnoa) as totnoa,sum(tottd) tottd,sum(totcasa) totcasa from fund_viewdpk inner join fundbran on fund_viewdpk.branch = fundbran.brc
where kode=1 and date_curr=’20111227′ group by kode) data_curr inner join
(select kode,sum(totbal) as balprev,sum(totnoa) as noaprev,sum(tottd) tottdprev,sum(totcasa) totcasaprev from fund_viewdpk inner join fundbran on fund_viewdpk.branch = fundbran.brc
where kode=1 and date_curr=’20111226′ group by kode) data_prev on data_curr.kode=data_prev.kode

/* TD NAS */
SELECT prdgroup,COUNT(acctno) as noa, SUM(cbal_konversi) as vol FROM db_fundingmast,fund_masterprd WHERE
db_fundingmast.prdcod=fund_masterprd.procd and prdtyp=’TD’ group by prdgroup order by prdgroup desc

/*CASA */
select prdkey,count(acctno) as totnoa, sum(cbal_konversi) as totbal from db_fundingmast,fund_masterprd
where db_fundingmast.prdcod = fund_masterprd.procd and status <> 2 and prdtyp=’CASA’ group by prdkey order by prdkey

select prdgroup,count(acctno) as tnoa, sum(cbal_konversi) as tvol from db_fundingmast,fund_masterprd
where db_fundingmast.prdcod = fund_masterprd.procd and status <> 2 and prdtyp=’CASA’ group by prdgroup

select count(acctno) as tnoa, sum(cbal_konversi) as tvol from db_fundingmast,fund_masterprd
where db_fundingmast.prdcod = fund_masterprd.procd and status <> 2 and prdtyp=’CASA’

/*casa with date nas */
declare @maxdate numeric (10,0)
set @maxdate = (select max(datcur_ymd) from fund_viewdpk)
select a.prdgroup,totnoa,totvol,totnoa1,totvol1,totnoa1-totnoa as growthnoa,totvol1-totvol as growthvol,totnoanas,totvolnas
from (select prdgroup,sum(arcnoa) as totnoa,sum(arcvol) as totvol
from fund_proarch where prdkey=’SA’ and datcurr=’20120118′ GROUP BY prdgroup) a
LEFT JOIN (select prdgroup,sum(arcnoa) as totnoa1,sum(arcvol) as totvol1
from fund_proarch where prdkey=’SA’ and datcurr=’20120119′ GROUP BY prdgroup) b
on a.prdgroup=b.prdgroup
LEFT JOIN (select prdgroup,sum(arcnoa) as totnoanas,sum(arcvol) as totvolnas
from fund_proarch where prdkey=’SA’ and datcurr=@maxdate GROUP BY prdgroup) c
on a.prdgroup=c.prdgroup

/* fresh aro kode=1=>cabang kode=2=>gallery*/
select brc,jdname,freshacct,freshvol,aroacct,arovol from
((select brc,rtrim(brname) as jdname from fundbran where brc not in (10001,40001)) branch
left outer join freshTD on branch.brc=freshTD.jdbr)
left outer join aroTD on branch.brc=aroTD.jdbr order by branch.brc

/*prform cabang */
select top 10 branch,rtrim(brname) as brname,totbal from fund_viewdpk inner join fundbran on fund_viewdpk.branch = fundbran.brc
where date_curr=’20111230′ and kode=1 order by totbal desc,branch

/* daily growth */
select top 5 a.branch,a.brname,a.dpk_curr,b.dpk_prev,dpk_curr – dpk_prev as growth from
(select branch,brname,totbal as dpk_curr,date_curr from fund_viewdpk inner join fundbran on
fund_viewdpk.branch = fundbran.brc where date_curr=’20120101′ and kode=1)a left outer join
(select branch,totbal as dpk_prev,date_curr from fund_viewdpk inner join fundbran on
fund_viewdpk.branch = fundbran.brc where date_curr=’20111230′ and kode=1)b on a.branch=b.branch order by growth desc

/* highest noa */
select top 5 branch,rtrim(brname) as brname,noacasa,noatd,totnoa from fund_viewdpk inner join fundbran on fund_viewdpk.branch = fundbran.brc
where date_curr=’20111230′ and kode=1 order by totnoa desc,branch

/* highest noa daily */
select top 5 a.branch,a.brname,a.noacasa – b.noacasa as GrNoaCasa, a.noatd – b.noatd as GrNoatd, noa_curr – noa_prev as growth_noa from
(select branch,brname,noacasa,noatd,totnoa as noa_curr,date_curr from fund_viewdpk inner join fundbran on
fund_viewdpk.branch = fundbran.brc where date_curr=’20120101′ and kode=1)a left outer join
(select branch,noacasa,noatd,totnoa as noa_prev,date_curr from fund_viewdpk inner join fundbran on
fund_viewdpk.branch = fundbran.brc where date_curr=’20111229′ and kode=1)b on a.branch=b.branch order by growth_noa desc

select kursTngh from fundkurs where currency=’USD’ and periode=(select max(periode) from fundkurs)

select brc,brname,brins,totcasa,tottd,totbal,kurs_tngh,fund_viewdpk.datcur_ymd
from fundbran left outer join fund_viewdpk on fundbran.brc=fund_viewdpk.branch where fund_viewdpk.datcur_ymd=’20120102′ and kode=’1′ order by totbal desc

select sum(tottd) as tottd,sum(totcasa) as totcasa,sum(totbal) as totbal from fund_viewdpk,fundbran
where fund_viewdpk.branch = fundbran.brc and datcur_ymd=’20120102′ and kode=’1′

declare @maxdate numeric (10,0)
set @maxdate = (select max(datcur_ymd) from fund_viewdpk)
select sum(totbal) from fund_viewdpk,fundbran where fund_viewdpk.branch=fundbran.brc and kode=1 and datcur_ymd=@maxdate

SELECT prdtyp,date_curr,COUNT(acctno) as noa, SUM(cbal_konversi) as vol FROM db_fundingmast,fundbran,fund_masterprd
WHERE db_fundingmast.jdbr=fundbran.brc and db_fundingmast.prdcod=fund_masterprd.procd and kode=1 GROUP BY date_curr, prdtyp

/*cab gal komposisi casa td */
select datcur_ymd,sum(totcasa) as tcasa,sum(tottd) as ttd,sum(totbal) as totbal from fund_viewdpk,fundbran
where fund_viewdpk.branch=fundbran.brc and kode=1 and date_curr=’20111231′ group by datcur_ymd

select prdgroup,count(acctno) as tnoa, sum(cbal_konversi) as tvol from db_fundingmast,fund_masterprd,fundbran
where db_fundingmast.prdcod = fund_masterprd.procd and db_fundingmast.jdbr=fundbran.brc and db_fundingmast.status <> 2 and fund_masterprd.prdtyp=’CASA’ and kode=1
group by fund_masterprd.prdgroup

select prdgroup,count(acctno) as tnoa, sum(cbal_konversi) as tvol from db_fundingmast,fund_masterprd,fundbran
where db_fundingmast.prdcod = fund_masterprd.procd and db_fundingmast.jdbr=fundbran.brc and db_fundingmast.status <> 2
and fund_masterprd.prdtyp=’CASA’ and kode=’1′ group by fund_masterprd.prdgroup

SELECT COUNT(acctno) as noa, SUM(cbal_konversi) as vol FROM db_fundingmast, fund_masterprd, fundbran WHERE
db_fundingmast.prdcod=fund_masterprd.procd and db_fundingmast.jdbr=fundbran.brc and prdtyp=’TD’ and kode=’1′

select * from db_fundingmast

SELECT prdtyp,date_curr,COUNT(acctno) as noa, SUM(cbal_konversi) as vol FROM db_fundingmast,fundbran,fund_masterprd
WHERE db_fundingmast.jdbr=fundbran.brc and db_fundingmast.prdcod=fund_masterprd.procd and kode=’1′ GROUP BY date_curr, prdtyp

select datcur_ymd,sum(totcasa) as tcasa,sum(tottd) as ttd,sum(totbal) as totbal from fund_viewdpk,fundbran
where fund_viewdpk.branch=fundbran.brc and kode=’1′ group by datcur_ymd

/*gallery */
select totbal,totnoa,balprev,noaprev,tottd,tottdprev,totcasa,totcasaprev from
(select kode,sum(totbal) as totbal,sum(totnoa) as totnoa,sum(tottd) tottd,sum(totcasa) totcasa from fund_viewdpk inner join fundbran on fund_viewdpk.branch = fundbran.brc
where kode=2 and date_curr=’20120104′ group by kode) data_curr inner join
(select kode,sum(totbal) as balprev,sum(totnoa) as noaprev,sum(tottd) tottdprev,sum(totcasa) totcasaprev from fund_viewdpk inner join fundbran on fund_viewdpk.branch = fundbran.brc
where kode=2 and date_curr=’20120103′ group by kode) data_prev on data_curr.kode=data_prev.kode

/* jatuh tempo td */
select fundbran.brc,lower(brname) as brname,tipeklas,rtrim(brmgr) as bm,openDate,totbal,datcur_ymd,tgtVolumJt from (fundbran inner join fund_viewdpk on fundbran.brc=fund_viewdpk.branch),fund_tgt2011
where fundbran.brc=’50001′ and fundbran.brc = fund_tgt2011.branch and datcur_ymd=’20120110′ and fund_tgt2011.periode=’2011-05′

SELECT * FROM jthTmp_today where brc=’50001′

SELECT dtcmast.brc,rtrim(brname) as brname,acctno,rtrim(cusname) as cusnm,cbal,cbal_konv,grtrat,datopn,matdt6,matdat,term,termcod,prdcod,rtrim(curtyp) as curtyp
FROM dtcmast,fundbran where dtcmast.brc=fundbran.brc and dtcmast.status <> 2 and
(matdat>=’2012011′ and matdat<’2012018′) and dtcmast.brc=’50001′ order by matdat,cbal DESC

/*hl fund */
declare @maxhid int, @lastEOM int
set @maxhid = (select max(hid) from fund_highlt)
set @lastEOM = (select max(hid) from fund_highlt where flag=3)
select totdpk,volGiro,volTab,volCASA,vol_depo,vol_flex,volTD,tglymd,flag from fund_highlt where (hid>@maxhid-4 and hid<=@maxhid) or hid=@lastEOM or flag=9 order by tglymd

/* store procedure dpk_chan6tgl */
ALTER PROCEDURE [dbo].[dpk_chan6tgl] AS
declare @maxdate int, @lastEOM int
set @maxdate = (select max(datcur_ymd) from fund_viewdpk)
set @lastEOM = (select max(datcur_ymd) from fund_viewdpk where flag=3)
select qnas.datcur_ymd,qnas.dpknas,qps.dpkps,qcab.dpkcab,qgall.dpkgal,qm2s.dpkm2s from
((((SELECT TOP 6 ‘nas’ as channel,datcur_ymd,sum(totbal) as dpknas FROM fund_viewdpk
where datcur_ymd>=(@maxdate-4) or datcur_ymd=@lastEOM
group by datcur_ymd) qnas inner join
(SELECT TOP 6 ‘psec’ as channel,datcur_ymd,sum(totbal) as dpkps FROM fund_viewdpk
where branch=10002 and (datcur_ymd>=(@maxdate-4) or datcur_ymd=@lastEOM)
group by datcur_ymd) qps on qnas.datcur_ymd=qps.datcur_ymd) inner join
(SELECT TOP 6 ‘cab’ as channel,datcur_ymd,sum(totbal) as dpkcab FROM fund_viewdpk inner join fundbran on fund_viewdpk.branch=fundbran.brc
where kode=1 and (datcur_ymd>=(@maxdate-4) or datcur_ymd=@lastEOM)
group by datcur_ymd) qcab on qnas.datcur_ymd=qcab.datcur_ymd) inner join
(SELECT TOP 6 ‘gall’ as channel,datcur_ymd,sum(totbal) as dpkgal FROM fund_viewdpk inner join fundbran on fund_viewdpk.branch=fundbran.brc
where kode=2 and (datcur_ymd>=(@maxdate-4) or datcur_ymd=@lastEOM)
group by datcur_ymd) qgall on qnas.datcur_ymd=qgall.datcur_ymd) inner join
(SELECT TOP 6 ‘m2s’ as channel,datcur_ymd,sum(totbal) as dpkm2s FROM fund_viewdpk left outer join fundbran on fund_viewdpk.branch=fundbran.brc
where kode is null and (datcur_ymd>=(@maxdate-4) or datcur_ymd=@lastEOM)
group by datcur_ymd) qm2s on qnas.datcur_ymd=qm2s.datcur_ymd
order by qnas.datcur_ymd

select max(datcur_ymd) from fund_viewdpk

select * from fund_viewdpk where datcur_ymd=’20120119′

Bookmark It