Calculate percent after retrieving column total

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)

Calculate percent after retrieving column total

Author

Topic

sherrireid
Yak Posting Veteran

58 Posts

Posted - 2009-05-04 : 16:04:10

Hi.

I need to get 3 sets of data from the same table.

1) How many Invoices we received in a month.
2) How many of those Invoices we had to touch more than once.
3) The percentage of Invoices we had to touch.

1) select distinct count(*)

from INV

where
INV.STAT in (1, 2, 10, 11)
and INV_DATE >= '04/01/05'
and INV_DATE <= '04/03/05'

2) select distinct count(*)

from INV

where
STAT in (1, 2, 10, 11)
and INV_DATE >= '04/01/05'
and INV_DATE <= '04/03/05' and
REC_CREATE_DATE <> REC_UPDATE_DATE

3) This is where I am at a loss. I don't know how to get the code from #1 and #2 to play together to get #3.

Expected results:

# of Invoices | # of Changed | Invoices Percentage
-----------------------------------------------
7900 395 5%

Thanks for any help you can give me.

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 2009-05-06 : 15:48:14

Can you store 2 results in variables and then calculate from that? Something like this:

DECLARE @R1 FLOAT
DECLARE @R2 FLOAT

select @R1 = distinct count(*)
from INV
where
INV.STAT in (1, 2, 10, 11)
and INV_DATE >= '04/01/05'
and INV_DATE <= '04/03/05'

select @R2 = distinct count(*)
from INV
where
STAT in (1, 2, 10, 11)
and INV_DATE >= '04/01/05'
and INV_DATE <= '04/03/05' and
REC_CREATE_DATE <> REC_UPDATE_DATE

SELECT @R1, @R2, @R2/@R1

sherrireid
Yak Posting Veteran

58 Posts

Posted - 2009-05-18 : 16:24:34

Great -- thank you. This got me going in the right direction. I appreciate the help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources