Question about conversion between two tables
-
Suppose i have a table named AccessLog that record all staffs' attendance. And the struture of it is like the top table on the following figure. And one of the field "InOut" represent that the staffs' in and out, "0" is thought to be "in", and "1" is thought to be out. What i want to do is how to convert this table into the second table named Report on the figure. I've already used 'cursor ' 'fetch' to implement. However, it's take me a lot of time to finished the query.
Any ideas?
CREATE TABLE [dbo].[AccessLog] (
[RCDID] [int] NOT NULL ,
[EmployeeID] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LogDate] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LogTime] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TerminalID] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[InOut] [int] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Report] (
[employeeid] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[in1] [datetime] NULL ,
[in2] [datetime] NULL ,
[in3] [datetime] NULL ,
[out1] [datetime] NULL ,
[out2] [datetime] NULL ,
[out3] [datetime] NULL ,
[recorddate] [datetime] NULL
) ON [PRIMARY]
What I want to do is:
If I query a staff whose employeeid is 0915 and the result is shown as following.
RCDID EmployeeID LogDate LogTime TerminalID InOut
-------- ---------------- ------------ --------- ----------- --------
1 0915 03/01/2006 10:00:00 0 0
2 0915 03/01/2006 10:10:32 0 0
3 0915 03/01/2006 18:00:00 0 1
And I would like to convert this table into Report table. Such like that:
EmployeeID In1 In2 Out1 Out2 RecordDate
------------ --------- ------- ------- -------- ---------
0915 10:00:00 10:10:32 18:00:00 3/1/2006
-
Sorry,... but could you be more specific in your description... because the code is too long 4 me to read
What is the output you want to get?
Unfortunately the your figure is not visible. I suggest to take a preview before post a msg.
PS:
------------
When you 've got a hammer, everything starts to look like a nail... -
It's easiest for us to help if you post your table structure as a create table statement, if you give us some sample data to work with and if you tell us what you want.
Posting a whole lot of code for would-be helpers to wade through and understand discourages most. The pic isn't visible to anyone else, cause it's on your local machine.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
This is what I got from your question...
--data
if Object_ID(N'TempDB.[dbo].[#AccessLog]') is not null drop table #AccessLog
CREATE TABLE [dbo].[#AccessLog] (
[RCDID] [int] NOT NULL ,
[EmployeeID] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LogDate] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LogTime] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TerminalID] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[InOut] [int] NULL
)
insert #AccessLog
select 1, '0915', '03/01/2006', '10:00:00', 0, 0
union all select 2, '0915', '03/01/2006', '10:10:32', 0, 0
union all select 3, '0915', '03/01/2006', '18:00:00', 0, 1
union all select 4, '0123', '03/01/2006', '11:00:00', 0, 0
union all select 5, '0123', '03/01/2006', '12:00:00', 0, 1
union all select 5, '0123', '03/01/2006', '13:00:00', 0, 1
union all select 5, '0123', '03/01/2006', '14:00:00', 0, 1
union all select 5, '0123', '04/01/2006', '14:00:00', 0, 1
--calculation
select
t.EmployeeID,
min(i1.LogTime) as In1, min(i2.LogTime) as In2, min(i3.LogTime) as In3,
min(o1.LogTime) as Out1, min(o2.LogTime) as Out2, min(o3.LogTime) as Out3,
t.LogDate as RecordDate
from
(select EmployeeID, LogDate from #AccessLog group by EmployeeID, LogDate) t
left outer join #AccessLog i1 on t.EmployeeID = i1.EmployeeID and t.LogDate = i1.LogDate and i1.InOut = 0
left outer join #AccessLog i2 on t.EmployeeID = i2.EmployeeID and t.LogDate = i2.LogDate and i2.InOut = 0 and i1.LogTime < i2.LogTime
left outer join #AccessLog i3 on t.EmployeeID = i3.EmployeeID and t.LogDate = i3.LogDate and i3.InOut = 0 and i2.LogTime < i3.LogTime
left outer join #AccessLog o1 on t.EmployeeID = o1.EmployeeID and t.LogDate = o1.LogDate and o1.InOut = 1
left outer join #AccessLog o2 on t.EmployeeID = o2.EmployeeID and t.LogDate = o2.LogDate and o2.InOut = 1 and o1.LogTime < o2.LogTime
left outer join #AccessLog o3 on t.EmployeeID = o3.EmployeeID and t.LogDate = o3.LogDate and o3.InOut = 1 and o2.LogTime < o3.LogTime
group by t.EmployeeID, t.LogDate
order by t.EmployeeID, t.LogDate
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
BTW, the above assumes there are no more than 3 ins or 3 outs for any employee on any day. I assumed this was okay since your Report table has that limitation.
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
It works exactly. RyanRandall, Thanks very much. Can you explain your code? I can't understand why left outer join is neccessary.
-
If I query a staff whose employeeid is 0915 and LogDate is between 3/1/2006 to 3/10/2006. the result is shown as following. There is only a record in 3/1/2006.
RCDID EmployeeID LogDate LogTime TerminalID InOut
-------- ---------------- ------------ --------- ----------- --------
1 0915 03/01/2006 10:00:00 0 0
2 0915 03/01/2006 10:10:32 0 0
3 0915 03/01/2006 18:00:00 0 1
And I would like to generate the report from 3/1/2006 to 3/10/2006. The result is as the following:
How can I do?
EmployeeID In1 In2 Out1 Out2 RecordDate
------------ --------- ------- ------- -------- ---------
0915 10:00:00 10:10:32 18:00:00 3/1/2006
0915 Null Null Null 3/2/2006
0915 Null Null Null 3/3/2006
0915 Null Null Null 3/4/2006
0915 Null Null Null 3/5/2006
0915 Null Null Null 3/6/2006
0915 Null Null Null 3/7/2006
0915 Null Null Null 3/8/2006
0915 Null Null Null 3/9/2006
0915 Null Null Null 3/10/2006
-
See changes in red below. Note that (both above and here) 't' is a query which gets all the rows we want - and that we use outer joins just in case there's no matching data when we join to 't' (which would unwantedly get rid those rows).
I hope that helps.
--data
set dateformat mdy
if Object_ID(N'TempDB.[dbo].[#AccessLog]') is not null drop table #AccessLog
CREATE TABLE [dbo].[#AccessLog] (
[RCDID] [int] NOT NULL ,
[EmployeeID] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LogDate] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LogTime] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TerminalID] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[InOut] [int] NULL
)
insert #AccessLog
select 1, '0915', '03/01/2006', '10:00:00', 0, 0
union all select 2, '0915', '03/01/2006', '10:10:32', 0, 0
union all select 3, '0915', '03/01/2006', '18:00:00', 0, 1
union all select 4, '0123', '03/01/2006', '11:00:00', 0, 0
union all select 5, '0123', '03/01/2006', '12:00:00', 0, 1
union all select 5, '0123', '03/01/2006', '13:00:00', 0, 1
union all select 5, '0123', '03/01/2006', '14:00:00', 0, 1
union all select 5, '0123', '04/01/2006', '14:00:00', 0, 1
--inputs
declare @from datetime
declare @to datetime
set @from = '3/1/2006'
set @to = '3/10/2006'
--calculation
declare @Numbers table (i int identity(0, 1), j bit)
insert @Numbers select top 100 null from master.dbo.syscolumns
select
t.EmployeeID,
min(i1.LogTime) as In1, min(i2.LogTime) as In2, min(i3.LogTime) as In3,
min(o1.LogTime) as Out1, min(o2.LogTime) as Out2, min(o3.LogTime) as Out3,
t.LogDate as RecordDate
from
(select distinct EmployeeID, dateadd(d, i, @from) as LogDate from #AccessLog, @Numbers where dateadd(d, i, @from) <= @to) t
left outer join #AccessLog i1 on t.EmployeeID = i1.EmployeeID and t.LogDate = i1.LogDate and i1.InOut = 0
left outer join #AccessLog i2 on t.EmployeeID = i2.EmployeeID and t.LogDate = i2.LogDate and i2.InOut = 0 and i1.LogTime < i2.LogTime
left outer join #AccessLog i3 on t.EmployeeID = i3.EmployeeID and t.LogDate = i3.LogDate and i3.InOut = 0 and i2.LogTime < i3.LogTime
left outer join #AccessLog o1 on t.EmployeeID = o1.EmployeeID and t.LogDate = o1.LogDate and o1.InOut = 1
left outer join #AccessLog o2 on t.EmployeeID = o2.EmployeeID and t.LogDate = o2.LogDate and o2.InOut = 1 and o1.LogTime < o2.LogTime
left outer join #AccessLog o3 on t.EmployeeID = o3.EmployeeID and t.LogDate = o3.LogDate and o3.InOut = 1 and o2.LogTime < o3.LogTime
group by t.EmployeeID, t.LogDate
order by t.EmployeeID, t.LogDate
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
(select distinct EmployeeID, dateadd(d, i, @from) as LogDate from #AccessLog, @Numbers where dateadd(d, i, @from) <= @to)
Why does this query can have a loop function?If doing this on my way, I will use "while...begin...end" to implement. However, I know that using "while...begin...end" is not a good way to do this.
And I have a question, I haven't seen that varible 'i' was declared. Why did this query can be executed without error?
And I've seen all of your code in this post. I've never seen you to use while...begin...end to implement. Why?
I'm a beginner of writing T-SQL. So, I've many questions about T-SQL. Anyways, many thanks for your help.
-
You don't need any looping. This query does all you need (well, all you've asked for) on its own.
Have you not run it to see? That always helps!
'i' is not a variable (variables have an '@' prefix). 'i' is a column name of the @Numbers table.
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
It works exactly. However, the speed of query execution is not good enough because of my big size table. Is there any faster method?Sorry for bothering you again.
-
I don't think you'll find a faster method to solve the problem you've defined. Try analysing the execution plan, and seeing if you can add indexes to speed up the slow bits.
Failing that you might want to look at the original problem you're trying to solve (i.e. the business problem) and think if you've got the best design (including your table design) for it.
Hope that helps
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
My company has a table called AccessLog in SQL Server. It stores all of the staff attendance records. And the struture of this table can't be change because the time card machine can only be run in this table. However, I'm requested for writing a code that can generate report. the report's struture is like what I show you before.
CREATE TABLE [dbo].[Report] (
[employeeid] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[in1] [datetime] NULL ,
[in2] [datetime] NULL ,
[in3] [datetime] NULL ,
[out1] [datetime] NULL ,
[out2] [datetime] NULL ,
[out3] [datetime] NULL ,
[recorddate] [datetime] NULL
) ON [PRIMARY]
So, I think what I do first is to convert "AccessLog" Table into "Report".
I've already tried using your code. But it executed for a long time while the query result had just about 9000 records
-
Hi
You can use the following Query:::
This Query will create a table of same structure:
select EmployeeID,ln1,ln2 into Record from acesslog where 1=0
Regards,
Amit Gupta..
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply