Power BI

Power BI

VBA code for PDF converter

Convert PDF2Excel

Window Schedule task

Yhttps://www.youtube.com/watch?v=Va-GdAjwOcY

Types of Cursor in MSSQL

SQL Server Different Types of Cursors

           

A Cursor allow us to retrieve data from a result set in singleton fashion means row by row. Cursor are required when we need to update records in a database table one row at a time. I have already explained the basic of cursor.

A Cursor impacts the performance of the SQL Server since it uses the SQL Server instances' memory, reduce concurrency, decrease network bandwidth and lock resources. Hence it is mandatory to understand the cursor types and its functions so that you can use suitable cursor according to your needs.
You should avoid the use of cursor. Basically you should use cursor alternatives like as WHILE loop, sub queries, Temporary tables and Table variables. We should use cursor in that case when there is no option except cursor.

Windows Installer Setup With Custom Actions

Custom Action Report

Cam Recording code in VBA

Download Link

Install Window Service Programmitcally

You can do it all in one executable. To pass installation parameters, you will need to derive classes System.ServiceProcess.ServiceProcessInstaller and System.ServiceProcess.ServiceInstaller. You only need to implement constructors of your classes.

Installation and uninstallation will be done by System.Configuration.Install.AssemblyInstaller. This class should use the assembly where the classes based on ServiceProcessInstaller and ServiceInstaller are implemented; the implementation will be found in your assembly automatically (which would create a hard-to-detect failure if you have a bug in the implementation). Not too bad though.

Finally, start/stop, etc. are handled by your service code which you create deriving the class System.ServiceProcess.ServiceBase. Triggering this actions is done by Service Controller. To do it programmatically you should use the class System.ServiceProcess.ServiceController.

You need to have a good reading of MSDN on the topics around the classes I listed above, which is well illustrated by code samples. This information is easy to find, a bit harder to understand the work flow and what happens in which process. (A Windows Service process is always a separate one; it takes another process to run installation and Service Controller part.) You can use all this like a cookbook, but I suggest you thoroughly understand it; in this case you won't have any problems.

You can even try to implement it all in one application as I did, but you still need to run it in at least two different processes: one would work in the Windows Service mode, another is must be interactive mode, which can be anything else, such as UI, Console or just invisible batch-mode application.

Important! You code can test System.Environment.UserInteractive during run time to calculate is currently running code is run as Windows Service or not.

Add Installer for Window Service

In the service project do the following:

1. In the solution explorer double click your services .cs file. It should bring up a screen that is all gray and talks about dragging stuff from the toolbox.
2. Then right click on the gray area and select add installer. This will add an installer project file to your project.
3. Then you will have 2 components on the design view of the ProjectInstaller.cs (serviceProcessInstaller1 and serviceInstaller1). You should then setup the properties as you need such as service name and user that it should run as.

Start window Servicesin C#.Net

Start window Services

Develop & Install window Service in C#

Develop & install window Service

Directives in asp.net

http://www.tutorialspoint.com/asp.net/asp.net_directives.htm

Looping through resutset in Table Valued Function

Database CASKoenigDB

alter function [dbo].[getFullMonthforRC](@TrainerID int,@CourseSDate datetime,@CourseEDate datetime,@LocationId int,@weekendstatus varchar(30),@courselist nvarchar(max)) 
returns  @TableofDates table(TrainerID int not null,trngdate datetime not null,locationid int not null) 
Begin 
declare @trnid int 
declare @lnid int 
Declare @startDate datetime 
declare @weekendbatch varchar(30)
Declare @interestedCourses nvarchar(max)
Declare @excluddates  datetime  
Declare @dateval  datetime
Declare @Count int
Declare @Loopcount int
Declare @endDate datetime
set @trnid=@TrainerID 
set @startDate=@CourseSDate 
 
set @endDate=@CourseEDate 
set @lnid=@LocationId 
set @weekendbatch=@weekendstatus 
set @interestedCourses=@courselist
  While (@startDate<=@endDate) begin
  if exists(Select  cast (ExcludedDate as nvarchar(max))  from TRAN_EXCLUDEDAY where InterestedCourseId=@interestedCourses)
  begin
  --counting total records
   Select @Count=count(*) from dbo.Split ( (Select  cast (ExcludedDate as nvarchar(max))  from TRAN_EXCLUDEDAY where InterestedCourseId=@interestedCourses),';')
   Set @Loopcount=1
   While  @Loopcount<=@Count
   begin
    Select @dateval=items from( Select *,ROW_NUMBER()over(Order by items)ID from dbo.Split ( (Select  cast (ExcludedDate as nvarchar(max))  from TRAN_EXCLUDEDAY where InterestedCourseId=@interestedCourses),';') ) RC where ID=@Loopcount 
   
    Set @Loopcount=@Loopcount+1
    if (@dateval=@startDate)  begin
      
       set @startDate=DATEADD(DAY, 1, @startDate)
    end
   
   end
   if  (@startDate<=@endDate) begin
     --print @startDate
     INSERT INTO @TableOfDates(TrainerID,trngdate,locationid) VALUES (@trnid,@startDate,@lnid) 
     set @startDate=DATEADD(DAY, 1, @startDate)
   end
  end
  else if not exists(Select  cast (ExcludedDate as nvarchar(max))  from TRAN_EXCLUDEDAY where InterestedCourseId=@interestedCourses )begin 
    --print @startDate
    INSERT INTO @TableOfDates(TrainerID,trngdate,locationid) VALUES (@trnid,@startDate,@lnid) 
    set @startDate=DATEADD(DAY, 1, @startDate)
  end 
end   
  
return 
End 

Looping through resultset without using cursor

Database:CasKoenigDB

Declare @dateval as datetime

Declare @Count int

Declare @Loopcount int

Select IDENTITY(int, 1,1) ID,items into #Temp from dbo.Split ( (Select cast (ExcludedDate as nvarchar(max)) from TRAN_EXCLUDEDAY where InterestedCourseId=1707),';')

Select @Count=@@ROWCOUNT

Set @Loopcount=1

While @Loopcount<=@Count

begin

Select @dateval=items from #Temp where ID=@Loopcount

Set @Loopcount=@Loopcount+1

print @dateval



end
 Drop table #Temp

SQL query using Union All in pivottable

/****** Script for SelectTopNRows command from SSMS  ******/
DECLARE @TrainerID INT
DECLARE @CourseSDate DATETIME
DECLARE @CourseEDate DATETIME
DECLARE @LocationId INT
Declare @batchstatus varchar(30)
create TABLE #tbl1 (TrainerID INT,trngdate DATETIME, locationid INT)
DECLARE LDates CURSOR
FOR

SELECT mtt.TrainerID,TRAN_SCINTERESTEDCOURSES.coursesdate,TRAN_SCINTERESTEDCOURSES.courseedate,TRAN_SCINTERESTEDCOURSES.LocationId,TRAN_SCINTERESTEDCOURSES.tscheduletype as BatchStatus from TRAN_SCINTERESTEDCOURSES
join MST_LOCATION on TRAN_SCINTERESTEDCOURSES.LocationId = MST_LOCATION.LocationId
right outer join MST_TRAINER mtt on mtt.TrainerID=  TRAN_SCINTERESTEDCOURSES.TrainerId and TRAN_SCINTERESTEDCOURSES.TrainerId IS NOT NULL
where
  CONVERT(datetime,'2016-05-01')
        between DATEADD(month, DATEDIFF(month, 0, coursesdate), 0)
            and DATEADD(month, DATEDIFF(month, 0, courseedate), 0) ORDER BY CourseSDate
           
OPEN LDates
FETCH NEXT FROM LDates
INTO @TrainerID,@CourseSDate,@CourseEDate,@LocationId ,@batchstatus
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #tbl1

SELECT TrainerID ,trngdate,locationid FROM getFullMonthforRC(@TrainerID,@CourseSDate,@CourseEDate,@LocationId,@batchstatus)
FETCH NEXT FROM LDates

INTO @TrainerID,@CourseSDate,@CourseEDate,@LocationId,@batchstatus
END
CLOSE LDates
DEALLOCATE LDates

