Find no. of currency note & coins (Tip)

[mirza_rehan]

I was make this query for help some one.......

SELECT Sal,TRUNC(Sal/1000) "1000", TRUNC(MOD(Sal,1000)/500) "500",
TRUNC(MOD(MOD(Sal,1000),500)/100) "100", TRUNC(MOD(MOD(MOD(Sal,1000),500),100)/50) "50",
TRUNC(MOD(MOD(MOD(MOD(Sal,1000),500),100),50)/20) "20",
TRUNC(MOD(MOD(MOD(MOD(MOD(Sal,1000),500),100),50),20)/10) "10",
TRUNC(MOD(MOD(MOD(MOD(MOD(MOD(Sal,1000),500),100),50),20),10)/5) "5",
TRUNC(MOD(MOD(MOD(MOD(MOD(MOD(MOD(Sal,1000),500),100),50),20),10),5)/2) "2",
TRUNC(MOD(MOD(MOD(MOD(MOD(MOD(MOD(MOD(Sal,1000),500),100),50),20),10),5),2)/1) "1"
FROM emp;

I have also developed a sample report using oracle reports 6i for this and you can download it.


[img]/images/icon_zip.gif[/img] <b>Attachment:</b> coinage.zip <i>( 10654bytes )</i>

if there is any other way please reply in this thread

Best regards,
R E H A N M I R Z A
System Analyst / Senior Oracle Developer
+92-304-2120807

When ever you pray
Please remember me and my family

[sikandar]

Dear Rehan seems result is not correct? Like in first row there are 8x100 so under 100 I was expecting 8 and same with others. What u say?

SAL 1000 500 100 50 20 10 5 2 1
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
800 0 1 3 0 0 0 0 0 0
1600 1 1 1 0 0 0 0 0 0
1250 1 0 2 1 0 0 0 0 0
2975 2 1 4 1 1 0 1 0 0
1250 1 0 2 1 0 0 0 0 0
2850 2 1 3 1 0 0 0 0 0
2450 2 0 4 1 0 0 0 0 0
3000 3 0 0 0 0 0 0 0 0
5000 5 0 0 0 0 0 0 0 0
1500 1 1 0 0 0 0 0 0 0
1100 1 0 1 0 0 0 0 0 0
950 0 1 4 1 0 0 0 0 0
3000 3 0 0 0 0 0 0 0 0
1300 1 0 3 0 0 0 0 0 0

[mirza_rehan]

Dear Sikandar result is 100% correct because it check from bigest currency to lowest in case of 800 there is 1 Note of 500 (1 x 500) & 3 Notes of 100 (3 x 100)

if any problem please reply......

Best regards,
R E H A N M I R Z A

When ever you pray
Please remember me and my family

[admin]

I got your point. I was thinking each column as separate. Thanks for sharing.

[mirza_rehan]

this query is very use for making salary, Dear Admin i was send you a report of coinage please share that reports in this thread.....

Best regards,
R E H A N M I R Z A
System Analyst / Senior Oracle Developer
Cell: +92-304-2120807

When ever you pray
Please remember me and my family

[admin]

Uploaded above in the main thread. thanks for sharing.

[mufti_sb]

dear brother Rehan
i have checked ur query it is remarkably working well and intrusting results on report.

Regards,

Mudasser Sharif

Oracle Certified Professional
System Analist , D.B.A