how to use pivot for this task

Question

i have a table named table 1,table data is showed below

attdate	inime	outtime	workedhours	empid
03-01-17	08:30	20:00	12	1
03-02-17	08:30	20:00	12	1
03-03-17	08:30	20:00	12	1
03-04-17	08:30	18:30	10	1
03-06-17	08:30	20:10	12	1

03-05-17 date is sunday for march month thats why im not entering into that record in table

i have another table named  table 2,record shown below

 

empid	leavedate
1	03-10-17
1	03-11-17

my expected result is like below i want,if worked hours >8 means P ,if employee present in table 2 means L otherwise AB

empid	1(03-01-17)	2	3	4	5...	10	11
1	p	p	p	p	p	L	L

1 means(03-01-17)   day 1 for march

2 means day 2 for march

 

 

Answer 1

Assuming yout Table1 will have all working day timings(except for leaves) and Table2 will have Leaves information Below TSQL can give you the required result.
SELECT * INTO #Temp
FROM
(
SELECT
[empid]
,[attdate] AS [Date]
,CAST(DAY([attdate]) AS VARCHAR(2))+ '(' +CAST([attdate] AS VARCHAR(25))+')' AS [Day]
,'Status' = CASE WHEN [ workedhours] > 8 THEN 'P' ELSE 'AB' END
FROM [Table1]
UNION
SELECT
[empid]
,[leavedate] AS [Date]
,CAST(DAY([leavedate]) AS VARCHAR(2))+ '(' +CAST([leavedate] AS VARCHAR(25))+')' AS [Day]
,'Status' = 'L'
FROM [Table2]
) AS [Result]

SELECT DISTINCT [Date],[Day] INTO #Temp2 FROM #Temp ORDER BY [Date]

DECLARE @ColumnNames NVarchar(MAX)
DECLARE @SqlQuery NVARCHAR(max)

SELECT @ColumnNames=STUFF((
select ',['+ [Day] +']'
from #Temp2
FOR XML PATH('')
)
,1,1,'')

SET @SqlQuery = N'SELECT [empID],' + @ColumnNames +' FROM (Select [EmpId],[Day],[Status] From #Temp) P Pivot (MAX([Status]) For [Day] in (' +@ColumnNames+'))AS PivotTable;'

EXEC Sp_executeSql @SqlQuery