Select Consecutive Dates - 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 2000 Forums

Transact-SQL (2000)

Select Consecutive Dates

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-12-09 : 06:49:26

a2l2en writes "This may stump you. ;-)
I need to write a query that pulls transactions that have happened for any userid, every day for a consecutive number of days.

I need the userid, countofconsecutivedays, transaction_type.

The format of the transactions are as follows:

userid date transaction
223344 Jan 6, 2005 jkl;
75499 Jan 1, 2001 jkl;
223344 Jan 7, 2005 asdf

Any ideas?"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-09 : 09:04:52

The sample data are not enough to give the answer. Post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2005-12-09 : 10:33:33

The following may get you started while I continue working. At this point it will identify the transactions that have a match the following day, and will return them all. At this point it doesn't count the number of consecutive days for it though it just shows those that have the same thing on the next day.

declare @mytable TABLE(userid int, actdate datetime, trantype char(1))
insert into @mytable
select 1, '1/1/05', 'A' UNION ALL
select 1, '1/3/05', 'B' UNION ALL
select 1, '1/4/05', 'B' UNION ALL
select 1, '2/1/05', 'A' UNION ALL
select 2, '2/1/05', 'C' UNION ALL
select 2, '2/2/05', 'C' UNION ALL
select 2, '2/3/05', 'C'

select t1.userid, t1.actdate, t1.trantype
from @mytable t1 inner join @mytable t2 on
t2.userid = t1.userid
and t2.trantype = t1.trantype
and datediff(day, t1.actdate, t2.actdate) = 1

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2005-12-09 : 11:48:13

This works. Probably could be improved but it does the trick.

set nocount on
declare @mytable TABLE(userid int, actdate datetime, trantype char(1))
insert into @mytable
select 1, '1/1/05', 'A' UNION ALL
select 1, '1/3/05', 'B' UNION ALL
select 1, '1/4/05', 'B' UNION ALL
select 1, '2/1/05', 'A' UNION ALL
select 2, '2/1/05', 'C' UNION ALL
select 2, '2/2/05', 'C' UNION ALL
select 2, '2/3/05', 'C' UNION ALL
select 2, '2/4/05', 'C' UNION ALL
select 2, '2/6/05', 'C' UNION ALL
select 2, '2/7/05', 'C'

declare @myResults table (id int identity(1,1), userid int, actdate datetime, trantype char(1), numtimes int)
-- pulls all of the ones that have a matching transaction after it into table variable
insert into @myResults
select t1.userid, t1.actdate, t1.trantype, 0
from @mytable t1 inner join @mytable t2 on
t2.userid = t1.userid
and t2.trantype = t1.trantype
and datediff(day, t1.actdate, t2.actdate) = 1

-- update the numtimes field to 2 if there is no transaction after it in the sequence
update @myResults
set numtimes = 2
from @myResults t1
where not exists(select id from @myresults t2 where t2.id = t1.id + 1 and t2.trantype = t1.trantype and datediff(day, t1.actdate, t2.actdate) = 1)

-- loop that finds all cases where numtimes is 0 but next item matches and moves the counter down to
-- the previous one and increments the number by 1 each time
declare @id int
select @id = t1.id from @myresults t1 inner join @myresults t2 on t2.id = t1.id + 1 and t2.userid = t1.userid and datediff(day, t1.actdate, t2.actdate) = 1 where t2.numtimes > 0 and t1.numtimes = 0
while (NOT @id IS NULL )
begin
-- update the numtimes to the next id's value + 1
update t1
set numtimes = t2.numtimes + 1
from @myresults t1 inner join @myresults t2 on t2.id = @id + 1 and t2.userid = t1.userid
where t1.id = @id

-- set the id that had a numtimes to 0 so it won't be counted anymore
update @myresults
set numtimes = 0
where id = @id + 1

-- clear the @id variable and see if it can find anymore with later sequences
select @id = NULL
select @id = t1.id from @myresults t1 inner join @myresults t2 on t2.id = t1.id + 1 and t2.userid = t1.userid and datediff(day, t1.actdate, t2.actdate) = 1 where t2.numtimes > 0 and t1.numtimes = 0
end

-- Return the results
select userid, actdate, trantype, numtimes
from @myresults where numtimes > 0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources