faizan ahmad

I ended up using the MS Excel objects to convert files from XLS to CSV . Converted CSV had comma "," as field separator. But, what if the a filed content has comma like "lastName,FirstName". Now if you use default .net split method to get the fileds of a row in csv file, it will treat "lastName,FirstName" as two fields. I ended up writing my own split method which will take care of the comma in fields:

    Private Function CustomSplit(ByVal sText As String,ByVal sSeparator As String) As String()
        Dim RetrunArray As  New ArrayList
        Dim CurrentIndex As Int32
        Dim CommaIndex As Int32
        Dim CurrentField As String
        Dim NextChar As String
       
        CommaIndex = sText.IndexOf(sSeparator)
        While(CommaIndex>0 and CommaIndex<sText.Length)
            CurrentField = sText.Substring(CurrentIndex,CommaIndex-CurrentIndex)
            RetrunArray.Add(CurrentField)
            NextChar = sText.Substring(CommaIndex+1,1)
            If(NextChar.Equals ("""")) Then
                CurrentIndex = CommaIndex +1
                CommaIndex = sText.IndexOf("""",CommaIndex+2)
                CommaIndex = CommaIndex +1
                If(CommaIndex<sText.Length)
                    NextChar = sText.Substring(CommaIndex,1)               
                    If(Not NextChar.Equals (",")) Then
                        CommaIndex = sText.IndexOf(",",CommaIndex+2)
                    End If
                  
                End If
            Else
                CurrentIndex = CommaIndex +1
                CommaIndex = sText.IndexOf(sSeparator,CurrentIndex)
            End If           
        End While
        'Pick up last field
        CurrentField = sText.Substring(CurrentIndex)
        RetrunArray.Add(CurrentField)

        Return RetrunArray.ToArray(Type.GetType("System.String"))
       
    End Function
 

Presumption/limitation:

1. yearly holidays are stored in a table
2. maximum 2 consecutive holidays

Table structure: HOLIDAY_DATE (DATETIME),HOLIDAY_DESC (VARCHAR)

DB Script :

DECLARE @CurrentDay DATETIME
DECLARE @LastWorkingDay DATETIME, @CurrentWeekDay INT

SET @CurrentDay = CAST(CONVERT(VARCHAR, GETDATE(), 101) AS DATETIME)

SET @LastWorkingDay = CAST(CONVERT(VARCHAR, GETDATE(), 101) AS DATETIME)

DECLARE @DaysToLastWorkingDay INT

SET @DaysToLastWorkingDay = 0

SET @LastWorkingDay = DATEADD(day, -1, @LastWorkingDay )

--check if yesterday was holiday

IF EXISTS (SELECT HOLIDAY_DATE FROM tblHOLIDAY WHERE HOLIDAY_DATE =CAST(CONVERT(VARCHAR, @LastWorkingDay, 101) AS DATETIME))                                                           

BEGIN                                                                                                                                                                                                            SET @LastWorkingDay= DATEADD(day,-1, @LastWorkingDay)
END                                                                                                                                                        

-- adjust for weekends, if needed

SET @CurrentWeekDay = DATEPART(weekday, @LastWorkingDay)

IF @CurrentWeekDay IN (1, 7)

SET @DaysToLastWorkingDay = (CASE @CurrentWeekDay

WHEN 1 THEN -2            -- 1 = Sunday

WHEN 7 THEN -1            -- 7 = Saturday

ELSE 0

END)

SET @LastWorkingDay = DATEADD(day,@DaysToLastWorkingDay, @LastWorkingDay)

IF EXISTS (SELECT HOLIDAY_DATE FROM tblHOLIDAY WHERE HOLIDAY_DATE =CAST(CONVERT(VARCHAR, @LastWorkingDay, 101) AS DATETIME))

BEGIN

SET @DaysToLastWorkingDay = -1

IF EXISTS (SELECT HOLIDAY_DATE FROM tblHOLIDAY WHERE HOLIDAY_DATE =CAST(CONVERT(VARCHAR, DATEADD(day, -1, @LastWorkingDay), 101) AS DATETIME))

SET @DaysToLastWorkingDay = @DaysToLastWorkingDay - 1

SET @LastWorkingDay= DATEADD(day,@DaysToLastWorkingDay, @LastWorkingDay)

END
SELECT @LastWorkingDay