Group By or Merge from table (15 minutes time interval)

[Wolfdzn]

The following table contains a row for every 15 minutes and I need to merge four rows (15,30,45,60) minutes interval into one hour, is there a way to do it ?

row_date starttime dept. Calls
11/24/2017 0 613 1 ------> Merge all four rows into One Hour 00:00
11/24/2017 15 613. 1
11/24/2017 30 613. 1
11/24/2017 45 613. 2

Date. Hour. Calls
Expected result : 11/24/2017. 01:00. 5



11/24/2017 100 613------> Merge all four rows into One Hour 01:00
11/24/2017 115 613
11/24/2017 130 613
11/24/2017 145 613
11/24/2017 200 613------> Merge all four rows into One Hour 02:00
11/24/2017 215 613
11/24/2017 230 613
11/24/2017 245 613
11/24/2017 300 613
11/24/2017 315 613
11/24/2017 330 613
11/24/2017 345 613
11/24/2017 400 613
11/24/2017 415 613
11/24/2017 430 613
11/24/2017 445 613
11/24/2017 500 613

 

[SkipVought]

Hi,

Just curious.

What’s the mathematical result for starttime...

100 - 45

...which is not the same as...

1:00 - 0:45

???

Also your requirement is not clear.

This...
[pre]
11/24/2017 0 613 1 ------> Merge all four rows into One Hour 00:00
11/24/2017 15 613. 1
11/24/2017 30 613. 1
11/24/2017 45 613. 2
[/pre]
...is different than this...
[pre]
I need to merge four rows (15,30,45,60)
[/pre]

The first starts with starttime 0, while the second starts with starttime 15.

???


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Wolfdzn]

this is a 24 hour military time 0-15 min, 15-30 min, 30-45 min and 45 to 60 min.

every hour there are four rows 15 minutes a part

I need to group all four rows in to one hour and calculate no of calls instead of showing four rows..

for example : the following four rows are from 01:00 am - 02:00 am

11/24/2017 115 613
11/24/2017 130 613
11/24/2017 145 613
11/24/2017 200 613

 

[fredericofonseca]

you didn't supply the table definition so making a few assumptions on it.

Main aspect of your requirement is to convert the starttime to a fixed size string (4 chars) with leading zeros
this can be done as
left(right('0000' + convert(varchar(4), t.starttime), 4), 2) if the field is a number
or
left(right('0000' + t.starttime, 4), 2) if field is a char

once its done standard rules for group by apply

if object_id('tempdb..#temp') is not null
drop table #temp

create table #temp
(row_date date
, starttime int
, dept int
, calls int
)

insert into #temp select convert(date, '20171124', 112), 100, 613, 17
insert into #temp select convert(date, '20171124', 112), 115, 613, 1
insert into #temp select convert(date, '20171124', 112), 130, 613, 5
insert into #temp select convert(date, '20171124', 112), 145, 613, 17
insert into #temp select convert(date, '20171124', 112), 200, 613, 2
insert into #temp select convert(date, '20171124', 112), 215, 613, 10
insert into #temp select convert(date, '20171124', 112), 230, 613, 14
insert into #temp select convert(date, '20171124', 112), 245, 613, 13
insert into #temp select convert(date, '20171124', 112), 300, 613, 5
insert into #temp select convert(date, '20171124', 112), 315, 613, 18
insert into #temp select convert(date, '20171124', 112), 330, 613, 19
insert into #temp select convert(date, '20171124', 112), 345, 613, 11
insert into #temp select convert(date, '20171124', 112), 400, 613, 7
insert into #temp select convert(date, '20171124', 112), 415, 613, 8
insert into #temp select convert(date, '20171124', 112), 430, 613, 12
insert into #temp select convert(date, '20171124', 112), 445, 613, 4
insert into #temp select convert(date, '20171124', 112), 500, 613, 16


select t.row_date
     , left(right('0000' + convert(varchar(4), t.starttime), 4), 2) + ':00' as row_time
     , t.dept
     , sum(t.calls)

from #temp t

group by t.row_date
       , t.dept
       , left(right('0000' + convert(varchar(4), t.starttime), 4), 2) + ':00'

or if you do wish to have the time part as a proper time

select t.row_date
     , convert(time(0), left(right('0000' + convert(varchar(4), t.starttime), 4), 2) + ':00') as row_time
     , t.dept
     , sum(t.calls)

from #temp t

group by t.row_date
       , t.dept
       , convert(time(0), left(right('0000' + convert(varchar(4), t.starttime), 4), 2) + ':00')

EDIT:
thinking on this a bit I have a question.
11/24/2017 100 613------> Merge all four rows into One Hour 01:00
11/24/2017 115 613
11/24/2017 130 613
11/24/2017 145 613
11/24/2017 200 613------> Merge all four rows into One Hour 02:00

In some cases that I have seen the 100 and the 200 would be considered to be part of the previous hour (or day if 000), not for the hour that was registered for.
Just as an example if a office closes at 18:00 there would be no calls on the period of 18:00 to 18:15, but the way you asked (and I coded) we would be reporting all calls reported at 18:00 as such.

something for you to consider and eventually ask the business
end edit:

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Olaf Doschke]

If the time is stored as int GROUP BY row_date, ROUND(starttime/100), dept if it's char(3) GROUP BY row_date, LEFT(starttime,1), dept.

It gets a bit trickier, as there are times 1000-2300, so casting as int would work best, I think:

SELECT row_date,MIN(starttime),dept, SUM(calls) as calls 
FROM yourtable GROUP BY row_date, ROUND(cast(starttime as int)/100), dept

If you store the time as int you obviously won't need the cast(), I asszne a char/4) field here.

Bye, Olaf.

 

[fredericofonseca]

Olaf,

That gives wrong results for times over 10:00 - this is 24 hours clock - but even on a 12 hours your code would not work.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Olaf Doschke]

No, 1000, 1015, 1030, and 1045 all divided by 100 and rounded will be in the same group (10).

Bye, Olaf.

 

[Olaf Doschke]

The only thing to fix is the second parameter of round (0 for integer precision) or not rounding at all, as the computation is in int anyway:

declare @data as table (row_date date, starttime char(4), dept int, calls int)

insert into @data values 
('20171124','100', 613, 17),('20171124','115', 613, 1),('20171124','130', 613, 5),('20171124','145', 613, 17),
('20171124','200', 613, 2),('20171124','215', 613, 10),('20171124','230', 613, 14),('20171124','245', 613, 13),
('20171124','300', 613, 5),('20171124','315', 613, 18),('20171124','330', 613, 19),('20171124','345', 613, 11),
('20171124','400', 613, 7),('20171124','415', 613, 8),('20171124','430', 613, 12),('20171124','445', 613, 4),
('20171124','500', 613, 16),('20171124','1400', 613, 7),('20171124','1415', 613, 8),('20171124','1430', 613, 12),
('20171124','1445', 613, 4),('20171124','1400', 613, 7),('20171124','1415', 613, 8),('20171124','1430', 613, 12);

