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)
 consecutive totals

Author  Topic 

00kevin
Yak Posting Veteran

78 Posts

Posted - 2006-10-02 : 14:59:52
Here is a tricky query that I just can't figure out today. I have a list of names and dates. What I need to do is total up the dates that are consecutive.

Given the following data

Jonathan 2006-09-21
Jonathan 2006-09-26
Joel 2006-09-26
Joel 2006-09-27
Joel 2006-09-29
Dave 2006-09-27
Dave 2006-09-21
Dave 2006-09-22
Dave 2006-09-23


The report will look like this

2 Joel
1 Jonathan
3 Dave

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-02 : 15:21:10
[code]
select
a.name,
count(*)+1
frim
MyTable a
join
MyTable b
on
a.name = b.name and
a.Date = dateadd(day,1,b.Date)
group by
a.name
order by
a.name
[/code]

CODO ERGO SUM
Go to Top of Page

00kevin
Yak Posting Veteran

78 Posts

Posted - 2006-10-02 : 15:37:29
thanks for your help Michael

only problem is that it doesn't exactly work, but that is my fault for not providing the correct set of data.

using the following data for Joe

2006-09-01
2006-09-05
2006-09-06
2006-09-07
2006-09-08
2006-09-11
2006-09-12
2006-09-13
2006-09-14
2006-09-15

I should only get total of 5, but I get 10 with that solution.

I think all I need to do is take the max of the two sets of consecutive totals.

Anyway, thanks again.

this problem is driving me nuts, but that could be due to the fact that I had drank too much whiskey last night :)



Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-10-02 : 18:17:58
I think you'll need to call a UDF multiple times, one per Name. THe UDF would SELECT the dates for a particular name (and other criteria as required) and accumulate a running count of the consective ones, keeping track of the longest consecutive and then returning it.

something like this (not tested or compiled, just typed in by hand)


create function MaxByName(@name varchar(100))
returns int
as
begin
declare @val int
declare @Max int
declare @lastDate datetime
set @val = 0
set @lastDate = '1/1/1900'
set @Max = 0

select @val = case when @lastDate =dateadd(dd,-1,date) then @val + 1 else 1 end,
@lastDate = date,
@max = case when @val > @max then @val else @max end
from
YourTable
where
Name = @name
order by
Date ASC

return @Max
end

go

select Name, dbo.MaxByName(Name) as Count
from YourTable
group by Name


It's almost like writing a custom aggregate function.
Add other parameters to your function (i.e., date ranges) to limit the values checked as necessary.

EDIT: it feels good to write some T-SQL here, it's been a while it seems .. I feel like all I do lately is yell at people: "Normalize! use datatypes! Presentation layer!" ... anyone who has joined this site in the last two years probably thinks I don't even KNOW T-SQL !

- Jeff
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-02 : 19:36:41
Here is a set based solution. I left out the names (since you didn't supply test data), but I'm sure you can add that into the joins with no problem.


declare @d table (date datetime primary key clustered )
declare @ds table (date datetime primary key clustered )
declare @de table (date datetime primary key clustered )

print 'Load test data'
insert into @d
select '2006-09-01' union all
select '2006-09-05' union all
select '2006-09-06' union all
select '2006-09-07' union all
select '2006-09-08' union all
select '2006-09-11' union all
select '2006-09-12' union all
select '2006-09-13' union all
select '2006-09-14' union all
select '2006-09-15'
order by 1

print 'Find Start of each series'
insert into @ds
select
a.date
from
@d a
left join
@d b
on
a.Date = dateadd(day,1,b.Date)
where
b.Date is null

print 'Find End of each series'
insert into @de
select
a.date
from
@d a
left join
@d b
on
a.Date = dateadd(day,-1,b.Date)
where
b.Date is null


print 'Find Longest and Shortest Series'
select
[Longest Series] =
max(datediff(dd,c.StartDate,EndDate))+1 ,
[Shortest Series] =
min(datediff(dd,c.StartDate,EndDate))+1
from
(
-- Match Start of series to End oi series
select
StartDate = a.date,
EndDate = min(b.date)
from
@ds a
join
@de b
on a.Date <= b.date
group by
a.Date
) c

Results:

Load test data

(10 row(s) affected)

Find Start of each series

(3 row(s) affected)

Find End of each series

(3 row(s) affected)

Find Longest and Shortest Series
Longest Series Shortest Series
-------------- ---------------
5 1

(1 row(s) affected)




CODO ERGO SUM
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-10-02 : 20:38:19
(my solution is set-based, too! )

- Jeff
Go to Top of Page

00kevin
Yak Posting Veteran

78 Posts

Posted - 2006-10-03 : 11:29:30
wow. you guys are sql gods.

thanks for your help!
Go to Top of Page
   

- Advertisement -