Find Alternate Monday,1st Monday,third Monday and like this

GO
/****** Object:  StoredProcedure [common].[DateInsert]    Script Date: 9/16/2015 5:00:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- PASS @noOfWeek = 0 AND @IsAlternateDate = 1 IF ALTERNATE WEEK NEEDED. 
-- PASS @noOfWeek = 1/2/3/4/5 AND @IsAlternateDate = 0  FOR nth, for example if finding the 3rd Monday, set @noOfWeek=3

-- EXEC [common].[DateInsert] 1,'','','01/01/2015','12/31/2015',2,0,1
ALTER PROCEDURE [common].[DateInsert] 
@ClinicID int,
@StartTime datetime,
@EndTime datetime,
@Start datetime, 
@End datetime,  
@dayno int ,-- 1=Mon, 2=Tue,... 7=Sun
@noOfWeek int, -- nth, for example if finding the 3rd Monday, set @noOfWeek=3
@IsAlternateDate bit 

AS
BEGIN
SET NOCOUNT OFF
SET FMTONLY OFF
DECLARE @COST DECIMAL(18,2)
  
IF OBJECT_ID('dbo.#t') is not null 
 DROP TABLE dbo.#t;

Declare @StartDateOfMonth date

SELECT @StartDateOfMonth= DATEADD(month, DATEDIFF(month, 0, @Start), 0)

CREATE TABLE #t ([Date] datetime,
  [Year] smallint,
  [Quarter] tinyint,
  [Month] tinyint
, [Day] smallint -- from 1 to 366 = 1st to 366th day in a year
, [Week] tinyint -- from 1 to 54 = the 1st to 54th week in a year; 
, [Monthly_week] tinyint -- 1/2/3/4/5=1st/2nd/3rd/4th/5th week in a month
, [Week_day] tinyint -- 1=Mon, 2=Tue, 3=Wed, 4=Thu, 5=Fri, 6=Sat, 7=Sun
);

-- populate the table #t, and the day of week is defined as
-- 1=Mon, 2=Tue, 3=Wed, 4=Thu,5=Fri, 6=Sat, 7=Sun

;WITH   C0   AS (SELECT c FROM (VALUES(1),(1)) AS D(c)),
  C1   AS (SELECT 1 AS c FROM C0 AS A CROSS JOIN C0 AS B),
  C2   AS (SELECT 1 AS c FROM C1 AS A CROSS JOIN C1 AS B),
  C3   AS (SELECT 1 AS c FROM C2 AS A CROSS JOIN C2 AS B),
  C4   AS (SELECT 1 AS c FROM C3 AS A CROSS JOIN C3 AS B), 
  C5   AS (SELECT 1 AS c FROM C4 AS A CROSS JOIN C3 AS B),
  C6   AS (select rn=row_number() over (order by c)  from C5),
  C7   as (select [date]=dateadd(day, rn-1, @Start) FROM C6 WHERE rn <= datediff(day, @Start, @End)+1)

INSERT INTO #t ([year], [quarter], [month], [week], [day], [monthly_week], [week_day], [date])

SELECT datepart(yy, [DATE]), datepart(qq, [date]), datepart(mm, [date]), datepart(wk, [date])
, datediff(day, dateadd(year, datediff(year, 0, [date]), 0), [date])+1
, datepart(week, [date]) -datepart(week, dateadd(month, datediff(month, 0, [date]) , 0))+1
, CASE WHEN datepart(dw, [date])+@@datefirst-1 > 7 THEN (datepart(dw, [date])+@@datefirst-1)%7
ELSE datepart(dw, [date])+@@datefirst-1 END
, [date]
FROM C7
--where [date] between @Start and @End;

--select * from  #t 

-- find nth week/weekend day of each prev/curr/next month
-- eg. find the 2nd Monday of each prev/curr/next month

; with c as (select 
ROW_NUMBER()OVER (ORDER BY [date])AS ROW,
RNK=RANK() over (partition by [month] order by [date] ASC),
Month,Day,Week,
Monthly_week, [Date] -- attention to 'ASC'
from #t
where Week_Day = @dayno -- 1=Mon, 2=Tue,... 7=Sun
--and Monthly_week>=@noOfWeek
)

select * INTO #t2 from c

--select * from #t2 

select ClinicID = @ClinicID,EffectiveDate = @Start,ForDate = [Date],StartTime=@StartTime,EndTime=@EndTime, IsActive=1 -- ,Monthly_week --,ROW--,rn 
from #t2 

where RNK =CASE WHEN ISNULL(@noOfWeek,0) = 0 THEN RNK ELSE @noOfWeek END-- nth, for example if finding the 3rd Monday, set rn=3
and (ROW%2)<> CASE WHEN @IsAlternateDate=1 THEN 0 ELSE 3 END


END

Read Csv File and Upload To database

protected void Upload(object sender, EventArgs e)
    {
        if (FileUpload1.HasFile)
        {
            FileInfo fileInfo = new FileInfo(FileUpload1.PostedFile.FileName);
            if (fileInfo.Name.Contains(".csv"))
            {
                string fileName = fileInfo.Name.Replace(".csv", "").ToString();
                string csvFilePath = Server.MapPath("UploadedCSVFiles") + "\\" + fileInfo.Name;
                //Save the CSV file in the Server inside 'UploadedCSVFiles'   
                FileUpload1.SaveAs(csvFilePath);
                //Fetch the location of CSV file   
                string filePath = Server.MapPath("UploadedCSVFiles") + "\\";
                string strSql = "SELECT * FROM [" + fileInfo.Name + "]";
                string strCsvConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";" + "Extended Properties='text;HDR=YES;'";
                // load the data from CSV to DataTable   
                DataTable dtCsv = new DataTable();
                using (OleDbDataAdapter adapter = new OleDbDataAdapter(strSql, strCsvConnString))
                {
                    adapter.FillSchema(dtCsv, SchemaType.Mapped);
                    adapter.Fill(dtCsv);
                }

                //master foramt csv
                string filePathMaster = Server.MapPath("UploadedCSVFiles") + "\\";
                string csvFilePathMaster = Server.MapPath("UploadedCSVFiles") + "\\Master-getdysonca-orderimport-format.csv";
                string strSqlMaster = "SELECT * FROM [Master-getdysonca-orderimport-format.csv]";
                string strCsvConnStringMaster = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePathMaster + ";" + "Extended Properties='text;HDR=YES;'";
                // load the data from CSV to DataTable   
                DataTable dtCsvMaster = new DataTable();
                using (OleDbDataAdapter adapter = new OleDbDataAdapter(strSqlMaster, strCsvConnStringMaster))
                {
                    adapter.FillSchema(dtCsvMaster, SchemaType.Mapped);
                    adapter.Fill(dtCsvMaster);
                }

                string uploadedFileHeaderString = ConvertHeaderToString(csvFilePath);
                string formatOfUploadedFileHeaderString = ConvertHeaderToString(csvFilePathMaster);
//check template 
                if (uploadedFileHeaderString != formatOfUploadedFileHeaderString)
                {
                    lblmsg.Text = "Template of csv file is different.";
                }
                else
                {
                    foreach (DataRow row in dtCsv.Rows)
                    {
                        object eMail = row["EMAIL"];
                        //check those field which cannot be blank 
                        if (eMail == DBNull.Value || row["PRODUCT01"] == DBNull.Value || row["BILL_ADDRESS1"] == DBNull.Value || row["BILL_CITY"] == DBNull.Value || row["BILL_STATE"] == DBNull.Value || row["BILL_ZIPCODE"] == DBNull.Value
                            || row["SHIP_TO_ADDRESS1"] == DBNull.Value || row["SHIP_TO_CITY"] == DBNull.Value || row["SHIP_TO_STATE"] == DBNull.Value || row["ORDER_NUMBER"] == DBNull.Value || row["ORDER_DATE"] == DBNull.Value)
                        {
                            lblmsg.Text = "Email, Product, Billing address, Shipping Address, Order Number, Order Date field(s) cannot be blank.";

                        }
                        else
                        {
                            var dt = dtCsv;
                            var rowNo=dtCsv.Rows.Count;
                            if (rowNo > 0)
                            {
                                string errMsg = "";
                                int purchaseID = 0;
                                List<int> list = new List<int>();
                                string consString = ConfigurationManager.ConnectionStrings["ecommerceConnectionString"].ToString();
                                using (SqlConnection con = new SqlConnection(consString))
                                {
                                    using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                                    {
                                        //Set the database table name
                                        sqlBulkCopy.DestinationTableName = "dbo.ImportedRawData";
                                        con.Open();
                                        sqlBulkCopy.WriteToServer(dt);
                                        con.Close();
                                    }

                                    int campaignID = Convert.ToInt32(ConfigurationManager.AppSettings["CampaignID"]);
                                    using (var cmd = con.CreateCommand())
                                    {
                                        con.Open();
                                        cmd.CommandText = "[dbo].[ImportDataFromCSV]";
                                        cmd.CommandType = CommandType.StoredProcedure;
                                        cmd.Parameters.Add(new SqlParameter("CampaignID", campaignID));
                                        cmd.Parameters.Add(new SqlParameter("FileName", fileInfo.Name));
                                        
                                        using (var reader = cmd.ExecuteReader())
                                        {
                                            if (reader.HasRows)
                                            {

                                                    //var result = reader[0];

                                                    while (reader.Read())
                                                    {
                                                        if (Convert.ToString(reader["ErrMsg"]) == "BLANK")
                                                        {
                                                        //purchaseID = Convert.ToInt32(reader["ImportedPurchaseID"]);
                                                        // errMsg = Convert.ToString(reader["ErrMsg"]);
                                                        list.Add(Convert.ToInt32(reader["ID"]));
                                                        }

                                                        else
                                                            list.Add(Convert.ToInt32(reader["ErrMsg"]));
                                                    }
                                                //purchaseID = Convert.ToInt32(list[0]);
                                                //errMsg = list[1].ToString();
                                            }
                                        }
                                        con.Close();
                                    }
                                    for (int j = 0; j < list.Count() ; j++)
                                    {
                                        purchaseID = Convert.ToInt32(list[j]);
                                        ImportedPurchase importedPurchase = InsertDataIntoImportedPurchase(purchaseID);

                                        //send mail
                                        new PaymentModule().MailRichConfirmationForImport(importedPurchase);
                                    }
                                }

                                if (errMsg == "")
                                {
                                    lblmsg.Text = string.Format("({0}) records has been loaded to the table.", rowNo, fileName);
                                }
                                else
                                    lblmsg.Text = errMsg;
                            }
                            else
                            {
                                lblmsg.Text = "File is empty.";
                            }
                        }

                    }

                }
            }
            else
            {
                lblmsg.Text = "Upload csv file only.";
            }
        }
    }

    public string ConvertHeaderToString(string fileNameWithPath)
    {
        //bool firstLineOfChunk = true;
        string columnData = null;
        bool firstLineOfFile = true;
        using (var sr = new StreamReader(fileNameWithPath))
        {
            string line = null;

            while ((line = sr.ReadLine()) != null)
            {
                if (firstLineOfFile)
                {
                    columnData = line;
                    firstLineOfFile = false;
                    continue;
                }
            }
        }
        return columnData;
    }


    public bool CompareDataTable(DataTable t1, DataTable t2)
    {
        if (t1.Rows.Count != t2.Rows.Count)
            return false;

        foreach (DataColumn dc in t1.Columns)
        {
            for (int i = 0; i < t1.Rows.Count; i++)
            {
                if (t1.Rows[i][dc.ColumnName] != t2.Rows[i][dc.ColumnName])
                {
                    return false;
                }
            }
        }
        return true;
    }