SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help- complicated query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shoham
Starting Member

6 Posts

Posted - 10/15/2012 :  09:28:08  Show Profile  Reply with Quote
Hello
I have 2 tables:
orders
customers

I need to get "VIP customers"- meaning customers that ordered at least once a month since they registerd (there is a column [subscription_date] on customers)

I cant figure it out :(
any ideas?

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/15/2012 :  10:33:15  Show Profile  Reply with Quote
do you've a calendar table to determine the months? also what will be input? will there be a date range passed by user? ie year, quarter etc period that need to be considered in which customer ordered every month

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

malpashaa
Constraint Violating Yak Guru

Saudi Arabia
257 Posts

Posted - 10/15/2012 :  12:06:17  Show Profile  Reply with Quote
Try something like this:

SELECT C.customer_name, C.subscription_date, COUNT(*) AS orders_count
  FROM Customers AS C
       INNER JOIN
       (SELECT O.customer_id, DATEADD(MONTH, DATEDIFF(MONTH, 0, O.order_date), 0) AS date
          FROM Orders AS O
         GROUP BY O.customer_id, DATEADD(MONTH, DATEDIFF(MONTH, 0, O.order_date), 0)) AS O
       ON O.customer_id = C.customer_id
          AND O.date >= DATEADD(MONTH, DATEDIFF(MONTH, 0, C.subscription_date), 0)
          AND O.date <= GETDATE()
 WHERE C.subscription_date <= GETDATE()
 GROUP BY C.customer_name, C.subscription_date
HAVING COUNT(*) = DATEDIFF(MONTH, C.subscription_date, GETDATE()) + 1;




For us, there is only the trying. The rest is not our business. ~T.S. Eliot

Muhammad Al Pasha
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000