Select data from one row, while on another row in the same table.

General
TDW
Enthusiast

Select data from one row, while on another row in the same table.

Hi.

No longer a newbie, but still need help.

I have a table based on our corporate calendar. I created this by joining the SYS_CALENDAR and a calendar table of our Due Dates.  I created a row in this table to add a number (next business day) if a due date falls on a weekend day. It adds 1 to the new due date if it falls on a Sunday, and 2 if it falls on a Saturday. The code below is how I created my calendar table. NewDoW is the value I add to the Saturday or Sunday date to change the due date to Monday. It is also used to change a weekday holiday due date to the next business day: Mon-Thu NewDoW is "1". For Fri NewDoW is "3" to change the due date to Monday. This works fine, except for the times for when Monday is a holiday. While there are not that many Monday holidays, rather than manually update the table to handle those situations (5 Monday holidays), I want to use a programmatic solution; adding 3 to Saturday due dates, and 2 to Sunday due dates. In both instances, the new due date will fall on a Tuesday.

CREATE TABLE TDWSTAGE.NJSACECalendar
(   DayofYear INTEGER,
    CalDate DATE FORMAT 'YY/MM/DD',
    Holiday CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
    DayName CHAR(9) CHARACTER SET LATIN NOT CASESPECIFIC,
    DoW INTEGER,
    NewDoW INTEGER
)
PRIMARY INDEX (DayofYear)
;
INSERT INTO TDWSTAGE.NJSACECalendar
( DayofYear,
  CalDate,
  Holiday,
  DayName,
  DoW,
  NewDoW
)
SELECT
  SCC.Day_of_Year,
  SCC.Calendar_Date,
  HC.Holiday,
  HC.DayName,
  SCC.Day_Of_Week,
  HC.NewDoW
 FROM SYS_CALENDAR.CALENDAR AS SCC
  LEFT JOIN TDWSTAGE.NJSHolidayCalendar AS HC
   ON SCC.Calendar_Date = HC.CalDate
  WHERE SCC.Year_Of_Calendar = '2015'

I have to run for the bus. I will finish my thoughts here tomorrow.

God bless,

Genesius

20 REPLIES
Senior Apprentice

Re: Select data from one row, while on another row in the same table.

Hi Genesius,

you need to find the minimum date where the date is not a weekend or holiday: 

min(case when SCC.Day_Of_Week in (1,7) or HC.Holiday is not null
then null
else calendar_date end)
over (order by calendar_date
rows between 1 following and unbounded following)
TDW
Enthusiast

Re: Select data from one row, while on another row in the same table.

Dieter to the rescue. ;-)

I am going to try this, but first let me finish my original post, so that it will be clear for future viewers.

This is what a portion of the table looks like.

CalDate            Holiday                            DayName     DoW    NewDoW

01/17/2015          ?                                         ?               7             ?   

01/18/2015          ?                                         ?               1             ?   

01/19/2015    Martin Luther King Jr. Day      Monday          2             1  

01/20/2015          ?                                         ?               3             ? 

This is what I would like it to look like; see the NewDoW for 01/17 3 and 01/17 2.

CalDate             Holiday                            DayName     DoW    NewDoW

01/17/2015           ?                                        ?                7            3

01/18/2015           ?                                        ?                1            2

01/19/2015    Martin Luther King Jr. Day      Monday           2            1

01/20/2015           ?                                        ?                3            ?

I have been searching for information/explanation on Row_Number and Partition and Over...and no luck finding a good example that I could understand. Believe me, I go fishing for an answer first before I run to the forum (my pride?). But would you mind explaining what this does line for line.

Thanks and God Bless,

Genesius

TDW
Enthusiast

Re: Select data from one row, while on another row in the same table.

Dieter,

Your code almost produced what I was looking for. Unfortunately, the new due date for Fridays that are not a holiday are being changed to the next Monday. Also, the new due date for Mon-Thu (non-holiday) is being changed to next day.

Ex:. Good Friday 4/3/2015 changed to Monday 4/6/2015            This is what should happen.

               Friday 4/10/2015 changed to Monday 4/13/2015         This should have remained as 4/10/2015

               Tuesday 3/3/2015 changed to Wednesday 3/4/2105    This should have remained as 3/3/2015

I like that your code populates the table with the new due date instead of using a value (NewDoW) to add later on. This will make the coding for updating the due dates much simpler. Not every client has the same due date each month. Some are on the 20th, others the 25th, etc. My code contains CASE scenarios for each possiblibilty (MON-FRI, SAT, SUN, HOL MON-THU, and HOL FRI). The only sceanrio that isn't be handled properly is the SAT and SUN where MON is a holiday. I can post that code if you (or anyone else) would like to see, but I think it might confuse things.

Using Excel I was able to create the calendar the way I need it. However, I would rather learn the proper coding in TD SQL, than create an Excel file, import it, etc.

Here is the formula I used for my Excel calendar version. This is Col F.

=IF(AND(E2=1,C3="?"),B2+1,IF(AND(E2=1,C3<>"?"),B2+2,IF(AND(E2=7,C4="?"),B2+2,IF(AND(E2=7,C4<>"?"),B2+3,IF(AND(OR(E2=2,E2=3,E2=4,E2=5),C2<>"?"),B2+1,IF(AND(E2=6,C2<>"?"),B2+3,B2))))))

If you, or anyone else, wishes to test it, create a spreadsheet as follows:

Col A is Day of Year

Col B is Due Date (All dates of the year)

Col C is Holiday (blank or null if non holiday)

Col D is Day Name

Col E is Day of Week (number: Sun = 1, Mon =2, etc.)

Looking at your code

min(case when SCC.Day_Of_Week in (1,7) or HC.Holiday is not null

then null

else calendar_date end)

over (order by calendar_date

rows between 1 following and unbounded following)

The HC.Holiday  needs to be the Monday after SCC.Day_Of_Week in (1,7). Not from the same Row.

   If Day_of_Week = 1, then HC.Holiday should be checked in the record 1 row after this row.

   If Day_of_Week = 2, then HC.Holiday should be checked in the record 2 rows after this row.

I apologize if I was not clearer in my opening post.

Thanks in advance and God Bless,

Genesius

TDW
Enthusiast

Re: Select data from one row, while on another row in the same table.

Dieter,

Did some research, because I think I need to do something with the ROWS function. I think this works; still have to test further.

SELECT
  SCC.Day_of_Year,
  SCC.Calendar_Date,
  HC.Holiday,
  CASE WHEN SCC.Day_Of_Week = 1 THEN 'Sunday'
     ELSE CASE WHEN SCC.Day_Of_Week = 2 THEN 'Monday'
      ELSE CASE WHEN SCC.Day_Of_Week = 3 THEN 'Tuesday'
       ELSE CASE WHEN SCC.Day_Of_Week = 4 THEN 'Wednesday'
        ELSE CASE WHEN SCC.Day_Of_Week = 5 THEN 'Thursday'
         ELSE CASE WHEN SCC.Day_Of_Week = 6 THEN 'Friday'
          ELSE 'Saturday'
            END
           END
          END
         END
        END 
  END AS /*HC.*/DayName,
  SCC.Day_Of_Week,
  CASE WHEN SCC.Day_Of_Week = 1 /*Sun*/
     THEN CASE WHEN MIN(HC.Holiday) OVER (ORDER BY SCC.Day_of_Year ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) IS NOT NULL
           THEN SCC.calendar_date + 2
           ELSE SCC.calendar_date + 1
        END
     ELSE CASE WHEN SCC.Day_Of_Week = 7 /*Sat*/
           THEN CASE WHEN MIN(HC.Holiday) OVER (ORDER BY SCC.Day_of_Year ROWS BETWEEN 2 FOLLOWING AND 2 FOLLOWING) IS NOT NULL
                 THEN SCC.calendar_date + 3
                 ELSE SCC.calendar_date + 2
              END
            ELSE CASE WHEN  SCC.Day_Of_Week IN (2,3,4,5)/*Mon-Fri*/
                  THEN CASE WHEN HC.Holiday IS NOT NULL
                        THEN SCC.calendar_date + 1
                        ELSE SCC.calendar_date
                    END
                 ELSE CASE WHEN SCC.Day_Of_Week = 6 /*Fri*/
                       THEN CASE WHEN HC.Holiday IS NOT NULL
                              THEN SCC.calendar_date + 3
                              ELSE SCC.calendar_date
                           END
                     END
               END         
         END
  END AS NewDueDateHol
FROM SYS_CALENDAR.CALENDAR AS SCC
LEFT JOIN TDWSTAGE.NJSHolidayCalendar AS HC
ON SCC.Calendar_Date = HC.CalDate
WHERE SCC.Year_Of_Calendar = '2015'
ORDER BY 1
;

Thank you Dieter for your help. Instead of giving me the fish and solving my problem outright, you taught me how to fish, and now I think I have it. Still need to chew on this and understand exactly what the MIN, OVER, ROWS, FOLLOWING, etc. are doing and why; but I think I have a good start. Now I need to draft up a explanation document for the staff in my department.

Thanks again and God Bless,

Genesius

Senior Apprentice

Re: Select data from one row, while on another row in the same table.

The MIN OVER returns the minumum date after the current row, you probably need to change it to:

min(case when SCC.Day_Of_Week in (1,7) or HC.Holiday is not null
then null
else calendar_date end)
over (order by calendar_date
rows between current row and unbounded following)
TDW
Enthusiast

Re: Select data from one row, while on another row in the same table.

Thanks Dieter,

Now I need to come up with an OLAP to handle a 45-day grace period end date for the same calendar.

I modified your code as follows, but I do not get the results I was looking for.

   MIN(CASE WHEN SCC.Day_Of_Week IN (1,7) OR HC.Holiday IS NOT NULL
        THEN NULL
        ELSE calendar_date
     END)
     OVER (ORDER BY calendar_date
           ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS NewDueDate,

  SCC.Calendar_Date + 45 AS OrigGracePeriodEndDate,
  Day_Of_Week(OrigGracePeriodEndDate) AS OrigGracePeriodEndDateDoW,

   MIN(CASE WHEN OrigGracePeriodEndDateDoW IN (1,7) OR HC.Holiday IS NOT NULL
        THEN NULL
        ELSE OrigGracePeriodEndDate
     END)
     OVER (ORDER BY calendar_date
           ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS NewGracePeriodEnd1,
  Day_Of_Week(NewGracePeriodEnd1) AS NewGracePeriodEndDateDoW1  

A couple of problems I noticed.

  1. If the original grace period date falls on a holiday, Good Friday 4/3/15, the new grace period date is the same day, 4/3/15. BAD
  2. If the original grace period date falls on a Saturday, 2/28/15, the new grace period date moves to Monday, 3/2/15. GOOD
  3. If the original grace period date falls on a Sunday, 2/15/15, the new grace period date moves to Monday, 2/16/15. BAD. While it did move to a Monday, this particular Monday is a holiday. It should have moved to 2/17/15, Tuesday.
  4. If I change SSC.Calendar_Date + 45 to NewDueDate, I receive this error in TDSQLA: "SELECT Failed.  [3710] Insufficient memory to parse this request, during Optimizer phase."

I thought of using LEAD, but that OLAP function is not available in Teradata. (I have been finding more about OLAP on Oracle sites than for Teradata.)

I re-wrote my long code and it works, see below. However, I would rather harness the full functionality of OLAP.

  NewDueDate + 45 AS OrigGracePeriodEndDate,
  Day_Of_Week(OrigGracePeriodEndDate) AS OrigGracePeriodEndDateDoW,

  CASE WHEN OrigGracePeriodEndDateDoW = 1 /*Sun*/
     THEN CASE WHEN MIN(HC.Holiday) OVER (ORDER BY SCC.Day_of_Year ROWS BETWEEN 46 FOLLOWING AND 46 FOLLOWING) IS NOT NULL
           THEN OrigGracePeriodEndDate + 2
           ELSE OrigGracePeriodEndDate + 1
        END
     ELSE CASE WHEN OrigGracePeriodEndDateDoW = 7 /*Sat*/
           THEN CASE WHEN MIN(HC.Holiday) OVER (ORDER BY SCC.Day_of_Year ROWS BETWEEN 47 FOLLOWING AND 47 FOLLOWING) IS NOT NULL
                 THEN OrigGracePeriodEndDate + 3
                 ELSE OrigGracePeriodEndDate + 2
              END
            ELSE CASE WHEN  OrigGracePeriodEndDateDoW IN (2,3,4,5) /*Mon-Thu*/
                  THEN CASE WHEN MIN(HC.Holiday) OVER (ORDER BY SCC.Day_of_Year ROWS BETWEEN 45 FOLLOWING AND 45 FOLLOWING) IS NOT NULL
                        THEN OrigGracePeriodEndDate + 1
                        ELSE OrigGracePeriodEndDate
                    END
                 ELSE CASE WHEN OrigGracePeriodEndDateDoW = 6 /*Fri*/
                       THEN CASE WHEN MIN(HC.Holiday) OVER (ORDER BY SCC.Day_of_Year ROWS BETWEEN 45 FOLLOWING AND 45 FOLLOWING) IS NOT NULL
                              THEN OrigGracePeriodEndDate + 3
                              ELSE OrigGracePeriodEndDate
                           END
                     END
               END         
         END
  END AS NewGracePeriodEnd,
  Day_Of_Week(NewGracePeriodEnd) AS NewGracePeriodEndDateDoW,

Also, I am believe I should be using the NewDueDate for my 45-day calculations and not the SCC.Calendar_Date + 45, as that date might be a holiday or a weekend day.

Thoughts?

Thanks in advance and God Bless,

Genesius

Senior Apprentice

Re: Select data from one row, while on another row in the same table.

Hi Genesius,

you need to do the same calculation on based on NewDueDate, but as OLAP functions can't be nested you must use a Derived Table instead (or a WITH) like this:

WITH cte AS
(
SELECT
...
MIN(CASE WHEN SCC.Day_Of_Week IN (1,7) OR HC.Holiday IS NOT NULL
THEN NULL
ELSE calendar_date end)
OVER (ORDER BY calendar_date
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS NewDueDate

FROM SYS_CALENDAR.CALENDAR AS SCC
LEFT JOIN NJSHolidayCalendar AS HC
ON SCC.Calendar_Date = HC.Holiday
WHERE SCC.Year_Of_Calendar = '2015'
)
SELECT
t1.*
,t2.Calendar_date AS OrigGracePeriodEndDate
,t2.Day_Of_Week AS OrigGracePeriodEndDateDoW
,t2.NewDueDate AS NewGracePeriodEnd
,Day_Of_Week(NewGracePeriodEnd) AS NewGracePeriodEndDateDoW
FROM cte AS t1 LEFT JOIN cte AS t2 ON t2.calendar_date = t1.NewDueDate + 45
TDW
Enthusiast

Re: Select data from one row, while on another row in the same table.

Thanks Dieter.

When I run the above I received an error: "SELECT Failed.  [3706] Syntax error: expected something between ')' and the 'AS' keyword."

CTE is considered a temporary table, correct? Therefore, there is no need for me to perform a CREATE TABLE prior to running the above code. I am running this code through TDSQLA. Is that why the WITH statement doesn't work?

God Bless,

Genesius

Senior Apprentice

Re: Select data from one row, while on another row in the same table.

Hi Genesius,

the CTE is a Common Table Expression, similar to a Derived Table (aka Inline View), just easier to use when you need to access the same Select twice.

You probably run this query on an older release where you need to add the list of columns after the cte name:

WITH cte (a,b,c,..., NewDueDate) AS
(
SELECT ...