select  EmpId, TrainerName,CASE WHEN [1] > 1 THEN 1 ELSE [1] END AS [1],CASE WHEN [2] > 1 THEN 1 ELSE [2] END AS [2],CASE WHEN [3] > 1 THEN 1 ELSE [3] END AS [3],CASE WHEN [4] > 1 THEN 1 ELSE [4] END AS [4],CASE WHEN [5] > 1 THEN 1 ELSE [5] END AS [5],CASE WHEN [6] > 1 THEN 1 ELSE [6] END AS [6],CASE WHEN [7] > 1 THEN 1 ELSE [7] END AS [7],CASE WHEN [8] > 1 THEN 1 ELSE [8] END AS [8],CASE WHEN [9] > 1 THEN 1 ELSE [9] END AS [9],CASE WHEN [10] > 1 THEN 1 ELSE [10] END AS [10],CASE WHEN [11] > 1 THEN 1 ELSE [11] END AS [11],CASE WHEN [12] > 1 THEN 1 ELSE [12] END AS [12],CASE WHEN [13] > 1 THEN 1 ELSE [13] END AS [13],CASE WHEN [14] > 1 THEN 1 ELSE [14] END AS [14],CASE WHEN [15] > 1 THEN 1 ELSE [15] END AS [15],CASE WHEN [16] > 1 THEN 1 ELSE [16] END AS [16],CASE WHEN [17] > 1 THEN 1 ELSE [17] END AS [17],CASE WHEN [18] > 1 THEN 1 ELSE [18] END AS [18],CASE WHEN [19] > 1 THEN 1 ELSE [19] END AS [19],CASE WHEN [20] > 1 THEN 1 ELSE [20] END AS [20],CASE WHEN [21] > 1 THEN 1 ELSE [21] END AS [21],CASE WHEN [22] > 1 THEN 1 ELSE [22] END AS [22],CASE WHEN [23] > 1 THEN 1 ELSE [23] END AS [23],CASE WHEN [24] > 1 THEN 1 ELSE [24] END AS [24],CASE WHEN [25] > 1 THEN 1 ELSE [25] END AS [25],CASE WHEN [26] > 1 THEN 1 ELSE [26] END AS [26],CASE WHEN [27] > 1 THEN 1 ELSE [27] END AS [27],CASE WHEN [28] > 1 THEN 1 ELSE [28] END AS [28],CASE WHEN [29] > 1 THEN 1 ELSE [29] END AS [29],CASE WHEN [30] > 1 THEN 1 ELSE [30] END AS [30],CASE WHEN [31] > 1 THEN 1 ELSE [31] END AS [31]
from
(SELECT distinct t1.EmpId,t1.TrainerName,t2.LocationName, Day(#tbl1.trngdate)as CourseDate FROM #tbl1  join MST_TRAINER t1
on #tbl1.TrainerID=t1.TrainerId LEFT Join MST_LOCATION t2 on t2.LocationId=#tbl1.locationid where t1.EmpId is not null  AND MONTH(#tbl1.trngdate)='05' ) RC
Pivot
(
COUNT(LocationName) for CourseDate in([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31])
)as Pivottable
union all
(SELECT EmpId, TrainerName,0 as [1],0 as [2],0 as [3],0 as [4],0 as [5],0 as [6],0 as [7],0 as [8],0 as [9],0 as [10],0 as [11],0 as [12],0 as [13],0 as [14],0 as [15],0 as [16],0 as [17],0 as [18],0 as [19],0 as [20],0 as [21],0 as [22],0 as [23],0 as [24],0 as [25],0 as [26],0 as [27],0 as [29],0 as [29],0 as [30],0 as [31]  from MST_TRAINER where isactive=1 and trainerid not in
(SELECT mtt.TrainerID from TRAN_SCINTERESTEDCOURSES
right outer join MST_TRAINER mtt on mtt.TrainerID=  TRAN_SCINTERESTEDCOURSES.TrainerId and TRAN_SCINTERESTEDCOURSES.TrainerId IS NOT NULL
where
  CONVERT(datetime,'2016-05-01')
        between DATEADD(month, DATEDIFF(month, 0, coursesdate), 0)
            and DATEADD(month, DATEDIFF(month, 0, courseedate), 0) ) and EmpId is not null   )
Drop table #tbl1

Recursive CTEs in AdventureWorks 2008

With myCTE(BusinessEnityID,ManagerId,lv)

As(

Select BusinessEntityID,ManagerId,1 From HumanResources.Employee

where ManagerID is null

union all

Select e.BusinessEntityID, e.ManagerId, lv+1 from HumanResources.Employee e

join myCTE on e.ManagerID=myCTE.BusinessEnityID



)
 Select emp.BusinessEntityID,emp.JobTitle, c.FirstName+' '+C.LastName as [Name],

M.FirstName+'' +M.LastName as [Manager],lv

from HumanResources.Employee emp

join myCTE

on emp.BusinessEntityID=myCTE.BusinessEnityID

join Person.Person as C

on c.BusinessEntityID=emp.BusinessEntityID

Left Join Person.Person as M

on emp.ManagerID=M.BusinessEntityID order by lv

Pivot Example using a CTE

use AdventureWorks2008
GO
-- First we'll retrieve Orders from 2003 and place into a CTE
WITH OrdCTE AS (
SELECT VendorID, Name As VendorName,  DatePart(m,OrderDate) AS OrderMonth, POHdr.TotalDue
FROM Purchasing.PurchaseOrderHeader POHdr
join Purchasing.Vendor ON Vendor.BusinessEntityID = POHdr.VendorID
where YEAR(OrderDate) = 2003
)   
-- Now we'll query the CTE, and use a PIVOT statement           
SELECT VendorID, VendorName, [1] AS Jan,[2] AS Feb,[3] AS Mar,[4] AS Apr, 
 [5] AS May, [6] AS June, [7] AS July, [8] AS Aug, 
 [9] AS Sep, [10] AS Oct, [11] AS Nov, [12] AS Dec
FROM OrdCTE
-- we take each of the 12 possible values and associate them with 12 buckets
PIVOT (SUM(TotalDue) FOR OrderMonth IN ([1],[2],[3],[4],
 [5], [6], [7], [8],           
 [9], [10], [11], [12]))  as TempList

Query for Multiple CTEs

With CTE1 AS (

Select ProductID,sum(OrderQty) as TotalOrderQty from Sales.SalesOrderDetail

group by ProductID



),
CTE2 as(

Select p.ProductID,pc.ProductCategoryID, pc.Name from Production.Product p join Production.ProductSubcategory psc

on psc.ProductSubcategoryID=p.ProductSubcategoryID

join Production.ProductCategory pc

on pc.ProductCategoryID=psc.ProductCategoryID



)
Select * from (

Select CTE2.ProductCategoryId,CTE2.Name,CTE1.ProductId,CTE1.TotalOrderQty,

rn=ROW_NUMBER() over( PARTITION by CTE2.ProductCategoryId order by CTE1.TotalOrderQty desc)

from CTE1 join CTE2 on cte1.ProductID=cte2.ProductID

) CTE where rn<=3

Function in sql for removing weekend

ALTER Function [dbo].[calculateTotalTestData11](@interestCourseId int)   

returns nvarchar(max) --@temptable TABLE (dates datetime)      

AS   

BEGIN  

DECLARE @stDate DateTime;

DECLARE @endDate DateTime;

DECLARE @Location int;

SELECT @stDate=CourseSDate,@endDate=CourseEDate,@Location=LocationId from TRAN_SCINTERESTEDCOURSES where InterestedCourseId=@interestCourseId;

DECLARE @temptable table(dates datetime) ;

; WITH CTE(dt) 

 AS 

 (  

 SELECT @stDate 

 UNION ALL 

 SELECT DATEADD(D,1,dt) FROM CTE 

 where dt < @endDate 

 ) 

  insert into @temptable(dates) 

 select dt  from CTE where (CASE WHEN @location = 5  

 THEN (CASE WHEN DATENAME(dw, dt) = 'FRIDAY' THEN 1 ELSE 0 END)  

 ELSE  ( 

 CASE WHEN @location = 8 OR @location = 9

 THEN ((CASE WHEN DATENAME(dw, dt) = 'SATURDAY' OR  DATENAME(dw, dt) = 'SUNDAY'  THEN 1 ELSE 0 END)) 

 ELSE (CASE WHEN DATENAME(dw, dt) = 'Sunday' THEN 1 ELSE 0 END)  END) 

 END )=1 and dt <> @stDate and dt <>@endDate;

  

 return   STUFF((SELECT DISTINCT '; ' + CONVERT(nvarchar(11),dates,113) FROM @temptable FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

END

Query for Specific Date Falling between Date Range

SELECT TRAN_SCINTERESTEDCOURSES.TrainerId,TRAN_SCINTERESTEDCOURSES.coursesdate,TRAN_SCINTERESTEDCOURSES.courseedate,TRAN_SCINTERESTEDCOURSES.LocationId,MST_LOCATION.LocationName from TRAN_SCINTERESTEDCOURSES  
inner join MST_LOCATION on TRAN_SCINTERESTEDCOURSES.LocationId = MST_LOCATION.LocationId where TrainerId is not null
and     CONVERT(datetime,'2016-05-01')
        between DATEADD(month, DATEDIFF(month, 0, CourseSDate), 0)
            and DATEADD(month, DATEDIFF(month, 0, CourseEDate), 0)

SQL Query for Pivottable

Database CASKoenigDB:
DECLARE @TrainerID INT
DECLARE @CourseSDate DATETIME
DECLARE @CourseEDate DATETIME
DECLARE @LocationId INT
Declare @batchstatus varchar(30)
create TABLE #tbl1 (TrainerID INT,trngdate DATETIME, locationid INT)
DECLARE LDates CURSOR
FOR
SELECT TRAN_SCINTERESTEDCOURSES.TrainerId,TRAN_SCINTERESTEDCOURSES.coursesdate,TRAN_SCINTERESTEDCOURSES.courseedate,TRAN_SCINTERESTEDCOURSES.LocationId,TRAN_SCINTERESTEDCOURSES.tscheduletype as BatchStatus from TRAN_SCINTERESTEDCOURSES
join MST_LOCATION on TRAN_SCINTERESTEDCOURSES.LocationId = MST_LOCATION.LocationId where TrainerId IS NOT NULL
and     CONVERT(datetime,'2016-05-01')
        between DATEADD(month, DATEDIFF(month, 0, coursesdate), 0)
            and DATEADD(month, DATEDIFF(month, 0, courseedate), 0) ORDER BY TRAN_SCINTERESTEDCOURSES.CourseSDate
OPEN LDates
FETCH NEXT FROM LDates
INTO @TrainerID,@CourseSDate,@CourseEDate,@LocationId ,@batchstatus
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #tbl1

SELECT TrainerID ,trngdate,locationid FROM getFullMonthforRC(@TrainerID,@CourseSDate,@CourseEDate,@LocationId,@batchstatus)
FETCH NEXT FROM LDates

INTO @TrainerID,@CourseSDate,@CourseEDate,@LocationId,@batchstatus
END
CLOSE LDates
DEALLOCATE LDates
select  EmpId, TrainerName,CASE WHEN [1] > 1 THEN 1 ELSE [1] END AS [1],CASE WHEN [2] > 1 THEN 1 ELSE [2] END AS [2],CASE WHEN [3] > 1 THEN 1 ELSE [3] END AS [3],CASE WHEN [4] > 1 THEN 1 ELSE [4] END AS [4],CASE WHEN [5] > 1 THEN 1 ELSE [5] END AS [5],CASE WHEN [6] > 1 THEN 1 ELSE [6] END AS [6],CASE WHEN [7] > 1 THEN 1 ELSE [7] END AS [7],CASE WHEN [8] > 1 THEN 1 ELSE [8] END AS [8],CASE WHEN [9] > 1 THEN 1 ELSE [9] END AS [9],CASE WHEN [10] > 1 THEN 1 ELSE [10] END AS [10],CASE WHEN [11] > 1 THEN 1 ELSE [11] END AS [11],CASE WHEN [12] > 1 THEN 1 ELSE [12] END AS [12],CASE WHEN [13] > 1 THEN 1 ELSE [13] END AS [13],CASE WHEN [14] > 1 THEN 1 ELSE [14] END AS [14],CASE WHEN [15] > 1 THEN 1 ELSE [15] END AS [15],CASE WHEN [16] > 1 THEN 1 ELSE [16] END AS [16],CASE WHEN [17] > 1 THEN 1 ELSE [17] END AS [17],CASE WHEN [18] > 1 THEN 1 ELSE [18] END AS [18],CASE WHEN [19] > 1 THEN 1 ELSE [19] END AS [19],CASE WHEN [20] > 1 THEN 1 ELSE [20] END AS [20],CASE WHEN [21] > 1 THEN 1 ELSE [21] END AS [21],CASE WHEN [22] > 1 THEN 1 ELSE [22] END AS [22],CASE WHEN [23] > 1 THEN 1 ELSE [23] END AS [23],CASE WHEN [24] > 1 THEN 1 ELSE [24] END AS [24],CASE WHEN [25] > 1 THEN 1 ELSE [25] END AS [25],CASE WHEN [26] > 1 THEN 1 ELSE [26] END AS [26],CASE WHEN [27] > 1 THEN 1 ELSE [27] END AS [27],CASE WHEN [28] > 1 THEN 1 ELSE [28] END AS [28],CASE WHEN [29] > 1 THEN 1 ELSE [29] END AS [29],CASE WHEN [30] > 1 THEN 1 ELSE [30] END AS [30],CASE WHEN [31] > 1 THEN 1 ELSE [31] END AS [31]
from
(SELECT distinct t1.EmpId,t1.TrainerName,t2.LocationName, Day(#tbl1.trngdate)as CourseDate FROM #tbl1 join MST_TRAINER t1
on #tbl1.TrainerID=t1.TrainerId LEFT Join MST_LOCATION t2 on t2.LocationId=#tbl1.locationid where t1.EmpId is not null  AND MONTH(#tbl1.trngdate)='05' ) RC
Pivot
(
COUNT(LocationName) for CourseDate in([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31])
)as Pivottable

Drop table #tbl1

SQL Query for Split Date

Database : CASKoenigDB

DECLARE @TrainerID INT

DECLARE @CourseSDate DATETIME

DECLARE @CourseEDate DATETIME

Declare @GDate datetime

DECLARE @LocationId INT

CREATE TABLE #tbl1 (TrainerID INT,GDate DATETIME, LocationId INT)

DECLARE LDates CURSOR

FOR

SELECT IC.TrainerId ,IC.CourseSDate,IC.CourseEDate,IC.LocationId

FROM TRAN_SCINTERESTEDCOURSES IC

WHERE IC.LocationId IN (1,2,3,4,5,6) AND IC.TrainerId IS NOT NULL AND IC.CourseSDate>'2016-03-31'

AND CourseEDate<='2016-04-30' ORDER BY IC.CourseSDate

OPEN LDates

FETCH NEXT FROM LDates

INTO @TrainerID,@CourseSDate,@CourseEDate,@LocationId

WHILE @@FETCH_STATUS = 0

BEGIN

INSERT INTO #tbl1

SELECT TrainerID ,trngdate,LocationId

FROM getFullMonthforRC(@TrainerID,@CourseSDate,@CourseEDate,@LocationId)

FETCH NEXT FROM LDates

INTO @TrainerID,@CourseSDate,@CourseEDate,@LocationId

END

CLOSE LDates

DEALLOCATE LDates

SELECT * FROM #tbl1

DROP TABLE #tbl1

Function in SQL

USE [CASKOENIGDB]
GO
/****** Object:  UserDefinedFunction [dbo].[getFullMonthforRC]    Script Date: 06/08/2016 17:17:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[getFullMonthforRC](@TrainerID int,@CourseSDate datetime,@CourseEDate datetime,@LocationId int,@weekendstatus varchar(30))
returns  @TableofDates table(TrainerID int not null,trngdate datetime not null,locationid int not null)
Begin
declare @trnid int
declare @lnid int
Declare @startDate datetime
declare @weekendbatch varchar(30)
set @trnid=@TrainerID
set @startDate=@CourseSDate
Declare @endDate datetime
set @endDate=@CourseEDate
set @lnid=@LocationId
set @weekendbatch=@weekendstatus
while @startDate<=@endDate
begin

if (@weekendbatch ='Weekend')  begin
IF (DATENAME(W,@startDate)='SATURDAY' or DATENAME(W,@startDate)= 'SUNDAY') BEGIN
INSERT INTO @TableOfDates(TrainerID,trngdate,locationid) VALUES (@trnid,@startDate,@lnid)
set @startDate=DATEADD(DAY, 1, @startDate)
END
ELSE BEGIN
set @startDate=DATEADD(DAY, 1, @startDate)

END
end
else IF CharIndex('5 Days',@weekendbatch)>0 begin
if @lnid= 5 begin
IF (DATENAME(W,@startDate)='THURSDAY' or DATENAME(W,@startDate)= 'FRIDAY') BEGIN
set @startDate=DATEADD(DAY, 1, @startDate)
END
ELSE BEGIN
INSERT INTO @TableOfDates(TrainerID,trngdate,locationid) VALUES (@trnid,@startDate,@lnid)
set @startDate=DATEADD(DAY, 1, @startDate)
END
end
else begin
IF (DATENAME(W,@startDate)='SUNDAY' or DATENAME(W,@startDate)= 'SATURDAY') BEGIN
set @startDate=DATEADD(DAY, 1, @startDate)
END
ELSE BEGIN
INSERT INTO @TableOfDates(TrainerID,trngdate,locationid) VALUES (@trnid,@startDate,@lnid)
set @startDate=DATEADD(DAY, 1, @startDate)
END
end
end
else IF CharIndex('7 Days',@weekendbatch)>0 begin
INSERT INTO @TableOfDates(TrainerID,trngdate,locationid) VALUES (@trnid,@startDate,@lnid)
set @startDate=DATEADD(DAY, 1, @startDate)
end
else IF CharIndex('Daily',@weekendbatch)>0 begin
INSERT INTO @TableOfDates(TrainerID,trngdate,locationid) VALUES (@trnid,@startDate,@lnid)
set @startDate=DATEADD(DAY, 1, @startDate)
end
else IF CharIndex('Weekly',@weekendbatch)>0 begin
INSERT INTO @TableOfDates(TrainerID,trngdate,locationid) VALUES (@trnid,@startDate,@lnid)
set @startDate=DATEADD(DAY, 1, @startDate)
end
else IF CharIndex('6 Days',@weekendbatch)>0 begin
if @lnid= 5 begin
IF  DATENAME(W,@startDate)= 'FRIDAY' BEGIN
set @startDate=DATEADD(DAY, 1, @startDate)
END
ELSE BEGIN
INSERT INTO @TableOfDates(TrainerID,trngdate,locationid) VALUES (@trnid,@startDate,@lnid)
set @startDate=DATEADD(DAY, 1, @startDate)
END
end
else begin
IF DATENAME(W,@startDate)='SUNDAY'  BEGIN
set @startDate=DATEADD(DAY, 1, @startDate)
END
ELSE BEGIN
INSERT INTO @TableOfDates(TrainerID,trngdate,locationid) VALUES (@trnid,@startDate,@lnid)
set @startDate=DATEADD(DAY, 1, @startDate)
END
end

end
else if @weekendbatch is null begin
INSERT INTO @TableOfDates(TrainerID,trngdate,locationid) VALUES (@trnid,@startDate,@lnid)
set @startDate=DATEADD(DAY, 1, @startDate)
end
end
return
End

Table-Valued Function in SQL

http://www.dotnet-tricks.com/Tutorial/sqlserver/KY3T010412-Different-Types-of-SQL-Server-Functions.html

Row_Number

Row_Number Example

Window Service

http://www.c-sharpcorner.com/uploadfile/naresh.avari/develop-and-install-a-windows-service-in-c-sharp/