使用storeprocedure和cursor的SQL [英] SQL using storeprocedure and cursor
问题描述
我有一个sql数据如下。
I have a sql data as follows.
SeqID DevicesID EmployeeID Type Time
1 2 624 0 2014-07-01 09:00:08.000
2 2 624 1 2014-07-02 01:00:18.000
3 2 624 0 2014-07-02 08:55:11.000
4 2 624 0 2014-07-02 09:00:12.000
5 2 624 1 2014-07-02 17:00:14.000
我想记录如下：
I want to take the record is as follows:
SeqID DevicesID EmployeeID Type Time
1 2 624 0 2014-07-01 09:00:08.000
2 2 624 1 2014-07-02 01:00:18.000
3 2 624 0 2014-07-02 08:55:11.000
5 2 624 1 2014-07-02 17:00:14.000
0 =入住
1 =退房
条款
如果有时钟在01-07-2014,09 ：00：08他错过了，而且他在2014年7月2日凌晨1点18分发布时间，
他在小时的同一时钟08：55：11,2014-07-02 09:00:12和17 ：00：14小时。
如果有人可以帮助我，请。
0 = Check In
1 = Check Out
provisions
if there is a clock in on 01-07-2014, 09:00:08 hours he missed, and he clocked 1:00:18 hours on 02-07-2014,
he was on the same clock in hours 08:55:11, 2014-07-02 09:00:12, and 17:00:14 hours clocked on.
If anyone could help me, please.
推荐答案
你好，
我用一个例子试过这个。请尝试使用此方法的代码
Hi,
I tried this with an example. Please try your code with this approach
create table #tracking
(
id int,
punchtime datetime,
checkinout bit
);
with abc as (
select row_number()over(partition by cast(punchtime as date)order by punchtime) sno,*
from #tracking
)
select * from abc where sno=1
union
select * from abc where checkinout=1 order by id
如果您遇到任何问题请告诉我这段代码片段。
-Ankit
Let me know if you face any problem using this code snippet.
-Ankit
如果您使用的是SQL Server 2012，那么您可以使用新的LAG功能允许你检查以前的记录。
鉴于您有一个序列，您只需要检查前一个条目是否与您正在检查的行相反，允许空值。
If you are using SQL Server 2012 then you can use the new LAG functionality which will allow you to check the previous record.
Given that you have a sequence you just need to check that that the previous entry was the opposite of the row you are examining, allowing for nulls.
with checking as (
SELECT SeqID, PunchTime,
LAG(t.Type) OVER (ORDER BY t.SeqID) PreviousValue,
t.Type,
LEAD(t.Type) OVER (ORDER BY t.SeqID) NextValue
FROM tracking t )
SELECT t.*
FROM tracking t
INNER JOIN checking c ON t.SeqID = c.SeqID
WHERE ISNULL(PreviousValue,-1) <> c.Type
order by t.SeqID
兴趣点 - 我使用 ISNULL（PreviousValue， -1 ）确保临时表（[checking]）的开头和结尾的空值与这些行的类型明显不同。
- 我还包括 LEAD 代码，这样你就可以看到如何向前看到下一行。
这产生了以下结果：
Points of interest - I used ISNULL(PreviousValue, -1) to make sure the nulls at the start and end of the temporary table ([checking]) were definitely different to the type of those rows.
- I've also included the LEAD code so you can see how to "look ahead" to the next row.
This produced the following results:
SEQID DEVICESID EMPLOYEEID TYPE PUNCHTIME
1 2 624 0 July, 01 2014 09:00:08+0000
2 2 624 1 July, 02 2014 01:00:18+0000
3 2 624 0 July, 02 2014 08:55:11+0000
5 2 624 1 July, 02 2014 17:00:14+0000
编辑 - 为了完整性，这里是SQL 2008 usi的等效解决方案公共表格表达式
EDIT - For completeness here is an equivalent solution for SQL 2008 using Common Table Expression
WITH CTE AS (
SELECT
rownum = ROW_NUMBER() OVER (ORDER BY t.SeqID),
t.Type,
t.SeqID
FROM tracking t )
SELECT CTE.SeqID,
prev.Type PreviousValue, CTE.Type, nex.Type NextValue
INTO ##checking
FROM CTE
LEFT JOIN CTE prev ON prev.rownum = CTE.rownum - 1
LEFT JOIN CTE nex ON nex.rownum = CTE.rownum + 1
SELECT t.*
FROM tracking t
INNER JOIN ##checking c ON t.SeqID = c.SeqID
WHERE ISNULL(PreviousValue,-1) <> c.Type
order by t.SeqID
本文可能有用： SQL SERVER 2008中的常用表格表达式（CTE） [ ^ ]这里是一个使用LAG的好例子和LEAD - SQLauthority blog [ ^ ]
This article may prove useful : Common Table Expressions(CTE) in SQL SERVER 2008[^] and here is a good example on using LAG and LEAD - SQLauthority blog[^]
这篇关于使用storeprocedure和cursor的SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
