February 4, 2008 at 10:50 am
Hi, first of all, thanks for reading my first post :).
I am currently trying to create a query to find the number of active customers on each month.
Business rule we have is, if the customer purchased an item in past 3 month, it is considered as active. So for example, if a customer made a purchase on month 12, and he made any purchased in month 10 through 12, then he is counted as active customer for month 12.
We have Invoice table, wiht customer_no, invoice_no, purchase_date fields. I am not really sure how to write a query for this. Do I use 'exist' or 'in' with subqeury?
How would you guys write a query for this???
Big Thanks.
February 4, 2008 at 2:10 pm
sdhan79 (2/4/2008)
Hi, first of all, thanks for reading my first post :).I am currently trying to create a query to find the number of active customers on each month.
Business rule we have is, if the customer purchased an item in past 3 month, it is considered as active. So for example, if a customer made a purchase on month 12, and he made any purchased in month 10 through 12, then he is counted as active customer for month 12.
We have Invoice table, wiht customer_no, invoice_no, purchase_date fields. I am not really sure how to write a query for this. Do I use 'exist' or 'in' with subqeury?
How would you guys write a query for this???
Big Thanks.
select *
from custoumer_table c
where exists
( select * from invoice_table i
where i.customer_no = c.customer_no
and purchase_date between 'date1' and 'date2'
)
* Noel
February 4, 2008 at 3:26 pm
Thank you noeld!! can I ask you one more???
If I am understanding this right, your query sample will look for
any customer who made purchase between given periods (let say month 3 and month 5).
But what if customer didn't make any purchase in month 5 and made purchase in month 3 or 4? Those customers still get selected correct?
What I am trying to do is..
a. select the every distinct customer who made their purchase in month 5
b. go back to past 3 months (Month 2, 3, 4)
c. see if same customer id exsits in those past 3 month period.
again thank you noeld for the advice!!!:)
February 4, 2008 at 3:42 pm
sdhan79 (2/4/2008)
Thank you noeld!! can I ask you one more???If I am understanding this right, your query sample will look for
any customer who made purchase between given periods (let say month 3 and month 5).
But what if customer didn't make any purchase in month 5 and made purchase in month 3 or 4? Those customers still get selected correct?
What I am trying to do is..
a. select the every distinct customer who made their purchase in month 5
b. go back to past 3 months (Month 2, 3, 4)
c. see if same customer id exsits in those past 3 month period.
again thank you noeld for the advice!!!:)
that makes a bit of a difference but not too much ... 😀
select *
from custoumer_table c
where
exists
( select * from invoice_table i
where i.customer_no = c.customer_no
and purchase_date between 'date1' and 'date2' --- Where date1 and date2 are the LAST Month
--- (lets say Month 5)
)
and
exists
( select * from invoice_table i
where i.customer_no = c.customer_no
and purchase_date between 'date3' and 'date4' --- Where date3 and date4 are the
--- LAST THREE Months
)
Is that what you want?
Or do you also need to verify that in the "past 3 months" the customer purchased once every month ?
* Noel
February 4, 2008 at 4:14 pm
Thahk you Thank you Thank You Sir!!!
This is my version:
select count(Customer_id)
from InvoiceHeader
where month(PostingDate) = 5
and year(PostingDate)= '2007'
and exists
( select *
from InvoiceHeader
where (month(PostingDate) between 2 and 3)
and year(PostingDate)= '2007'
)
Our InvoiceHeader table had all the necessary information i need so I didn't join them with customer table :).
Now, one last question. This has to do with reporting.
Would it be possible to create output like this?
Month Count
-----------------------
1 12
2 30
3 23
.....
or run a query for the each month and make it in excel?
Reason I am asking this is because, if I was to build a report based on above query with
Reporting Services, i am not sure which way would work better for the reporting purpose.
Anyway, big thanks to Joel again. Thank you very much for your help. You saved me from my intern job!!!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply