get number of consecutive days in table

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)

get number of consecutive days in table

Author

Topic

viguro
Starting Member

12 Posts

Posted - 2008-04-17 : 12:01:39

Hello,

I have a table with 3 columns: Item# | Date | ItemAmount.
Everyday there is a number of transactions entered. An Item# can only be entered once par day (if it has occurred that day).

What I want to do is to :
retrieve the number of total days where an Item has been entered for more than 2 consecutive days (for the month).

Example: if item I022 has been entered Monday and wed, then ignore, but if it's been entered Mon, Tues then return 2, if Mon, Tues, Wed then return 3 because the days are consecutive.

Does anyone have an idea.
thanks in advance,

bfoster
Starting Member

30 Posts

Posted - 2008-04-17 : 17:54:47

What if a particular item was ordered for the first 3 days of the month, then not ordered the next day and then ordered for the next 4 consecutive days. What value would you want to return in that case?

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-17 : 18:24:19

select t1.item#, cnt = max(convert(int,t2.date - t1.date))
from tbl t1 -- start of streak
join tbl t2 -- end of streak
on t1.item# = t2.item#
where t1.date < t2.date
and not exists (select * from tbl t3 where t3.item# = t1.item# and t3.date = t1.date - 1)
and not exists (select * from tbl t3 where t3.item# = t1.item# and t3.date = t2.date + 1)
and (select count(*) from tbl t3 where t3.item# = t1.item# and t3.date between t1.date and t2.date) = t2.date - t1.date
group by t1.item#

take out the group by and max to get all the sequences rather than the max for an item.

Not very efficient but my first thought

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

viguro
Starting Member

12 Posts

Posted - 2008-04-18 : 09:05:42

thanks the replies folks,

bfoster, if the item was ordered for the 3 first consecutive days and not the next day then ordered the next consecutives 4 days, the value would be 7 for that month.

I tally the number of days whenever there are 2 or more consecutive days.

thanks,

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-18 : 09:16:19

That would be

select t1.item#, cnt = sum(convert(int,t2.date - t1.date))
from tbl t1 -- start of streak
join tbl t2 -- end of streak
on t1.item# = t2.item#
where t1.date < t2.date
and not exists (select * from tbl t3 where t3.item# = t1.item# and t3.date = t1.date - 1)
and not exists (select * from tbl t3 where t3.item# = t1.item# and t3.date = t2.date + 1)
and (select count(*) from tbl t3 where t3.item# = t1.item# and t3.date between t1.date and t2.date) = t2.date - t1.date
group by t1.item#

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

viguro
Starting Member

12 Posts

Posted - 2008-04-18 : 10:11:02

thanks for you help nr, there is actually one table involved.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-18 : 10:12:22

Another version...

declare @tbl table (item# int, Date datetime)
insert @tbl
          select 1, '20080101'
union all select 1, '20080103'
union all select 2, '20080101'
union all select 2, '20080102'
union all select 3, '20080101'
union all select 3, '20080102'
union all select 3, '20080103'
union all select 4, '20080101'
union all select 4, '20080102'
union all select 4, '20080103'
union all select 4, '20080105'
union all select 4, '20080106'
union all select 4, '20080107'
union all select 4, '20080108'
union all select 5, '20071231' --watch out for this
union all select 5, '20080101'
union all select 5, '20080102'
union all select 5, '20080103'
union all select 5, '20080105'
union all select 5, '20080107'
union all select 5, '20080108'

select item#, count(*) as cnt
from @tbl a
where
    '20080101' <= date and date < '20080201' and
    exists (select * from @tbl where item# = a.item# and (date = a.date - 1 or date = a.date + 1))
group by item#

/* Results
item#       cnt
----------- -----------
2           2
3           3
4           7
5           5
*/

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

viguro
Starting Member

12 Posts

Posted - 2008-04-18 : 16:18:13

Ryan, NR, thanks. much appreciated!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources