March 1, 2012 at 3:58 am
Dear All,
I have employee punch table with the following structure:
ID DateTime IsManual.
=====================================
001 2012/02/01 08:10:00 N
001 2012/02/01 17:34:00 N
003 2012/02/02 07:10:00 Y
003 2012/02/02 11:00:00 N
003 2012/02/02 13:21:00 N
003 2012/02/02 17:38:00 N
As we can see an employee can have multiple punches on a day. (assuming the max. number of punch per day is 4).
The time may vary based on shifts. so we cant put condition on time.
Now Just want to generate a report based on ID, the punches for an employee should display in column manner.
EG:
Date: 02/02/2012.
ID IN OUT IN OUT
003 07:10:00 11:00:00 13:21:00 17:38:00
March 1, 2012 at 4:23 am
Please read this article --> http://www.sqlservercentral.com/articles/Best+Practices/61537/%5B/url%5D, then post your sample data and DDL in a readily consumable format. Help the unpaid volunteers of this site to help you, and you'll quickly find that people are much more likely to post tested and working code as a solution to your issue.
March 1, 2012 at 4:31 am
Next time, please post your question as per forum etiquette well described in the link at the bottom of my signature, this will guaranteer faster and better answers.
-- setup your case:
create table #tab (id char(3), dt datetime, IsManual char(1))
insert #tab values ('001','2012/02/01 08:10:00','N'),
('001','2012/02/01 17:34:00','N'),
('003','2012/02/02 07:10:00','Y'),
('003','2012/02/02 11:00:00','N'),
('003','2012/02/02 13:21:00','N'),
('003','2012/02/02 17:38:00','N')
-- Put the result into temp table, looks like you could use CTE, but as
-- we will need to join to it mulitple times, it will be better to have in the table...
select id, dt, ROW_NUMBER() OVER (PARTITION BY id ORDER BY dt) RN
into #tabn
from #tab
-- ...to have in the table, which, if your dataset quite large, you may index and get better performance:
create unique clustered index ix_#tabn on #tabn(id asc, rn asc)
-- here is the final query
select in1.id, in1.dt [IN1], out1.dt [OUT1], in2.dt [IN2], out2.dt [OUT2]
from #tabn in1
left join #tabn out1 on out1.id = in1.id and out1.RN = 2
left join #tabn in2 on in2.id = in1.id and in2.RN = 3
left join #tabn out2 on out2.id = in1.id and out2.RN = 4
where in1.RN = 1
As you can see it will work for your assumption of having maximum 4 "punches"
What about if you have more? What about if it vary?
Search for dynamic cross-tab query.
But, do you really need cross-tab?
May be you can have something like this:
Id PunchNumber In Out
003 1 07:10:00 11:00:00
003 2 13:21:00 17:38:00
...
003 n ....
It will much easier to work with above than with cross-tab...
March 1, 2012 at 4:45 am
Thanks Eugene for the data that the OP hasn't provided.
You mentioned a dynamic cross-tab solution, but didn't use a static cross-tab solution and instead hit the table a lot. Any reason why you didn't go for something like this?
SELECT id,
MAX(CASE WHEN clockPos = 'In 1' THEN dt ELSE NULL END) AS [In 1],
MAX(CASE WHEN clockPos = 'Out 1' THEN dt ELSE NULL END) AS [Out 1],
MAX(CASE WHEN clockPos = 'In 2' THEN dt ELSE NULL END) AS [In 2],
MAX(CASE WHEN clockPos = 'Out 2' THEN dt ELSE NULL END) AS [Out 2]
FROM (SELECT id, dt,
clock + SPACE(1) + CAST(ROW_NUMBER() OVER (PARTITION BY id, clock ORDER BY pos) AS CHAR(1))
FROM (SELECT id, dt, ROW_NUMBER() OVER (PARTITION BY id ORDER BY dt)
FROM #tab) a(id, dt, pos)
CROSS APPLY (SELECT CASE WHEN pos%2 = 1 THEN 'In' ELSE 'Out' END) b(clock)
) c(id, dt, clockPos)
GROUP BY id
March 2, 2012 at 3:03 am
Right now there is max. no. of punches are 4 only.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply