Kumpulan QUERY SQL Server

Berikut adalah kumpulan beberapa Query yang saya simpan untuk mempermudah dalam menyelesaikan pekerjaan saya , :

Untuk Update dari 2 Tabel

UPDATE  Tabel_1
SET Nilai_1= Tabel_2
FROM Tabel_1,Tabel_2
WHERE Tabel_2.Nilai = Tabel_1.Nilai
GO


Untuk Cari Dobel

select tid, COUNT (Nilai) as jumlah
from Tabel_1
group by Nilai
order by jumlah desc


Cari Untuk MAX

select tid, MAX(NOMOR_SPK_PENARIKAN)
from TT_PENARIKAN
where STATUS = 1
group by TID

Untuk data RECON

select a.mid, a.tid , a.nama_merchant , a.alamat,
c.keterangan as status, B.KETERANGAN AS LOKASI,
f.KETERANGAN as RO, k.KETERANGAN AS KANWIL,
a.NOMOR_SPK, a.TGL_SPK,
a.TGL_PEMASANGAN, d.KETERANGAN as EDC,a.NOSERIAL_TERMINAL,h.KETERANGAN AS dongle,
a.NOSERIAL_DONGLE,i.NO_HP AS simcard,j.KETERANGAN AS jenis_edc,
 g.KETERANGAN as exeption, a.KETERANGAN_EXEPTION

    from  tm_data_pemasangan a
    left join tm_lokasi b on a.KODE_KOTA = b.kode_lokasi
    left join TM_STATUS_EDC c on a.status = c.KODE_STATUS
    left join TM_MESIN d on a.KODE_MESIN = d.kode_mesin
    left join TT_FITUR e on a.TID = e.TID
    left join TM_RO f on b.KODE_RO = f.KODE_RO
    left join TM_EXEPTION g on a.KODE_EXEPTION = g.KODE_EXEPTION
    left join TM_DONGLE h on a.KODE_DONGLE = h.KODE_DONGLE
    left join TT_SIMCARD i on a.TID = i.TID
    left join TM_JENIS_EDC j on d.KODE_JENIS_EDC = j.KODE_JENIS_EDC
    left join TM_KANWIL k on b.KANWIL = k.KODE_KANWIL


where -- h.KODE_DONGLE in ('01','02','03')
j.KODE_JENIS_EDC ='002'
--and a.STATUS    in ('1','6','8','9','10')

order by a.TGL_SPK asc


Cari tis child


SELECT     a.TID,  a.TID_GAZ_CARD,  a.TID_MINI_ATM,
a.TID_VISA_MASTER, a.TID_JCB, a.TID_AMEX, a.TID_DINNERS

FROM         TT_SERVICE a
left join TM_DATA_PEMASANGAN b on a.TID = b.TID
LEFT JOIN TM_LOKASI C ON B.KODE_KOTA = C.KODE_LOKASI
left join TM_MESIN d on b.KODE_MESIN = b.KODE_MESIN
left join TM_STATUS_EDC e on b.STATUS = e.KODE_STATUS
left join TM_RO f on C.KODE_RO = f.KODE_RO

where f.KODE_RO ='0109'


Untuk Data Assiransi

select a.mid, a.tid , a.nama_merchant , a.alamat,
c.keterangan as status, B.KETERANGAN AS LOKASI,
f.KETERANGAN as RO,  a.NOMOR_SPK, a.TGL_SPK,
a.TGL_PEMASANGAN,  d.KETERANGAN as EDC,a.NOSERIAL_TERMINAL,a.NOSERIAL_DONGLE, k.KETERANGAN as dongle

    from  tm_data_pemasangan a
    left join tm_lokasi b on a.KODE_KOTA = b.kode_lokasi
    left join TM_STATUS_EDC c on a.status = c.KODE_STATUS
    left join TM_MESIN d on a.KODE_MESIN = d.kode_mesin
    left join TT_FITUR e on a.TID = e.TID
    left join TM_RO f on b.KODE_RO = f.KODE_RO
    left join TM_EXEPTION g on a.KODE_EXEPTION = g.KODE_EXEPTION
    left join TM_TEKNISI h on a.KODE_TEKNISI = h.NIP
    left join TM_KANWIL i on b.KANWIL = i.KODE_KANWIL
    left join TM_JENIS_EDC j on d.KODE_JENIS_EDC = j.KODE_JENIS_EDC
    left join TM_DONGLE k on a.KODE_DONGLE = k.KODE_DONGLE
   
where a.TGL_PEMASANGAN <= '06/30/2012'
and a.STATUS in (1,8,9,10)
and k.KODE_DONGLE in (1,2,3)


order by a.TGL_PEMASANGAN asc

------
UPDATE  TT_PERBAIKAN 
SET MID  = TMP_TID.MID
FROM TT_PERBAIKAN,TMP_TID
WHERE TMP_TID.TID= TT_PERBAIKAN.TID
GO