Need proper query - 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)

Need proper query

Author

Topic

swatib
Posting Yak Master

173 Posts

Posted - 2006-07-28 : 02:12:02

I have a view -

View-
Fields are SiteID, activityid and Status (status could be WIP work in progress, WNS work not started, or Completed). A siteid can have more than one activityid.

I need a query giving me the final status of the siteid
Condition:
1. For a siteid if all activity's status are Completed then the final status will be 'Completed'
2. For a siteid if all activity's status are WNS then the final status will be 'WNS'
3. For a siteid if all activity's status are WIP then the final status will be 'WIP'
4. For a siteid if all activity's status are combination of Completed/WIP/WNS then the final status will be 'WIP'

For e.g
Siteid - Activityid - Status
101 - A - WNS
101 - B - WNS
101 - C - Completed

then result should be
Siteid - FinalStatus
101 - WIP

Njoy Life

Q
Yak Posting Veteran

76 Posts

Posted - 2006-07-28 : 03:44:01

SELECT tmp.siteId,
CASE WHEN tmp.status = 0 THEN 'completed'
WHEN tmp.status = 0.5 THEN 'WIP'
WHEN tmp.status = 1 THEN 'WIP'
WHEN tmp.status = 1.5 THEN 'WIP'
WHEN tmp.status = 2 THEN 'WNS' END
FROM (SELECT allSite.siteId,
AVG(CASE WHEN allSite.statusId = 'completed' THEN 0.0
WHEN allSite.statusId = 'WNS' THEN 2.0
WHEN allSite.statusId = 'WIP' THEN 1.0 END) AS status
FROM (SELECT DISTINCT siteId, statusId
FROM dbo.site) allSite
GROUP BY allSite.siteId) tmp

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-07-28 : 04:19:32

[code]
declare @t table(Siteid int,Activityid varchar(2), Status varchar (20))

insert into @t(siteid,activityid,status)
select '101','A','WNS'
union select
'101','B','WNS'
union select
'101','C','Completed'

select distinct siteid,case when siteid in(select siteid from
(select siteid,status
from @t t
group by siteid,status
having count(*)=(select count(*) from @t where siteid=t.siteid)
)a) then status else 'WIP' end as status
from @t
[/code]

--------------------
keeping it simple...

swatib
Posting Yak Master

173 Posts

Posted - 2006-07-28 : 04:36:20

quote:
Originally posted by Q

SELECT tmp.siteId,
CASE WHEN tmp.status = 0 THEN 'completed'
WHEN tmp.status = 0.5 THEN 'WIP'
WHEN tmp.status = 1 THEN 'WIP'
WHEN tmp.status = 1.5 THEN 'WIP'
WHEN tmp.status = 2 THEN 'WNS' END
FROM (SELECT allSite.siteId,
AVG(CASE WHEN allSite.statusId = 'completed' THEN 0.0
WHEN allSite.statusId = 'WNS' THEN 2.0
WHEN allSite.statusId = 'WIP' THEN 1.0 END) AS status
FROM (SELECT DISTINCT siteId, statusId
FROM dbo.site) allSite
GROUP BY allSite.siteId) tmp

Thats Amazing!!!!
It works.........Really thanks 4 timely help. Understood the query.

Njoy Life

swatib
Posting Yak Master

173 Posts

Posted - 2006-07-28 : 04:39:07

quote:

Originally posted by jen


declare @t table(Siteid int,Activityid varchar(2), Status varchar (20))

insert into @t(siteid,activityid,status)
select '101','A','WNS'
union select
'101','B','WNS'
union select
'101','C','Completed'

select distinct siteid,case when siteid in(select siteid from
(select siteid,status
from @t t
group by siteid,status
having count(*)=(select count(*) from @t where siteid=t.siteid)
)a) then status else 'WIP' end as status
from @t

--------------------
keeping it simple...

Excellent.......It also works fine..Thanks a lot

Njoy Life

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-28 : 06:00:34

How about this one?

select Siteid, case min(Status) when max(Status) then min(Status) else 'WIP' end as 'Status'
from dbo.site group by Siteid

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

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

swatib
Posting Yak Master

173 Posts

Posted - 2006-07-28 : 06:08:41

quote:
Originally posted by RyanRandall

How about this one?

select Siteid, case min(Status) when max(Status) then min(Status) else 'WIP' end as 'Status'
from dbo.site group by Siteid

ITs working but I'm not getting it fully.

Njoy Life

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-07-28 : 07:06:57

This is a VERY familiar question. Will have to search on it.

ah, here is why: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=68381

For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-28 : 09:56:38

quote:
Originally posted by DonAtWork

This is a VERY familiar question. Will have to search on it.

ah, here is why: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=68381

LOL - My own memory is obviously not so good

Still, at least I've improved on my own solution

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources