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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Date Filter Problem?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

akpaga
Constraint Violating Yak Guru

USA
311 Posts

Posted - 10/15/2012 :  15:06:22  Show Profile  Reply with Quote
Hi friends,

I have a peculiar problem with dates

I have table called customers

Customer_name,Customer_id,CustomerD_date,CustomerE_Date,CustomerF_date
John,123,09/09/2012,null,null
James,345,null,09/09/2012,null
Jake,789,8/19/2012,null,09/09/2012
Jake,789,03/19/2012,null,04/22/2012


I want a simple query to count the number of customer that have come to us within a certain date range(ex.08/09/2012-09/23/2012). The count should be 3 even though the Date fields are different they still fall with the date range entered by the use.


How can check the three date fields in my where condition to see if they fall with in the date range provided by user and then count it accordingly..Thank you

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/15/2012 :  15:29:22  Show Profile  Reply with Quote
What is the definition of "a customer that have come to us"? If that means that if any of the three dates are within the date range, then the following query will count those for you:
SELECT
	COUNT(*)
FROM
	Customers
WHERE
	CustomerD_date BETWEEN @startDate AND @endDate
	OR 
	CustomerE_Date BETWEEN @startDate AND @endDate
	OR 
	CustomerF_date BETWEEN @startDate AND @endDate	
Go to Top of Page

akpaga
Constraint Violating Yak Guru

USA
311 Posts

Posted - 10/15/2012 :  15:47:21  Show Profile  Reply with Quote
thank you for your response Sunitha..but Sorry..I missed to ask the main part

Customer_name,Customer_id,CustomerD_date,CustomerE_Date,CustomerF_date,CustomerD_Purchase,CustomerE_Purchase,CustomerF_purchase
John,123,09/09/2012,null,null,4,null,null
James,345,null,09/09/2012,null,null,5,null
Jake,789,8/19/2012,null,09/09/2012,null,,null,6
Jake,789,03/19/2012,null,04/22/2012,12,null,8

Along with count i need the Total of the Purchases Which is 4+5+6=15
but they should be calculated where their respective date has a date in them ie for John has rev_D date hence his revd_miles should be taken in to account and so on..

Hope i am clear..
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.05 seconds. Powered By: Snitz Forums 2000