-
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.,
Last edited by cruser3; 03-29-2006 at 03:26 PM.
-
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:
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')
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
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.
Last edited by cruser3; 03-29-2006 at 06:12 PM.
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|