June 24, 2010 at 4:27 am
Guys i have a problem, I'm working on a time attendance data, the field Reader determines if it's Time-In or Time-Out, if equal to I meaning it's TimeIN data, and if O it's TimeOut data.
As you can see in my input data, timein and timeout data are not shown as columns, what i need to do is to show them in columns and that you will notice in my sample output.
--Input Data
CREATE TABLE #Log_Transactions (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[EmpNo] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EmpName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Adate] [datetime] NULL ,
[Atime] [datetime] NULL ,
[Reader] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
INSERT INTO #Log_Transactions (EmpNo,EmpName,Adate,Atime,Reader) VALUES('1','AAA','2010-06-20 00:00:00.000','2010-06-20 08:05:48.000','I')
INSERT INTO #Log_Transactions (EmpNo,EmpName,Adate,Atime,Reader) VALUES('1','AAA','2010-06-20 00:00:00.000','2010-06-20 17:32:48.000','O')
INSERT INTO #Log_Transactions (EmpNo,EmpName,Adate,Atime,Reader) VALUES('2','BBB','2010-06-21 00:00:00.000','2010-06-20 08:30:48.000','I')
INSERT INTO #Log_Transactions (EmpNo,EmpName,Adate,Atime,Reader) VALUES('2','BBB','2010-06-21 00:00:00.000','2010-06-20 17:45:48.000','O')
INSERT INTO #Log_Transactions (EmpNo,EmpName,Adate,Atime,Reader) VALUES('3','CCC','2010-06-22 00:00:00.000','2010-06-20 08:26:48.000','I')
INSERT INTO #Log_Transactions (EmpNo,EmpName,Adate,Atime,Reader) VALUES('3','CCC','2010-06-22 00:00:00.000','2010-06-20 17:56:48.000','O')
--Sample Output
CREATE TABLE #NewLog_Transactions (
[EmpNo] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EmpName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Adate] [datetime] NULL ,
[TimeIN] [datetime] NULL ,
[TimeOut] [datetime] NULL
)
INSERT INTO #NewLog_Transactions (EmpNo,EmpName,Adate,TimeIN,TimeOut) VALUES('1','AAA','2010-06-20 00:00:00.000','2010-06-20 08:05:48.000','2010-06-20 17:32:48.000')
INSERT INTO #NewLog_Transactions (EmpNo,EmpName,Adate,TimeIN,TimeOut) VALUES('2','BBB','2010-06-21 00:00:00.000','2010-06-21 08:30:48.000','2010-06-21 17:45:48.000')
INSERT INTO #NewLog_Transactions (EmpNo,EmpName,Adate,TimeIN,TimeOut) VALUES('3','CCC','2010-06-22 00:00:00.000','2010-06-20 08:26:48.000','2010-06-20 17:56:48.000')
I hoped someone would help
thanks
Thanks,
Morris
June 24, 2010 at 5:21 am
Something like this?
SELECT
EmpNo,
EmpName,
Adate,
MAX(CASE WHEN Reader='I' THEN Atime ELSE NULL END) AS TimeIN,
MAX(CASE WHEN Reader='O' THEN Atime ELSE NULL END) AS TimeOut
FROM #Log_Transactions
GROUP BY EmpNo,EmpName,Adate
June 24, 2010 at 5:53 pm
It's perfectly working, thanks!, honestly im having a hard time doing things in columns like this.
Thank you again!
Thanks,
Morris
June 24, 2010 at 11:49 pm
You're welcome 😀
The concept is called "CroosTab" (see the related link in my signature). The next "level" would be DynamicCrossTab that allows you to create a matrix with flexible name/number of columns...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply