Query for seasonal data - 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)

Query for seasonal data

Author

Topic

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2011-03-07 : 14:11:40

Hi All -

I'm trying to figure out a way to return some customers that have activity during a specific date range - Nov through Jan. Basically, holiday season traffic.

I thought that I'd create some cte or sub-select that would only have the data for the months of Nov, Dec and Jan. But, I keep getting some customers that also have data for other months of the year.

The table I have is like:

CustomerID (int), ActivityDate (datetime) and Traffic (bigint)

So, for example, if i have the following two customers (1001 and 12001), I just want 12001 in my results.


CustomerID     ActivityDate   Traffic
1001           2010-07-01     47595
1001           2010-08-01     456
1001           2010-09-01     2556
1001           2010-10-01     5958484
1001           2010-11-01     5959696
1001           2010-12-01     7658594
1001           2011-01-01     598586
1001           2011-02-01     457686
12001          2010-11-01     576869
12001          2010-12-01     627475

Thanks for any help or suggestions.
- will

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-07 : 14:16:01

Why are these records excluded?
1001 2010-11-01 5959696
1001 2010-12-01 7658594

Jim

Everyday I learn something that somebody else already knew

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2011-03-07 : 14:29:57

quote:
Originally posted by jimf

Why are these records excluded?
1001 2010-11-01 5959696
1001 2010-12-01 7658594

Hi

The reason that the two are excluded is because the request I've received is for ONLY those customers that have traffic during the holiday date range. They're trying to identify the customers that only have activity during this time of the year and they don't want to include those that have activity during the holiday AND also during other parts of the year.

thanks - will

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-07 : 14:46:35

There's probably a better way to do this, but I think this work.

SELECT
t1.*
FROM @Table t1
INNER JOIN
(
select customerid ,min(month(activitydate)) AS TdATE
from @table
group by customerid
having max(case when month(activitydate) not in (11,12,1) then 1 else 0 end) = 0
) t2
ON t1.customerid = t2.customerid

Jim

Everyday I learn something that somebody else already knew

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2011-03-07 : 14:47:53

Okay, so this is what I ended up doing.

I created a cte of the customer id's that have activity in the months of Feb through October. I then use that cte result in a left join to exclude from the full data.

It seems to work all right, not sure if it is the best way.


; with cte_CustToExclude (CustomerID)
	As	(Select Distinct CustomerID
		From	CustTraffic
		Where	Datepart(month, ActivityDate) in (2, 3, 4, 5, 6, 7, 8, 9, 10)
		)
	Select	ct.CustomerID
			, Case
			When Len(LTrim(Rtrim(Str(datepart(month, ct.ActivityDate))))) = 2
				Then LTrim(Rtrim(Str(datepart(year, ct.ActivityDate)))) + '-' 
				+ LTrim(Rtrim(Str(datepart(month, ct.ActivityDate))))
			Else LTrim(Rtrim(Str(datepart(year, ct.ActivityDate)))) + '-0' 
				+ LTrim(Rtrim(Str(datepart(month, ct.ActivityDate))))
			End
			As YearMonth
			, sum(ct.Traffic) as Traffic
	from	CustTraffic ct
	Left 
	Join	cte_CustToExclude cx
			on ct.CustomerID = cx.CustomerID
	Where	cx.CustomerID is null
	Group	By ct.CustomerID
			, Case
				When Len(LTrim(Rtrim(Str(datepart(month, ct.ActivityDate))))) = 2
					Then LTrim(Rtrim(Str(datepart(year, ct.ActivityDate)))) + '-' 
					+ LTrim(Rtrim(Str(datepart(month, ct.ActivityDate))))
				Else LTrim(Rtrim(Str(datepart(year, ct.ActivityDate)))) + '-0' 
					+ LTrim(Rtrim(Str(datepart(month, ct.ActivityDate))))
				End
	order	by ct.CustomerID
			, YearMonth

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2011-03-07 : 15:11:04

quote:
Originally posted by jimf

There's probably a better way to do this, but I think this work.

SELECT
t1.*
FROM @Table t1
INNER JOIN
(
select customerid ,min(month(activitydate)) AS TdATE
from @table
group by customerid
having max(case when month(activitydate) not in (11,12,1) then 1 else 0 end) = 0
) t2
ON t1.customerid = t2.customerid

Thanks Jim. I was able to get the results with the cte method, but I'll try yours now too and compare results.

Thanks so much for the help.
- will

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-03-07 : 17:26:11

A NOT EXISTS predicate might perform better.[CODE]select t.*
from @Table t
where month(activitydate) in (11,12,1)
and NOT EXISTS (
select *
from @Table t1
where t.CustomerID = t1.CustomerID
and month(activitydate) between 2 and 10
)[/CODE]Guess and Test!

=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources