Query solution - 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 2000 Forums

SQL Server Development (2000)

Query solution

Author

Topic

swatib
Posting Yak Master

173 Posts

Posted - 2006-07-26 : 05:56:04

I have 2 tables - WorkScope and Activity
* Table : WorkScope *
SiteID - ActivityID
768 - 101
768 - 102
768 - 103

* Table : Activity *
SiteID - ActivityID - Status
768 - 101 - Not Completed
768 - 102 - Completed

Now I want the following result
SiteID - Status
768 - Not Completed

Because SiteId 768 has 3 activities and those all 3 are not completed or entered in the Activity table.

May be the solution is simple, help me

Njoy Life

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-26 : 06:09:43

>> all 3 are not completed or entered in the Activity table
How do you knkow a task is not completed if it's not enetered in the activity table?

looks to me from this that if it's in the activity table you want it and if it's not you want it so
select distinct SiteID from WorkScope

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

swatib
Posting Yak Master

173 Posts

Posted - 2006-07-26 : 06:20:46

See there is a logic that for an activity, its start & end dates are entered then that Activity's status becomes 'Completed' in Activity table
But for e.g 103 activity is not partially entered or fully entered and thats why its not in Activity table for that SiteID

Njoy Life

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-07-26 : 06:23:25

This is what I got (I added a few mor activities and workscopes to test it better):

DECLARE @WorkScope table (SiteID int, ActivityID int)
DECLARE @Activity table (SiteID int, ActivityID int, Status varchar(20))
INSERT INTO @WorkScope
SELECT 768, 101 UNION ALL SELECT 768, 102 UNION ALL 
SELECT 768, 103 UNION ALL SELECT 770, 103 UNION ALL 
SELECT 780, 500
INSERT INTO @Activity
SELECT 768, 101, 'Not Completed' UNION ALL SELECT 768, 102, 'Completed' UNION ALL
SELECT 780, 500, 'Completed' 

SELECT * FROM @WorkScope
SELECT * FROM @Activity

SELECT dt1.SiteID, Activities, Completed, Status = CASE 
  WHEN ISNULL(Activities, 0) <> ISNULL(Completed, 0)
    THEN 'Not completed'
    ELSE 'Completed'
    END
FROM (SELECT SiteID, Count(*) AS Activities
    FROM @WorkScope
    GROUP BY SiteID) AS dt1
  LEFT OUTER JOIN 
    (SELECT SiteID, Count(*) AS Completed
    FROM @Activity
    WHERE Status = 'Completed'
    GROUP BY SiteID) AS dt2
  ON dt1.SiteID = dt2.SiteID

If you have more than one activity with the same SiteID in the @Activity table it wount work but if you need it I'm sure I can find a workaround.

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-26 : 06:25:56

This is the most simplest way to accomplish your task, I think

-- Prepare test data
declare @WorkScope table (SiteID int, ActivityID int)

insert	@workscope
select 768, 101 union all
select 768, 102 union all
select 768, 103

declare	@Activity table (SiteID int, ActivityID int, Status varchar(20))

insert	@activity
select	768, 101, 'Not Completed' union all
select	768, 102, 'Completed'

-- Do the work!
select	SiteID,			-- Select all Sites having status 'Not Completed'
	Status
from	@activity
where	status = 'not completed'
union
select		w.SiteID,	-- Select all Sites not having all activities
		'Not completed'
from		@workscope w
left join	@activity a on a.siteid = w.siteid and a.activityid = w.activityid
group by	w.siteid
having		count(distinct w.activityid) <> count(distinct a.activityid)

This is only an error list. You haven't told what to do if all activities for a site is status 'Completed'...

Peter Larsson
Helsingborg, Sweden

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-07-26 : 06:27:17

Heh, I belive we did the same thing...didn't we?

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-26 : 06:27:46

You mean you want those SiteIDs whose tasks are not all complleted in Activity?

select t.SiteID
from (select SiteID, num = count(*) from WorkScope group by SiteID) t
left join (select SiteID, num = count(*) from Activity where Status = 'Completed' group by SiteID) t2
where t.num <> coalesce(t2.num,0)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-26 : 06:32:42

quote:
Originally posted by nr

select t.SiteID
from (select SiteID, num = count(*) from WorkScope group by SiteID) t
left join (select SiteID, num = count(*) from Activity where Status = 'Completed' group by SiteID) t2
where t.num <> coalesce(t2.num,0)

Nice solution!
Just add on t2.siteid = t.siteid after t2 in the left join, then you're done.

Peter Larsson
Helsingborg, Sweden

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-26 : 06:36:41

oops - thanks
select t.SiteID
from (select SiteID, num = count(*) from WorkScope group by SiteID) t
left join (select SiteID, num = count(*) from Activity where Status = 'Completed' group by SiteID) t2
on t2.SiteID = t.SiteID
where t.num <> coalesce(t2.num,0)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-26 : 07:52:27

I think I'd do one of these...

select distinct SiteID, 'Not Completed' as Status
from workscope a
where not exists (select * from activity where ActivityID = a.ActivityID and Status = 'Completed')

select distinct a.SiteID, 'Not Completed' as Status
from workscope a left outer join activity b on a.ActivityID = b.ActivityID
where not Status = 'Completed'

But then I think I must be missing something because the other suggestions seem more complicated.

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-07-26 : 08:46:45

hoeps it will work.

Select
W.SiteId
,A.Status
From
WorkScope As W
,Activity As A
Where
W.SiteId = A.SiteId
And A.ActivityId = 101

BMahesh

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-26 : 09:23:41

RyanRandall's should include the SiteID in the join

select distinct a.SiteID, 'Not Completed' as Status
from workscope a
left outer join activity b
on a.SiteID = b.SiteID
and a.ActivityID = b.ActivityID
and Status = 'Completed'
where b.ActivityID is null

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

swatib
Posting Yak Master

173 Posts

Posted - 2006-07-27 : 04:29:36

Thanks all of u

Njoy Life

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources