SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Plzzzzz Solve this
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

soori457
Yak Posting Veteran

India
85 Posts

Posted - 12/26/2008 :  00:42:54  Show Profile  Reply with Quote
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

India
82 Posts

Posted - 12/26/2008 :  01:37:13  Show Profile  Send suresha_b a Yahoo! Message  Reply with Quote
>>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
Go to Top of Page

soori457
Yak Posting Veteran

India
85 Posts

Posted - 12/26/2008 :  01:41:33  Show Profile  Reply with Quote
Hai Suresh, thnks for ur reply.

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

Suresh Kumar
Go to Top of Page

suresha_b
Yak Posting Veteran

India
82 Posts

Posted - 12/26/2008 :  02:19:45  Show Profile  Send suresha_b a Yahoo! Message  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/27/2008 :  11:45:23  Show Profile  Reply with Quote

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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000