tanya dong,
bagaimana script di MSSQL Server
untuk nampilin data dengan kondisi:
item code dengan supplier BBB saja
dan sales pricenya (baik ada/tdk)
dengan effectif date yang terakhir
dari dua tabel berikut:
-----------------------
tabel(1): item_master
-----------------------
itm_code itm_suplier
1111 BBB
2222 AAA
3333 BBB
4444 CCC
5555 BBB
6666 BBB
----------------------------
tabel(2): sales_price
----------------------------
sls_code sls_price sls_effdt
1111 0.55 2006-01-31
1111 0.66 2006-08-31
3333 0.77 2006-01-31
4444 0.88 2006-08-31
6666 0.99 2006-01-31
6666 0.33 2006-08-31
---------------------------------------------
hasil yang diinginkan
---------------------------------------------
itm_code itm_suplier sls_price sls_effdt
1111 BBB 0.66 2006-08-31
3333 BBB 0.77 2006-01-31
5555 BBB NULL NULL
6666 BBB 0.33 2006-08-31
sorry panjang,
sudah coba pakai berikut tapi tak berhasil
bila pricenya ikut ditampilin.
select aa.itm_code,aa.itm_suplier,bb.sls_price
from
( select aa.itm_code,aa.itm_suplier,
max(bb.sls_effdt)effdt
from item_master aa left outer join sales_price bb
on aa.itm_code = bb.sls_code
where aa.itm_suplier = 'BBB'
group by aa.itm_code,aa.itm_suplier,bb.sls_price
) aa ,
( select sls_code,sls_price,sls_effdt
from sales_price
) bb
where aa.itm_code = bb.sls_code
and aa.effdt = bb.sls_effdt
thanks sebelumnya
__________________________________________________
Apakah Anda Yahoo!?
Lelah menerima spam? Surat Yahoo! memiliki perlindungan terbaik terhadap spam
http://id.mail.yahoo.com
Wahana Programmer Groups Links
<*> Untuk mengunjungi sponsor milis ini, klik link berikut:
http://wahanaprogrammer.net
<*> Untuk menghubungi owner milis ini, kirim email ke:
[EMAIL PROTECTED]
<*> Konsultasi pemrogramman bisa chat disini:
Yahoo! Messenger: wahanaprogrammer
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/Programmer-VB/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/Programmer-VB/join
(Yahoo! ID required)
<*> To change settings via email:
mailto:[EMAIL PROTECTED]
mailto:[EMAIL PROTECTED]
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
