Calculate Done and Not Done - 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)

Calculate Done and Not Done

Author

Topic

edx
Starting Member

2 Posts

Posted - 2006-05-14 : 23:36:46

Hello all,

i'm a newbie in sql, and currently i am facing some problems in sql. My questions is as shown:

I have a table with about 30,000 records, the sample columns of this table is as following:

------------------------------------------------------------------
CPN | MPN | Status | Requester | Project_Name | ReceivedDate
------------------------------------------------------------------
CN8 CNA1 DONE John SUN 2006/03/01
CN8 CNA2 NOT_DONE John SUN 2006/03/01
BF7 GHE1 DONE Alex MICRO 2006/04/01
BF8 GHE2 DONE Alex MICRO 2006/04/01
BF9 GHE3 NOT_DONE Alex MICRO 2006/04/01
BF0 GHE4 NOT_DONE Alex MICRO 2006/04/01

How can i make it to become:
-------------------------------------------------------------------
Project_Name | Requester | ReceivedDate | Total_DONE(%) |Total_CPN(%)| DONE | NOT_DONE | Total |

SUN | John | 2006/03/01 | 50% | 100% | 1 | 1 | 2
MICRO| Alex | 2006/04/01 | 50% | 50% | 2 | 2 | 4

Total_DONE(%) is base on how many % of MPN done
Total_CPN(%) is base on how many % of CPN done

For example, in John case, 'CNA1' is done while 'CNA2' is not done, so Total_DONE(%) = 50%, but since both MPN is under same CPN (CN8), so once one of the MPN under this CPN is done, it considered done for Total_CPN(%), so Total_CPN(%) = 100%

Any unclear and doubt please ask me.
Your advices and help is much appreciated, thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-15 : 02:14:26

Don't quite under stand the Total_CPN(%). Anyway you should be able to work it out with the code below

declare @table table
(
	CPN		varchar(10),
	MPN		varchar(10),
	Status		varchar(10),
	Requester	varchar(10),
	Project_Name	varchar(10),
	ReceivedDate	datetime
)
insert into @table
select 'CN8', 'CNA1', 'DONE', 		'John', 'SUN',   '2006/03/01' union all
select 'CN8', 'CNA2', 'NOT_DONE', 	'John', 'SUN',   '2006/03/01' union all
select 'BF7', 'GHE1', 'DONE', 		'Alex', 'MICRO', '2006/04/01' union all
select 'BF8', 'GHE2', 'DONE', 		'Alex', 'MICRO', '2006/04/01' union all
select 'BF9', 'GHE3', 'NOT_DONE', 	'Alex', 'MICRO', '2006/04/01' union all
select 'BF0', 'GHE4', 'NOT_DONE', 	'Alex', 'MICRO', '2006/04/01'

select	Project_Name, Requester, ReceivedDate, 
	count(case when Status = 'DONE' then 1 end) * 100 / count(*) as [Total_DONE(%)],
	count(case when Status = 'DONE' then 1 end) as Total_DONE,
	count(case when Status = 'NOT_DONE' then 1 end) as Total_NOT_DONE,
	count(*) as Total
from	@table
group by Requester, Project_Name, ReceivedDate

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-15 : 04:05:37

Also read about Cross-tab Reports in sql server help file and
http://weblogs.sqlteam.com/jeffs/archive/2005/05.aspx

Madhivanan

Failing to plan is Planning to fail

edx
Starting Member

2 Posts

Posted - 2006-05-15 : 21:28:47

Thanks madhivanan for the reply, but i have some unclear part for the article, can u please explain further for the cross tab method? thanks, and, could you send me some example?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-17 : 06:17:30

Read the link I suggested

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources