Min Date based on comparison to running total

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 2005 Forums

Transact-SQL (2005)

Min Date based on comparison to running total

Author

Topic

samuelclay
Yak Posting Veteran

71 Posts

Posted - 2007-09-06 : 13:59:40

What I'm looking for is a way to find the date when customers pass a threshold for transactions processed.

Simplified, I have two tables, a customer table and a transaction per day table.

-- Company info, including the threshold value to compare the running totals to
CREATE TABLE #Company
( [CompanyID] int NOT NULL,
[Name] [varchar](150) NOT NULL,
[Threshold] [int] NOT NULL
)
-- List of total transaction per day for each company
CREATE TABLE #Transactions
( [TransactionID] [bigint] NOT NULL,
[CompanyID] [int] NOT NULL,
[Date] [smalldatetime] NOT NULL,
[Transactions] [int] NOT NULL,
)

here's some test data:

insert into #Company
( CompanyID, [Name], Threshold)
SELECT 1,'Alpha Dog School',15
UNION SELECT 2, 'Bee Hive Supplies',8
UNION SELECT 3, 'Clive''s Clarinets',55

insert into #Transactions
(TransactionID, CompanyID, Date, Transactions)
SELECT 1,1,'1/1/2007 12:00:00 AM',2
UNION SELECT 2,2,'1/1/2007 12:00:00 AM',1
UNION SELECT 3,3,'1/1/2007 12:00:00 AM',8
UNION SELECT 4,2,'1/2/2007 12:00:00 AM',1
UNION SELECT 5,1,'1/2/2007 12:00:00 AM',1
UNION SELECT 6,3,'1/2/2007 12:00:00 AM',15
UNION SELECT 7,1,'1/3/2007 12:00:00 AM',6
UNION SELECT 8,2,'1/3/2007 12:00:00 AM',9
UNION SELECT 9,3,'1/3/2007 12:00:00 AM',12
UNION SELECT 10,1,'1/4/2007 12:00:00 AM',3
UNION SELECT 11,2,'1/4/2007 12:00:00 AM',5
UNION SELECT 12,3,'1/4/2007 12:00:00 AM',11
UNION SELECT 13,1,'1/5/2007 12:00:00 AM',4
UNION SELECT 14,2,'1/5/2007 12:00:00 AM',4
UNION SELECT 15,3,'1/5/2007 12:00:00 AM',15
UNION SELECT 16,1,'1/6/2007 12:00:00 AM',4
UNION SELECT 17,2,'1/6/2007 12:00:00 AM',5
UNION SELECT 18,3,'1/6/2007 12:00:00 AM',19
UNION SELECT 19,1,'1/7/2007 12:00:00 AM',8
UNION SELECT 20,2,'1/7/2007 12:00:00 AM',9

a quick query:
select c.*,t.Date, t.Transactions
from #Company c
inner join #Transactions t
on c.CompanyID = t.CompanyID
where c.CompanyID = 1
order by Date

shows :
CompanyID Name Threshold Date Transactions
1 Alpha Dog School 15 2007-01-01 00:00:00 2
1 Alpha Dog School 15 2007-01-02 00:00:00 1
1 Alpha Dog School 15 2007-01-03 00:00:00 6
1 Alpha Dog School 15 2007-01-04 00:00:00 3
1 Alpha Dog School 15 2007-01-05 00:00:00 4
1 Alpha Dog School 15 2007-01-06 00:00:00 4
1 Alpha Dog School 15 2007-01-07 00:00:00 8

and adding the transactions, we see that they passed the threshold of 15 on 2007-01-05.

What I'd like would each company and the date they crossed the threshold value in the company table. So for this test data, it would look like : (assuming I can add properly this morning)
CompanyID CompanyName Date
1 Alpha Dog School 2007-01-05 00:00:00
2 Bee Hive Supplies 2007-01-03 00:00:00
3 Clive's Clarinets 2007-01-05 00:00:00

I would like to do this without a cursor, but I keep going around in circles trying to get the set logic straight. I have read some of the posts here about running totals, but didn't see a way to then pull a specific date from the list...

Anyway, I hope someone with a clearer head this morning could see the quick and easy query I am missing.

Samuel Clay
SQL/ASP Developer
samuel_clay@asp-dudes.com

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-06 : 15:42:28

don't you just need to do a group by with max(date) in your query?

i mean use your query as source

select company,max(date)
from (query)
where threshold=5
group by company

anyways... i haven't tested this so ...

--------------------
keeping it simple...

samuelclay
Yak Posting Veteran

71 Posts

Posted - 2007-09-06 : 16:36:53

quote:
Originally posted by jen

don't you just need to do a group by with max(date) in your query?

i mean use your query as source

select company,max(date)
from (query)
where threshold=5
group by company

anyways... i haven't tested this so ...

--------------------
keeping it simple...

not positive where you were going with this, but I don't want the max date... there could be three years worth of data, and they passed their threshold on day 5.. I want that date. Also the threshold is different for each company.

A problem (the problem?) I'm having using a derived table, is that the transaction table has hundreds of thousand rows (a record for every customer, for every day). So the sub query that calculates the running total is taking too long.

I have a partial solution (at least for my little example) using a udf. Going to test it on the actual data and see how it performs.

Thanks for looking guys... if anyone sees a clever (effecient) way to do this without a udf, I'd be happy to try it out...

Samuel Clay
SQL/ASP Developer
samuel_clay@asp-dudes.com

saad.ahnad@gmail.com
Starting Member

15 Posts

Posted - 2007-09-06 : 19:57:03

select c.CompanyID, c.[Name], c.Threshold, min(t.Date) threshold_date
from #Company c
left outer join
(
select t1.TransactionID, t1.CompanyID, t1.Date, t1.Transactions, sum(t2.Transactions) t2
from #Transactions t1 join #Transactions t2
on t1.CompanyID = t2.CompanyId
and t2.Date <= t1.Date
group by t1.TransactionID, t1.CompanyID, t1.Date, t1.Transactions
) t
on t.CompanyID = c.CompanyID
and t.t2 >= c.Threshold
group by c.CompanyID, c.[Name], c.Threshold

--
Saad Ahmad
saad.ahmad@gmail.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources