ALTER FUNCTION [dbo].[IF_Calendar] 
(	
	@StartDate DATE,
	@EndDate DATE,
	@FirstWeekDay VARCHAR(10)
)
RETURNS TABLE WITH SCHEMABINDING AS  
RETURN 
(

	WITH E1(N) AS (
		SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
		SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
		SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
	),                          --10E+1 or 10 rows
	E2(N) AS (SELECT 1 FROM E1 a CROSS JOIN E1 b), --10E+2 or 100 rows
	E3(N) AS (SELECT 1 FROM E2 a CROSS JOIN E2 b CROSS JOIN E1 c), --1M rows max

	iTally AS ( -- generate sufficient rows to cover startdate to enddate inclusive
		SELECT TOP(1+DATEDIFF(DAY,@StartDate,@EndDate)) 
			rn = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1
		FROM E3
	)

	-- Do some date arithmetic 
	--select * from dbo.IF_Calendar('01/03/2013','01/06/2013',DATENAME(dw, '01/03/2013')) c 

	SELECT
		a.DateRange,
		c.[Year],
		c.[Month],
		c.[DayOfMonth],
		c.AbsWeekno,
		c.[DayName],
		d.Holiday 
	FROM iTally
	CROSS APPLY (SELECT DateRange = DATEADD(day,rn,@StartDate)) a
	CROSS APPLY (VALUES ('Tuesday',1),('Wednesday',2),('Thursday',3),('Friday',4),('Saturday',5),('Sunday',6),('Monday',7)
	) b (FirstWeekDay, FirstWeekdayOffset)
	CROSS APPLY (
		SELECT 
			[Year] = YEAR(a.DateRange),
			[Month] = left(DateName(MONTH,a.DateRange),3),
			[DayOfMonth] = DAY(a.DateRange),
			AbsWeekno	= DATEDIFF(day,FirstWeekdayOffset,a.DateRange)/7,
			[DayName]	= DATENAME(weekday,a.DateRange)
	) c
	CROSS APPLY (
		SELECT Holiday = CASE 
			WHEN [Month] = 'JAN'  AND [DayOfMonth] = 1 THEN 'New Year' 
			WHEN [Month] = 'MAY'  AND [DayOfMonth] >= 25 AND [DayName] = 'Monday' THEN 'Memorial Day' 
			WHEN [Month] = 'JUL'  AND [DayOfMonth] = 4 THEN 'Independence Day' 
			WHEN [Month] = 'SEP'  AND [DayOfMonth] <= 7 AND [DayName] = 'Monday' THEN 'Labor Day' 
			WHEN [Month] = 'NOV' AND [DayOfMonth] BETWEEN 22 AND 28 AND [DayName] = 'Thursday' THEN 'Thanksgiving Day' 
			WHEN [Month] = 'DEC' AND [DayOfMonth] = 25 THEN 'Christmas Day' 
			ELSE NULL END
	) d
	WHERE b.FirstWeekDay = @FirstWeekDay
		AND @EndDate IS NOT NULL

)

if OBJECT_ID('tempdb..#Something') is not null
	drop table #Something
	
create table #Something
(
	ID int,
	Employee varchar(20),
	Job varchar(20),
	StartDate datetime,
	EndDate datetime,
	Workload int
)

insert #Something
select *
from (Values
(1, 'John Doe', 'HSBC', '04/01/2013', '04/03/2013', 100)
,(2, 'John Doe', 'Vacation', '06/05/2013', '06/07/2013', 100)
,(5, 'John Doe', 'Santander', '02/01/2014', '02/02/2014', 50)
) x(a,b,c,d,e,f)
;

//How to transform the data to be plotted like above attachment

DECLARE @FinStartYear DATE=  '04/01/2013', @FinEndYear DATE=  '03/31/2014'
;WITH CTE (Date, Dys, Months) AS 
(
	SELECT @FinStartYear, 1, DATENAME(MM, @FinStartYear)  
	UNION ALL
	SELECT DATEADD (DD, 1, Date),  DATEPART( DD, DATEADD (DD, 1, Date)), DATENAME( MM, DATEADD (DD, 1, Date))
	FROM CTE
	WHERE  DATEADD (DD, 1, Date) <= @FinEndYear
)
SELECT Months, [1], [2], [3], [4], [5], [6], [7], , [9], [10], [11], [12], [Workload]   -- add upto 31 days
FROM (
	SELECT * FROM (SELECT 
		Months
		,Dys
		,SUM([Workload]) OVER(PARTITION BY MONTH([Date]))/COUNT([Workload]) OVER(PARTITION BY MONTH([Date]))  [Workload]
		,YEAR([Date]) Yr, MONTH( [Date]) Mnth
		,job
	FROM CTE c LEFT JOIN #Something s ON (c.Date BETWEEN s.StartDate AND s.EndDate) AND Employee = 'John Doe' )p
	PIVOT(
		MAX(JOB) FOR Dys IN ([1], [2], [3], [4], [5], [6], [7], , [9], [10], [11], [12] -- add upto 31 days)
		) pvt
	) Temp
ORDER BY Yr, Mnth
OPTION (MAXRECURSION 0)

quote:

---

Originally posted by bandi

--Without using UDF ( CalendarTable)

DECLARE @FinStartYear DATE=  '04/01/2013', @FinEndYear DATE=  '03/31/2014'
;WITH CTE (Date, Dys, Months) AS 
(
	SELECT @FinStartYear, 1, DATENAME(MM, @FinStartYear )  
	UNION ALL
	SELECT DATEADD (DD, 1, Date),  DATEPART( DD, DATEADD (DD, 1, Date)), DATENAME( MM, DATEADD (DD, 1, Date))
	FROM CTE
	WHERE  DATEADD (DD, 1, Date) <= @FinEndYear
)
SELECT Months, [1], [2], [3], [4], [5], [6], [7], , [9], [10], [11], [12], ..... upto [31], [Workload]  
FROM (
	SELECT * FROM (SELECT 
		Months
		,Dys
		,[Workload]
		,job
	FROM CTE c LEFT JOIN #Something s ON (c.Date BETWEEN s.StartDate AND s.EndDate) AND Employee = 'John Doe' )p
	PIVOT(
		MAX(JOB) FOR Dys IN ([1], [2], [3], [4], [5], [6], [7], , [9], [10], [11], [12] ,..... upto ,[31])
		) pvt
	) Temp
OPTION (MAXRECURSION 0)

--
Chandu

---

quote:

---

Originally posted by visakh16
DECLARE @FinStartYear DATE= '04/01/2012', @FinEndYear DATE= '03/31/2014'

---

quote:

---

Originally posted by bandi

quote:

---

Originally posted by visakh16
DECLARE @FinStartYear DATE= '04/01/2012', @FinEndYear DATE= '03/31/2014'

---

Visakh, My query is for one financial year...

By looking at OP's output and Function I decided to provide query for one financial year..

--
Chandu

---

quote:

---

Originally posted by micnie_2020

Thank you Bandi.

Yes. I am looking for FY.

Regards,
Micheale

---

quote:

---

Originally posted by micnie_2020

Thank you Bandi.

Yes. I am looking for FY.

Regards,
Micheale

---