Data comparsion - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Data comparsion

Author

Topic

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-11-18 : 01:17:05

RequestTbl
RequestID userid

97 z100
98 z100
99 z100
100 m125
101 m125
102 e65
103 f789
104 k75
105 k75

TaskTbl

TaskID RequestID

1 97
2 98
3 97
4 97
5 98
6 99
7 100
8 101
9 102
10 104

TaskUserAssigned

TUID TaskID Submitted
150 1 1
151 2 1
152 3 1
153 4 1
154 5 1
155 6 1
156 7 0
157 8 1
158 10 1

Relation:
Requesttbl,Tasktabl:Requestid
Tasktbl,TaskuserAssignedtbl: Taskid

Case 1:

If input Parameter passed as z100

First it will get the Requestid from RequestTbl for z100,this case requestid is : 97,98,99.Then

In Tasktbl,to find the Taskid for 97(1,3,4)
to find the Taskid for 98(2,5)
to find the Taskid for 99 (6)

Then

In TaskUserAssigned tbl,

To see the Submitted column as 1 for taskid is 1
To see the Submitted column as 1 for taskid is 3
To see the Submitted column as 1 for taskid is 4
To see the Submitted column as 1 for taskid is 2
To see the Submitted column as 1 for taskid is 5
To see the Submitted column as 1 for taskid is 6

if this is the case,Return 1

case 2:Parameter : m125

for this case requesid :100,101

Tasktbl:
to find the Taskid for 100(7)
to find the Taskid for 101(8)

TaskUserAssignedTbl,
To see the Submitted column as 0 for taskid is 7
To see the Submitted column as 1 for taskid is 8

will hvae to Return 0 (Reason : all the taskid should be 1,if any 0 means,return 0)

Case 3:Parameter : e65

First it will get the Requestid from RequestTbl for e65,this case requestid is : 102.Then

In Tasktbl,to find the Taskid for 102(9)

In TaskUserAssigned tbl,No Task,I mean ,for Taskid 9 is not there..

so this case Return 0.

Case 4: f789
First it will get the Requestid from RequestTbl for f789,this case requestid is : 103.Then

In Tasktbl,for requets id 103,no task is there..

So return 0.

Case 5: k75
First it will get the Requestid from RequestTbl for f789,this case requestid is : 104,105.Then

In Tasktbl,to find the Taskid for 104(10)
to find the Taskid for 105.(No Task is there)..
even though 104 requestid,Task no 10,TaskUserAssigned tbl for task no 10,
submitted column is 1 but no task created for 105.

So return 0.

Only for Case 1,Return 1,rest of the cases return 0.
please any sample query..

any help much appreciated..

if iam unclear,kindly please reply

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-18 : 07:20:10

[code]SELECT CASE WHEN COUNT(t.TaskID) = COUNT(DISTINCT tua.TaskID) THEN 1 ELSE 0 END
FROM RequestTbl r
INNER JOIN TaskTbl t
ON t.RequestID= r.RequestID
LEFT JOIN TaskUserAssigned tua
ON tua.TaskID = t.TaskID
AND Submitted = 1
WHERE r.userid=@UserID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources