Archives of the TeradataForum

Message Posted: Sat, 23 Jan 2010 @ 19:29:32 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Dieter, what are you spending the time on, on Saturday? Is the weather so bad? :-)

I am watching Manchester Utd v. Hull and, as it is not very interesting, keep thinking about your idea. I am not sure all cases are covered.

I'll just give one simple example: minutes when no session started or ended.

As far as I can see, those minutes can only come from the last query of the UNION:

     >    -- all possible minutes per id_prod
     >    -- # of products * 1440
     >    SELECT id_prod, id_minute, 0, 0
     >    FROM ref_table CROSS JOIN products

With usage set to zero (cnt & secs). But there could be a lot of active sessions during those minutes. I'm afraid this usage is lost in

     > SELECT
     >    id_prod
     >    ,id_minute
     >    ,(-- all sessions started < this minute
     >      -- minus all sessions ended <= this minute
     >      -- = active sessions throughout this minute (full 60 seconds)
     >      SUM(SUM(cnt))
     >      OVER (PARTITION BY id_prod
     >            ORDER BY id_minute
     >            ROWS UNBOUNDED PRECEDING)
     >      - SUM(CASE WHEN cnt > 0 THEN cnt ELSE 0 END)
     >     ) * 60
     >     -- seconds from sessions started/ended within this minute
     >      + SUM(secs)

What do you think?

Back to the football. The break is almost over.

Victor

	<Prev	Next>		<<First	<Prev	Next>	Last>>