Removing the Sum & group by

Question

Removing the Sum & group by

sql_Reporter

SSCrazy

Points: 2329
More actions
December 14, 2006 at 10:03 am

#115398

Hi i'd like help with this bit of code that some one had written for me, i want to remove the sum and group by part..
The code breaks the time into 30 mins slots and then shows how much time was use up in the 30mins slots ...

-- prepare test data
declare @slots table (slot int, fromtime datetime, totime datetime)
insert @slots
select number,
dateadd(second, 21600 + 1800 * number, 0),
dateadd(second, 23400 + 1800 * number, 0)
from master..spt_values
where name is null
and number between 0 and 23
declare @times table (timeid int, fromtime datetime, totime datetime)
insert @times
select 1, '08:03:04', '10:35:00' union all
select 2, '08:20', '08:42' union all
select 2, '08:20', '08:42'
-- do the work
select t.timeid,
s.fromtime,
s.totime,
sum (
case when t.fromtime >= s.fromtime and t.fromtime < s.totime then datediff(second, t.fromtime, s.totime) else 0 end +
case when t.fromtime < s.fromtime and t.totime >= s.totime then datediff(second, s.fromtime, s.totime) else 0 end +
case when t.totime >= s.fromtime and t.totime < s.totime then datediff(second, s.fromtime, t.totime) else 0 end
) seconds
from @slots s
inner join @times t on t.totime >= s.fromtime and t.totime < s.totime
or t.fromtime < s.fromtime and t.totime >= s.totime
or t.fromtime >= s.fromtime and t.fromtime < s.totime
group by t.timeid,
s.fromtime,
s.totime
order by t.timeid,
s.fromtime

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

David McFarland SSChampion Points: 11815 More actions · Answer 1

I'm very confused as to what you are trying to accomplish, unless you want detail records with a timeslot identifier added.

Why exactly are you wanting to remove the sum and group part, and what are you expecting the final result of this removal to be?

DaveItz SSCrazy Points: 2146 More actions · Answer 2

You can probably cut them out and try a SELECT DISTINCT, instead.

actually, you could probably just remove the SUM(...) part, if you wanted.

It IS very confusing...

sql_Reporter SSCrazy Points: 2329 More actions · Answer 3

ok here is my problem if you run this code see below you get the result which is incorrect

as the final result should be 60 and not 1860 for some reason i get 30 mins = 1800 seconds added on to

the result.

Expected result = 60

Actual result = 1860

-- the time spent between the time slot 8:30 and 9:00 was 1 min = 60 seconds

Results

FromTime ToTime Seconds

1900-01-01 08:30:00.000 1900-01-01 09:00:00.000 1860

drop table table1

create table table1

(

FromTime datetime,

ToTime datetime

)

insert into table1 (FromTime,Totime)

values('01/01/1900 08:51:00','01/01/1900 08:52:00')

-- the time spent between the time slot 8:30 and 9:00 was 1 min = 60 seconds

-- prepare test data

declare @slots table (slot int, fromtime datetime, totime datetime)

insert @slots

select number,

dateadd(second, 21600 + 1800 * number, 0),

dateadd(second, 23400 + 1800 * number, 0)

from master..spt_values

where name is null

and number between 0 and 23

declare @times table (fromtime datetime, totime datetime)

insert @times(FromTime,Totime)

select FromTime,

Totime

From table1

-- do the work

select s.fromtime,

s.totime,

sum(

case when t.fromtime >= s.fromtime and t.fromtime < s.totime then datediff(second, t.fromtime, s.totime) else 0 end +

case when t.fromtime < s.fromtime and t.totime >= s.totime then datediff(second, s.fromtime, s.totime) else 0 end +

case when t.totime >= s.fromtime and t.totime < s.totime then datediff(second, s.fromtime, t.totime) else 0 end

&nbsp seconds

from @slots s

inner join @times t on t.totime >= s.fromtime and t.totime < s.totime

or t.fromtime < s.fromtime and t.totime >= s.totime

or t.fromtime >= s.fromtime and t.fromtime < s.totime

group by

s.fromtime,

s.totime

order by

s.fromtime

Vladan SSC-Insane Points: 22072 More actions · Answer 4

The result is incorrect because both join conditions and CASE are incorrect. This could work... Sorry, but I don't have time enough to think about better solution so I just corrected the mistakes. Hope at least this helps.

SELECT s.fromtime, s.totime,

sum(

CASE WHEN t.fromtime >= s.fromtime AND t.fromtime < s.totime

THEN datediff(second, t.fromtime, t.totime) ELSE 0 END +

CASE WHEN t.fromtime < s.fromtime AND t.totime >= s.totime

THEN datediff(second, s.fromtime, t.totime) ELSE 0 END +

CASE WHEN t.totime >= s.fromtime AND t.totime > s.totime

THEN datediff(second, t.fromtime, s.totime) ELSE 0 END)

AS seconds

FROM @slots s

JOIN @times t ON t.totime >= s.fromtime AND t.fromtime < s.totime

GROUP BY s.fromtime, s.totime

ORDER BY s.fromtime

Read further - this only works when there is one row in @times.

Vladan SSC-Insane Points: 22072 More actions · Answer 5

Actually, one more thing - in case you need to display all rows:

LEFT JOIN @times t ON t.totime >= s.fromtime AND t.fromtime < s.totime

will do the trick.

DaveItz SSCrazy Points: 2146 More actions · Answer 6

All this is giving me a headache.

Must be the lack of parentheses separating the ANDs from the ORs.

I'm not even going to try to read the previous posts. Here's ANOTHER rewrite. Needs testing.

select
   s.fromtime, s.totime,
   sum( datediff(second, s.fromtime, s.totime) -
        (case when t.fromtime between s.fromtime and s.totime
                 then datediff(second, s.fromtime, t.fromtime)
              else 0
         end +
         case when t.totime between s.fromtime and s.totime
                 then datediff(second, t.totime, s.totime)
              else 0
         end)
       ) seconds
from @slots s
inner join @times t on (t.fromtime between s.fromtime and s.totime)
                    or (t.totime between s.fromtime and s.totime)
                    or ((t.fromtime  s.totime))
group by
   s.fromtime, s.totime
order by
   s.fromtime

Vladan SSC-Insane Points: 22072 More actions · Answer 7

Dave,

I'm pretty sure I got it in my code.. it isn't lack of parentheses, the original code is all wrong. Don't read the original code, read the description... as I understand it, he just wants to find out how much time was "used" during each time period (30 minutes).

I think this would be much better than the solution I posted before, and so far passed all tests:

SELECT s.fromtime, s.totime,

sum(DATEDIFF(second, CASE WHEN t.fromtime > s.fromtime THEN t.fromtime ELSE s.fromtime END,

CASE WHEN t.totime > s.totime THEN s.totime ELSE t.totime END)) AS seconds

FROM @slots s

JOIN @times t ON t.totime >= s.fromtime AND t.fromtime < s.totime

GROUP BY s.fromtime, s.totime

ORDER BY s.fromtime

DaveItz SSCrazy Points: 2146 More actions · Answer 8

I know.

I just meant that reading the original code annoyed me so much I pretty much gave up on it.

You may have managed to salvage it but I was beyond caring.

Your code is pretty elegant. I'm sorry.

Vladan SSC-Insane Points: 22072 More actions · Answer 9

I have verified the code now on a few examples.

The code I posted originally only works correctly if there is one single row in table @times.

The newer code - with CASE moved inside DATEDIFF function - works correctly with several rows in @times table and with times overlapping from one period to another.

We have no info about what to do when there are overlapping times, like one from 8:41 to 8:50 and one from 8:48 to 8:58. Can this happen or not? If it happens, what is the result : 19 minutes, or 17 minutes?

DaveItz SSCrazy Points: 2146 More actions · Answer 10

DaveItz

SSCrazy

Points: 2146

December 15, 2006 at 9:27 am

#678381

All good points.

Vladan SSC-Insane Points: 22072 More actions · Answer 11

Don't be sorry I can understand you very well... and btw, my original "solution" was pretty stupid, since it only worked well when there was one row in the table.