Plzzzzz Solve this - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Plzzzzz Solve this

Author

Topic

soori457
Yak Posting Veteran

85 Posts

Posted - 2008-12-26 : 00:42:54

Hai Everyone.

In the below table I have to retrieve the CardNo who has In and Out on the same date. If there is only I(In) and there is no O(Out) or viceversa. then, we have to update the status of the CardNo as Mispunched(Yes/No).

For this, which one I have to create, wither Stored proc or Cursors
Anyone tell me, how to solve this AS I'm new to Sql Server.

Thanks in Advance

Attendance(Table Name)

CardNo Dt Time In_Out Mispunched
9C1H000083 12/12/2008 01:15 O
9C3H000014 12/12/2008 01:16 O
9C1H000242 12/12/2008 01:16 O
9C3C000003 12/12/2008 01:16 O
9C4T000087 12/12/2008 01:16 O
9C1T000076 12/12/2008 01:16 O
9C2H000013 12/12/2008 01:17 O
9COS000012 12/12/2008 05:31 O
9C1H000092 12/12/2008 07:21 I
9C1H000124 12/12/2008 07:27 I
9C1H000100 12/12/2008 07:30 I
9C1H000249 12/12/2008 07:30 I
9C1H000112 12/12/2008 07:30 I
9COS000037 12/12/2008 07:30 I
9C4T000031 12/12/2008 09:08 I
9C1H000169 12/12/2008 09:09 I
9COF000018 12/12/2008 09:09 I
9C4PS00012 12/12/2008 09:09 I
9COC000037 12/12/2008 09:09 I
9COS000111 12/12/2008 09:09 I
9C1T000110 12/12/2008 09:09 I
9C1T000159 12/12/2008 09:10 I
9C1H000250 12/12/2008 09:10 I
9COS000158 12/12/2008 09:10 I
9COS000087 12/12/2008 09:10 I
9C1T000286 12/12/2008 09:10 I
9COC000028 12/12/2008 09:10 I
9COT000052 12/12/2008 09:10 I
9COPS00022 12/12/2008 09:10 I
9COF000012 12/12/2008 09:10 I
9COH000072 12/12/2008 09:10 I
9COT000082 12/12/2008 09:10 I
9C4PS00026 12/12/2008 09:11 I
9C2T000046 12/12/2008 09:11 I
9COT000066 12/12/2008 09:11 I
9C2PS00035 12/12/2008 09:11 I
9C4F000006 12/12/2008 09:11 I
9C1H000193 12/12/2008 09:11 I
9COPS00124 12/12/2008 09:11 I
9C1PS00116 12/12/2008 09:11 I
9COPS00110 12/12/2008 09:11 I
9C1T000121 12/12/2008 09:11 I
9C1T000241 12/12/2008 09:21 I
9C2H000042 12/12/2008 09:28 I
9COT000078 12/12/2008 09:14 I
9COC000009 12/12/2008 08:57 I
9C1PS00078 12/12/2008 09:16 I
9C1T000281 12/12/2008 09:24 I
9C1C000009 12/12/2008 10:41 O
9C1T000085 12/12/2008 09:24 I
9C1PS00097 12/12/2008 09:30 I
9C3T000078 12/12/2008 09:24 I
9C3T000057 12/12/2008 11:01 O
9C3T000088 12/12/2008 13:20 I
9C1H000033 12/12/2008 10:17 I
9C4H000094 12/12/2008 10:41 O
9C1H000099 12/12/2008 12:45 O
9COS000114 12/12/2008 13:19 O
9C3F000001 12/12/2008 14:06 O
9C1F000052 12/12/2008 14:30 O
9C1T000019 12/12/2008 01:15 O
9C4T000087 12/12/2008 01:15 O
9C2F000007 12/12/2008 01:19 O
9COS000092 12/12/2008 06:57 I
9C1H000114 12/12/2008 07:23 I
9COS000035 12/12/2008 08:42 I
9COF000047 12/12/2008 08:50 I
9COS000112 12/12/2008 09:04 I
9COC000004 12/12/2008 09:04 I
9COC000042 12/12/2008 09:04 I
9C1PS00015 12/12/2008 09:04 I
9C4C000005 12/12/2008 09:04 I
9COPS00054 12/12/2008 09:15 I
9COS000127 12/12/2008 09:15 I
9C4PS00010 12/12/2008 09:15 I
9COS000168 12/12/2008 09:15 I

Suresh Kumar

suresha_b
Yak Posting Veteran

82 Posts

Posted - 2008-12-26 : 01:37:13

>>For this, which one I have to create, wither Stored proc or Cursors
Anyone tell me, how to solve this AS I'm new to Sql Server.

Create stored proc without cursors

soori457
Yak Posting Veteran

85 Posts

Posted - 2008-12-26 : 01:41:33

Hai Suresh, thnks for ur reply.

Could you please tell me, how to write the stores proc

Suresh Kumar

suresha_b
Yak Posting Veteran

82 Posts

Posted - 2008-12-26 : 02:19:45

update a set MisPunched = 1
-- select *
from (select * from dbo.Attendance where In_Out = 'I') a
left outer join (select * from dbo.Attendance where In_Out = 'O') b
on a.CardNo = b.CardNo
and a.Dt = b.Dt
where b.CardNo is null

update a set MisPunched = 1
-- select *
from (select * from dbo.Attendance where In_Out = 'O') a
left outer join (select * from dbo.Attendance where In_Out = 'I') b
on a.CardNo = b.CardNo
and a.Dt = b.Dt
where b.CardNo is null

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-27 : 11:45:23

[code]
UPDATE t
SET t.Mispunched=1
FROM
(SELECT *,SUM(CASE WHEN In_Out='I' THEN 1 ELSE 0 END) OVER(PARTITION BY CardNo,Dt) AS InCount,
SUM(CASE WHEN In_Out='O' THEN 1 ELSE 0 END) OVER(PARTITION BY CardNo,Dt) AS OutCount
FROM YourTable
)t
WHERE t.InCount * t.OutCount=0
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources