I need a pivot to do this but can't quite figure it out. I have two tables accesslog and employee
RCID EmployeeID LogDate LogTime IN/OUT
559 050098 8/18/2015 15:53:14 3
586 050098 8/19/2015 07:14:09 0
587 050098 8/19/2015 07:14:19 0
640 050098 8/19/2015 12:32:31 1
643 050098 8/19/2015 12:33:48 2
661 050098 8/19/2015 17:17:00 3
678 050098 8/20/2015 07:12:41 0
723 050098 8/20/2015 12:31:35 1
724 050098 8/20/2015 12:34:28 2
736 050098 8/20/2015 16:56:04 3
863 050098 8/25/2015 07:47:45 0
927 050098 8/25/2015 12:48:57 1
928 050098 8/25/2015 12:53:23 2
952 050098 8/25/2015 17:20:05 3
972 050098 8/26/2015 07:41:54 0
1016 050098 8/26/2015 12:14:11 1
1017 050098 8/26/2015 12:17:19 2
1051 050098 8/26/2015 17:07:16 3
1084 050098 8/27/2015 07:39:04 0
1119 050098 8/27/2015 12:20:56 1
1123 050098 8/27/2015 12:23:34 2
1153 050098 8/27/2015 17:25:57 3
1270 050098 9/1/2015 07:41:44 0
1292 050098 9/1/2015 12:14:06 1
1294 050098 9/1/2015 12:15:34 2
1324 050098 9/1/2015 17:19:00 3
1355 050098 9/3/2015 07:39:25 0
1399 050098 9/3/2015 12:17:29 1
1401 050098 9/3/2015 12:21:41 2
1419 050098 9/3/2015 17:09:23 3
1445 050098 9/4/2015 07:24:23 0
1510 050098 9/4/2015 12:58:15 1
1511 050098 9/4/2015 12:59:36 2
1536 050098 9/4/2015 17:40:01 3
1562 050098 9/7/2015 07:03:31 0
1637 050098 9/7/2015 17:34:07 3
1649 050098 9/8/2015 07:11:26 0
1709 050098 9/8/2015 17:13:09 3
1734 050098 9/9/2015 07:44:49 0
1755 050098 9/9/2015 12:09:06 1
1760 050098 9/9/2015 12:12:30 2
Employee table;
employee_id firtsname lastname
050098 Alex Nomam
050057 Mandy Hampton
Can I use a Pivot to display this table like this?
Name : Alex Nomam Month: August 2015
AM PM
In Out In Out
1
2
3
4
5
6
7
8
9
10
11.
12
13
14
15
16
17
18 07:14:19
19 07:14:19 12:32:31 12:33:48 17:17:00
20
21
22
23
24
25
26
27
28
29
30
I'm using php and mssql please help me i'm new in php and sql
see a screen rep here
Query:
DECLARE @firstname varchar(30), @lastname varchar(30), @Month date
SET @firstname='Alex'
SET @lastname='Nomam'
SET @Month= '08/01/2015'
DECLARE @endDay int
SET @endDay=
DAY( DATEADD( MONTH, 1 + DATEDIFF(MONTH,0,
CAST(YEAR(@Month) * 10000 + MONTH(@Month) * 100 + 01 AS NVARCHAR(20))),-1))
select
[day],
[0] as [AM In],
[1] as [AM Out],
[2] as [PM In],
[3] as [PM out]
from
daysOfMonth d left join
(
select
LogDate, LogTime ,[In/Out]
from
accesslog a left join employee e
on a.EmployeeID=e.Employee_id
where
firstname=@firstname and
lastname=@lastname and
MONTH(LogDate)=MONTH(@Month) and
YEAR(LogDate)=YEAR(@Month)
) s
PIVOT
(
MAX(LogTime) for [In/Out] in ([0],[1],[2],[3])
)p
on d.[day]=DAY(Logdate)
where [day]<=@endDay
Explanation:
So this has actually two problems in it. One is pivoting and calculating the times for a date and other is showing it in the manner for all dates of the month.
To calculate dates of month I assumed a table daysofmonth which has days 1-31 in int column.
I expcted that you'd pass @firstname , @lastname and @month parameters for query.
DECLARE @firstname varchar(30), @lastname varchar(30), @Month date
Using the @month parameter I was able to calculate the last date of month which served in the where clause when showing final values for each day of month
SET @endDay = DAY( DATEADD(
MONTH,
1 + DATEDIFF(
MONTH,
0,
CAST(
YEAR(@Month) * 10000
+ MONTH(@Month) * 100
+ 01
AS NVARCHAR(20)
)
),
-1
)
)
Also the pivot calculation can be expressed as
select *
from
(
select
LogDate, LogTime ,[In/Out]
from
accesslog a left join employee e
on a.EmployeeID=e.Employee_id
where
firstname=@firstname and
lastname=@lastname and
MONTH(LogDate)=MONTH(@Month) and
YEAR(LogDate)=YEAR(@Month)
) s
PIVOT
(
MAX(LogTime) for [In/Out] in ([0],[1],[2],[3])
)p
Now we simply left join this pivot output to the daysofmonth table and add the @endDay clause in where condition to get the desired output.
Demo SQL fiddle link: http://sqlfiddle.com/#!3/b2d23/11