Audit trail in SQL

Question

Following is the problem statement:

I have a database table with entries that look like (this is fictional table :-) ):

Company    Customer ID         Date          

Amazon            1         2012-08-30      
Amazon            2         2012-09-30
Amazon            2         2012-09-1
amazon            2         2012-12-10
eBay              1         2012-03-10
eBay              10        2012-01-01
BananaR           1         2012-02-20  
BananaR           1         2012-02-28
BananaR           1         2012-05-10
BananaR           1         2012-05-25

Now each entry in this table denotes the security check done for online transaction. So the 1st row would mean that "For Customer ID 1, Amazon did a online security check on 2012-08-30 for a transaction".

So, I need to figure how many additional security checks where done for the same Customer and company combination in every month after the 1st security check. i.e.

For customer 2, Amazon did its 1st check on 2012-09-01, then the additional ones would be ones on 2012-09-30 and 2012-12-10. So expected answer is:

Company      CustomerID     Month            Additional checks
Amazon          1            2012-08               0
Amazon          2            2012-09               1
Amazon          2            2012-12               1
eBay            1            2012-03               0
eBay            10           2012-01               0
BananaR         1            2012-02               1
BananaR         1            2012-05               2

My existing query outputs for additional checks since 1st check done in every month, but I do not know if I can back track to previous months. So my query looks like:

select company as 'Company',
       customer_id as 'Customer',
       format_date as 'Month',
       (add_count-1) as 'Additioanl Checks'
  from (select count(*) as add_count,
               company,
               customer_id,
               to_char(date, 'yyyy-mm') as format_date
          from my_table
          group by company, customer_id, format_date)

The result is get now is:

Company      Customer         Month       Additional Checks
Amazon         1               2012-08         0
Amazon         2               2012-09         1
**Amazon       2               2012-12         0**
eBay           1               2012-03         0
eBay           10              2012-01         0
BananaR        1               2012-02         1
**BananaR      1               2012-05         1**

The rows in ** are incorrect since they are counting for additional checks per month basis and not since the 1st check was done in previous months.

Answer 1

10:27:57 HR@vm_xe> l                                                              
  1  select company                                                               
  2         ,customer_id                                                          
  3         ,mon                                                                  
  4         ,sum(sign(rn-1)) cnt                                                  
  5  from (                                                                       
  6    select company                                                             
  7           ,customer_id                                                        
  8           ,to_char(dt, 'yyyy-mm') mon                                         
  9           ,row_number() over(partition by company, customer_id order by dt) rn
 10      from security_checks                                                     
 11  )                                                                            
 12  group by company, customer_id, mon                                           
 13* order by company, customer_id                                                
10:27:57 HR@vm_xe> /                                                              

COMPANY         CUSTOMER_ID MON            CNT                                    
--------------- ----------- ------- ----------                                    
Amazon                    1 2012-08          0                                    
Amazon                    2 2012-09          1                                    
Amazon                    2 2012-12          1                                    
BananaR                   1 2012-02          1                                    
BananaR                   1 2012-05          2                                    
eBay                      1 2012-03          0                                    
eBay                     10 2012-01          0                                    

7 rows selected.                                                                  

Elapsed: 00:00:00.01                                  

Answer 2

If I correctly understood your logic, you want to subtract 1 from the count for the first month, but not for subsequent months. Here is one way:

select Company,
       Customer,
       to_char(date, 'yyyy-mm') as month,
       count(*) - (case
                     when to_char(min(MinDate), 'yyyy-mm') = to_char(date, 'yyyy-mm')
                     then 1
                     else 0
                   end)
  from (select t.*,
               min(date) over (partition by company, customer) as MinDate
          from t) t
  group by Company, Customer, to_char(date, 'yyyy-mm')