i need a query tip

Question

i need a query tip

canuzun

SSC Enthusiast

Points: 107
More actions
June 22, 2010 at 5:43 am

#234085

Hi guys,
i have a transaction table 🙂
I need to find a sale frequency for each customer. In the end what i need to do is;
Find how much time passes between each sale of a customer.
A tmp table might look like this;
CustomerID TrnxID PreviousTrnxDate TrnxDate Difference
Customer1 Trnx1 null Date1 null
Customer1 Trnx2 Date1 Date2 (date2-date1)
Customer1 Trnx3 Date2 Date3 (date3-date2)
Customer2 Trnx4 null Date1 null
Customer2 Trnx5 Date1 Date2 (date2-date1)
.
.
goes on, i think you get the idea.
Now i can do this with some tmptables and loops, or cursors.
Any idea, how to do this, without cursors or loops?
Recursive CTE maybe?
Sorry for delay, here is the script.
Basically, how can i go from tmp to tmp2 in one select statement. Or can i?
CREATE TABLE #TMP (TRNX_ID INT,CUSTOMER_ID INT, TRNX_TIME DATETIME)
INSERT INTO #TMP VALUES(1,1,'20100601 11:23')
INSERT INTO #TMP VALUES(2,1,'20100601 11:33')
INSERT INTO #TMP VALUES(3,2,'20100601 11:43')
INSERT INTO #TMP VALUES(4,3,'20100601 11:53')
INSERT INTO #TMP VALUES(5,3,'20100601 14:13')
INSERT INTO #TMP VALUES(6,1,'20100601 15:33')
INSERT INTO #TMP VALUES(7,1,'20100601 17:33')
INSERT INTO #TMP VALUES(8,2,'20100601 19:43')
INSERT INTO #TMP VALUES(9,3,'20100601 21:00')
SELECT * FROM #TMP
CREATE TABLE #TMP2 (CUSTOMER_ID INT, TRNX_ID INT, PREVIOUS_TRNX_TIME DATETIME, CURRENT_TRNX_TIME DATETIME, TIMEDIFF_MINUTES INT)
INSERT INTO #TMP2 VALUES (1, 1, NULL, '20100601 11:23', 0)
INSERT INTO #TMP2 VALUES (2, 2, '20100601 11:23', '20100601 11:33', 10)
INSERT INTO #TMP2 VALUES (2, 6, '20100601 11:33', '20100601 15:33', 240)
INSERT INTO #TMP2 VALUES (2, 7, '20100601 15:33', '20100601 17:33', 120)
INSERT INTO #TMP2 VALUES (2, 2, NULL, '20100601 11:43', 0)
INSERT INTO #TMP2 VALUES (2, 8, '20100601 11:43', '20100601 19:43', 480)
INSERT INTO #TMP2 VALUES (3, 4, NULL, '20100601 11:53', 0)
INSERT INTO #TMP2 VALUES (3, 5, '20100601 11:53', '20100601 14:13', 20)
INSERT INTO #TMP2 VALUES (3, 1, '20100601 14:13', '20100601 21:00', 407)
SELECT * FROM #TMP2
SELECT CUSTOMER_ID, AVG(TIMEDIFF_MINUTES)
FROM #TMP2
WHERE TIMEDIFF_MINUTES > 0
GROUP BY CUSTOMER_ID
DROP TABLE #TMP
DROP TABLE #TMP2

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

wouldn't this just be a min( date), and count(*) grouped by customerid ,year(min(date))?? so you can calculate counts per year or some other period?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

LutzM SSC Guru Points: 107049 More actions · Answer 2

I'm not sure what the DDL for the transaction table looks like... Table def, sample data and expected result would help a lot here...

I'd probably use "ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY TrnxDate) as row" and do a self join with t1.row = t2.row-1

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

canuzun SSC Enthusiast Points: 107 More actions · Answer 3

Hi,

i can group by and find counts, thats no problem.

The problem is, i need to find the time period between customers transactions.

How to find, daily frequency for example. But day means a 24h period in this system, not 20100622 for ex.

Am i making any sense?

Thanks.

canuzun SSC Enthusiast Points: 107 More actions · Answer 4

lmu92 (6/22/2010)
I'm not sure what the DDL for the transaction table looks like... Table def, sample data and expected result would help a lot here...
I'd probably use "ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY TrnxDate) as row" and do a self join with t1.row = t2.row-1

i did exactly this,

but this works only for one customer. When query switches to another customer +1 will not work anymore 🙂

so i tried 2 ROW_NUMBER, one for entire resultset, other over customer.

But i couldnt find a way to do, without using a loop or cursor.

Thanks.

WayneS SSC Guru Points: 95460 More actions · Answer 5

You know, the people that help out here are all un-paid volunteers. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.

Lutz has already asked you for this... you even quoted him. Without some sample data to work off of, we just can't really help you resolve this.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

canuzun SSC Enthusiast Points: 107 More actions · Answer 6

WayneS (6/22/2010)
You know, the people that help out here are all un-paid volunteers. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.
Lutz has already asked you for this... you even quoted him. Without some sample data to work off of, we just can't really help you resolve this.

I will do this, i even started the create scripts but very busy day 🙁

I will post as soon as i can, sorry.

LutzM SSC Guru Points: 107049 More actions · Answer 7

canuzun (6/22/2010)
...
I will do this, i even started the create scripts but very busy day 🙁
I will post as soon as i can, sorry.

You know, I consider this forum as a resource for information about stuff I don't know, not stuff I simply don't have the time for to do it by myself... That's the reason why I asked for test data. The other reason is that I prefer to provide tested solutions.

Therefore, I'll take your note from above as a "standby request". I'll be here doing "my" part as soon as you're done doing "your" part. 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

canuzun SSC Enthusiast Points: 107 More actions · Answer 8

i have updated my post. it was never my intention to make you do my job, sorry for the misunderstanding.

this was my first topic here 🙂

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

Grouping by customer; get min date, max date, row count (transaction count).

Dividing the difference between the two dates by the row count will give you the average interval between sales.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

canuzun SSC Enthusiast Points: 107 More actions · Answer 10

Thanks for the tip.

Only curious, is it possible to create the second table, from the first one with single query?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

canuzun (6/28/2010)
Thanks for the tip.
Only curious, is it possible to create the second table, from the first one with single query?

Of course. SELECT...INTO...FROM...

Best practice is to check for existence of the new table first.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Victor Dvoryaninov SSC Veteran Points: 208 More actions · Answer 12

canuzun (6/28/2010)
Thanks for the tip.
Only curious, is it possible to create the second table, from the first one with single query?

Possible

SELECT

CUSTOMER_ID = T.CUSTOMER_ID,

TRNX_ID = T.TRNX_ID,

PREVIOUS_TRNX_TIME = P.TRNX_TIME,

CURRENT_TRNX_TIME = T.TRNX_TIME,

TIMEDIFF_MINUTES = DATEDIFF(MI, ISNULL(P.TRNX_TIME, T.TRNX_TIME), T.TRNX_TIME)

FROM #TMP T

OUTER APPLY

(

SELECT TOP 1 TRNX_TIME FROM #TMP

WHERE CUSTOMER_ID = T.CUSTOMER_ID

AND TRNX_TIME < T.TRNX_TIME

ORDER BY TRNX_TIME DESC

) P