August 19, 2008 at 2:27 pm
Can this be done in T-SQL?
I have a table with the following information:
RowId CustomerNumber InvoiceDate TotalPurchased
1 130212001-07-03 00:00:00.0001
2 130212004-07-21 00:00:00.0001
3 130212004-09-21 00:00:00.0001
4 130212005-02-15 00:00:00.0001
5 130212006-07-17 00:00:00.0001
6 150262001-07-12 00:00:00.0002
7 150262003-04-19 00:00:00.0001
8 150262007-08-14 00:00:00.0001
9 150262007-11-16 00:00:00.0001
I need the sql to return customerNumber 13021 because that customer has made 3 or more purchases within a 12 month period (rows 2,3,4). However customerNumber 15026 should not be returned. The 12 month period can be any 12 month period, it doesn't have to be from 1/1 to 12/31. Each customerNumber can have a different number of rows in the table depending on how many purchases they have made.
August 19, 2008 at 2:49 pm
Is the number of purchases based on rows in the table or a sum of the TotalPurchased column?
Do you want to use some kind of rolling total?
I think does what you want.
[font="Courier New"]DECLARE @test TABLE (row_id INT, customer_no INT, order_date smalldatetime, total_purchased smallint)
DECLARE @start_date smalldatetime, @end_date smalldatetime
INSERT INTO @test
SELECT
1, 13021, '2001-07-03 00:00:00.000',1
UNION ALL
SELECT
2, 13021, '2004-07-21 00:00:00.000',1
UNION ALL
SELECT
3, 13021, '2004-09-21 00:00:00.000',1
UNION ALL
SELECT
4, 13021, '2005-02-15 00:00:00.000',1
UNION ALL
SELECT
5, 13021, '2006-07-17 00:00:00.000',1
UNION ALL
SELECT
6, 15026, '2001-07-12 00:00:00.000',2
UNION ALL
SELECT
7, 15026, '2003-04-19 00:00:00.000',1
UNION ALL
SELECT
8, 15026, '2007-08-14 00:00:00.000',1
UNION ALL
SELECT
9, 15026, '2007-11-16 00:00:00.000',1
SET @start_date = '7/21/04'
SET @end_date = DATEADD(YEAR, 1, @start_date)
SELECT
customer_no,
COUNT(row_id) AS orders,
@start_date AS start_date,
@end_Date AS end_date
FROM
@test
WHERE
order_date >= @start_date AND order_date < @end_date
GROUP BY
customer_no
HAVING
COUNT(row_id) >= 3
[/font]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 19, 2008 at 2:54 pm
It is based on the sum of totalPurchased. The problem is with the date. It can be any 12 month period, so I cannot use where the date is between ? and ?. For example it would also need to bring back the following customerNumber also:
10 13024 2002-07-03 00:00:00.000 1
11 13024 2005-08-21 00:00:00.000 1
12 13024 2005-09-21 00:00:00.000 1
13 13024 2006-01-15 00:00:00.000 1
14 13024 2007-07-17 00:00:00.000 1
August 19, 2008 at 8:25 pm
timbw,
First, look on Jack's code and learn to post the test data code.
Always.
If not Jack's post I would reply here.
Because I'm quite busy with my own tasks.
Second, there is the query you need :
(Jeff, don't kill me! I know, I know!
Just recon it's appropriate here - it's not triangle, it's trapezium and it's aggregated
🙂 )
SELECT T1.customer_no,
COUNT(T2.row_id) AS orders,
SUM(T2.total_purchased ) AS total_purchased,
T1.order_date AS start_date,
MAX(T2.order_date) AS end_date
FROM @test-2 T1
INNER JOIN @test-2 T2 ON T1.customer_no = T2.customer_no
AND T2.order_date >=T1.order_date
AND T2.order_date < DATEADD(YY, 1, T1.order_date )
GROUP BY
T1.customer_no, T1.order_date
HAVING
COUNT(T2.row_id) >= 3
Table @test-2 here is created and populated by Jack (see above).
Thanks again, Jack.
_____________
Code for TallyGenerator
August 20, 2008 at 7:41 am
timbw
It is based on the sum of totalPurchased
Sergiv
HAVING
COUNT(T2.row_id) >= 3
Sergiv a nice solution. I puzzled for over an hour how NOT to use a cursor and RBAR processing, and came up with a blank, but I think your counting of rows (which I take to be a count of the number of orders entered by a customer is not the criteria stated by timbw). I think your Having clause should be:
HAVING
SUM(T2.totalpurchased) >=3
Of course I could be misreading the question only the original poster could clarify what they truly need.
August 20, 2008 at 7:48 am
I think Sergiy's solution with bitbucket's modification should meet your needs. Of course I would think you still would want to at least have a start date because it will be a pig if you want forever.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 20, 2008 at 5:13 pm
Thanks everyone, I believe this works. It's my first time to post. I will remember to post the test code next time.
August 21, 2008 at 8:47 am
Based on the OP's (timbw's) request, the timeframe has to be ANY year, so I looked at this problem as the sum within one year of any given order, including the order date. Try the following:
DECLARE @test-2 TABLE (
row_id INT,
customer_no INT,
order_date smalldatetime,
total_purchased smallint
)
INSERT INTO @test-2 (row_id, customer_no, order_date, total_purchased)
SELECT 1, 13021, '2001-07-03 00:00:00.000', 1 UNION ALL
SELECT 2, 13021, '2004-07-21 00:00:00.000', 1 UNION ALL
SELECT 3, 13021, '2004-09-21 00:00:00.000', 1 UNION ALL
SELECT 4, 13021, '2005-02-15 00:00:00.000', 1 UNION ALL
SELECT 5, 13021, '2006-07-17 00:00:00.000', 1 UNION ALL
SELECT 6, 15026, '2001-07-12 00:00:00.000', 2 UNION ALL
SELECT 7, 15026, '2003-04-19 00:00:00.000', 1 UNION ALL
SELECT 8, 15026, '2007-08-14 00:00:00.000', 1 UNION ALL
SELECT 9, 15026, '2007-11-16 00:00:00.000', 1
;WITH YEAR_RANGES AS (
SELECT T.row_id, T.customer_no,
(SELECT SUM(T2.total_purchased)
FROM @test-2 AS T2
WHERE T2.order_date <= T.order_date AND
T2.order_date >= DATEADD(yy, -1, T.order_date) AND
T2.customer_no = T.customer_no) AS total_year_before,
(SELECT SUM(T3.total_purchased)
FROM @test-2 AS T3
WHERE T3.order_date >= T.order_date AND
T3.order_date <= DATEADD(yy, 1, T.order_date) AND
T3.customer_no = T.customer_no) AS total_year_after
FROM @test-2 AS T
)
SELECT DISTINCT customer_no
FROM YEAR_RANGES
WHERE total_year_before >=3 OR total_year_after >=3
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 21, 2008 at 7:21 pm
Interesting problem.
declare @Table table(
RowID int Identity(1,1),
CustNo int,
InvoiceDate datetime,
TotalPurchased int
);
insert @Table
(CustNo, InvoiceDate, TotalPurchased)
select 13021, '2001-07-03', 1 union all
select 13021, '2004-07-21', 1 union all
select 13021, '2004-09-21', 1 union all
select 13021, '2005-02-15', 1 union all
select 13021, '2006-07-17', 1 union all
select 15026, '2001-07-12', 2 union all
select 15026, '2003-04-19', 1 union all
select 15026, '2007-08-14', 1 union all
select 15026, '2007-11-16', 1;
select t1.CustNo, t1.InvoiceDate as WithinYearOf,
Sum(t1.TotalPurchased + t2.TotalPurchased) - Count(*) + 1 as QualifyingTotal
from @Table t1
join @Table t2
on t1.CustNo = t2.CustNo
and t1.InvoiceDate < t2.InvoiceDate
where DateDiff( d, t1.InvoiceDate, t2.InvoiceDate ) / 365 = 0
group by t1.CustNo, t1.InvoiceDate
having Sum(t1.TotalPurchased + t2.TotalPurchased) - Count(*) + 1 >= 3;
The little math manipulation is because some of the values are double counted. Be aware that the QualifyingTotal value may be greater than the actual sales in one year period beyond 3. But it is accurate to 3 and that is the value you need. If you want a completely accurate total, I'll leave that to you to work out. 😛
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply