SQL Query to group sums by hour

[databit]

Ok I don't know what I'm doing
I'm a telecom guy that knows a little bit of SQL, enough to troubleshoot my systems and talk with DBA folk about stuff I need. But I need a query that I can't figure out. Wondering if anyone can help me out.
Here is the table

TableName: call_detail
ORIG_GROUP	ORIG_TIME	TERM_TIME
1			65200		72500
1			65800		65900
1			72000		72100
1			72304		72335
1			72421		72431
3			62500		63200
3			64000		84000

And I need a query that pulls something like so:

Hour			Group	Total Duration In Seconds
6:00am-6:59am	1		540
				 3		1620

7:00am-7:59am	1		1601
				 3		3600

8:00am-8:59am	3		2400

I'm ok running 24 queries (1 for each hour) if I have too. Here is the part that is tricky for me. If a call spans an hour, for instance the first call. The time needs to be slit up between the hours that it is in. So for this call 8 minutes or 480 seconds falls into the 6:00am-6:59am category.
To me this seems pretty simple from a programming standpoint. If I had to parse the data via C++, VB or perl this would be simple cause I could do conditional statements and loops and such. I just can't seem to figure out how to do it with SQL.

Any ideas/help/things I can plagerize

 

[SkipVought]

Hi,

what's the UNITS of ORIG_TIME?

Skip,

_[red][/red]

 

[databit]

oh good point. time is hh:mm:ss military time. so 65200 = 6:52:00 AM.

 

[SkipVought]

Then you must FIRST convert your STRING representation of time to TIME. Time values are in

units of DAYS, as TIME is part of DATE.

hOrig:

TimeSerial(Mid(iif(Len([ORIG_TIME])=5,"0","")&[ORIG_TIME],1,2),Mid(iif(Len([ORIG_TIME])=5,"0

","")&[ORIG_TIME],3,2),Mid(iif(Len([ORIG_TIME])=5,"0","")&[ORIG_TIME],5,2))/24

hTerm:

TimeSerial(Mid(iif(Len([TERM_TIME])=5,"0","")&[TERM_TIME],1,2),Mid(iif(Len([TERM_TIME])=5,"0

","")&[TERM_TIME],3,2),Mid(iif(Len([TERM_TIME])=5,"0","")&[TERM_TIME],5,2))/24


sDur: (hTerm - hOrig)/3600

Select Int(hOrig) & "00" & iif(hOrig<12,"am","pm") & "-" Int(hOrig) & ":59" & iif(hOrig<12,"am","pm"), sDur
From [YourTable]


Skip,

_[red][/red]

 

[databit]

I think I'm confused. how do I run this in an Access query or if not access then oracle query

 

[SkipVought]

Sorry. You need to MULTIPLY by 24 to get HRS & MULTIPLY the HRS Duration by 3600 to get SECONDS.

You will need to define this in the expression builder OR in the SQL Editor. I showed the expressions for
[tt]
hOrig: Origin HRS
hTerm: Termination HRS
sDur: Duration in SECS
[/tt]
to simplify the SQL...

Select Int(hOrig) & "00" & iif(hOrig<12,"am","pm") & "-" Int(hOrig) & ":59"  & iif(hOrig<12,"am","pm"), sDur
From [YourTable]

YOU must substitute the expressions for the values in the SQL.

Skip,

_[red][/red]

 

[databit]

Ah got it. Ok so this takes care of the time conversion portion. Any ideas on how to get it to group only the portion of the duration that happens in a time period into that time period?

 

[SkipVought]

Ah yes

Select Int(hOrig) & "00" & iif(hOrig<12,"am","pm") & "-" Int(hOrig) & ":59"  & iif(hOrig<12,"am","pm"), Sum(sDur)
From [YourTable]
Group By Int(hOrig) & "00" & iif(hOrig<12,"am","pm") & "-" Int(hOrig) & ":59"  & iif(hOrig<12,"am","pm")

Skip,

_[red][/red]

 

[gmmastros]

You do know that the answer to this question does depend on the database you are using.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[databit]

Yes and that's one reason I posted in multiple forums. I can use either oracle, sql server or access. The data is on an oracle server but I can import to either sql server or access. I only have read access to the oracle system so I'm a touch limited there.

 

[SkipVought]

Oracle:

Select Trunc(hOrig,0) || '00' || Case When hOrig<12 Then 'am' Else 'pm' End || '-' || Trunc(hOrig,0) || ':59'  || Case When hOrig<12 Then 'am' Else 'pm' End , Sum(sDur)
From [YourTable]
Group By Trunc(hOrig,0) || '00' || Case When hOrig<12 Then 'am' Else 'pm' End || '-' || Trunc(hOrig,0) || ':59'  || Case When hOrig<12 Then 'am' Else 'pm' End 
[/code

Skip,
[sub]
[glasses] [b][red][/red][/b]
[b][/b] [tongue][/sub]

 

[databit]

Skip,
Thanks for all your help. I think this is on the right track but can't seem to get it to work. Got 2 questions but only need an answer for 1 of them.
I've never worked with variables and databases before (like I said I'm a phone guy) so I'm having a bit of difficulty figuring out how to use them in either access or the oracle that you just posted.
So:
In Access I open up the query designer. I select the CallDetail table which has the 3 fields. Then I have to put in fields down in the lower part which if I select a field it lets me use the expression builder. Which fields do I select for the hOrig, hTerm and hDur expressions?

Or:
In oracle how do I create these 3 variables?

Ug. It sounds like I'm needing a hand holding through this process. Which I am :-/

 

[lespaul]

In Access, once you open a new query, change the view to SQL view and don't use the Query Design Grid. Then you can just cut and paste Skip's SQL, fix the table and field names if necessary and run the query.



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[SkipVought]

These are NOT really variables, just names. I NAMED them for the sake of clarity. The actual code must be substituted for the names.

I acutally had a few syntax problems.

Here are my results from your example data
[tt]
Expr1000 DUR
6:00am-6:59am 9660
7:00am-7:59am 101
[/tt]
Here's the SQL UNVARNISHED

Select 
  Int(TimeSerial(Mid(iif(Len(ORIG_TIME)=5,'0','') & ORIG_TIME,1,2),Mid(iif(Len(ORIG_TIME)=5,'0','') & ORIG_TIME,3,2),Mid(iif(Len(ORIG_TIME)=5,'0','') & ORIG_TIME,5,2))*24
) & ':00' & iif(TimeSerial(Mid(iif(Len(ORIG_TIME)=5,'0','') & ORIG_TIME,1,2),Mid(iif(Len(ORIG_TIME)=5,'0','') & ORIG_TIME,3,2),Mid(iif(Len(ORIG_TIME)=5,'0','') & ORIG_TIME,5,2))*24
<12,'am','pm') & '-' & Int(TimeSerial(Mid(iif(Len(ORIG_TIME)=5,'0','') & ORIG_TIME,1,2),Mid(iif(Len(ORIG_TIME)=5,'0','') & ORIG_TIME,3,2),Mid(iif(Len(ORIG_TIME)=5,'0','') & ORIG_TIME,5,2))*24
) & ':59'  & iif(TimeSerial(Mid(iif(Len(ORIG_TIME)=5,'0','') & ORIG_TIME,1,2),Mid(iif(Len(ORIG_TIME)=5,'0','') & ORIG_TIME,3,2),Mid(iif(Len(ORIG_TIME)=5,'0','') & ORIG_TIME,5,2))*24
<12,'am','pm')
, Sum((TimeSerial(Mid(iif(Len(TERM_TIME)=5,'0','') & TERM_TIME,1,2),Mid(iif(Len(TERM_TIME)=5,'0','') & TERM_TIME,3,2),Mid(iif(Len(TERM_TIME)=5,'0','') & TERM_TIME,5,2))*24 - TimeSerial(Mid(iif(Len(ORIG_TIME)=5,'0','') & ORIG_TIME,1,2),Mid(iif(Len(ORIG_TIME)=5,'0','') & ORIG_TIME,3,2),Mid(iif(Len(ORIG_TIME)=5,'0','') & ORIG_TIME,5,2))*24
)*3600) As DUR

FROM `D:\My Documents\vba\query stuff C`.`Query$` 

Group By 
  Int(TimeSerial(Mid(iif(Len(ORIG_TIME)=5,'0','') & ORIG_TIME,1,2),Mid(iif(Len(ORIG_TIME)=5,'0','') & ORIG_TIME,3,2),Mid(iif(Len(ORIG_TIME)=5,'0','') & ORIG_TIME,5,2))*24
) & ':00' & iif(TimeSerial(Mid(iif(Len(ORIG_TIME)=5,'0','') & ORIG_TIME,1,2),Mid(iif(Len(ORIG_TIME)=5,'0','') & ORIG_TIME,3,2),Mid(iif(Len(ORIG_TIME)=5,'0','') & ORIG_TIME,5,2))*24
<12,'am','pm') & '-' & Int(TimeSerial(Mid(iif(Len(ORIG_TIME)=5,'0','') & ORIG_TIME,1,2),Mid(iif(Len(ORIG_TIME)=5,'0','') & ORIG_TIME,3,2),Mid(iif(Len(ORIG_TIME)=5,'0','') & ORIG_TIME,5,2))*24
) & ':59'  & iif(TimeSerial(Mid(iif(Len(ORIG_TIME)=5,'0','') & ORIG_TIME,1,2),Mid(iif(Len(ORIG_TIME)=5,'0','') & ORIG_TIME,3,2),Mid(iif(Len(ORIG_TIME)=5,'0','') & ORIG_TIME,5,2))*24
<12,'am','pm')

Here is the SIMPLIFIED version for substitution...

hOrig: 
TimeSerial(Mid(iif(Len(ORIG_TIME)=5,'0','') & ORIG_TIME,1,2),Mid(iif(Len(ORIG_TIME)=5,'0','') & ORIG_TIME,3,2),Mid(iif(Len(ORIG_TIME)=5,'0','') & ORIG_TIME,5,2))*24

hTerm:

TimeSerial(Mid(iif(Len(TERM_TIME)=5,'0','') & TERM_TIME,1,2),Mid(iif(Len(TERM_TIME)=5,'0','') & TERM_TIME,3,2),Mid(iif(Len(TERM_TIME)=5,'0','') & TERM_TIME,5,2))*24


sDur: (hTerm - hOrig)*3600

Select 
  Int(hOrig) & ':00' & iif(hOrig<12,'am','pm') & '-' & Int(hOrig) & ':59'  & iif(hOrig<12,'am','pm')
, Sum(sDur) As DUR

FROM `D:\My Documents\vba\query stuff C`.`Query$` 

Group By
  Int(hOrig) & ':00' & iif(hOrig<12,'am','pm') & '-' & Int(hOrig) & ':59'  & iif(hOrig<12,'am','pm')

Skip,

_[red][/red]

 

[Golom]

I dispair of doing this with just SQL. I needed some code to complete the process

Private Sub Command1_Click()
    Dim rs As DAO.Recordset
    On Error Resume Next
    [COLOR=black cyan]' Create a Temporary table[/color]
    db.Execute "DROP TABLE CallDetailNew"
    db.Execute "CREATE TABLE CallDetailNew " & _
               "(OGroup Long, RStart Text(10), REnd Text(10), TStart Date, TEnd Date) "
    On Error GoTo 0
    
    Set rs = db.OpenRecordset("Select * From CallDetailNumbers")
    Do Until rs.EOF
        [COLOR=black cyan]' Stick One or more records into the[/color]
        [COLOR=black cyan]' new table for each one in the source.[/color]
        BuildNewRecords rs![OGroup], rs![TStart], rs![TEnd]
        rs.MoveNext
    Loop

Dim SQL As String
[COLOR=black cyan]' Build & Run the report Query[/color]
SQL = "Select (RStart & '-' & REnd) As [HOUR], OGroup, " & _
      "SUM(DateDiff(""s"", [TStart], [TEnd])) As [Seconds] " & _
      "From CallDetailNew " & _
      "Group By (RStart & '-' & REnd), OGroup " & _
      "Order By 1, 2 "
      Debug.Print SQL
    
End Sub

Where "BuildNewRecords" looks like this

Private Sub BuildNewRecords(Group As Long, ByVal XST As String, ByVal XET As String)
Dim SQL                         As String
Dim LT                          As Date
Dim RStart                      As String
Dim REnd                        As String
Dim ST                          As Date
Dim ET                          As Date
[COLOR=black cyan]' Convert the strings to times[/color]
ST = IIf(Len(XST) = 5, _
     TimeSerial(Val(Left(XST, 1)),Val(Mid(XST, 2, 2)),Val(Right(XST, 2))), _
     TimeSerial(Val(Left(XST, 2)),Val(Mid(XST, 3, 2)),Val(Right(XST, 2))))
ET = IIf(Len(XET) = 5, _
     TimeSerial(Val(Left(XET, 1)),Val(Mid(XET, 2, 2)),Val(Right(XET, 2))), _
     TimeSerial(Val(Left(XET, 2)),Val(Mid(XET, 3, 2)),Val(Right(XET, 2))))


If Hour(ST) = Hour(ET) Then
    [COLOR=black cyan]' Within the same hour[/color]
    RStart = Format(ST, "hh") & ":00" [COLOR=black cyan]' E.g. 6:00[/color]
    REnd = Format(ET, "hh") & ":59"   [COLOR=black cyan]' E.g. 6:59[/color]
    SQL = "INSERT INTO CallDetailNew (OGroup, RStart, REnd, TStart, TEnd) " & _
          "VALUES(" & Group & ",'" & RStart & "','" & REnd & "',#" & ST & "#,#" & ET & "#)"
    db.Execute SQL
Else
    [COLOR=black cyan]' Record spans more than one hour[/color]
    LT = TimeSerial(Hour(ST), 59, 59)
    Do While LT < ET
        RStart = Format(ST, "hh") & ":00"
        REnd = Format(LT, "hh") & ":59"
        SQL = "INSERT INTO CallDetailNew (OGroup, RStart, REnd, TStart, TEnd) " & _
              "VALUES(" & Group & ",'" & RStart & "','" & REnd & "',#" & ST & "#,#" & LT & "#)"
        db.Execute SQL
        ST = DateAdd("s", 1, LT)
        LT = TimeSerial(Hour(ST), 59, 59)
    Loop
    RStart = Format(ST, "hh") & ":00"
    REnd = Format(ET, "hh") & ":59"
    SQL = "INSERT INTO CallDetailNew (OGroup, RStart, REnd, TStart, TEnd) " & _
          "VALUES(" & Group & ",'" & RStart & "','" & REnd & "',#" & ST & "#,#" & ET & "#)"
    db.Execute SQL
End If

End With
End Sub

The temporary table has the form
[tt]
OGroup RStart REnd TStart TEnd
1 06:00 06:59 6:52:00 AM 6:59:59 AM
1 07:00 07:59 7:00:00 AM 7:25:00 AM
1 06:00 06:59 6:58:00 AM 6:59:00 AM
1 07:00 07:59 7:20:00 AM 7:21:00 AM
1 07:00 07:59 7:23:04 AM 7:23:35 AM
1 07:00 07:59 7:24:21 AM 7:24:31 AM
3 06:00 06:59 6:25:00 AM 6:32:00 AM
3 06:00 06:59 6:40:00 AM 6:59:59 AM
3 07:00 07:59 7:00:00 AM 7:59:59 AM
3 08:00 08:59 8:00:00 AM 8:40:00 AM
[/tt]

Final result looks like this
[tt]
HOUR OGroup Seconds
06:00-06:59 1 539
06:00-06:59 3 1619
07:00-07:59 1 1601
07:00-07:59 3 3599
08:00-08:59 3 2400
[/tt]