Archives of the TeradataForum

Message Posted: Tue, 19 Jan 2010 @ 19:51:25 GMT

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

<-- Anonymously Posted: Tuesday, January 19, 2010 12:49 -->

Hello everyone,

I have two tables and a requirement as follows. I'll try to be as brief as possible, hopefully my explanation makes sense.

Table A:

     ID_USER         - user id (not important in the calculation)
     ID_PROD         - product id
     MINUTE_STARTING - a minute of a day when user logged on to a given
                       product (1-1440)
     MINUTE_ENDING   - a minute of a day when user logged off (1-1440)
     SEC_FIRST_MIN   - a second of the first minute when user logged
                       on to a given product (0-60)
     SEC_LAST_MIN    - a second of the last minute when user logged off (0-60)

Ref. table:

     ID_MINUTE (values: 1-1440) : each ID corresponds to a one-minute
                                  slice of a day (24 hours = 1440 minutes)

The requirement is to calculate a SUM of seconds each product was in use within every minute-slice of a day.

The solution proposed by the author of this idea is similiar to this:

     SELECT
               A.ID_PROD     ,
               R.ID_MINUTE   ,
               SUM(case when R.ID_MINUTE = A.MINUTE_STARTING THEN A.SEC_FIRST_MIN
                        when R.ID_MINUTE = A.MINUTE_ENDING   THEN A.SEC_LAST_MIN
                        else 60 end) NB_SECONDS_SLICE
     FROM      Table A    A  ,
               Ref_table  R
     WHERE     R.ID_MINUTE between A.MINUTE_STARTING and A.MINUTE_ENDING
     GROUP BY  A.ID_PROD     ,
               R.ID_MINUTE
     ;

Of course, it necessarily causes a product join. As Table A can have up to 150 million rows, this is not acceptable. Unfortunately, this issue fell on my shoulders and I need to resolve it. I must admit that I am running out of ideas, as the product join somehow makes sense in order to achieve the goal. So if someone can help, I would appreciate very much.

Thanks in advance

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