Query Help 1

[heydyrtt]

Hello,

I have a field called payroll_name and the data in it is like
AP-Bonus,
CH-PayGrade,
Dumpster Program.

The query needs to look for the dash (-) if it's not there like for Dumpster Program then column needs to read "All other wages paid". If it's AP- CH- or what ever the AP- will be AP Wages Paid, CH Wages Paid and so on.

The ending result should look like this

Summary by Grouping Residents Paid Paid
AP Wages Paid 43 77 140.89
CH Wages Paid 40 131 299.02
FL Wages Paid 45 99 206.98
IS Wages Paid 43 144 63.73
Education Bonus Paid 16 63 1,575.00
All Other Wages Paid 3 27 10.80
Totals 190 541 $2,296.42

Thanks



Heydyrtt

 

[SantaMufasa]

Heydyrtt,

How about this:

col a heading "Summary by Grouping" format a20
select decode(instr(payroll_name,'-'),
   0,'All Other Wages Paid'
    ,substr(payroll_name,1,instr(payroll_name,'-')-1)||' Wages Paid')a from heydyrtt;

Summary by Grouping
--------------------
AP Wages Paid
CH Wages Paid
All Other Wages Paid

3 rows selected.

Let us know if this meets your need.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 18:24 (29Nov04) UTC (aka "GMT" and "Zulu"),
@ 11:24 (29Nov04) Mountain Time

 

[heydyrtt]

That got it


Thanks Mufasa



Heydyrtt

 

[heydyrtt]

One more to this in the field I will have
AP-BONUS
AP-PAYGRADE
AP-PAYGRADE2 AND SO ON

How can I get so all the AP's will will be grouped
together.

The way it is now when I run it I get the
AP Wages Paid 2 77 789.00
AP Wages Paid 2 77 850.00

Should be
AP Wages Paid 4 154 1649.00

Thanks

Heydyrtt

 

[SantaMufasa]

Heydyrtt,

For code economy, I'd try the following (I've made up column names for "paid_cnt" and "paid_amt" since I don't know what your real columns names are):

break on report
compute sum of res_cnt paid_cnt paid_amt on report
select groups,res_cnt,paid_cnt,paid_amt
from (select decode(instr(payroll_name,'-'),
                0,'All Other Wages Paid'
                 ,substr(payroll_name,1,instr(payroll_name,'-')-1)||' Wages Paid') groups
             ,sum(residents)res_cnt
             ,sum(paid_cnt)paid_cnt
             ,sum(paid_amt)paid_amt
        from heydyrtt);

Let us konw how that works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 23:17 (29Nov04) UTC (aka "GMT" and "Zulu"),
@ 16:17 (29Nov04) Mountain Time