Some Fun Toys to change margin on Master Item By Query

[Widi Satriya™]

DROP Temporary table IF EXISTS `temphjualmargin20`;

Create Temporary table `temphjualmargin20`

SELECT itemid, itemdescr, bkp, hpp, hbeli, hjual

, IFNULL((((hjual/IF(BKP=1,1.1,1))-hbeli)/(hjual/IF(BKP=1,1.1,1))) * 100,0) as current_margin 

, (hbeli*((100+(((20/100)/(1-(20/100)))*100))/100))*(IF(BKP=1,1.1,1)) as new_hjual

FROM m_item 

WHERE nonaktif = 0;

SELECT * FROM `temphjualmargin20`;

#setelah yakin akan nilai yang didapat, jalankan query update di bawah ini (hilangkan tanda pagar)

#UPDATE m_item a

#LEFT JOIN `temphjualmargin20` b ON b.itemid = a.itemid

#SET a.hjual = b.new_hjual

#WHERE b.itemid = a.itemid;

Terima Kasih

Widi Satriya A
Senior Programmer
PT. Datadigi Indonesia
Perkantoran Plaza Pasifik B4/81
Jl. Boulevard Barat, Kelapa Gading
Jakarta Utara 14241 Indonesia
Telp. 021-4587 5368/69
Fax. 021-4587 6442
http://www.datadigi.com