Using SQL with percents - 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)

Using SQL with percents

Author

Topic

mariocr
Starting Member

1 Post

Posted - 2002-11-20 : 15:51:08

I need to calculate a percent of quality in a factory. I have rows with date, deparment code, quality and tons. Usually i have 3 rows that match with date and departament code.

I need these datas

I need to make the add of all the rows with estado = p.
Make the add of all the rows that match with date and department code

Finally divide the first add and the second add. Do this for every day. I can make the selects separately but i need one select to make all the operation.

My code is this:
select sum(toneladas) from produccion where cdepto = '008' and estado = 'p'

select sum(toneladas) from produccion where cdepto = '008'

Thank you every one.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-11-20 : 17:11:49

If you want to filter on date add to your where clause

and date = @YourDateVariable

If you want to group by date add a group by clause

It sounds to me that you want to group by date

So here goes:

SELECT
A.cdepto,
A.estado,
A.dateonly,
CASE SECONDADD
WHEN 0 THEN 0
ELSE CAST(FIRSTADD AS REAL)/CAST(SECONDADD AS REAL)
END As PercentageX
FROM
(
SELECT
cdepto, estado,DATEADD(d, DATEDIFF(d, 0, [datefield]), 0) As dateonly, SUM(toneladas) As FIRSTADD
FROM Produccion
WHERE cdepto = '008' AND estado='p'
GROUP BY cdepto, estado, DATEADD(d, DATEDIFF(d, 0, [datefield]), 0)) As A
INNER JOIN
(
SELECT
cdepto, DATEADD(d, DATEDIFF(d, 0, [datefield]), 0) As dateonly, SUM(toneladas) As SECONDADD
FROM Produccion
WHERE cdepto='008'
GROUP BY cdepto, DATEADD(d, DATEDIFF(d, 0, [datefield]), 0)
) As B ON A.cdepto = B.cdepto AND A.dateonly = B.dateonly

Method2:

SELECT
AA.cdepto,
AA.estado,
AA.dateonly,
CASE AA.SECONDADD
WHEN 0 THEN 0.00
ELSE
CAST(AA.FIRSTADD AS REAL) / CAST(AA.SECONDADD AS REAL)
END
FROM
(
SELECT
B.cdepto,
B.estado,
DATEADD(d, DATEDIFF(d, 0, B.[datefield]), 0) As dateonly,
SUM(B.TONELADAS)AS FIRSTADD,
(SELECT SUM(A.TONELADAS)
FROM Produccion A
WHERE A.cdepto = B.cdepto AND DATEADD(d, DATEDIFF(d, 0, A.[datefield]), 0) = DATEADD(d, DATEDIFF(d, 0, B.[datefield]), 0)
) AS SECONDADD
FROM Produccion B
WHERE B.cdepto = '08' AND B.estado='p'
GROUP BY B.cdepto, B.estado, DATEADD(d, DATEDIFF(d, 0, B.[datefield]), 0)
) AS AA

I'm sure someone might be able to simply this to one query.

The second method will perform better than first and even better if you have a clustered index on cdepto, estado, datefield

Edited by - ValterBorges on 11/20/2002 19:27:56

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources