All Forums General
TDW 36 posts Joined 11/12
20 Oct 2014
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

Thanks and God Bless,
Genesius

dnoeth 4628 posts Joined 11/04
20 Oct 2014

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)

 

Dieter

TDW 36 posts Joined 11/12
21 Oct 2014

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

Thanks and God Bless,
Genesius

TDW 36 posts Joined 11/12
21 Oct 2014

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

Thanks and God Bless,
Genesius

TDW 36 posts Joined 11/12
21 Oct 2014

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

Thanks and God Bless,
Genesius

dnoeth 4628 posts Joined 11/04
21 Oct 2014

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)

 

Dieter

TDW 36 posts Joined 11/12
23 Oct 2014

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
 

Thanks and God Bless,
Genesius

dnoeth 4628 posts Joined 11/04
24 Oct 2014

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

 

Dieter

TDW 36 posts Joined 11/12
27 Oct 2014

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

Thanks and God Bless,
Genesius

dnoeth 4628 posts Joined 11/04
27 Oct 2014

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 ...

 

Dieter

TDW 36 posts Joined 11/12
28 Oct 2014

Thanks Dieter. That stopped the 3706 error. Now I have another. "2666 Invalid date supplied for NJSHoliday.Holiday." Below is the full code I am runnning. I am posting this here now (so I won't forget later) and will continue to troubleshoot on my own. I will post if anything changes.
Thanks again for your help; I'm learning a lot.
God Bless,
Genesius

WITH cte (DoY, OrigDueDate, DoW, Hol, NewDueDate) AS
(SELECT
  SCC.Day_of_Year AS DoY,
  SCC.Calendar_Date AS OrigDueDate,
  SCC.Day_Of_Week AS DoW,
  HC.Holiday AS Hol,
  MIN(CASE WHEN DoW /*SCC.Day_Of_Week*/ IN (1,7) OR Hol /*HC.Holiday*/ IS NOT NULL
        THEN NULL
        ELSE OrigDueDate /*calendar_date*/ END)
  OVER (ORDER BY OrigDueDate /*calendar_date*/ ROWS BETWEEN CURRENT ROW 
   AND UNBOUNDED FOLLOWING) AS NewDueDate
 FROM SYS_CALENDAR.CALENDAR AS SCC
  LEFT JOIN NJSHolidayCalendar AS HC
   ON OrigDueDate /*SCC.Calendar_Date*/ = Hol /*HC.Holiday*/
  WHERE SCC.Year_Of_Calendar = '2015')
SELECT
  t1.*,
  t2.OrigDueDate /*Calendar_date*/ AS OrigGracePeriodEndDate,
  t2.DoW /*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.OrigDueDate /*calendar_date*/ = t1.NewDueDate + 45
;

 

Thanks and God Bless,
Genesius

dnoeth 4628 posts Joined 11/04
28 Oct 2014

Hi Genesius,
Holiday holds the name of the holiday but no date, based on your table definition you need to join using CalDate instead.

Dieter

TDW 36 posts Joined 11/12
28 Oct 2014

Dieter,
I don't have time to test right now, but it appears like this line of code

ON SCC.Calendar_Date = HC.Holiday

that is causing the error. I copied it from the posting here, and did not compare it to my previous code that was running. Calendar_Date is DATE YYYY-MM-DD. Whereas Holiday is CHAR.
Will let you know the correction and the results later.
Thanks and God Bless,
Genesius

Thanks and God Bless,
Genesius

TDW 36 posts Joined 11/12
28 Oct 2014

Dieter,
I did not refresh my Internet Explorer, so I did not see your post an hour before mine about the 2666 error.
I believe it finally works! However, along the way I found an anomoly that I hope you can shed some light on.
First, here is my code that is now working. 

WITH cte (YoC, DoY, OrigDueDate, DoW, Hol, NewDueDate) AS
(SELECT
  SCC.Year_Of_Calendar AS YoC,
  SCC.Day_of_Year AS DoY,
  SCC.Calendar_Date AS OrigDueDate,
  SCC.Day_Of_Week AS DoW,
  HC.Holiday AS Hol,

   MIN(CASE WHEN /*DoW*/ SCC.Day_Of_Week IN (1,7) OR Hol IS NOT NULL
        THEN NULL
        ELSE OrigDueDate 
     END)
     OVER (ORDER BY OrigDueDate
       ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS NewDueDate

 FROM SYS_CALENDAR.CALENDAR AS SCC
  LEFT JOIN NJSHolidayCalendar AS HC
   ON OrigDueDate = HC.CalDate
  WHERE YoC = '2015' OR YoC = '2016')
SELECT
  t1.*,
  t2.OrigDueDate AS OrigGracePeriodEndDate,
  t2.DoW AS OrigGracePeriodEndDateDoW,
  t2.NewDueDate AS NewGracePeriodEnd,
  Day_Of_Week(NewGracePeriodEnd) AS NewGracePeriodEndDateDoW
   FROM cte AS t1 
     LEFT JOIN cte AS t2
      ON t2.OrigDueDate = t1.NewDueDate + 45
     WHERE t1.Yoc = '2015' AND t1.DoY <= '366'
  ORDER BY 1,2   
;

If I use the Alias DoW in MIN(CASE WHEN... instead of SCC.Day_of_Week, I end up with different results for the weekend days. *Doesn't make a difference if I use DoW anywhere else in the code.

    MIN(CASE WHEN DoW /*SCC.Day_Of_Week*/ IN (1,7) OR Hol IS NOT NULL
        THEN NULL
        ELSE OrigDueDate
     END)
     OVER (ORDER BY OrigDueDate
       ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS NewDueDate

 Here is a comparison. NewDueDate is using SCC.Day_Of_Week, and NewDueDate2 uses DoW.

 Yoc DoY  OrigDueDate  DoW Hol   NewDueDate   NewDueDate2
2015  3    01/03/2015   7   ?     01/05/2015   01/03/2015  
2015  4    01/04/2015   1   ?     01/05/2015   01/04/2015  

Thanks and God Bless,
Genesius
 
 
 
 

Thanks and God Bless,
Genesius

dnoeth 4628 posts Joined 11/04
28 Oct 2014

Hi Genesius,
in your table TDWSTAGE.NJSACECalendar there's a column DoW and in the Select you alias SCC.Day_Of_Week AS DoW.
Now when you use DoW the parser has two DoWs to choose from, and it's always prefering the base column over the alias.
There's a basic rule, never alias to an existing column name if you want to use it in any other place.

Dieter

TDW 36 posts Joined 11/12
29 Oct 2014

I didn't notice the duplicate column names. Now I know.
Thanks Dieter, for all your help. You have given me a lot to work off of.
Take care, and God Bless,
Genesius

Thanks and God Bless,
Genesius

TDW 36 posts Joined 11/12
07 Nov 2014

I thought I was done with this calendar, but no. Now I need to include another NewDueDate, but for the previous month.
Here is what I have written so far, but I am stuck.

 WITH cte (YofC, DofY, OrigDueDate, DofW, Hol, NewDueDate, NewDoW, NewDueDatePrevMonth, NewDoWPrevMonth) AS
  (SELECT  
    SCC.Year_Of_Calendar AS YofC,  
    SCC.Day_of_Year AS DofY,  
    SCC.Calendar_Date AS OrigDueDate,  
    SCC.Day_Of_Week AS DofW, 
    HC.Holiday AS Hol,   
    MIN(CASE WHEN DofW /*SCC.Day_Of_Week*/ IN (1,7) OR Hol IS NOT NULL        
          THEN NULL        
          ELSE OrigDueDate     
       END)     
       OVER (ORDER BY OrigDueDate ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS NewDueDate,
     Day_of_Week(NewDueDate) AS NewDoW,

    MIN(CASE WHEN Day_Of_Week(ADD_MONTHS(OrigDueDate, - 1)) IN (1,7) OR Hol IS NOT NULL        
          THEN NULL        
          ELSE ADD_MONTHS(OrigDueDate, - 1)    
       END)     
       OVER (ORDER BY OrigDueDate ROWS BETWEEN CURRENT ROW AND 30 PRECEDING) AS NewDueDatePrevMonth,
     Day_of_Week(ADD_MONTHS(OrigDueDate, - 1) AS NewDoWPrevMonth

    FROM SYS_CALENDAR.CALENDAR AS SCC  
     LEFT JOIN NJSHolidayCalendar AS HC   
      ON OrigDueDate = HC.CalDate  
    WHERE YofC = '2014' OR YofC = '2015' OR YofC = '2016')
  SELECT  
    t1.*,  
    t2.OrigDueDate AS OrigGracePeriodEndDate,  
    t2.DofW AS OrigGracePeriodEndDateDoW,  
    t2.NewDueDate AS NewGracePeriodEndDate,  
    Day_Of_Week(NewGracePeriodEndDate) AS NewGracePeriodEndDateDoW   
   FROM cte AS t1      
    LEFT JOIN cte AS t2      
     ON t2.OrigDueDate = t1.NewDueDate + 45     
    WHERE t1.YofC = '2015' AND t1.DofY <= '366'
     OR  t1.YofC = '2014' AND t1.DofY <= '366'  /*ORDER BY 1,2*/
;

The yellow highlighted code is my new code that is not working. [3706] Syntax error: expected something between ')' and the 'AS' keyword.
The results should be:

YoC

2014

DoY

338

OrigDueDate

12/4/2014

DoW

5

Hol

?

NewDueDate

12/4/2014

NewDoW

5

OrigGracePeriodEndDate

1/18/2015

OrigGracePeriodEndDateDoW

1

NewGracePeriodEndDate

1/20/2015

NewGracePeriodEndDateDoW

3

NewDueDatePrevMonth

11/5/2014

NewDoWPrevMonth

4

However, the actual "ADD_MONTHS(OrigDueDate, - 1)" would result in 11/04/2014, which is Veteran's Day in the US and therefore, the NewDueDatePrevMonth should be 11/05/2014.
 
I will be in on Monday, but will try to login here over the weekend to answer any questions or clarifications requested. I don't have access to our data warehouse, so...
 
Thanks and God bless in advance.
Genesius

Thanks and God Bless,
Genesius

dnoeth 4628 posts Joined 11/04
08 Nov 2014

Hi Genesius,
don't try to calculate the new date in the CTE, better add another join:

WITH cte (YofC, DofY, OrigDueDate, DofW, Hol, NewDueDate, NewDoW) AS
  (SELECT 
    SCC.Year_Of_Calendar AS YofC,  
    SCC.Day_of_Year AS DofY,  
    SCC.Calendar_Date AS OrigDueDate,  
    SCC.Day_Of_Week AS DofW, 
    HC.Holiday AS Hol,   
    MIN(CASE WHEN DofW /*SCC.Day_Of_Week*/ IN (1,7) OR Hol IS NOT NULL       
          THEN NULL       
          ELSE OrigDueDate     
       END)     
       OVER (ORDER BY OrigDueDate ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS NewDueDate,
     Day_of_Week(NewDueDate) AS NewDoW
    FROM SYS_CALENDAR.CALENDAR AS SCC  
     LEFT JOIN NJSHolidayCalendar AS HC   
      ON OrigDueDate = HC.Holiday  
    WHERE YofC = '2014' OR YofC = '2015' OR YofC = '2016')
  SELECT 
    t1.*,  
    t2.OrigDueDate AS OrigGracePeriodEndDate,  
    t2.DofW AS OrigGracePeriodEndDateDoW,  
    t2.NewDueDate AS NewGracePeriodEndDate,  
    Day_Of_Week(NewGracePeriodEndDate) AS NewGracePeriodEndDateDoW,   
    t3.OrigDueDate AS OrigGracePeriodPrevMonth,  
    t3.DofW AS OrigGracePeriodPrevMonthDoW2,  
    t3.NewDueDate AS NewGracePeriodPrevMonth2,  
    Day_Of_Week(NewGracePeriodPrevMonth2) AS NewGracePeriodPrevMonthDoW2   
   FROM cte AS t1      
    LEFT JOIN cte AS t2      
     ON t2.OrigDueDate = t1.NewDueDate + 45     
    LEFT JOIN cte AS t3      
     ON t3.OrigDueDate = ADD_MONTHS(t1.OrigDueDate,-1)  
    WHERE t1.YofC = '2015' AND t1.DofY <= '366'
     OR  t1.YofC = '2014' AND t1.DofY <= '366'  /*ORDER BY 1,2*/

Btw, assuming YofC and DofY are integers and not characters you should remove the single quotes, e.g. t1.YofC = 2015.
And are you shure about the ADD_MONTHS logic for the previous month? One month is not always the same number of days, so simply substracting 28 or 30 days might be better...

Dieter

TDW 36 posts Joined 11/12
10 Nov 2014

Dieter,
When I was riding the bus home on Friday, I thought about performing another JOIN. Without the code in front of me I wasn't sure what to do. Also, you are correct, YofC and DofY are integers and do not need the single quotes.
Using the ADD_MONTHS logic should be correct because I want the result to be as follows.
ADD_MONTHS(t1.OrigDueDate,-1)
12/31/2014 should become 11/30/2014
I'm going about this a slightly different way, but I am not getting correct results.
This is the SELECT portion only. 
I am trying to figure out how to have the NewPrevDueDate check to see if it falls on a Sat, Sun or Holiday, and adjust it as it did for the NewDueDate.
BTW, I used the CAST clauses on the instead of Day_Of_Week to see the text for the day instead of having to constantly decipher the number.

(SELECT
       SCC.Year_Of_Calendar AS YofC,
       SCC.Day_of_Year AS DofY,
       SCC.Calendar_Date AS OrigDueDate,
       CAST(CAST(OrigDueDate AS FORMAT 'E4') AS VARCHAR(3)) AS OrigDofW,
       HC.Holiday AS Hol,
       MIN(CASE WHEN OrigDofW IN ('Sun', 'Sat') OR Hol IS NOT NULL
            THEN NULL
            ELSE OrigDueDate
         END)
        OVER (ORDER BY OrigDueDate ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS NewDueDate,
    CAST(CAST(NewDueDate AS FORMAT 'E4') AS VARCHAR(3)) AS NewDofW,       
       ADD_MONTHS(OrigDueDate, -1) AS PrevDueDate,
    CAST(CAST(PrevDueDate AS FORMAT 'E4') AS VARCHAR(3)) AS PrevDofW,
      
       MIN(CASE WHEN PrevDofW IN ('Sun', 'Sat') OR Hol IS NOT NULL
            THEN NULL
            ELSE PrevDueDate
         END)
        OVER (ORDER BY ADD_MONTHS(OrigDueDate, -1) ROWS BETWEEN CURRENT ROW AND CURRENT ROW ) AS NewPrevDueDate,
/*        OVER (ORDER BY ADD_MONTHS(OrigDueDate, -1) ROWS BETWEEN CURRENT ROW AND CURRENT ROW ) AS NewPrevDueDate,*/
     /*NewPrevDueDate is NULL when PreDofW is Sat or Sun, or when Hol is NOT NULL*/        
/*        OVER (ORDER BY ADD_MONTHS(OrigDueDate, -1) ROWS BETWEEN 31 PRECEDING AND 31 PRECEDING ) AS NewPrevDueDate,*/
     /*NewPrevDueDate is NULL for first 32 days. Then it is at least 1 month earlier than PrevDueDate*/
/*        OVER (ORDER BY ADD_MONTHS(OrigDueDate, -1) ROWS BETWEEN 31 PRECEDING AND CURRENT ROW) AS NewPrevDueDate,*/ 
     /*NewPrevDueDate is 12/02/2013 for Jan 2014. Then it begins counting a month or so before the PreDueDate for the remainder of the calendar*/            
/*        OVER (ORDER BY ADD_MONTHS(OrigDueDate, -1) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS NewPrevDueDate,*/
     /*NewPrevDueDate is 12/02/2013 for the entire calendar*/
/*        OVER (ORDER BY ADD_MONTHS(OrigDueDate, -1) ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS NewPrevDueDate,*/  
     /*NewPrevDueDate is based on Hol from OrigDueDate and not PrevDueDate*/

    CAST(CAST(NewPrevDueDate AS FORMAT 'E4') AS VARCHAR(3)) AS NewPrevDofW
    
      FROM SYS_CALENDAR.CALENDAR AS SCC
           LEFT JOIN NJSHolidayCalendar AS HC
            ON OrigDueDate = HC.CalDate
           WHERE YofC = 2014 OR YofC = 2015 OR YofC = 2016  ORDER BY 1,2)

The Commented out portions of the ORDER BY are the different scenarios of what I tried and the results.
For the un-Commented ORDER BY the NewPrevDueDate is resulting in NULL when the PrevDofW is Sat, Sun or the OrigDueDate's Holiday is NOT NULL.
I will calculate the 45-Grace Period Date with the same logic afterwards. Unlesss you see an easier way within this code.
 
I hope I didn't confuse you too much.
 
Thanks and God Bless,
Genesius

Thanks and God Bless,
Genesius

dnoeth 4628 posts Joined 11/04
11 Nov 2014

Hi Genesius,
you will probably not succeed without adding a join.
And to get the first three characters of the weekday you better use CAST(CAST(OrigDueDate AS FORMAT 'E3') AS CHAR(3))

Dieter

TDW 36 posts Joined 11/12
14 Nov 2014

Thanks Dieter.
Here is the final code that calculates everything I need.

CREATE TABLE TDWSTAGE.NJSACECalendar
( /*t1*/
 YoC INTEGER,
 DoY INTEGER,
 DueDate DATE FORMAT 'YY/MM/DD',
 DofW CHAR(3),
    Hol CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
    NewDueDate DATE FORMAT 'YY/MM/DD',
    NewDofW CHAR(3),
 /*t2*/
    GracePeriodEndDate DATE FORMAT 'YY/MM/DD',
    GracePeriodEndDateDofW CHAR(3),
    NewGracePeriodEndDate DATE FORMAT 'YY/MM/DD',
    NewGracePeriodEndDateDofW CHAR(3),
    /*t3*/
    GracePeriodPrevMonth DATE FORMAT 'YY/MM/DD',
    GracePeriodPrevMonthDofW CHAR(3),
    NewGracePeriodPrevMonth2 DATE FORMAT 'YY/MM/DD',
    NewGracePeriodPrevMonthDofW CHAR(3)
)
PRIMARY INDEX (DoY) 
;
INSERT INTO TDWSTAGE.NJSACECalendar
 WITH cte (YofC, DofY, DueDate, DofW, Hol, NewDueDate, NewDofW) AS  
  ( SELECT
        SCC.Year_Of_Calendar AS YofC,
        SCC.Day_of_Year AS DofY,
        SCC.Calendar_Date AS DueDate,
     CAST(CAST(DueDate AS FORMAT 'E4') AS CHAR(3)) AS DofW,
        HC.Holiday AS Hol,
        MIN(CASE WHEN DofW IN ('Sun', 'Sat') OR Hol IS NOT NULL
                THEN NULL
             ELSE DueDate
          END)
         OVER (ORDER BY DueDate ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS NewDueDate,
     CAST(CAST(NewDueDate AS FORMAT 'E4') AS CHAR(3)) AS NewDofW
       FROM SYS_CALENDAR.CALENDAR AS SCC
            LEFT JOIN NJSHolidayCalendar AS HC
             ON DueDate = HC.CalDate
            WHERE YofC = 2014 OR YofC = 2015 OR YofC = 2016 /*ORDER BY 1,2*/)
   SELECT
     t1.*,   
     t2.DueDate AS GracePeriodEndDate,
     CAST(CAST(GracePeriodEndDate AS FORMAT 'E4') AS CHAR(3)) AS GracePeriodEndDateDofW,
     t2.NewDueDate AS NewGracePeriodEndDate,
     CAST(CAST(NewGracePeriodEndDate AS FORMAT 'E4') AS CHAR(3)) AS NewGracePeriodEndDateDofW,    
     t3.DueDate AS GracePeriodPrevMonth,
     CAST(CAST(GracePeriodPrevMonth AS FORMAT 'E4') AS CHAR(3)) AS GracePeriodPrevMonthDofW,
     t3.NewDueDate AS NewGracePeriodPrevMonth,
     CAST(CAST(NewGracePeriodPrevMonth AS FORMAT 'E4') AS CHAR(3)) AS NewGracePeriodPrevMonthDoW 
    FROM cte AS t1
     LEFT JOIN cte AS t2
      ON t2.DueDate = t1.NewDueDate + 45
     LEFT JOIN cte AS t3
      ON t3.DueDate = ADD_MONTHS(t1.DueDate,-1)
     WHERE t1.YofC = 2015 AND t1.DofY <= 366
         OR t1.YofC = 2014 AND t1.DofY BETWEEN 305 AND 366  /*ORDER BY 1,2*/
;

God Bless,
Genesius

Thanks and God Bless,
Genesius

You must sign in to leave a comment.