Count on recurring - 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

General SQL Server Forums

New to SQL Server Programming

Count on recurring

Author

Topic

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2012-09-20 : 10:09:03

How can I query to count how many people purchase product A and product B within 6 months time?

Example:
Customer123 bought productA last 6 months.
Customer123 bought productA again today.
Want to count how many productA bought by same customer.

How to query for recurring customer bought same products?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-20 : 11:20:03

is time range an input from user?

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

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2012-09-20 : 18:35:27

Yes.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-20 : 23:15:16

so what should be output?

all data that fall within 6 months (ie + /- 6 months) from given range?

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

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2012-09-20 : 23:27:00

user should select date range let say from 2012-01-01 till 2012-01-31

would like to track back this range of user, did they bought the same product in within 2011-06-01 till 2011-06-30 (6 months ago)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-20 : 23:33:53

[code]
DECLARE @StartDate datetime,@EndDate datetime

SELECT @StartDate='20120101',@EndDate='20120131'

SELECT t.*
FROM YourTable t
INNER JOIN
(
SELECT Customer
FROM YourTable
WHERE Product='ProductA'
GROUP BY Customer
HAVING COUNT(*) > 1
)s
ON s.Customer = t.Customer
[/code]

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

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2012-09-20 : 23:39:23

This will return every date?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-21 : 12:24:30

sorry what do you mean by every date?

show your expect output format

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

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2012-09-22 : 00:55:17

Customer123 but productA on 2012-01-01, would like to track back the same user(Customer123) whether this user bought the same product 6 months ago which is 2011-06-01

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-22 : 12:43:35

[code]
SELECT t.*
FROM YourTable t
CROSS APPLY (SELECT COUNT(*) AS Cnt
FROM YourTable
WHERE Customer = t.Customer
AND Product = t.Product
WHERE datebought > = dateadd(mm,datediff(mm,0,t.date)-6,0)
AND datebought < dateadd(mm,datediff(mm,0,t.date)-5,0)
)s
WHERE DateBought >= @StartDate
AND DateBought < @EndDate +1
AND Cnt >0
[/code]

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

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2012-09-22 : 13:19:01

Sorry im new in sql.
May i know the last -6,0 is it representing the month?

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2012-09-22 : 13:19:05

Sorry im new in sql.
May i know the last -6,0 is it representing the month?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-22 : 16:27:18

quote:
Originally posted by peace

Sorry im new in sql.
May i know the last -6,0 is it representing the month?

yep it is

understand logic with explanation here

http://visakhm.blogspot.com/2012/07/generate-datetime-values-from-integers.html

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources