coba yang ini, semoga sukses : select itm.itm_code ,itm.itm_suplier , sp.sls_price from item_master itm left outer join sales_price sp on itm.itm_code = sp.sls_code where itm.itm_suplier = 'BBB'
--- mellon <[EMAIL PROTECTED]> wrote: > tanya dong, > moga tak salah milis. > > 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 > ____________________________________________________________________________________ Cheap Talk? Check out Yahoo! Messenger's low PC-to-Phone call rates (http://voice.yahoo.com) ----------------------------------------------- IndoPB - Indonesia PB User Group An Indonesian PowerBuilder User Group's Event 2nd PowerBuilder Update Rumah Sakit Jantung Harapan Kita 26/27 August 2006 To Register : http://groups.yahoo.com/group/indopb/database?method=addRecord&tbl=4 Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/indopb/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/indopb/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/
