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

Transact-SQL (2000)

Need proper query

Author

Topic

swatib
Posting Yak Master

173 Posts

Posted - 2006-06-27 : 05:51:01

I have a table called SiteActivity

SiteId:ActivityId:Status
A:101:'Completed'
A:102:'WIP'
A:103:'WIP'
B:101:'WIP'

I need a query where I'll get the overall status of the site
e.g. for site 'A' if all the activities status is 'Completed' then and then the overall site status is 'Completed'
How to write such a query

please help ..its urgent

Njoy Life

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-27 : 05:55:12

[code]-- Prepare test data
declare @SiteActivity table (SiteID char, ActivityID tinyint, Status varchar(9))

insert @SiteActivity
select 'A', 101, 'Completed' union all
select 'A', 102, 'WIP' union all
select 'A', 103, 'WIP' union all
select 'B', 101, 'WIP'

-- Do the work
SELECT SiteID,
MAX(Status)
FROM @SiteActivity
GROUP BY SiteID
HAVING COUNT(DISTINCT Status) = COUNT(Status)[/code]
Peter Larsson
Helsingborg, Sweden

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-27 : 06:02:05

>> HAVING COUNT(DISTINCT Status) = COUNT(SiteID)
That'll miss any sites with duplicate statuses for tasks
maybe
SELECT SiteID,
case when MAX(case when Status = 'Completed' then 0 else 1 end) = 0 then 'completed' else 'not completed' end
FROM @SiteActivity
GROUP BY SiteID

==========================================
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-06-27 : 06:02:23

This is another way to solve your issue.

-- Prepare test data
declare @SiteActivity table (SiteID char, ActivityID tinyint, Status varchar(9))

insert @SiteActivity
select 'A', 101, 'Completed' union all
select 'A', 102, 'WIP' union all
select 'A', 103, 'WIP' union all
select 'B', 101, 'WIP'

-- Do the work
SELECT		z.SiteID,
		CASE WHEN z.Completed = z.Total THEN 'Completed' ELSE 'Not completed' END OverallStatus
FROM		(
			SELECT		SiteID,
					SUM(CASE WHEN Status = 'Completed' THEN 1 ELSE 0 END) Completed,
					COUNT(Status) Total
			FROM		@SiteActivity
			GROUP BY	SiteID
		) z
ORDER BY	z.SiteID

Peter Larsson
Helsingborg, Sweden

swatib
Posting Yak Master

173 Posts

Posted - 2006-06-27 : 06:03:00

I'm not getting your reply.

Also I have 3 status 'Completed', 'WNS', 'WIP'
IF all the activities for a site have status 'Completed' then overall site status is 'completed'
same as 'WNS' and with 'WIP'.
else the site status is 'WIP'

Njoy Life

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-27 : 06:04:57

SELECT SiteID,
case MAX(case when Status = 'Completed' then 0 when Status = 'WNS' then 1 else 2 end) when 0 then 'completed' when 1 then 'WNS' else 'WIP' end
FROM @SiteActivity
GROUP BY SiteID

==========================================
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-06-27 : 06:08:17

NR

this query is very near to answer. plz modify the same according to my need

Njoy Life

swatib
Posting Yak Master

173 Posts

Posted - 2006-06-27 : 06:11:34

Thanks NR I did modify it

Njoy Life

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-27 : 06:12:44

quote:
Originally posted by nr

SELECT SiteID,
case MAX(case when Status = 'Completed' then 0 when Status = 'WNS' then 1 else 2 end) when 0 then 'completed' when 1 then 'WNS' else 'WIP' end
FROM @SiteActivity
GROUP BY SiteID

Nice!

But does not work for

insert @SiteActivity
select 'A', 101, 'Completed' union all
select 'A', 102, 'WNS' union all
select 'A', 103, 'WNS' union all
select 'B', 103, 'Completed' union all
select 'B', 102, 'Completed' union all
select 'B', 101, 'Completed'

Peter Larsson
Helsingborg, Sweden

swatib
Posting Yak Master

173 Posts

Posted - 2006-06-27 : 06:12:46

thanks all of you for really quick replies.

Njoy Life

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-27 : 06:21:55

[code]SELECT z.SiteID,
CASE WHEN z.mi = z.ma THEN CASE WHEN z.mi = 0 THEN 'Completed' WHEN z.mi = 1 THEN 'WNS' WHEN z.mi = 2 THEN 'WIP' END ELSE 'WIP' END Status
FROM (
SELECT SiteID,
MIN(CASE
WHEN Status = 'Completed' THEN 0
WHEN Status = 'WNS' THEN 1
ELSE 2
END) mi,
MAX(CASE
WHEN Status = 'Completed' THEN 0
WHEN Status = 'WNS' THEN 1
ELSE 2
END) ma
FROM @SiteActivity
GROUP BY SiteID
) z[/code]
Peter Larsson
Helsingborg, Sweden

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-27 : 06:29:14

quote:
Originally posted by Peso

quote:
Originally posted by nr

SELECT SiteID,
case MAX(case when Status = 'Completed' then 0 when Status = 'WNS' then 1 else 2 end) when 0 then 'completed' when 1 then 'WNS' else 'WIP' end
FROM @SiteActivity
GROUP BY SiteID
Nice!

