Script for displaying respective WORK days, TRAVEL days and LEAVE days for a particular month

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

This is how the available data is

EmployeeName | EmployeeNo     | Type   | StartDate  | EndDate

Dumitru      | M1975012200006 | TRAVEL | 06/03/2013 | 06/03/2013
Mottaghi     | M1975040500009 | TRAVEL | 06/17/2013 | 06/17/2013
Alvarado     | M1986092400012 | TRAVEL | 06/03/2013 | 06/03/2013
Dehghan      | M1975111100029 | TRAVEL | 06/15/2013 | 06/15/2013

Dumitru      | M1975012200006 | WORK   | 06/04/2013 | 07/15/2013
Mottaghi     | M1975040500009 | WORK   | 06/18/2013 | 09/01/2013
Alvarado     | M1986092400012 | WORK   | 06/04/2013 | 07/15/2013
Dehghan      | M1975111100029 | WORK   | 06/16/2013 | 07/25/2013

Dumitru      | M1975012200006 | LEAVE  | 07/17/2013 | 09/01/2013
Mottaghi     | M1975040500009 | LEAVE  | 09/03/2013 | 10/15/2013
Alvarado     | M1986092400012 | LEAVE  | 07/17/2013 | 09/01/2013
Dehghan      | M1975111100029 | LEAVE  | 07/27/2013 | 09/02/2013

I want to display all the days of a month column wise.

The expected output is as follows

June Month Timeline

EmployeeName - 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
Dumitru,Georg - T W W W W W W W W W W W W W W L L L L L L L L L L L L L L L L L L L L L L L L L 
Employee Name2 - W W W W W W W W W W W W W W W W W L L L L L.... etc.

T - represents TRAVEL days; W - represents WORK days; L - represents LEAVE days;

I appreciate any help in this regard. Thank you in advance

Posted 23-Jul-13 23:41pm

nvikas7

Updated 24-Jul-13 21:31pm

v15

Add a Solution

Comments

damodara naidu betha 24-Jul-13 5:57am

post your table structure and sample data. Please post what you did so far.

gvprabu 24-Jul-13 10:19am

hi U need in String format or all days values in different Column

nvikas7 25-Jul-13 0:24am

Hi gvprabu. Thank you for your reply. I prefer all days values in different column.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2013-07-24T23:17:00

Have a look at below example:

SQL

--declare temporary table to store example data
DECLARE @tmp TABLE (EmployeeName VARCHAR(30), EmployeeNo VARCHAR(30), [Type] VARCHAR(30), StartDate DATETIME, EndDate DATETIME)
--insert data into temporary table
INSERT INTO @tmp (EmployeeName, EmployeeNo, [Type] , StartDate, EndDate)
SELECT 'Dumitru', 'M1975012200006', 'TRAVEL', '06/03/2013', '06/03/2013'
UNION ALL SELECT 'Mottaghi', 'M1975040500009', 'TRAVEL', '06/17/2013', '06/17/2013' 
UNION ALL SELECT 'Alvarado', 'M1986092400012', 'TRAVEL', '06/03/2013', '06/03/2013' 
UNION ALL SELECT 'Dehghan', 'M1975111100029', 'TRAVEL', '06/15/2013', '06/15/2013'
UNION ALL SELECT 'Dumitru', 'M1975012200006', 'WORK', '06/04/2013', '07/15/2013' 
UNION ALL SELECT 'Mottaghi', 'M1975040500009', 'WORK', '06/18/2013', '09/01/2013' 
UNION ALL SELECT 'Alvarado', 'M1986092400012', 'WORK', '06/04/2013', '07/15/2013' 
UNION ALL SELECT 'Dehghan', 'M1975111100029', 'WORK', '06/16/2013', '07/25/2013' 
UNION ALL SELECT 'Dumitru', 'M1975012200006', 'LEAVE', '07/17/2013', '09/01/2013' 
UNION ALL SELECT 'Mottaghi', 'M1975040500009', 'LEAVE', '09/03/2013', '10/15/2013' 
UNION ALL SELECT 'Alvarado', 'M1986092400012', 'LEAVE', '07/17/2013', '09/01/2013' 
UNION ALL SELECT 'Dehghan', 'M1975111100029', 'LEAVE', '07/27/2013', '09/02/2013'
 
IF OBJECT_ID(N'#pvtsrc',N'U') IS NOT NULL
	DROP TABLE #pvtsrc
--destination table: source for pivot
CREATE TABLE #pvtsrc (EmployeeName VARCHAR(30), EmployeeNo VARCHAR(30), TypeID VARCHAR(1), CurrDate DATETIME) 
 
--enum type for each day and employee
--insert into destination table
;WITH Days AS
(
	-- initial 
	SELECT EmployeeName, EmployeeNo, LEFT([Type],1) AS TypeID, StartDate, EndDate, StartDate AS CurrDate
	FROM @tmp
	UNION ALL
	SELECT EmployeeName, EmployeeNo, TypeID, StartDate, EndDate, DATEADD(dd,1,CurrDate) AS CurrDate
	FROM Days 
	WHERE DATEADD(dd,1,CurrDate)<=EndDate
)
INSERT INTO #pvtsrc (EmployeeName, EmployeeNo, TypeID, CurrDate)
SELECT EmployeeName, EmployeeNo, TypeID, CurrDate
FROM Days
ORDER BY EmployeeName, CurrDate
 
--declare varables needed for pivot
DECLARE @cols VARCHAR(1000)
DECLARE @dt VARCHAR(2000)
DECLARE @pt VARCHAR(4000)
DECLARE @ft VARCHAR(MAX)
 
DECLARE @startDay DATETIME
DECLARE @endDay DATETIME
DECLARE @currDay DATETIME
 
--set range of dates
SET @startDay = '2013-06-01'
SET @endDay = '2013-06-30'
SET @currDay = @startDay
 
--set columns collection (1-28/31)
SET @cols = ''
WHILE (@currDay <= @endDay)
BEGIN
	SET @cols = @cols + '[' + CONVERT(VARCHAR(2),DAY(@currDay)) + '],'
	SET @currDay = DATEADD(dd,1,@currDay)
END
SET @cols = LEFT(@cols, LEN(@cols)-1)
 
--set data source for pivot
SET @dt = N'SELECT EmployeeName, EmployeeNo, TypeID, DAY(CurrDate) AS xDay
			FROM #pvtsrc
			WHERE CurrDate BETWEEN ''' + CONVERT(VARCHAR(10),@startDay,121) + ''' AND  ''' + CONVERT(VARCHAR(10),@endDay,121) + ''''
--set pivot query
SET @pt = N'SELECT EmployeeName, EmployeeNo, ' + @cols + ' ' +
		'FROM(' + @dt + ') AS DT ' +
		'PIVOT(MAX(TypeID) FOR xDay IN(' + @cols + ')) AS PT'

SET @cols = ''
SET @currDay = @startDay
WHILE (@currDay <= @endDay)
BEGIN
	SET @cols = @cols + 'ISNULL([' + CONVERT(VARCHAR(2),DAY(@currDay)) + '],''L'') AS [' + CONVERT(VARCHAR(2),DAY(@currDay)) + '],'
	SET @currDay = DATEADD(dd,1,@currDay)
END
SET @cols = LEFT(@cols, LEN(@cols)-1)

--SELECT @cols

SET @ft = N'SELECT EmployeeName, EmployeeNo, ' + @cols + ' ' +
		'FROM (' + @pt + ') AS FT '
--PRINT @ft
--execute query
EXEC(@ft)
 
--drop temp. table
DROP TABLE #pvtsrc

Result:

EmpName		EmpNo           1       2       3       4	...	30
Dumitru		M1975012200006	L	L	T	W	...	W
Mottaghi	M1975040500009	L	L	L	W	...	W
Dehghan		M1975111100029	L	L	L	L	...	W
Alvarado	M1986092400012	L	L	T	W	...	W