DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: SQL q !!

  1. #1
    Join Date
    Jul 2003
    Posts
    323

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

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool


    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

  3. #3
    Join Date
    Jul 2003
    Posts
    323
    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.

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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
    /
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Jul 2003
    Posts
    323
    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
  •  


Click Here to Expand Forum to Full Width