Archives of the TeradataForum

Message Posted: Wed, 20 Apr 2011 @ 20:28:44 GMT

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

I have successfully coded a similar problem. I had to use an ordered analytical function. I found them discussed in the Teradata reference titled "SQL Reference: Functions and Operators", in Chapter 8 Ordered Analytical Functions, in the section titled, "Applying windows to aggregate functions."

In your case, I would first do a subquery, grouping by Casenumber, Usernumber. or visa versa.

Then in the next level up subquery you can calculate the start time based on the last period's end time. This is where you would use the analytic function. (Look for "PARTITION BY" in the example below.)

Finally, in an outer query you can use the new field to calculate your difference.

I am including the kernel of my code here, hoping it will help you. My tables are of course very different from yours but this is a working query. Maybe you can adapt it to your circumstances.

     SELECT
        USER_TAR_START_END.USER_ID,
        USER_TAR_START_END.TAR_ID,
        USER_TAR_START_END.CHG_HX_LINE,
        USER_TAR_START_END.END_SECS,
        USER_TAR_START_END.START_SECS,
        (CASE WHEN USER_TAR_START_END.START_SECS IS NULL THEN NULL
                ELSE USER_TAR_START_END.END_SECS - USER_TAR_START_END.START_SECS
        END) AS DURATION_SECS
     FROM (
            SELECT
               USER_TAR_GROUP.USER_ID,
               USER_TAR_GROUP.TAR_ID,
               USER_TAR_GROUP.TIMESECS AS END_SECS,
               USER_TAR_GROUP.CHG_HX_LINE,
               MAX(USER_TAR_GROUP.TIMESECS) OVER
                       (PARTITION BY USER_TAR_GROUP.USER_ID ORDER BY USER_TAR_GROUP.TIMESECS
                               ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS START_SECS
            FROM (
                   SELECT
                      PRE_AR_CHG_HX.USER_ID,
                      PRE_AR_CHG_HX.TAR_ID,
                      MAX(PRE_AR_CHG_HX.ACTIVITY_TIMESTAMP) AS TIMESECS,
                      MAX(PRE_AR_CHG_HX.CHARGE_HX_LINE) AS CHG_HX_LINE
                   FROM PRE_AR_CHG_HX
                   WHERE PRE_AR_CHG_HX.ACTIVITY_DATE = DATE'2011-04-19'
                   GROUP BY 1,2
                 ) USER_TAR_GROUP
          ) USER_TAR_START_END
     ORDER BY 1,4

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