SQL using storeprocedure and cursor

Question

0.00/5 (No votes)

See more:

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 = 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.

Posted 2-Jul-14 6:03am

mixiya

Updated 2-Jul-14 6:05am

CHill60

v2

Add a Solution

Comments

coded007 3-Jul-14 3:17am

can you give us some more considering fields so it will help to design best solution

[no name] 3-Jul-14 3:24am

can you test one thing, Make a subquery where you will not add your seqID and select as DISTINT after select ROWNUM() as DeqID, * from (SELECT DISTINCT DevicesID,EmployeeID, Type, convert(varchar(10),Time) as Time)

something like this

CHill60 16-Jul-14 5:34am

Are you using SQL 2012 or an earlier version?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

anki.mathur · Answer 1 · 2014-07-15T21:38:00

Hi,

I tried this with an example. Please try your code with this approach

SQL

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

Let me know if you face any problem using this code snippet.

-Ankit

CHill60 · Answer 2 · 2014-07-15T23:58:00

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.

SQL

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

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

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

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[^]

SQL using storeprocedure and cursor

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0