DSXchange

Posted: **Wed May 17, 2006 11:48 am**

Actually, I'll probably create something more generic so it can find the nth whatever of a month - first Saturday, second Tuesday, etc.

However, I'm having a bit of a brain fart. I know we've got the TAG transforms to work with and can get the week number of the year for example. I can also rather easily determine the day of the week any particular date belongs to. But what I can't seem to put all together elegantly is if a given passed in date is the 'First Saturday' of the month or not.

Anybody want to give me a nudge in the right direction?

Posted: **Wed May 17, 2006 12:56 pm**

It's not a generic solution, but.......

E.g., our FISCAL_DAY dimension has both a CALENDAR_DAY (i.e., 1 - 31) and DAY_OF_WEEK (e.g., SAT) attribute.

My solution would be to select CALENDAR_DATE(s) from FISCAL_DAY where DAY_OF_WEEK = 'SAT' and CALENDAR_DAY < 8, and load to a hashed file to be used as a reference for the primary input dates.

Posted: **Wed May 17, 2006 12:59 pm**

I would say day of the week equals Saturday and daynumber is 7 or less and that's all it takes to see if the passed date is the First Saturday of the month. But is that what you mean indeed? It sounds too simple

Posted: **Wed May 17, 2006 1:18 pm**

Hello Craig,

Just a thought. You said you could easily find out what day it is when given a particular date (say in the format MM/DD/YYYY).

If you can now find out what day is the first of every month. Then you can easily figure out the 1st Monday or 1st Tuesday and so on.

Lets say, we would be passing in a date in the format MM/DD/YYYY. Pluck the month and year out of this given date. Substitute 01 for the first day of the month. Now the date would become MM/01/YYYY.

Now as you know how to find out the day when a particular date is given. Find out the day of MM/01/YYYY.

Code: Select all


         If ( MM/01/YYYY ) = 'Monday'
            Then    FirstMonday    =  MM/DD/YYYY
                    FirstTuesday   =  MM/DD+1/YYYY
                    FirstWednesday =  MM/DD+2/YYYY
                    FirstThursday  =  MM/DD+3/YYYY
                    FirstFriday    =  MM/DD+4/YYYY
                    FirstSaturday  =  MM/DD+5/YYYY
                    FirstSunday    =  MM/DD+6/YYYY
         Else If ( MM/01/YYYY ) = 'Tuesday'
            Then
                    FirstMonday    =  MM/DD-1/YYYY
                    FirstTuesday   =  MM/DD/YYYY
                    FirstWednesday =  MM/DD+1/YYYY
                    FirstThursday  =  MM/DD+2/YYYY
                    FirstFriday    =  MM/DD+3/YYYY
                    FirstSaturday  =  MM/DD+4/YYYY
                    FirstSunday    =  MM/DD+5/YYYY
         Else If ( MM/01/YYYY ) = 'Wednesday'
            Then
                    FirstMonday    =  MM/DD-2/YYYY
                    FirstTuesday   =  MM/DD-1/YYYY
                    FirstWednesday =  MM/DD/YYYY
                    FirstThursday  =  MM/DD+1/YYYY
                    FirstFriday    =  MM/DD+2/YYYY
                    FirstSaturday  =  MM/DD+3/YYYY
                    FirstSunday    =  MM/DD+4/YYYY
         Else If ( MM/01/YYYY ) = 'Thursday'
            Then
                    FirstMonday    =  MM/DD-3/YYYY
                    FirstTuesday   =  MM/DD-2/YYYY
                    FirstWednesday =  MM/DD-1/YYYY
                    FirstThursday  =  MM/DD/YYYY
                    FirstFriday    =  MM/DD+1/YYYY
                    FirstSaturday  =  MM/DD+2/YYYY
                    FirstSunday    =  MM/DD+3/YYYY
         Else If ( MM/01/YYYY ) = 'Friday'
            Then
                    FirstMonday    =  MM/DD-4/YYYY
                    FirstTuesday   =  MM/DD-3/YYYY
                    FirstWednesday =  MM/DD-2/YYYY
                    FirstThursday  =  MM/DD-1/YYYY
                    FirstFriday    =  MM/DD/YYYY
                    FirstSaturday  =  MM/DD+1/YYYY
                    FirstSunday    =  MM/DD+2/YYYY
         Else If ( MM/01/YYYY ) = 'Saturday'
            Then
                    FirstMonday    =  MM/DD-5/YYYY
                    FirstTuesday   =  MM/DD-4/YYYY
                    FirstWednesday =  MM/DD-3/YYYY
                    FirstThursday  =  MM/DD-2/YYYY
                    FirstFriday    =  MM/DD-1/YYYY
                    FirstSaturday  =  MM/DD/YYYY
                    FirstSunday    =  MM/DD+1/YYYY
         Else If ( MM/01/YYYY ) = 'Sunday'
            Then
                    FirstMonday    =  MM/DD-6/YYYY
                    FirstTuesday   =  MM/DD-5/YYYY
                    FirstWednesday =  MM/DD-4/YYYY
                    FirstThursday  =  MM/DD-3/YYYY
                    FirstFriday    =  MM/DD-2/YYYY
                    FirstSaturday  =  MM/DD-1/YYYY
                    FirstSunday    =  MM/DD/YYYY

Now you have the 1st Mon,Tue, Wed, Thu, Fri, Sat and Sun of a given date.

May be it could be more efficient, this is what I could get from the top of my head.

Thanks,
Whale.

Posted: **Wed May 17, 2006 1:35 pm**

Craig,
I came up with a generic routine. Very basic.
It takes in two arguments
Arg1 = Date
Arg2 = Day of the week to check
Valid inputs for Arg2 are (Monday, tuesday, wednesday, thursday, friday, saturday, sunday)

If the specified day is found in the first week of the date, the routine returns 1 else returns 0

Code: Select all

      InDate = Iconv(Trim(Arg1),"DYMD")
      DayToFind = UPCASE(Trim(Arg2))
      DayByName = ''
      DayByNum= ''

      DayByName = Oconv(InDate,"DWA")
      DayByNum = Oconv(InDate,"D D")


      If (DayByName = DayToFind AND DayByNum < 8)
      Then
         Ans = @TRUE
      END
      Else
         Ans = @FALSE
      End

My 2 cents

Posted: **Wed May 17, 2006 3:03 pm**

Take a look at the routine that calculates WEEK.TAG - you can probably adapt its business rule algorithm.

Or you can use the fact that Mod(internal_date,7) is 6 if the day is Saturday, and adjust forward from Iconv(first_of_month,"D").

Posted: **Wed May 17, 2006 10:16 pm**

Thanks for all the 'nudges'.

Hopefully will get some time in the next couple of days to work on this little side project and see what comes of it all.

Thanks again!

Posted: **Thu May 18, 2006 2:48 am**

Off the top of my head:

Code: Select all

FUNCTION DayOfMonth(Year, Month, Day, WhichOne)
Ans = @NULL
DayList = "SUNDAY|MONDAY|TUESDAY|WEDNESDAY|THURSDAY|FRIDAY|SATURDAY"
Convert "|" To @FM In DayList
Msg = ""
* Calculate date of first of month
MonthFirst = Iconv(Year : "-" : Month : "-01", "DYMD")
TestDate = MonthFirst
StartMonth = Oconv(TestDate, "DM")
If Status() = 0
Then
   If Day Matches "1A0A"
   Then
      FindStr Upcase(Day) In DayList Setting FMC 
      Then 
         DayNumber = FMC - 1
         Counter = 0
         Found = @FALSE
         Loop
         While Oconv(TestDate, "DM") = StartMonth
            TheDay = Mod(TestDate,7)
            If DayNumber = TheDay Then Counter += 1
            If Counter = WhichOne 
            Then 
               Found = @TRUE
               Ans = TestDate
               Exit
            End
            TestDate += 1
         Repeat
         If IsNull(Ans)
         Then
            Msg = 'Requested date not found.'
         End
      End
      Else
         Msg = 'Day not a known day.'
      End
   End
   Else
      Msg = 'Day not a day name (not wholly alphabetic).'
   End
End
Else
   Msg = 'Invalid year/month combination.'
End
If Len(Msg) > 0
Then
   Call DSTransformError(Msg, "DayOfMonth")
End
RETURN(Ans)

Posted: **Thu May 18, 2006 7:27 am**

Nice top of your head, Ray.

I did manage to slap something together last night, taking a similar approach. Not quite the same level of error checking and messaging that you did, perhaps I'll 'borrow' some of that.

Code: Select all

FUNCTION IsCorrectDate(DateToCheck,Iteration,DayName)
Ans = -99
lvFound = @FALSE
lvIteration = 0
* Make the day name upper case to match the OConv output
lvDayName = upcase(DayName)
* Valdiate the incoming date to check
lvTargetDate = OConv(IConv(DateToCheck,"DYMD"),"D-YMD[4,2,2]")
If Len(lvTargetDate) # 0 Then
  lvMonth = Field(lvTargetDate,"-",2,1)
  lvYear = Field(lvTargetDate,"-",1,1)
  lvCheckDate = IConv(lvYear:"-":lvMonth:"-01","D-YMD")
  lvEndDate = IConv(lvYear:"-":lvMonth+1:"-01","D-YMD") - 1
  * Basically, we are going to iterate thru all the days in the 
  * passed in year/month, checking each one. Exit when we run off
  * the end of the month or if we find the Nth Day.
  For lvCnt = 1 to 31 step 1
    If OConv(lvCheckDate,"DWA") = lvDayName Then
      * Day name of the week matches
      lvIteration += 1
      If lvIteration = Iteration Then
        * Get out if this is the iteration
        * we want to check
        lvFound = @TRUE
        lvFoundDate = OConv(lvCheckDate,"D-YMD[4,2,2]")
        Exit
      End
    End
    lvCheckDate +=1
    If lvCheckDate > lvEndDate Then
      Exit
    End
  Next lvCnt
  *
  * If the date corresponding to the Nth Day found equals our target
  * date, pass back the good news.
  If lvFound And lvFoundDate = lvTargetDate Then
    Ans=@TRUE
  End Else
    Ans=@FALSE
  End
End

DSXchange

First Saturday of the month

First Saturday of the month

Re: First Saturday of the month