macam query di SQLServer

karena sering lupa, maka perlu ditulis disini:
biar mudah dicari bila diperlukan.

::: current date time
update pre_tbl set PRE_DATE = GETDATE() where PRE_CRDNO = 'CRD-123460' ;
insert into PRE_TBL values ('CRD-123461','V03A00049900',50,GETDATE());
SELECT * FROM tabel where tabel_date = CURDATE( );

::: group berdasarkan nama bulan, in dan out

select hari as bulan,
sum (case when a.remark = 'I' then jumlah else 0 end)Incoming,
sum (case when a.remark = 'O' then jumlah else 0 end)Outgoing
from (SELECT remark, DATENAME(month,tanggal) hari, sum(qty) jumlah from vwunion group by remark, DATENAME(month,tanggal))a
group by hari



:::: menentukan sisa quantity memakai HAVING
select DocNo,
sum (case when a.remark = 'I' then jumlah else 0 end)INCOMING ,
sum (case when a.remark = 'O' then jumlah else 0 end)OUTGOING,
(sum (case when a.remark = 'I' then jumlah else 0 end) - sum (case when a.remark = 'O' then jumlah else 0 end))SISA
from (select remark, docno, sum(qty)jumlah from vwunion group by docno,remark)a
group by DocNo
having (sum (case when a.remark = 'I' then jumlah else 0 end) - sum (case when a.remark = 'O' then jumlah else 0 end)) > '0.00'


::: untuk group berdasar row code and set to 0 if NULL
select hari as bulan,
sum (case when a.remark = 'I' then jumlah else 0 end)Incoming,
sum (case when a.remark = 'O' then jumlah else 0 end)Outgoing
from (SELECT remark, month(tanggal) hari, sum(qty) jumlah from vwunion group by remark, month(tanggal))a
group by hari


::: untuk group date berdasarkan bulan.
SELECT remark, month(tanggal) hari, sum(qty) jumlah from vwunion group by remark, month(tanggal)


:: updated tabel#1 dengan referensi tabel#2
update MITM_TBL SET MITM_STS = '1' from IN_TBL,mitm_tbl where IN_ITM = MITM_ITM and IN_DOCNO = 'IN-444444'