Aggregating Consecutive fields

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

General SQL Server Forums

New to SQL Server Programming

Aggregating Consecutive fields

Author

Topic

d33pblu
Starting Member

1 Post

Posted - 2015-04-04 : 18:44:21

Problem 1
I have the following table which shows the location of a person at 1 hour intervals

Id EntityID EntityName LocationID Timex delta
1 1 Mickey Club house 0300 1
2 1 Mickey Club house 0400 1
3 1 Mickey Park 0500 2
4 1 Mickey Minnies Boutique 0600 3
5 1 Mickey Minnies Boutique 0700 3
6 1 Mickey Club house 0800 4
7 1 Mickey Club house 0900 4
8 1 Mickey Park 1000 5
9 1 Mickey Club house 1100 6

The delta increments by +1 every time the location changes.
I would like to return an aggregate grouped by delta as per example below.

EntityName LocationID StartTime EndTime
Mickey Club house 0300 0500
Mickey Park 0500 0600
Mickey Minnies Boutique 0600 0800
Mickey Club house 0800 1000
Mickey Park 1000 1100
Mickey Club house 1100 1200

I am using the following query (which works fine):

select
min(timex) as start_date
,end_date
,entityid
,entityname
,locationid
,delta
from
(
select
s1.timex
,(
select
max(timex)
from
[locationreport2] s2
where
s2.entityid = s1.entityid
and s2.delta = s1.delta
and not exists
(
select
null
from
[dbo].[locationreport2] s3
where
s3.timex < s2.timex
and s3.timex > s1.timex
and s3.entityid <> s1.entityid
and s3.entityname <> s1.entityname
and s3.delta <> s1.delta
)
) as end_date
, s1.entityid
, s1.entityname
, s1.locationid
, s1.delta
from
[dbo].[locationreport2] s1
where S1.entityid = 590
) Result

group by
end_date
, entityid
, entityname
, locationid
, delta
order by
1 asc

However I would like to not use the delta (it takes effort to calculate and populate it); instead I am wondering if there is any way to calculate it as part / whilst

running the query.
I wouldn’t mind using a view either.

Problem 2

I have the following table which shows the location of different people at 1 hour intervals

Id EntityID EntityName LocationID Timex Delta
1 1 Mickey Club house 0900 1
2 1 Mickey Club house 1000 1
3 1 Mickey Park 1100 2
4 2 Donald Club house 0900 1
5 2 Donald Park 1000 2
6 2 Donald Park 1100 2
7 3 Goofy Park 0900 1
8 3 Goofy Club house 1000 2
9 3 Goofy Park 1100 3

I would like to return an aggregate grouped by person and location.
For example

EntityID EntityName LocationID StartTime EndTime
1 Mickey Club house 0900 1100
1 Mickey Park 1100 1200
2 Donald Club house 0900 1000
2 Donald Park 1000 1200
3 Goofy Park 0900 1000
3 Goofy Club house 1000 1100
3 Goofy Park 1100 1200

What modifications do I need to the above query (Problem 1)?

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-04-04 : 21:13:58

I don't see why you want to avoid the delta field, as it plays an important part of solving your problem:

Problem 1:
select entityname
      ,locationid
      ,min(timex) as starttime
      ,max(timex)+59 as endtime
  from dbo.locationreport2
 group by entityname
         ,locationid
         ,delta
 order by entityname
         ,delta

Problem 2:
select entityid
      ,entityname
      ,locationid
      ,min(timex) as starttime
      ,max(timex)+59 as endtime
  from dbo.locationreport2
 group by entityid
         ,entityname
         ,locationid
         ,delta
 order by entityid
         ,delta

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources