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.
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')