see the image. i am trying to generate full result with sql. now i am generating UI with lots of front end code.

one guy gave me the whole sql which giving me the right output by SQL. here is the full sql.

DECLARE @FirstOfMonth DATE={ts'2011-02-01 00:00:00'};

WITH DateBorders AS
(
    SELECT @FirstOfMonth AS FirstOfMonth
          ,DATEADD(DAY,-1,DATEADD(MONTH,1,@FirstOfMonth)) AS LastOfMonth 
)
,ThirtyOneNumbers(N) AS
(
        SELECT N FROM(VALUES (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))t(N)
)
,RunningDates AS
(
    SELECT N AS DayNumber
          ,DATEADD(DAY,N-1,DateBorders.FirstOfMonth) DayDate
    FROM ThirtyOneNumbers,DateBorders
    WHERE ThirtyOneNumbers.N<=DATEDIFF(DAY,DateBorders.FirstOfMonth,DateBorders.LastOfMonth) + 1
)
,RunningDatesExt AS
(
    SELECT RunningDates.*
          ,wd.WeekDayInx
    FROM RunningDates
    CROSS APPLY(SELECT DATEPART(WEEKDAY,DayDate)) AS wd(WeekDayInx)
)
,HourSheetSum AS
(
    SELECT hs.SpecialistID
          ,hs.EntryDate
          ,SUM(hs.HoursData) AS SumHoursData
    FROM HourSheet AS hs
    GROUP BY hs.SpecialistID,hs.EntryDate
)
,DataToPivot AS
(
    SELECT s.SpecialistID,
           s.Name, 
           rde.DayNumber,

           CASE WHEN h.SumHoursData IS NULL THEN CASE WHEN rde.WeekDayInx IN(6,7) THEN 'S'
                                                  ELSE '8.00'  
                                             END
                ELSE CAST(h.SumHoursData AS VARCHAR(100)) END AS HoursData

    FROM RunningDatesExt AS rde
         CROSS JOIN Specialists AS s
         LEFT JOIN HourSheetSum AS h ON h.SpecialistID=s.SpecialistID AND rde.DayDate=h.EntryDate
)
SELECT  pvt.*
FROM    DataToPivot AS d
        PIVOT
        (   MIN(HoursData)
            FOR DayNumber 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 pvt
ORDER BY pvt.SpecialistID;

now issue is the above sql is working fine but become very big. how can i shorten it.

in this CTE value is hard coded

ThirtyOneNumbers(N) AS
(
        SELECT N FROM(VALUES (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))t(N)
)

but my below sql return days in month.....it could be 31 or 30 or 28 etc

DECLARE @Days AS INT
DECLARE @DateInput AS VARCHAR(10)
SET @DateInput = '01/01/2011'

SELECT @Days = DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,@DateInput),0)))
PRINT @Days

;WITH TotalDaysInMoth(MonthNumber) AS
(
    SELECT 1
    UNION ALL
    SELECT MonthNumber+1 
    FROM TotalDaysInMoth
    WHERE MonthNumber < @Days
)

select * from TotalDaysInMoth;

now my question is how can i make this area dynamic instead of hard coded value

SELECT  pvt.*
FROM    DataToPivot AS d
        PIVOT
        (   MIN(HoursData)
            FOR DayNumber 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 pvt
ORDER BY pvt.SpecialistID;

 i am looking for suggestion. in FOR DayNumber IN (1,2,3......31) is hard coded which i do not want. i want it should show  no of days in month. without making this area dynamic how can i achieve my goal and make shorter.

looking for suggestion. thanks

Hi Moukolkata,

For shorten and dynamic purpose, you can refer to below sample.

SET DATEFIRST 1

DECLARE @FirstOfMonth DATE={ts'2011-02-01 00:00:00'};
DECLARE @pivotCol NVARCHAR(MAX)

SELECT @FirstOfMonth

;WITH Cte AS
(
SELECT 1 AS DayNumber,@FirstOfMonth DayDate,DATEPART(weekday,@FirstOfMonth) WeekDayInx
UNION ALL
SELECT DayNumber+1,DATEADD(DAY,1,DayDate),DATEPART(weekday,DATEADD(DAY,1,DayDate)) FROM Cte
WHERE DayDate< DATEADD(DAY,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,@FirstOfMonth)+1,0))
)
SELECT @pivotCol=STUFF((SELECT ','+'['+CAST(DayNumber AS VARCHAR(20))+']' FROM CTE FOR XML PATH('')),1,1,'')

DECLARE @SQL NVARCHAR(MAX)='
;WITH Cte AS
(
SELECT 1 AS DayNumber,CAST('''+CAST(@FirstOfMonth AS NVARCHAR(10))+''' AS DATE) DayDate,DATEPART(weekday,'''+CAST(@FirstOfMonth AS NVARCHAR(10))+''') WeekDayInx
UNION ALL
SELECT DayNumber+1,DATEADD(DAY,1,DayDate),DATEPART(weekday,DATEADD(DAY,1,DayDate)) FROM Cte
WHERE DayDate< DATEADD(DAY,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,'''+CAST(@FirstOfMonth AS VARCHAR(10))+''')+1,0))
)
,HourSheetSum AS
(
    SELECT hs.SpecialistID
          ,hs.EntryDate
          ,SUM(hs.HoursData) AS SumHoursData
    FROM HourSheet AS hs
    GROUP BY hs.SpecialistID,hs.EntryDate
)
,DataToPivot AS
(
    SELECT s.SpecialistID,
           s.Name, 
           rde.DayNumber,

           CASE WHEN h.SumHoursData IS NULL THEN CASE WHEN rde.WeekDayInx IN(6,7) THEN ''S''
                                                  ELSE ''8.00''  
                                             END
                ELSE CAST(h.SumHoursData AS VARCHAR(100)) END AS HoursData

    FROM RunningDatesExt AS rde
         CROSS JOIN Specialists AS s
         LEFT JOIN HourSheetSum AS h ON h.SpecialistID=s.SpecialistID AND rde.DayDate=h.EntryDate
)
SELECT  pvt.*
FROM    DataToPivot AS d
        PIVOT
        (   MIN(HoursData)
            FOR DayNumber IN 
            (  
			'+@pivotCol+'
            ) 
        ) AS pvt
ORDER BY pvt.SpecialistID;'  

--print @sql
EXEC sp_executesql @sql

Rather than generate all days and weekdayInx for a given month, using a calendar table would be a better practice.

If you have any question, feel free to let me know.