CODEWITHSUNDEEP
sqlsql-serverdatetime
user3470214
user3470214

Reputation: 35

Count of people doing activity by 15 min increments - SQL Server

I have a table in SQL Server which is structured like:

ActivityName(varchar), UserId(varchar), ActivityStartTime(varchar), ActivityTimeInMinutes(smallint)

And has this sample data:

Cooking, James, 1300, 60
Cleaning, James, 1400, 120
Sleeping, James, 1600, 90
Driving, Jill, 1100, 120
Cleaning, Jill, 1300, 30
Cooking, Jill, 1330, 45
Sleeping, Jill, 1414, 120

How can I create a table in sql that counts the number of people doing each activity in 15 minute increments, so in the above example this would be the entry for 1300-1400:

TimePeriod, Cooking, Cleaning, Driving, Sleeping
1300 , 1 , 1 , 0 , 0
1315 , 1 , 1 , 0 , 0
1330 , 2 , 0 , 0 , 0
1400 , 1 , 1 , 0 , 0

Any help would be greatly appreciated!

Upvotes: 1

Views: 294

Answers (2)

Ron Smith
Ron Smith

Reputation: 3266

The data needs to be pivoted to get the desired results. This sql assumes that you are only interested in the time, (not the date) and that the times are all formated "hhmm". So 6AM would be rendered 0600. If this is not the case, this code would need to be updated to add the extra zero.

-- load your test data
declare @Activity table
    (
    ActivityName varchar(50),
    UserId varchar(50),
    ActivityStartTime varchar(50),
    ActivityTimeInMinutes smallint
    )
insert into @Activity values
    ('Cooking','James','1300',60),
    ('Cleaning','James','1400',120),
    ('Sleeping','James','1600',90),
    ('Driving','Jill','1100',120),
    ('Cleaning','Jill','1300',30),
    ('Cooking','Jill','1330',45),
    ('Sleeping','Jill','1414',120)

-- populate @TimeTable with 15 min increments based on your time data
declare @TimeTable table
    (
    StartTime datetime,
    EndTime datetime
    )
declare @ActivityStartTime datetime,
        @ActivityEndTime datetime
select @ActivityStartTime =
        min(left(ActivityStartTime,2) + ':' + right(ActivityStartTime,2)),
  @ActivityEndTime =
        max(dateadd(minute,ActivityTimeInMinutes,left(ActivityStartTime,2)
        + ':' + right(ActivityStartTime,2)))
from @Activity
while @ActivityStartTime <= @ActivityEndTime
    begin
        insert into @TimeTable values
            (@ActivityStartTime,dateadd(minute,15,@ActivityStartTime))
        set @ActivityStartTime = dateadd(minute,15,@ActivityStartTime)
    end

-- pivot results
select
    replace(left(convert(varchar(8),TimePeriod,108),5),':','') TimePeriod,
    isnull(Cooking,0) Cooking,
    isnull(Cleaning,0) Cleaning,
    isnull(Driving,0) Driving,
    isnull(Sleeping,0) Sleeping
from    (
        select
            t.StartTime TimePeriod,
            a.ActivityName,
            count(a.UserID) Cnt
        from @TimeTable t
            inner join @Activity a
                on cast(left(ActivityStartTime,2) + ':' +
                    right(ActivityStartTime,2) as datetime) < t.EndTime
                and dateadd(minute,a.ActivityTimeInMinutes,
                    cast(left(ActivityStartTime,2) + ':' +
                    right(ActivityStartTime,2) as datetime)) > t.StartTime
        group by 
            t.StartTime,
            a.ActivityName
        ) t
        pivot   (
                sum(Cnt)
                for ActivityName in([Cooking],[Cleaning],[Driving],[Sleeping])
                ) p
order by TimePeriod

Upvotes: 2

Metaphor
Metaphor

Reputation: 6405

This should do it:

SELECT 
    UserId, 
    ActivityName,
    DATETIMEFROMPARTS(
        YEAR(ActivityStartTime),
        MONTH(ActivityStartTime),
        DAY(ActivityStartTime),
        HOUR(ActivityStartTime),
        (MINUTE(ActivityStartTime)/15) * 15,
        0,
        0
    ) AS ActivityStartQtrHour
    SUM(ActivityTimeInMinutes) AS ActivityDurationMinutes
GROUP BY 
    UserId, 
    ActivityName,
    DATETIMEFROMPARTS(
        YEAR(ActivityStartTime),
        MONTH(ActivityStartTime),
        DAY(ActivityStartTime),
        HOUR(ActivityStartTime),
        (MINUTE(ActivityStartTime)/15) * 15,
        0,
        0
    )

Not tested as there was no test data, but you should be able to follow it in any case.

Upvotes: 1

Related Questions