I have a table(PayRoll_AttendanceStatus) that contains 4 columns like
======================================
StatusID | Status | ShortName | color
======================================
01 | Present| P | Yellow
02 | Absent | A | Red
=======================================
from above mentioned table I used to mark day wise attendance into another table (PayRollMarkAttendance)and it contains values like
====================================================
MarkId | Employee_Code | StatusID | DateTime
====================================================
1 | 001 | 01 | 2016-06-11
2 | 002 | 02 | 2016-06-11
====================================================
For view I am using Pivot table and the code for pivot table is
SELECT * FROM (SELECT [EMPLOYEENAME] ,[EMPLOYEE_CODE] ,[ShortName], DATENAME(M, [DateTime])as [Month] ,DAY([DateTime]) as [DayValue] FROM [View_PayRollMarkAttendance]) as Composite PIVOT (MAX([ShortName]) FOR [DayValue] 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])) AS PivotTable
Through this pivottable I can view my status quite good. But I want to view ABSENT AND PRESENT through its unique code color. Absent for Red and Present for Yellow.
Please help any one.
Thanks