March 6, 2009 at 11:46 pm
I have a Empoyee table
Emp_Id INT
Att VARCHAR(10) -- Present/absent/Leave....
date datetime
As you can see, it stores data of employee attendnce.
Now I want the output as
Year Month EMpId 1 2 3 ....28 29 30 31
2009 Jan 1 P P A......
.
.
.
for all the empoyees.
Mr. 25$ (Article authors) stay away.
March 7, 2009 at 4:23 am
I think the PIVOT query below does what you're looking for.
DECLARE @Employee TABLE (
Emp_Id INT NOT NULL,
Att VARCHAR(10) NOT NULL, -- Present/absent/Leave....
date datetime
)
/* Add some test data: previous 365 days for 3 employee IDs */
INSERT @Employee (Emp_Id, Att, date)
SELECT
E.Emp_Id,
CASE ABS(CHECKSUM(NEWID()) % 10)
WHEN 0 THEN 'Absent'
WHEN 1 THEN 'Leave'
ELSE 'Present'
END,
DATEADD(day, DATEDIFF(day, 0, GETDATE()), -T.RN)
FROM (
SELECT TOP 365 ROW_NUMBER() OVER (ORDER BY id) AS RN
FROM sys.sysobjects
) T
CROSS JOIN (
SELECT 1 AS Emp_Id UNION ALL
SELECT 2 UNION ALL
SELECT 3
) E
/* Check test data */
SELECT YEAR(date), MONTH(date), DAY(date), Emp_Id, Att
FROM @Employee
ORDER BY date, Emp_Id
/* Pivot query */
SELECT P.[Year], P.[Month], P.[EmpId],
[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]
FROM (
SELECT
YEAR(date) AS [Year],
MONTH(date) AS [Month],
DAY(date) AS [Day],
Emp_Id AS [EmpId],
SUBSTRING(Att, 1, 1) AS [Att]
FROM @Employee
) E
PIVOT (
MIN(E.[Att]) FOR E.[Day] 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]
)
) P
ORDER BY P.[Year], P.[Month], P.[EmpId]
March 7, 2009 at 5:44 am
good one Andrew!!
Can it be done without writting [1] to [31]?
Mr. 25$ (Article authors) stay away.
March 7, 2009 at 5:57 am
------------------------------------------------------------------------
---CREATE TEST DATA-----------------------------------------------------
------------------------------------------------------------------------
IF OBJECT_ID('employeeTEST') IS NOT NULL
DROP TABLE employeeTEST
CREATE TABLE employeeTEST
(
ID INT Identity,
EmpId INT ,
Att CHAR(1) , -- Present/absent/Leave....
DATE smalldatetime
);
WITH dates AS
( SELECT TOP (1000)
CASE DATENAME(dw, DATEADD(dd, (ROW_NUMBER() OVER (ORDER BY sc1.ID)-1), DATEADD(yy,DATEDIFF(yy,0,GETDATE())-1,0)))
WHEN 'Wednesday'
THEN 'L'
WHEN 'Monday'
THEN 'A'
ELSE 'P'
END ATT,
DATEADD(dd, (ROW_NUMBER() OVER (ORDER BY sc1.ID)-1), DATEADD(yy,DATEDIFF(yy,0,GETDATE())-1,0)) AS DATE
FROM Master.sys.SysColumns sc1
CROSS JOIN Master.sys.SysColumns sc2
)
INSERT employeeTEST
SELECT 1 ,
ATT,
DATE
FROM dates
UNION ALL
SELECT 2 ,
ATT,
DATE
FROM dates
UNION ALL
SELECT 3 ,
ATT,
DATE
FROM dates
UNION ALL
SELECT 4 ,
ATT,
DATE
FROM dates
------------------------------------------------------------------------
---CROSS TAB WITH COLUMN FOR EACH DAY OF MONTH--------------------------
------------------------------------------------------------------------
SELECT DATEPART(yy,DATE),
DATENAME(mm,DATE),
EMpID ,
MAX(
CASE
WHEN DATEPART(DAY, DATE) = 1
THEN Att
ELSE ''
END) [1],
MAX(
CASE
WHEN DATEPART(DAY, DATE) = 2
THEN Att
ELSE ''
END) [2],
MAX(
CASE
WHEN DATEPART(DAY, DATE) = 3
THEN Att
ELSE ''
END) [3],
MAX(
CASE
WHEN DATEPART(DAY, DATE) = 4
THEN Att
ELSE ''
END )[4],
MAX(
CASE
WHEN DATEPART(DAY, DATE) = 5
THEN Att
ELSE ''
END) [5],
MAX(
CASE
WHEN DATEPART(DAY, DATE) = 6
THEN Att
ELSE ''
END) [6],
MAX(
CASE
WHEN DATEPART(DAY, DATE) = 7
THEN Att
ELSE ''
END) [7],
MAX(
CASE
WHEN DATEPART(DAY, DATE) = 8
THEN Att
ELSE ''
END) [8],
MAX(
CASE
WHEN DATEPART(DAY, DATE) = 9
THEN Att
ELSE ''
END) [9],
MAX(
CASE
WHEN DATEPART(DAY, DATE) = 10
THEN Att
ELSE ''
END) [10],
MAX(
CASE
WHEN DATEPART(DAY, DATE) = 11
THEN Att
ELSE ''
END) [11],
MAX(
CASE
WHEN DATEPART(DAY, DATE) = 12
THEN Att
ELSE ''
END) [12],
MAX(
CASE
WHEN DATEPART(DAY, DATE) = 13
THEN Att
ELSE ''
END) [13],
MAX(
CASE
WHEN DATEPART(DAY, DATE) = 14
THEN Att
ELSE ''
END) [14],
MAX(
CASE
WHEN DATEPART(DAY, DATE) = 15
THEN Att
ELSE ''
END) [15],
MAX(
CASE
WHEN DATEPART(DAY, DATE) = 16
THEN Att
ELSE ''
END) [16],
MAX(
CASE
WHEN DATEPART(DAY, DATE) = 17
THEN Att
ELSE ''
END )[17],
MAX(
CASE
WHEN DATEPART(DAY, DATE) = 18
THEN Att
ELSE ''
END) [18],
MAX(
CASE
WHEN DATEPART(DAY, DATE) = 19
THEN Att
ELSE ''
END) [19],
MAX(
CASE
WHEN DATEPART(DAY, DATE) = 20
THEN Att
ELSE ''
END) [20],
MAX(
CASE
WHEN DATEPART(DAY, DATE) = 21
THEN Att
ELSE ''
END) [21],
MAX(
CASE
WHEN DATEPART(DAY, DATE) = 22
THEN Att
ELSE ''
END) [22],
MAX(
CASE
WHEN DATEPART(DAY, DATE) = 23
THEN Att
ELSE ''
END) [23],
MAX(
CASE
WHEN DATEPART(DAY, DATE) = 24
THEN Att
ELSE ''
END) [24],
MAX(
CASE
WHEN DATEPART(DAY, DATE) = 25
THEN Att
ELSE ''
END) [25],
MAX(
CASE
WHEN DATEPART(DAY, DATE) = 26
THEN Att
ELSE ''
END) [26],
MAX(
CASE
WHEN DATEPART(DAY, DATE) = 27
THEN Att
ELSE ''
END) [27],
MAX(
CASE
WHEN DATEPART(DAY, DATE) = 28
THEN Att
ELSE ''
END) [28],
MAX(
CASE
WHEN DATEPART(DAY, DATE) = 29
THEN Att
ELSE ''
END) [29],
MAX(
CASE
WHEN DATEPART(DAY, DATE) = 30
THEN Att
ELSE ''
END) [30],
MAX(
CASE
WHEN DATEPART(DAY, DATE) = 31
THEN Att
ELSE ''
END )[31]
FROM employeeTEST
GROUP BY DATEPART(yy,DATE),
DATENAME(mm,DATE),
EMpID
ORDER BY DATEPART(yy,DATE),
DATENAME(mm,DATE),
EMpID ASC
March 7, 2009 at 7:38 am
Thanks again.
But I already asked, can we do it without putting 1 to 31 hard coded?
Mr. 25$ (Article authors) stay away.
March 7, 2009 at 7:57 am
Not without dynamic sql which you dont want to do for several reasons.
It seemed MS were going to make the pivot function inherently dynamic for 2005 which shouldn’t have been too tricky as you should have been able to set it a column/expression to get values from for the new columns. This didnt make it into 2008 either i dont think. So you are stuck hard coding them or making some hard to maintain dynamic sql.
i prefer case statements to the pivot function as they have the same execution plan but the logic with case is explicit. I suspect the pivot command actually build case statements internally at a very high level anyway. This is partly why i think MS must have had grander plans for the PIVOT function initially
March 7, 2009 at 2:16 pm
NY153 (3/7/2009)
Thanks again.But I already asked, can we do it without putting 1 to 31 hard coded?
Absolutely... and, as Jules already pointed out, it will require the use of some dynamic SQL. If you'd like to post (attach) some readily consumable test data (see the link in my signature for how to do that best), I'd be happy to show you how as I'm sure others will, as well. If not, then take a look at the following article which does a very similar thing and contains the same method I'd use to solve your problem.
http://qa.sqlservercentral.com/articles/cross+tab/65048/
The first article in that series also shows several reasons, one being performance related, as to why you may want to consider using cross-tabs instead of the PIVOT function. That's located at the following URL...
http://qa.sqlservercentral.com/articles/T-SQL/63681/
Last, but not least, I'm always curious as to why folks want to do this type of denormalization for two reasons, the first being that it may help me help others in the future and the second being that there may be a better way to solve your problem other than the way you proposed in your original post. Thanks in advance for the reply.
--Jeff Moden
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply