Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

micnie_2020
Posting Yak Master

Malaysia
232 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
2241 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
52326 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
232 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
52326 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
2241 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
52326 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
2241 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
52326 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
2241 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
232 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
52326 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
2241 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  
 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.11 seconds. Powered By: Snitz Forums 2000