CODEWITHSUNDEEP
sqldatetime
user1683899
user1683899

Reputation: 13

Anyone advise about SQL queries and grouping 15 min interval data in to days?

I'm hoping someone can answer this question about an SQL query I'm tyring to write, it's probably fairly basic but it's driving me nuts.

I have a table which contains data which is written every 15 minutes for some agents logged in to a telephone system. Here's a sample of some of the fields setup, there's more but I'm keeping things simple and quick to type

FieldName          Size  Type
BeginTimePeriodDt  93    datetime
User_Id            12    varchar
EndTimePeriodDt    93    datetime
TotalLoginTime      4    int

And a sample of what the data is. There's a lot more agents but I've kept it simple again.

BeginTimePeriodDT    User_ID EndTimePeriodDt      TotalLoginTime
2012-09-10 13:30:00  XXXXXX  2012-09-10 13:45:00  35
2012-09-10 13:30:00  YYYYYY  2012-09-10 13:45:00  900
2012-09-10 13:45:00  XXXXXX  2012-09-10 14:00:00  900
2012-09-10 13:45:00  YYYYYY  2012-09-10 14:00:00  600
2012-09-10 14:00:00  XXXXXX  2012-09-10 14:15:00  250
2012-09-10 14:00:00  YYYYYY  2012-09-10 14:15:00  95
2012-09-11 13:30:00  XXXXXX  2012-09-11 13:45:00  35
2012-09-11 13:30:00  YYYYYY  2012-09-11 13:45:00  900
2012-09-11 13:45:00  XXXXXX  2012-09-11 14:00:00  900
2012-09-11 13:45:00  YYYYYY  2012-09-11 14:00:00  600
2012-09-11 14:00:00  XXXXXX  2012-09-11 14:15:00  250
2012-09-11 14:00:00  YYYYYY  2012-09-11 14:15:00  95

As you can see every agent logged in gets a record written every 15 minutes. What I'm trying to do is takes those records and add up the log in time. Now if the table only contained 1 days records per agent then it would be a very easy thing to do, but...

the table contains records for multiple days 15 minute intervals per agent :-(

The bit I'm having the trouble with is figuring out how to group and add an agents intervals per day up and return a daily total.

So I want to end up with something like

2012-09-10 XXXXXX 1185
2012-09-11 XXXXXX 1185
2012-09-10 YYYYYY 2000
2012-09-11 YYYYYY 1300

(I know my maths don't add up to the data above, I can't be arsed working the sums out :-P)

I'd be much appreciated if someone could come up with an answer for me.

Upvotes: 1

Views: 489

Answers (2)

Danielle Paquette-Harvey
Danielle Paquette-Harvey

Reputation: 1791

This should do It

SELECT dateadd(dd,0, datediff(dd,0, beginTimePeriodDt)), UserId, SUM(TotalLoginTime)
FROM test 
group by dateadd(dd,0, datediff(dd,0, beginTimePeriodDt)), userId
order by 1, 2

Upvotes: 1

Dylan Smith
Dylan Smith

Reputation: 22255

SELECT YEAR(BeginTimePeriodDT), 
       MONTH(BeginTimePeriodDT), 
       DAY(BeginTimePeriodDT), 
       User_ID, 
       SUM(TotalLoginTime)
FROM MyTable
GROUP BY YEAR(BeginTimePeriodDT), 
         MONTH(BeginTimePeriodDT), 
         DAY(BeginTimePeriodDT), 
         User_ID

Upvotes: 1

Related Questions