Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a database of coded actions with this structure.

{User, Date, ActionCode}

I need to generate reports based on dates and action codes, and the action codes are not clean because we have to import them from multiple sources.

I can handle most of them (like the average number of times users perform ActionCode 13).

But, here's what I'm having trouble with: The average number of times users perform an action that can be defined by any one of the action codes 61, 62, 700, or 701. This action can also be defined by using both action codes 84 and 85 or by using the three action codes 10, 11, and 12.

Here is what I have that handles the first set of action codes:

select
  average(cnt)
from
(
  select
    count(distinct(date)) as cnt
  from
    codes
  where
    actioncode in (61, 62, 700, 701)
  group by user
)

I use distinct date because some users code to multiple places and we get back more than one code for the action. How do I add "or they had both 84 and 85 on the same date"?

share|improve this question
1  
Hello kainaw and welcome to DBA.SE. I'm not 100% sure I understand your requirement. Say a user Bob has actions 61, 62, 84 and 85 recorded on the same date. That's supposed to count as 3 actions for Bob on that day? What if there's a 10 on one day, and 11 and 12 the next? If you could edit your code with some sample data and expected output, I think it would help clarify your point. –  Mat Apr 26 '13 at 15:01
    
Perhaps you mean "... or they had both 84 and 85 at the same date"? –  ypercube Apr 26 '13 at 15:06
    
Yes. an 84+85 on the same date counts the same as one of the simple codes, like 61. To make more sense, these actions are administrative. A 61 and 62 refer to "Billed a client, received payment, deposited payment." A 700 and 701 refer to "Received an invoice and made a payment". A 10 is "Billed a client", an 11 is "Received payment, and a 12 is "Deposited payment". So, 10+11+12 at the same time is equivalent to completing either 61 or 62. They are looking for people who complete the entire task - which is why it has to be on the same day. –  kainaw Apr 26 '13 at 16:53
add comment

1 Answer

up vote 1 down vote accepted

One of many possible ways:

SELECT  avg(cnt) AS avg_per_user
FROM   (
   SELECT usr, count(DISTINCT date) AS cnt
   FROM   codes c
   WHERE  actioncode in (61, 62, 700, 701)

   OR     actioncode = 84
   AND EXISTS (
      SELECT 1 FROM codes c1
      WHERE  c1.date = c.date
      AND    c1.actioncode = 85
      )

   OR     actioncode = 10
   AND EXISTS (
      SELECT 1 FROM codes c1
      WHERE  c1.date = c.date
      AND    c1.actioncode = 11
      )
   AND EXISTS (
      SELECT 1 FROM codes c1
      WHERE  c1.date = c.date
      AND    c1.actioncode = 12
      )
   GROUP  BY usr
   ) sub

We don't need parenthesis, since operator precedence works in our favor.

DISTINCT is only still necessary if there can be duplicates. The cases for (10 + 11 + 12) and (84 + 85) only produce a single row.

I am using usr instead of user, since I am not comfortable with abusing reserved words as identifiers. date isn't a good idea either.

share|improve this answer
    
Thanks. I haven't used "exists" before. It is very helpful in this case. –  kainaw Apr 26 '13 at 18:41
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.