Calculating Total number of visits

Question

Calculating Total number of visits

DeJesus

Valued Member

Points: 59
More actions
February 5, 2011 at 9:19 pm

#233184

Hi All,
I need help calculating the number of visits in the last 12 months, example data:
CustomerID MonthID MonthDate NumOfVisits
001 200405 2004-05-01 1
001 200406 2004-06-01 2
001 200407 2004-07-01 1
001 200505 2005-05-01 5
001 200507 2005-07-01 10
002 200811 2008-11-01 3
002 200811 2008-11-20 1
The result should look like:
CustomerID MonthID NumOfVisitsInLast12Month
001 200405 1
001 200406 3
001 200407 4
001 200505 10
001 200507 16
002 200811 4
It looks like running total, isn't it? I am using SQL Server 2008
Thank you!

Viewing 8 posts - 1 through 8 (of 8 total)

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

Jeff Moden SSC Guru Points: 1004683 More actions · Answer 1

DeJesus (2/5/2011)
Hi All,
I need help calculating the number of visits in the last 12 months, example data:
CustomerID MonthID MonthDate NumOfVisits
001 200405 2004-05-01 1
001 200406 2004-06-01 2
001 200407 2004-07-01 1
001 200505 2005-05-01 5
001 200507 2005-07-01 10
002 200811 2008-11-01 3
002 200811 2008-11-20 1
The result should look like:
CustomerID MonthID NumOfVisitsInLast12Month
001 200405 1
001 200406 3
001 200407 4
001 200505 10
001 200507 16
002 200811 4
It looks like running total, isn't it? I am using SQL Server 2008
Thank you!

No. It doesn't look like a running total. A running total would build a sum for each row consisiting of the previous balance of all rows plus the current row value.

All you need to do is a simple sum using your CustomerID and MonthID.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

DeJesus Valued Member Points: 59 More actions · Answer 2

Thank you for the quick response but I don't know how using the SUM can return the result expected, it is clear I am missing something.

If you check row 001 200507 16 -from the expected output-, you will notice the 16 is the result of adding the value from records 200507,200505 and 200407 -example data-, because it is the set of records within 12 months.

What I haven’t been able to do is, for each record in the table go back twelve months and sum the values from NumOfVisits.

Thank you!

LutzM SSC Guru Points: 107049 More actions · Answer 3

I would start with two variables holding the start and end month of the period you're looking for.

Based on that I would build a subquery holding all months within that time frame. Then I would query the table to get the sum between a given date and 12 month before.

If you'd take the time to provide some ready to use sample data as described in the first link in my signature I could provide a coded version. But you should help us help you.

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]

Jeff Moden SSC Guru Points: 1004683 More actions · Answer 4

{edit} Sorry. Post didn't format properly. See below for replacement.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004683 More actions · Answer 5

DeJesus (2/6/2011)
Thank you for the quick response but I don't know how using the SUM can return the result expected, it is clear I am missing something.
If you check row 001 200507 16 -from the expected output-, you will notice the 16 is the result of adding the value from records 200507,200505 and 200407 -example data-, because it is the set of records within 12 months.
What I haven’t been able to do is, for each record in the table go back twelve months and sum the values from NumOfVisits.
Thank you!

Ah... misread your original post. The following will do as you ask. Note that you have an error in your original request... there are only 9 visits for 200505. Also notice how I created the test table. You'll get better answers more quickly if you post data in such a fashion. See the first link in my signature line below for an article on how to easily do such a thing.

/***************************************************************

Create the test data. This is NOT a part of the solution.

***************************************************************/

--===== Conditionally drop the test table to make reruns easier

IF OBJECT_ID('TempDB..#TestTable','U') IS NOT NULL

DROP TABLE #TestTable

;

--===== Create the test table

CREATE TABLE #TestTable

(

CustomerID VARCHAR(3),

MonthID INT,

MonthDate DATETIME,

NumOfVisits INT

)

;

--===== Populate the test table with data from the OP

INSERT INTO #TestTable

(CustomerID,MonthID,MonthDate,NumOfVisits)

SELECT '001',200405,'2004-05-01',1 UNION ALL

SELECT '001',200406,'2004-06-01',2 UNION ALL

SELECT '001',200407,'2004-07-01',1 UNION ALL

SELECT '001',200505,'2005-05-01',5 UNION ALL

SELECT '001',200507,'2005-07-01',10 UNION ALL

SELECT '002',200811,'2008-11-01',3 UNION ALL

SELECT '002',200811,'2008-11-20',1

;

/***************************************************************

Code to solve the problem.

***************************************************************/

--===== Preaggregate NumOfVisits by CustomerID, MonthID

-- We use a Temp Table to avoid a douple call on a CTE

-- which would cause the code of the CTE to execute twice.

SELECT CustomerID,

MonthDate = DATEADD(mm,DATEDIFF(mm,0,MonthDate),0),

NumOfVisits = SUM(NumOfVisits)

INTO #Preagg

FROM #TestTable

GROUP BY CustomerID,DATEADD(mm,DATEDIFF(mm,0,MonthDate),0)

;

--===== Now that we've aggregated data by month,

-- solve the problem.

SELECT agg.CustomerID,

MonthID = CONVERT(CHAR(6),agg.MonthDate,112),

ca.NumOfVisitsInLast12Month

FROM #Preagg agg

CROSS APPLY

(

SELECT NumOfVisitsInLast12Month = SUM(NumOfVisits)

FROM #Preagg ts

WHERE ts.CustomerID = agg.CustomerID

AND ts.MonthDate BETWEEN DATEADD(mm,-12,agg.MonthDate) AND agg.MonthDate

)ca

;

drop table #Preagg

Also, be aware that the code above necessarily has a Triangular Join in it which can lead to performance problems depending on how it's used. Also, this still isn't a "running total". It's a "floating window total".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

DeJesus Valued Member Points: 59 More actions · Answer 6

I am sorry, I shouldn't post my question/reply before reading the “Forum Etiquette: How to post data/code on a forum to get the best help”. Next time, I will follow the protocol.

Thank you for the solution 🙂

Jeff Moden SSC Guru Points: 1004683 More actions · Answer 7

It's ok... you're new. That's why I pointed it out. Thanks for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)