Date Filter Problem? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Date Filter Problem?

Author

Topic

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2012-10-15 : 15:06:22

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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-15 : 15:29:22

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

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2012-10-15 : 15:47:21

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources