June 26, 2013 at 11:00 pm
hello all masters plese help me to join this query
SELECT
rj_poliklinik.nmpoli as namapoli,count (rj_reg.kdpoli) as jumpoli,rj_reg.kdperusahaan as kodeperusahaan,
instansi.nminstansi as namainstansi,sum (rj_transdt.komptr1) as karcis
FROM rj_reg
JOIN rj_transdt on rj_reg.noreg = rj_transdt.noreg
JOIN rj_poliklinik on rj_reg.kdpoli = rj_poliklinik.kdpoli
JOIN instansi on rj_reg.kdperusahaan = instansi.kdinstansi
JOIN Tarif_Pelayanan ON rj_transdt.kdlayan = Tarif_Pelayanan.kdlayan
WHERE rj_reg.tglregistrasi BETWEEN '2013/01/01' and '2013/01/01' AND (Tarif_Pelayanan.groupkd = '1') and rj_reg.batal='false'
group by rj_reg.kdpoli,rj_reg.kdperusahaan,rj_poliklinik.nmpoli,instansi.nminstansi order by namapoli desc
SELECT instansi.nminstansi as namainstansi,count (rj_reg.kdpoli) as jumpoli,rj_reg.kdperusahaan as kodeperusahaan,sum (rj_transdt.komptr1) as tindakan
FROM rj_transdt
JOIN rj_reg on rj_transdt.noreg=rj_reg.noreg
JOIN instansi on rj_reg.kdperusahaan = instansi.kdinstansi
JOIN Tarif_Pelayanan ON rj_transdt.kdlayan = Tarif_Pelayanan.kdlayan
WHERE rj_reg.tglregistrasi BETWEEN '2013/01/01' and '2013/01/01' and Tarif_Pelayanan.groupkd = '2'
group by rj_reg.kdperusahaan,instansi.nminstansi
SELECT instansi.nminstansi as namainstansi,count (rj_reg.kdpoli) as jumpoli,rj_reg.kdperusahaan as kodeperusahaan,sum (rj_transdt.komptr1) as alkes
FROM rj_transdt
JOIN rj_reg on rj_transdt.noreg=rj_reg.noreg
JOIN instansi on rj_reg.kdperusahaan = instansi.kdinstansi
JOIN Tarif_Pelayanan ON rj_transdt.kdlayan = Tarif_Pelayanan.kdlayan
WHERE rj_reg.tglregistrasi BETWEEN '2013/01/01' and '2013/01/01' and Tarif_Pelayanan.groupkd = '3'
group by rj_reg.kdperusahaan,instansi.nminstansi
June 26, 2013 at 11:19 pm
Can you explain a little more what you are actually trying to do? You seem to have 3 separate queries there - what final result do you want?
Mike John
June 27, 2013 at 12:50 am
i'm sorry master...the output look likethis
No. Nama Poli | Jum | Kd.Instansi | Nama Instansi | Karcis | Tindakan | ALKES |
June 27, 2013 at 1:40 am
i'm sorry master...the output look like this
Nama Poli | Jum | Kd.Instansi | Nama Instansi | Karcis | Tindakan | ALKES |
June 27, 2013 at 2:02 am
If you just want to combine the results of all three queries to one single result, you'll can use UNION ALL to put the three queries together. Make sure all three queries have the same number and sequence of the selected columns.
SELECT......--{query 1}
UNION ALL
SELECT......--{query 2}
UNION ALL
SELECT......--{query 3}
June 27, 2013 at 3:04 am
Tyr this....
select a.namapoli, a.jumpoli, a.namainstansi, a.kodeperusahaan, a.karcis, b.tindakan, c.alkes
from
(SELECT
rj_poliklinik.nmpoli as namapoli,count (rj_reg.kdpoli) as jumpoli,rj_reg.kdperusahaan as kodeperusahaan,
instansi.nminstansi as namainstansi,sum (rj_transdt.komptr1) as karcis
FROM rj_reg
JOIN rj_transdt on rj_reg.noreg = rj_transdt.noreg
JOIN rj_poliklinik on rj_reg.kdpoli = rj_poliklinik.kdpoli
JOIN instansi on rj_reg.kdperusahaan = instansi.kdinstansi
JOIN Tarif_Pelayanan ON rj_transdt.kdlayan = Tarif_Pelayanan.kdlayan
WHERE rj_reg.tglregistrasi BETWEEN '2013/01/01' and '2013/01/01' AND (Tarif_Pelayanan.groupkd = '1') and rj_reg.batal='false'
group by rj_reg.kdpoli,rj_reg.kdperusahaan,rj_poliklinik.nmpoli,instansi.nminstansi order by namapoli desc) A,
(SELECT instansi.nminstansi as namainstansi,count (rj_reg.kdpoli) as jumpoli,rj_reg.kdperusahaan as kodeperusahaan,
sum (rj_transdt.komptr1) as tindakan
FROM rj_transdt
JOIN rj_reg on rj_transdt.noreg=rj_reg.noreg
JOIN instansi on rj_reg.kdperusahaan = instansi.kdinstansi
JOIN Tarif_Pelayanan ON rj_transdt.kdlayan = Tarif_Pelayanan.kdlayan
WHERE rj_reg.tglregistrasi BETWEEN '2013/01/01' and '2013/01/01' and Tarif_Pelayanan.groupkd = '2'
group by rj_reg.kdperusahaan,instansi.nminstansi) B,
(SELECT instansi.nminstansi as namainstansi,count (rj_reg.kdpoli) as jumpoli,rj_reg.kdperusahaan as kodeperusahaan,sum (rj_transdt.komptr1) as alkes
FROM rj_transdt
JOIN rj_reg on rj_transdt.noreg=rj_reg.noreg
JOIN instansi on rj_reg.kdperusahaan = instansi.kdinstansi
JOIN Tarif_Pelayanan ON rj_transdt.kdlayan = Tarif_Pelayanan.kdlayan
WHERE rj_reg.tglregistrasi BETWEEN '2013/01/01' and '2013/01/01' and Tarif_Pelayanan.groupkd = '3'
group by rj_reg.kdperusahaan,instansi.nminstansi) C
where a.jumpoli=b.jumpoli and a.jumpoli=c.jumpoli
and a.namainstansi=b.namainstansi and a.namainstansi=c.namainstansi
and a.kodeperusahaan=b.kodeperusahaan and a.kodeperusahaan=c.kodeperusahaan
June 27, 2013 at 8:26 pm
i'm sorry master...but it said
Msg 1033, Level 15, State 1, Line 13
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
Msg 102, Level 15, State 1, Line 22
Incorrect syntax near 'B'.
Msg 102, Level 15, State 1, Line 30
Incorrect syntax near 'C'.
June 27, 2013 at 8:33 pm
if i use union all the result like this
Msg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
June 27, 2013 at 11:47 pm
xmanletoy (6/27/2013)
i'm sorry master...but it saidMsg 1033, Level 15, State 1, Line 13
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
Msg 102, Level 15, State 1, Line 22
Incorrect syntax near 'B'.
Msg 102, Level 15, State 1, Line 30
Incorrect syntax near 'C'.
Remove the ORDER BY from the first query (A) and add an ORDER BY (if necassary) to the outer most query.
June 27, 2013 at 11:51 pm
xmanletoy (6/27/2013)
if i use union all the result like thisMsg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
As I mentioned in my post ALL queries need to have the same number of columns (and in the same order). If a query doesn't have a specific column, you can add it by selecting an empty value. See the simplified sample below where the second query doesnt have column2:SELECT column1, column2, column3 FROM table1
UNION ALL
SELECT column1, '', column3 FROM table2
June 28, 2013 at 2:01 am
in desperation I use the following script, perhaps the master anyone want to add? This script really takes a long time to process the results...
SELECT
rj_poliklinik.nmpoli as namapoli,count (rj_reg.noreg) as jumpasien,rj_reg.kdperusahaan as kodeperusahaan,
instansi.nminstansi as namainstansi,
(CASE WHEN Tarif_Pelayanan.groupkd = '1'
THEN sum(rj_transdt.komptr1)END) AS karcis,
(CASE WHEN Tarif_Pelayanan.groupkd = '2'
THEN sum(rj_transdt.komptr1)END) AS tindakan,
(CASE WHEN Tarif_Pelayanan.groupkd = '3'
THEN sum(rj_transdt.komptr1)END) AS alkes
FROM rj_reg
INNER JOIN rj_transdt on rj_reg.noreg = rj_transdt.noreg
INNER JOIN rj_poliklinik on rj_reg.kdpoli = rj_poliklinik.kdpoli
INNER JOIN instansi on rj_reg.kdperusahaan = instansi.kdinstansi
INNER JOIN Tarif_Pelayanan ON rj_transdt.kdlayan = Tarif_Pelayanan.kdlayan
WHERE rj_reg.tglregistrasi BETWEEN '2013/01/01' and '2013/01/03' and rj_reg.batal='false'
group by rj_poliklinik.nmpoli,rj_reg.kdperusahaan,rj_poliklinik.nmpoli,instansi.nminstansi,Tarif_Pelayanan.groupkd order by namapoli desc
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply