-->

Transact SQL - nth day of Month (DateName)

maqk® by Unknown | 3:10 AM

One of my projects wants me to identify the given date's day name (DateName) ad find its occurrence in the given month. Say today is Tuesday, 20th May 2014, and this is the 3rd Tuesday of this month.

Once it is identified that the given date [20 may 2014] is 3rd Tuesday of the month, go back to last month's 3rd Tuesday, print sales data, keep going back for last 4 months and print sales data, interesting :) yet tiring too

So we are going to know if today, or any given date's day (DateName) is 2nd Monday of the Month, or 4th Tuesday  ? or 3rd Sunday ? or nth day of Month

Just now I wrote some code to find the occurrence of day in current month. The code is not refactored for any good :D (Sorry its mid mid night and my brain cells are too short i guess). Any ways it does the trick

Declare @du_date datetime, @du_date_FirstDate datetime
Declare @du_date_DateName nvarchar(10), @thDayOccStr nvarchar(4)
Declare @thDayOcc int

Set @du_date = getDate()

Select @du_date_DateName = DATENAME(dw, @du_date)

Set @thDayOcc = 0
Set @du_date_FirstDate = dateadd(day,1-datepart(day,@du_date),@du_date)

While (@du_date_FirstDate <= @du_date)
Begin
--** Calculate which th day it is of this month, next we will find same for last month(s) **--
if (DATENAME(dw, @du_date_FirstDate) = @du_date_DateName)
Begin
Set @thDayOcc += 1
--** FOR LOGGIN , U MAY ENABLE FOLLOWING **--
--Select 'Adding', DATENAME(dw, @du_date_FirstDate_Copy), @du_date_DateName, @thDayOcc
End

Select @du_date_FirstDate = DateAdd(dd,1,@du_date_FirstDate)
End
Set @thDayOccStr = Convert(nvarchar(100), @thDayOcc)

Select [Date processed] = @DU_Date, @du_date_DateName, [DateName occurence in Month] = @thDayOcc,
 @thDayOccStr +
Case
When @thDayOcc = 1 Then 'st '
When @thDayOcc = 2 Then 'nd '
When @thDayOcc = 3 Then 'rd '
Else 'th '
End
+ @du_date_DateName + ' of the Month'
There are tons of blog posts available on the internet guiding to use business connector for read/write operations. Most of them are dealing with how to write opening a table buffer, initializing it, setting field values (set_Field()) and then hitting the write operation.

This is good and simple, and so is sending parameters to table / class, but I could not find the later under any relevant title, hence getting the exactly required thing difficult.

I found my need fulfilled somewhere in Mr. Corey Lasley's blog post: Using the .NET Business Connector to Export...

So the topic actually is about reusing existing business logic. And in doing so, you will need to call several existing, well defined, well tested Class / Table methods. And in doing so.... :D you may need to call paramater less and parameter requiring static methods.

There are two methods inside the 'Axapta' class, 'CallStaticClassMethod' and 'CallStaticRecordMethod'. Both of them calls static methods from Class objects and Table objects respectively. The below code (.Net --> C#) excerpt provides a tutorial for calling an existing existing table method and passing parameters to it.

Axapta ax;
            AxaptaRecord objAxRec;
            Object[] parm = new Object[2]; 
            Object result;
            parm[0] = "Param1Value";
            parm[1] = "Param1Value";

            try
            { 
                ax = new Axapta();
                ax.Logon(null, null, null, null);
                result = ax.CallStaticRecordMethod("InventSum", "find", parm);                
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
As you can see, an array of objects (size of method's parameter count) is assigned values and passed it as the last parameter.

For classes, use 'CallStaticClassMethod'
top