SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Manipulate Data into tabular
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

micnie_2020
Posting Yak Master

Malaysia
228 Posts

Posted - 06/19/2013 :  00:39:28  Show Profile  Reply with Quote
Hi All,

I want the output to look like this:-



I have:



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

)



And


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


Please advise.

Thank you.

Regards,
Micheale

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 06/19/2013 :  00:50:52  Show Profile  Reply with Quote
No need of Function/Procedure.. You can use PIVOT query for this kind of requirement

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/19/2013 :  01:03:21  Show Profile  Reply with Quote
What does day/Month represent? i can see only 1 ...16 so isit just days within your selected date range?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

micnie_2020
Posting Yak Master

Malaysia
228 Posts

Posted - 06/19/2013 :  02:00:35  Show Profile  Reply with Quote
Hi Visakh16,


It is up to 31 day. Is too long to show in the img. Sorry.

Can you advise me how am i goinng to write the sql? I am stuck.

Thank you.

Regards,
Micheale
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/19/2013 :  02:13:46  Show Profile  Reply with Quote

SELECT *
FROM
(
SELECT DATENAME(mm,f.[Date]) + DATENAME(yy,f.[Date]) AS MonthYr,
DAY(f.[Date]) AS DayVal,
s.Employee,
s.Job,
SUM(s.WorkLoad) AS Amount
FROM #Something s
CROSS APPLY dbo.CalendarTable(s.StartDate,s.EndDate,0,0)f
WHERE s.Employee = 'John Doe' --you may pass any value here or use a parameter
GROUP BY DATENAME(mm,f.[Date]) + DATENAME(yy,f.[Date]),
DAY(f.[Date]),
s.Employee 
)m
PIVOT (MAX(Job) FOR MonthYr IN ([Jan2013],[Feb2013],[Mar2013],....add all the month values here,[Dec2013]))p


CalendarTable can be found in below link

http://visakhm.blogspot.com/2010/02/generating-calendar-table.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 06/19/2013 :  02:32:50  Show Profile  Reply with Quote
--Without using UDF ( CalendarTable)
-- Small modifications to provide result in order
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)

--
Chandu

Edited by - bandi on 06/19/2013 03:01:06
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/19/2013 :  02:45:16  Show Profile  Reply with Quote
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


Make the dates below and see the change

DECLARE @FinStartYear DATE= '04/01/2012', @FinEndYear DATE= '03/31/2014'


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 06/19/2013 :  02:58:36  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/19/2013 :  03:02:18  Show Profile  Reply with Quote
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


OP's function had start and enddates as parameters so how can you assume it will always fall within a FY?


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 06/19/2013 :  03:13:11  Show Profile  Reply with Quote
Visakh,
Lets OP ask for that change or tweet above query by himself/herself

--
Chandu
Go to Top of Page

micnie_2020
Posting Yak Master

Malaysia
228 Posts

Posted - 06/19/2013 :  03:49:30  Show Profile  Reply with Quote
Thank you Bandi.

Yes. I am looking for FY.

Regards,
Micheale
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/19/2013 :  04:00:54  Show Profile  Reply with Quote
quote:
Originally posted by micnie_2020

Thank you Bandi.

Yes. I am looking for FY.

Regards,
Micheale


then ideally you should have financial year as a parameter for making it clear

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 06/19/2013 :  04:33:09  Show Profile  Reply with Quote
quote:
Originally posted by micnie_2020

Thank you Bandi.

Yes. I am looking for FY.

Regards,
Micheale


Welcome
No need of any changes... right?

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000