SQL q !!

[cruser3]

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

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

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

:)