SQL q !!

[LKBrwn_DBA]

Your data is not consistent: when you have two rules, there is only one dot, and when you have one rule there is also one dot.

If the rules would be 4 characters each plus the dot, then you could use the following SQL:

select to_char(DATE,'Mon YYYY'), count(RULES)
      , SUM(LENGTH(RULES)/5) 
from DETAIL
where RULES IS NOT NULL
group by rollup(to_char(DATE,'Mon YYYY'))
order by to_date(to_char(DATE,'Mon YYYY'),'Mon YYYY')

[cruser3]

Tx - LKB

The way data is populated is unfortunately something we cannot change - it's done through an SP which puts the (.) and each row is a transaction matched against a rule. So that field could have values besides null as:

324.1786.1886

The range for rules is -4 to 2137.

The output should look like
MONTH RULE COUNT
SEPT 05 1886 123

Any other ideas will be greatly appreciated.

[slimdave]

Calculate the number of rules in the field by comparing the length of the original string to it's length with all the dots removed, and adding one.

example ...

Select length(rules)-length(replace(rules,'.'))+1
from whatever
/

[cruser3]

TX slim can you explain why add 1 and how can I fit this into the query to get the totals by month or can it be incorportaed with LKB 's query below ?

select to_char(DATE,'Mon YYYY'), count(RULES)
, SUM(LENGTH(RULES)/5)
from DETAIL
where RULES IS NOT NULL
group by rollup(to_char(DATE,'Mon YYYY'))
order by to_date(to_char(DATE,'Mon YYYY'),'Mon YYYY')

rgds