SELECT row_date, MIN(starttime),dept, SUM(calls) as calls 
FROM @data GROUP BY row_date, ROUND(cast(starttime as int)/100[highlight #FCE94F],0[/highlight]), dept 

SELECT row_date, MIN(starttime),dept, SUM(calls) as calls 
FROM @data GROUP BY row_date, cast(starttime as int)/100, dept

That doesn't stop working after 10 hours, it also would continue to work, if days had more than 100 or any number of hours, because what /100 removes is the minute part.
You seem to get something stuck in your logic. Dividing by 100 simply takes the notion of military time as a number (as it's spoken) ignoring the fact the real base for hours is 60 and not decimal.

Note: I first spoke of LEFT(time,1), but then already corrected myself about times having more than one hour digit. You may just have answered my original post, but I advanced this already - and even that post was already starting with the division by 100 idea. I figured LEFT(time,1) would be much faster, but then also not work for 10 or more hours, true.

Also note: I already stated how to change this, if starttime is not char but int itself, you can simply drop the cast, then.

Bye, Olaf.

 

[Wolfdzn]

Thanks Olaf, I will try this..

 

[Wolfdzn]

thanks olaf and fredericofonseca

Errors
Olaf Code :
ERROR: A syntax error has occurred. Error Code: -201

fredericofonseca Code :
ERROR: Routine (time) can not be resolved. Error Code: -674 -


I have tried both solutions but getting above syntax errors, I apologize, may be I was not very clear on my requirement.

here is my table structure (informix DB) and full 24 hours data

row_date (date)
starttime(integer)
dept(integet)
calls(integer

row_date(date) startime(integer) Dept(Integer) Calls(Integer)
20170331 0 501 1
20170331 15 501 4
20170331 30 501 1
20170331 45 501 4
20170331 100 501 1
20170331 115 501 4
20170331 130 501 1
20170331 145 501 4
20170331 200 501 1
20170331 215 501 4
20170331 230 501 1
20170331 245 501 4
20170331 300 501 1
20170331 315 501 4
20170331 330 501 1
20170331 345 501 4
20170331 400 501 1
20170331 415 501 4
20170331 430 501 1
20170331 445 501 4
20170331 500 501 1
20170331 515 501 4
20170331 530 501 1
20170331 545 501 4
20170331 600 501 1
20170331 615 501 4
20170331 630 501 1
20170331 645 501 4
20170331 700 501 1
20170331 715 501 4
20170331 730 501 1
20170331 745 501 4
20170331 800 501 1
20170331 815 501 4
20170331 830 501 1
20170331 845 501 4
20170331 900 501 1
20170331 915 501 4
20170331 930 501 1
20170331 945 501 4
20170331 1000 501 1
20170331 1015 501 4
20170331 1030 501 1
20170331 1045 501 4
20170331 1100 501 1
20170331 1115 501 4
20170331 1130 501 1
20170331 1145 501 4
20170331 1200 501 1
20170331 1215 501 4
20170331 1230 501 1
20170331 1245 501 4
20170331 1300 501 1
20170331 1315 501 4
20170331 1330 501 1
20170331 1345 501 4
20170331 1400 501 1
20170331 1415 501 4
20170331 1430 501 1
20170331 1445 501 4
20170331 1500 501 1
20170331 1515 501 4
20170331 1530 501 1
20170331 1545 501 4
20170331 1600 501 1
20170331 1615 501 4
20170331 1630 501 1
20170331 1645 501 4
20170331 1700 501 1
20170331 1715 501 4
20170331 1730 501 1
20170331 1745 501 4
20170331 1800 501 1
20170331 1815 501 4
20170331 1830 501 1
20170331 1845 501 4
20170331 1900 501 1
20170331 1915 501 4
20170331 1930 501 1
20170331 1945 501 4
20170331 2000 501 1
20170331 2015 501 4
20170331 2030 501 1
20170331 2045 501 4
20170331 2100 501 1
20170331 2115 501 4
20170331 2130 501 1
20170331 2145 501 4
20170331 2200 501 1
20170331 2215 501 4
20170331 2230 501 1
20170331 2245 501 4
20170331 2300 501 1
20170331 2315 501 4
20170331 2330 501 1
20170331 2345 501 4
20170331 230 501 1
20170331 245 501 4
20170331 300 501 1
20170331 315 501 4
20170331 330 501 1
20170331 345 501 4
20170331 400 501 1
20170331 415 501 4
20170331 430 501 1
20170331 445 501 4
20170331 500 501 1
20170331 515 501 4
20170331 530 501 1
20170331 545 501 4
20170331 600 501 1
20170331 615 501 4
20170331 630 501 1
20170331 645 501 4
20170331 700 501 1
20170331 715 501 4
20170331 730 501 1
20170331 745 501 4
20170331 800 501 1
20170331 815 501 4
20170331 830 501 1
20170331 845 501 4
20170331 900 501 1
20170331 915 501 4
20170331 930 501 1
20170331 945 501 4
20170331 1000 501 1
20170331 1015 501 4
20170331 1030 501 1
20170331 1045 501 4
20170331 1100 501 1
20170331 1115 501 4
20170331 1130 501 1
20170331 1145 501 4
20170331 1200 501 1
20170331 1215 501 4
20170331 1230 501 1
20170331 1245 501 4
20170331 1300 501 1
20170331 1315 501 4
20170331 1330 501 1
20170331 1345 501 4
20170331 1400 501 1
20170331 1415 501 4
20170331 1430 501 1
20170331 1445 501 4
20170331 1500 501 1
20170331 1515 501 4
20170331 1530 501 1
20170331 1545 501 4
20170331 1600 501 1
20170331 1615 501 4
20170331 1630 501 1
20170331 1645 501 4
20170331 1700 501 1
20170331 1715 501 4
20170331 1730 501 1
20170331 1745 501 4
20170331 1800 501 1
20170331 1815 501 4
20170331 1830 501 1
20170331 1845 501 4
20170331 1900 501 1
20170331 1915 501 4
20170331 1930 501 1
20170331 1945 501 4
20170331 2000 501 1
20170331 2015 501 4
20170331 2030 501 1
20170331 2045 501 4
20170331 2100 501 1
20170331 2115 501 4
20170331 2130 501 1
20170331 2145 501 4
20170331 2200 501 1
20170331 2215 501 4
20170331 2230 501 1
20170331 2245 501 4
20170331 2300 501 1
20170331 2315 501 4
20170331 2330 501 1
20170331 2345 501 4

This is what I need in one single SQL - One hour data per line from the above table

select sum(calls) as A from hsplit where starttime between 0 and 100;
select sum(calls) as B from hsplit where starttime between 100 and 200;
select sum(calls) as C from hsplit where starttime between 200 and 300;
select sum(calls) as D from hsplit where starttime between 300 and 400;
select sum(calls) as E from hsplit where starttime between 400 and 500;
select sum(calls) as F from hsplit where starttime between 500 and 600;
select sum(calls) as G from hsplit where starttime between 600 and 700;
select sum(calls) as H from hsplit where starttime between 700 and 800;
select sum(calls) as I from hsplit where starttime between 800 and 900;
select sum(calls) as J from hsplit where starttime between 900 and 1000;
select sum(calls) as K from hsplit where starttime between 1000 and 1100;
select sum(calls) as L from hsplit where starttime between 1100 and 1200;
select sum(calls) as M from hsplit where starttime between 1200 and 1300;
select sum(calls) as N from hsplit where starttime between 1300 and 1400;
select sum(calls) as O from hsplit where starttime between 1400 and 1500;
select sum(calls) as P from hsplit where starttime between 1500 and 1600;
select sum(calls) as Q from hsplit where starttime between 1600 and 1700;
select sum(calls) as R from hsplit where starttime between 1700 and 1800;
select sum(calls) as S from hsplit where starttime between 1800 and 1900;
select sum(calls) as T from hsplit where starttime between 1900 and 2000;
select sum(calls) as U from hsplit where starttime between 2000 and 2100;
select sum(calls) as V from hsplit where starttime between 2100 and 2200;
select sum(calls) as W from hsplit where starttime between 2200 and 2300;
select sum(calls) as X from hsplit where starttime between 2300 and 2345;

 

[Olaf Doschke]

Please look for an INFORMIX DB forum and don't ask your question in an MS SQL Server forum.

I just adapted to you stating starttime is an integer column.

It works here, so it will depend on the database server. If the error is in data preparation, you can skip that, you already have your data.
SQL dialects vary a lot and, for example, convert() is MSSQL specific; being able to group by expressions is not allowed in all databases etc.

You only need one of the last two queries and change the table variable name @data to your table name, to see whether Informix has ROUND or rounds integer divisions to integer results, too and allows group by expressions. If not, you need someone "talking" Informix SQL. I don't.

Bye, Olaf.

 

[fredericofonseca]

one of these may work

select t.row_date
     , left(right('0000' || cast(t.starttime as varchar(4)), 4), 2) || ':00' as row_time
     , t.dept
     , sum(t.calls)
from hsplit t
group by t.row_date
       , t.dept
       , left(right('0000' || cast(t.starttime as varchar(4)), 4), 2) || ':00'

select t.row_date
     , left(right('0000' || t.starttime::varchar, 4), 2) || ':00' as row_time
     , t.dept
     , sum(t.calls)
from hsplit t
group by t.row_date
       , t.dept
       , left(right('0000' || t.starttime::varchar, 4), 2) || ':00'

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Andrzejek]

You gave this example of your data for 1 hour:

[pre]
row_date starttime dept. Calls
11/24/2017 0 613 1
11/24/2017 15 613 1
11/24/2017 30 613 1
11/24/2017 45 613 2[/pre]

Could you show how your data looks like for 3 (or more) full hours of the same day for the same dept, please?


---- Andy

There is a great need for a sarcasm font.