But does not work for
insert @SiteActivity
select 'A', 101, 'Completed' union all
select 'A', 102, 'WNS' union all
select 'A', 103, 'WNS' union all
select 'B', 103, 'Completed' union all
select 'B', 102, 'Completed' union all
select 'B', 101, 'Completed'

Peter Larsson
Helsingborg, Sweden

???
Should give A, WNS; B, Completed which I think is what is wanted.

Maybe not - from your code you interpret it as if there are any mixed statuses then it's WIP. I interpret it as take the lowest in order of completed, WNS, WIP.
I think you have what was requested but I suspect what I have is more like what is wanted.

==========================================
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-06-27 : 06:41:50

Without looking at the alternatives too much, here's what I came up with. It seems to have the same execution plan as Peso's most recent reply.

--data
declare @SiteActivity table (SiteID char, ActivityID tinyint, Status varchar(9))
insert @SiteActivity
          select 'A', 101, 'Completed'
union all select 'A', 102, 'WIP'
union all select 'A', 103, 'WIP'
union all select 'B', 101, 'WIP'
union all select 'C', 103, 'Completed'
union all select 'C', 102, 'Completed'
union all select 'C', 101, 'Completed'
union all select 'D', 101, 'WNS'
union all select 'E', 101, 'Completed'
union all select 'E', 102, 'WNS'
union all select 'E', 103, 'WNS'

--calculation
select
    SiteID,
    case when sum = count then 'Completed'
         when sum = -count then 'WNS'
         else 'WIP'
    end as Status
from (
    select SiteID, count(*) as count,
        sum(case Status when 'Completed' then 1 when 'WNS' then -1 else 0 end) as sum
    from @SiteActivity
    group by SiteID) a

/*results
SiteID Status    
------ --------- 
A      WIP
B      WIP
C      Completed
D      WNS
E      WIP
*/

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

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-27 : 06:42:25

Perhaps you're right, nr. I have read

quote:
If all the activities for a site have status 'Completed' then overall site status is 'completed'
same as 'WNS' and with 'WIP'.
else the site status is 'WIP'

over and over again, and I put my emphasis on else the site status is 'WIP'.

Peter Larsson
Helsingborg, Sweden

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-27 : 06:46:23

quote:
I think you have what was requested but I suspect what I have is more like what is wanted.

LOL. I think you're probably right, Nigel!

Oh, and - in case you were wondering - the first SQL Team mind-reading seminar is next Thursday.

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

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-27 : 06:50:04

quote:
I think you have what was requested but I suspect what I have is more like what is wanted.

Ahhhrghhh!! When is the first SQL Team mind-reading seminar!?

Well, I think we will se more of swatib soon, anyway.

Peter Larsson
Helsingborg, Sweden

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-27 : 06:53:08

It's dangerous making asumption about a question but sometimes equally dangerous reading the question too carefully.

==========================================
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-06-27 : 07:16:31

quote:
Originally posted by RyanRandall

Oh, and - in case you were wondering - the first SQL Team mind-reading seminar is next Thursday.

See!? If I had taken the seminar last year, I would have known that already!

Peter Larsson
Helsingborg, Sweden

swatib
Posting Yak Master

173 Posts

Posted - 2006-06-27 : 07:58:04

quote:

Originally posted by RyanRandall

Without looking at the alternatives too much, here's what I came up with. It seems to have the same execution plan as Peso's most recent reply.

--data
declare @SiteActivity table (SiteID char, ActivityID tinyint, Status varchar(9))
insert @SiteActivity
          select 'A', 101, 'Completed'
union all select 'A', 102, 'WIP'
union all select 'A', 103, 'WIP'
union all select 'B', 101, 'WIP'
union all select 'C', 103, 'Completed'
union all select 'C', 102, 'Completed'
union all select 'C', 101, 'Completed'
union all select 'D', 101, 'WNS'
union all select 'E', 101, 'Completed'
union all select 'E', 102, 'WNS'
union all select 'E', 103, 'WNS'

--calculation
select
    SiteID,
    case when sum = count then 'Completed'
         when sum = -count then 'WNS'
         else 'WIP'
    end as Status
from (
    select SiteID, count(*) as count,
        sum(case Status when 'Completed' then 1 when 'WNS' then -1 else 0 end) as sum
    from @SiteActivity
    group by SiteID) a

/*results
SiteID Status    
------ --------- 
A      WIP
B      WIP
C      Completed
D      WNS
E      WIP
*/

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

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

Yes, this is exact result I wanted.

Njoy Life

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-27 : 08:13:30

If the status is completed, WNS, WNS for a site do you want the result to be WNS or WIP?
if WNS then my previous query
SELECT SiteID,
case MAX(case when Status = 'Completed' then 0 when Status = 'WNS' then 1 else 2 end) when 0 then 'completed' when 1 then 'WNS' else 'WIP' end
FROM @SiteActivity
GROUP BY SiteID

if WIP then
SELECT SiteID,
case when max(status) = min(status) then max(Status) else 'WIP' end
FROM @SiteActivity
GROUP BY SiteID

==========================================
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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources