Need Help in writing correlated subqueries..Plzzz

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

Need Help in writing correlated subqueries..Plzzz

Author

Topic

soori457
Yak Posting Veteran

85 Posts

Posted - 2008-12-29 : 05:17:47

This is my table data.

Table Name is Attendance

CardNo Dt OutCnt InCnt MisPunch
a 12/12/2008 0 2 Null
a 14/12/2008 2 1 Null
a 12/12/2008 0 1 Null
b 13/12/2008 1 1 Null
c 13/12/2008 1 1 Null
b 12/12/2008 0 2 Null
b 12/12/2008 1 2 Null
c 11/12/2008 1 0 Null
c 13/12/2008 1 1 Null
d 13/12/2008 0 1 Null

I have to update table(Attendance) column Mispunch as M, based on both CardNo and Date when OutCnt or InCnt is equal to zero(0).

I have tried, but its not working. This is my query

update Attendance set Mispunch = 'M'
where CardNo in
(select CardNo from Attendance where Dt in
(select Dt from Attendance where OutCnt = 0 or InCnt = 0))

any please help me, how to write query for this....its urgent

Suresh Kumar

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-12-29 : 05:30:04

Your requirements are not clear. I think you need this,

update Attendance set Mispunch = 'M'
where 
OutCnt = 0 or InCnt = 0

soori457
Yak Posting Veteran

85 Posts

Posted - 2008-12-29 : 05:36:28

Thanks for ur reply Sakets..

My requirements is, after executing the update query, The o/p should be

CardNo Dt OutCnt InCnt MisPunch
a 12/12/2008 0 2 M
a 14/12/2008 2 1 Null
a 12/12/2008 0 1 M
b 13/12/2008 1 1 Null
c 13/12/2008 1 1 Null
b 12/12/2008 0 2 M
b 12/12/2008 1 2 Null
c 11/12/2008 1 0 M
c 13/12/2008 1 1 Null
d 13/12/2008 0 1 M

But, when I'm executing my query, every row is updated. This is the o/p of my query.

CardNo Dt OutCnt InCnt MisPunch
a 12/12/2008 0 2 M
a 14/12/2008 2 1 M
a 12/12/2008 0 1 M
b 13/12/2008 1 1 M
c 13/12/2008 1 1 M
b 12/12/2008 0 2 M
b 12/12/2008 1 2 M
c 11/12/2008 1 0 M
c 13/12/2008 1 1 M
d 13/12/2008 0 1 M

You can see the difference now

Suresh Kumar

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-29 : 05:37:39

didnt we answered this?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=116860

soori457
Yak Posting Veteran

85 Posts

Posted - 2008-12-29 : 05:39:11

The CardNo 'a' has three differene dates, based on CardNo and Dt, the column Mispunch should get updated and the condition is OutCnt = 0 or InCnt = 0

Suresh Kumar

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-29 : 05:39:35

ah...so your reqmnt is changed? in that case what you need is query given by sakets

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-12-29 : 05:40:34

quote:
Originally posted by visakh16

didnt we answered this?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=116860

Have you tried running the query I posted before ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-29 : 05:45:55

quote:
Originally posted by sakets_2000

quote:
Originally posted by visakh16

didnt we answered this?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=116860

Have you tried running the query I posted before ?

it seems his reqmnts have changed from last post

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2008-12-29 : 06:49:18

Use or Instead of and between tow checks,,,,
Thanks...

Like dat...
OutCnt = 0 or InCnt = 0

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-29 : 07:20:38

quote:
Originally posted by soori457

This is my table data.

Table Name is Attendance

CardNo Dt OutCnt InCnt MisPunch
a 12/12/2008 0 2 Null
a 14/12/2008 2 1 Null
a 12/12/2008 0 1 Null
b 13/12/2008 1 1 Null
c 13/12/2008 1 1 Null
b 12/12/2008 0 2 Null
b 12/12/2008 1 2 Null
c 11/12/2008 1 0 Null
c 13/12/2008 1 1 Null
d 13/12/2008 0 1 Null

I have to update table(Attendance) column Mispunch as M, based on both CardNo and Date when OutCnt or InCnt is equal to zero(0).

I have tried, but its not working. This is my query

update Attendance set Mispunch = 'M'
where CardNo in
(select CardNo from Attendance where Dt in
(select Dt from Attendance where OutCnt = 0 or InCnt = 0) )

any please help me, how to write query for this....its urgent

Suresh Kumar

The highlighted subquery o/p is a,b,c,d
so all the rows are getting updated since all of them has either a or b or c or d as card no

use sakets solution it will solve ur problem

Jai Krishna

soori457
Yak Posting Veteran

85 Posts

Posted - 2008-12-29 : 23:10:19

Everyone, Thanks for all ur replies.
The solution provided by Visakh16 is correct.
here's is the solution..

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

Suresh Kumar

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 00:20:05

welcome

soori457
Yak Posting Veteran

85 Posts

Posted - 2008-12-30 : 01:02:55

You are doing a Great Job...Visak16

Suresh Kumar

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 01:04:25

Thanks Soori