SQL q !!

Is this possible through SQL:

We have a table with a date column and another which is populated with a flag(rule) from another table which has a bunch of rules.

So the data is like this:

TDATE RULES
----------- ------
1-Oct-2004 1884.1886
2-Nov-2005 1921.
4-Jan-2006 1884.
7-Nov-2006

Each of the values (1884,1921) is a rule in another table and is concatenated with a dot (.) and inserted into the above table.

First we wanted the count of rules by month so this worked:

select to_char(DATE,'Mon YYYY'), count(RULES)
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')

Now we need a count of individual rules by month - each 4 digit number above is a rule.

Is this possible through SQL ?

Rgds.,

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. :rolleyes:

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

Code:

---

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')

---

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.

:)

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 ...

Code:

---

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

---

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

:)