Friday, 29 April 2011

SQL to Find message per MT per Currency by date range

i want to make a query to my database to find message per MT per Currency by date range. here’s my query

— OUTGOING MESSAGES
SELECT TOP (100) PERCENT dbo.rMesg.x_receiver_X1, dbo.rMesg.mesg_type, dbo.rMesg.x_fin_ccy, SUM(dbo.rMesg.x_fin_amount) AS Jumlah,
COUNT(dbo.rMesg.x_receiver_X1) AS jml_trx, dbo.rMesg.mesg_sub_format
FROM dbo.rMesg INNER JOIN
dbo.rInst ON dbo.rMesg.aid = dbo.rInst.aid AND dbo.rMesg.mesg_s_umidl = dbo.rInst.inst_s_umidl AND
dbo.rMesg.mesg_s_umidh = dbo.rInst.inst_s_umidh INNER JOIN
dbo.rAppe ON dbo.rInst.aid = dbo.rAppe.aid AND dbo.rInst.inst_s_umidh = dbo.rAppe.appe_s_umidh AND
dbo.rInst.inst_s_umidl = dbo.rAppe.appe_s_umidl AND dbo.rInst.inst_num = dbo.rAppe.appe_inst_num
WHERE (dbo.rMesg.mesg_sub_format = ‘INPUT’) AND (dbo.rAppe.x_appe_last = 1) AND (dbo.rInst.inst_num = 0) AND
(dbo.rAppe.appe_iapp_name = ‘SWIFT’) AND
–Range tanggal
(dbo.rMesg.mesg_crea_date_time BETWEEN CONVERT(datetime, ’2009/08/01′, 102)
AND CONVERT(datetime, ’2009/09/1′, 102))
– MT
and (dbo.rMesg.mesg_type = ’103′)
– BIC
and x_receiver_x1 = ‘CENAIDJAXXX’
GROUP BY dbo.rMesg.x_receiver_X1,dbo.rMesg.mesg_type, dbo.rMesg.x_fin_ccy, dbo.rMesg.mesg_sub_format

– INCOMING MESSAGES
SELECT TOP (100) PERCENT dbo.rMesg.mesg_sender_X1, dbo.rMesg.mesg_type, dbo.rMesg.x_fin_ccy, SUM(dbo.rMesg.x_fin_amount) AS Jumlah,
COUNT(dbo.rMesg.mesg_sender_X1) AS jml_trx , dbo.rMesg.mesg_sub_format
FROM dbo.rMesg INNER JOIN
dbo.rInst ON dbo.rMesg.aid = dbo.rInst.aid AND dbo.rMesg.mesg_s_umidl = dbo.rInst.inst_s_umidl AND
dbo.rMesg.mesg_s_umidh = dbo.rInst.inst_s_umidh INNER JOIN
dbo.rAppe ON dbo.rInst.aid = dbo.rAppe.aid AND dbo.rInst.inst_s_umidh = dbo.rAppe.appe_s_umidh AND
dbo.rInst.inst_s_umidl = dbo.rAppe.appe_s_umidl AND dbo.rInst.inst_num = dbo.rAppe.appe_inst_num
WHERE (dbo.rMesg.mesg_sub_format = ‘OUTPUT’) AND (dbo.rAppe.x_appe_last = 1) AND (dbo.rInst.inst_num = 0) AND
(dbo.rAppe.appe_iapp_name = ‘SWIFT’)
AND
–Range tanggal
(dbo.rMesg.mesg_crea_date_time BETWEEN CONVERT(datetime, ’2009/08/01′, 102)
AND CONVERT(datetime, ’2009/09/1′, 102))
– MT
and (dbo.rMesg.mesg_type = ’103′)
– BIC
and mesg_sender_x1 = ‘CENAIDJAXXX’
GROUP BY dbo.rMesg.mesg_sender_X1, dbo.rMesg.mesg_type, dbo.rMesg.x_fin_ccy, dbo.rMesg.mesg_sub_format
ORDER BY dbo.rMesg.mesg_sender_X1

No comments:

Post a comment

Popular Posts