Selasa, 23 Juli 2013

Quary melihat data perkara

SELECT
right(LEFT(DATA_REGISTER.NO_PERK,4),3)
+'/Pdt.'+
SUBSTRING(DATA_REGISTER.NO_PERK, LEN(DATA_REGISTER.NO_PERK) - 2, 1)
+'/20'+
RIGHT(DATA_REGISTER.NO_PERK, 2)
+'/PA.Sjj'
AS NOMOR_PERKARA,
replace(MS_DATAHAKIM.NAMA_HAKIM,char(13)+char(10),'|') AS MAJELIS_LENGKAP,
DATA_REGISTER.TGL_DAFTAR AS DAFTAR,
DATA_REGISTER.TGL_PMH AS PMH,
DATA_REGISTER.TGL_PHS AS PHS,
DATA_SIDANG.TANGGAL AS SDG_I,
DATA_REGISTER.TGL_PUTUSAN AS PUTUSAN,
DATA_REGISTER.TGL_MINUTASI AS MINUTASI,
DATA_REGISTER.ARSP_TANGGAL AS ARSIP,
MS_PERKARA.KETERANGAN AS JENIS_PERKARA,
right(LEFT(DATA_REGISTER.NO_PERK,5),1) AS KD,
DATA_REGISTER.TINGKAT AS TK
FROM
SiadpaWin.dbo.DATA_REGISTER
INNER JOIN DATA_SIDANG ON DATA_REGISTER.NO_PERK= DATA_SIDANG.NO_PERK
INNER JOIN MS_HAKIM ON DATA_SIDANG.TGL_SK= MS_HAKIM.TGL_SK
INNER JOIN MS_DATAHAKIM ON MS_HAKIM.KODE= MS_DATAHAKIM.KODE
INNER JOIN MS_PERKARA ON DATA_REGISTER.KODE_PERK=MS_PERKARA.KODE_PERK
AND DATA_SIDANG.RUANG= MS_DATAHAKIM.RUANG
AND DATA_SIDANG.NO_MAJELIS= MS_DATAHAKIM.TIM
AND DATEPART(weekday, DATA_SIDANG.TANGGAL)= MS_DATAHAKIM.HARI
WHERE RIGHT(DATA_REGISTER.NO_PERK,2)='12'
AND (DATA_SIDANG.KE=0)
ORDER BY DATA_SIDANG.NO_PERK