Help with building Query - 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)

Help with building Query

Author

Topic

Tamaraberg725
Starting Member

2 Posts

Posted - 2011-07-09 : 16:11:27

I have the following table and I need to write the SQL statement that
…

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-09 : 16:27:30

[code]select sum(Spend) from TransactionsTable ta
where CCreate_date >= '20110101'
and exists
(
select * from TransactionsTable tb
where tb.UserId = ta.UserId and
tb.CCreate_date < '20110101'
and tb.CCreate_date >= '20100101'
)[/code]

Tamaraberg725
Starting Member

2 Posts

Posted - 2011-07-09 : 18:04:32

So with this SQL, am I created one table and then querying against that new table with the original?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-09 : 18:59:09

;WITH cteSource(UserID, Spend)
AS (
SELECT UserID, SUM(CASE WHEN CCreate_date >= '20110101' THEN Spend ELSE 0 END)
FROM dbo.Transactions
WHERE CCreate_date >= '2010010' AND CCreate_date < '20120101'
GROUP BY UserID
HAVING MIN(CCreate_date) < '20110101'
AND MAX(CCreate_date) >= '20110101'
)
SELECT SUM(Spend) FROM cteSource

N 56°04'39.26"
E 12°55'05.63"

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-10 : 07:56:48

quote:
Originally posted by Tamaraberg725

So with this SQL, am I created one table and then querying against that new table with the original?

The query I posted is querying the same table twice. It sums the data in the table, but looks again at the same table to see if the user had any activity in 2010.

While my query would work perfectly well, the query that Peso posted would produce the same results and is likely to be much faster. You can sort of see it even without looking at any query plans by observing that in my query, I have to reference the table twice, but in Peso's query, it is referencing the table only once.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-11 : 17:30:11

For those interested in the now deleted original query, for which we have spent time on solving, here is the original problem statement.

quote:
I have the following table and I need to write the SQL statement that pulls the answer to this question:
How much did users who purchased in 2010 spend in 2011 to date?
Table name: Transactions (50 million entries)

ID CCreate_date UserID ProductID Quantity Spend

1 2009-04-01 1 1 1 $95.00
200 2010-06-24 45 2 2 $125.00
30000 2011-01-12 21098 3 1 $45.00

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources