1. Registered User
Join Date
Oct 2002
Location
Western part of Georgia, USA
Posts
123

I am trying to get the percentage that each row contributes to the total rows for a given time frame on a given line (Select ScrapCat, ScrapLbs/Sum(ScrapLbs .... Group by Category, LineNum) as percentage). We have 11 categories for each line for each day. The percentage for each category would be the sum of all rows for that category for that line that time frame divided the sum of all rows for all categories for that line and time frame.

A return would look like this
Category........ProLine.........ScrapLbs......Sum( ScrapLbs)...Percentage...
CateA------------1---------------.25------------2.0---------------.125---
CateB------------1---------------.35------------2.0---------------.175---
CateC------------1---------------.5-------------2.0---------------.25---
etc
CateA------------2---------------.25------------1.0---------------.25--
CateB------------2---------------.50------------1.0---------------.5---
etc
Table looks like this

ProDate ..................smalldatetime
ProLine ...................int
Category.................char
ScrapLbs ................float
ProShift ..................int

is this possible?
Thanks,
Lee

2. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
Can you post the DDL and some sample base data

Like CREATE TABLE myTable99 (Col1 int, ..ect

And sample data that would put the data in to the table, like

INSERT INTO myTable99 (Col1, col2, ect)

That way we can execute the code, set up a test bed and figure it out...

but this kinda throws me right away..

ScrapLbs......Sum(ScrapLbs)...
How can you have the sum of something, and 1 occurance of something on the same row?

3. Registered User
Join Date
Oct 2002
Location
Western part of Georgia, USA
Posts
123

## Sorry for the delay

Sorry my boss shifted my focus!

Hopefully this is what you need.

What I need to do is sum all the scrap for each line for the date range (Sum( lbs) as LineTotal group by line then sum(Category) as EachCategory group by line and Category then divide EachCategory by LineTotal

EachCategory/LineTotal = EachCategory is what percent of Total Line Scrap

Create Table tblScrap
{
Category varchar 15
lbs float
LineNum int
Shift int
}
Insert tblScrap Values ( ' 10/29/2003',PM , 0, 1 ,1)
Insert tblScrap Values ( ' 10/29/2004',DA , 0.66, 1 ,1)
Insert tblScrap Values ( ' 10/29/2005',DT , 0.5, 1 ,1)
Insert tblScrap Values ( ' 10/29/2006',Short , 0, 1 ,1)
Insert tblScrap Values ( ' 10/29/2007',Longs , 3.4, 1 ,1)
Insert tblScrap Values ( ' 10/29/2008',Bent , 1.48, 1 ,1)
Insert tblScrap Values ( ' 10/29/2009',NTA , 4.44, 1 ,1)
Insert tblScrap Values ( ' 10/29/2010',PIP , 0, 1 ,1)
Insert tblScrap Values ( ' 10/29/2011',Caps , 2.36, 1 ,1)
Insert tblScrap Values ( ' 10/29/2012',Paper , 5.26, 1 ,1)
Insert tblScrap Values ( ' 10/29/2013',NAPS , 0.66, 1 ,1)
Insert tblScrap Values ( ' 10/28/2003',PM , 0, 1 ,1)
Insert tblScrap Values ( ' 10/28/2004',DA , 0, 1 ,1)
Insert tblScrap Values ( ' 10/28/2005',DT , 0, 1 ,1)
Insert tblScrap Values ( ' 10/28/2006',Short , 0, 1 ,1)
Insert tblScrap Values ( ' 10/28/2007',Longs , 0, 1 ,1)
Insert tblScrap Values ( ' 10/28/2008',Bent , 0, 1 ,1)
Insert tblScrap Values ( ' 10/28/2009',NTA , 0, 1 ,1)
Insert tblScrap Values ( ' 10/28/2010',PIP , 0, 1 ,1)
Insert tblScrap Values ( ' 10/28/2011',Caps , 0, 1 ,1)
Insert tblScrap Values ( ' 10/28/2012',Paper , 0, 1 ,1)
Insert tblScrap Values ( ' 10/28/2013',NAPS , 0, 1 ,1)
Insert tblScrap Values ( ' 10/27/2003',PM , 0, 1 ,1)
Insert tblScrap Values ( ' 10/27/2004',DA , 0.44, 1 ,1)
Insert tblScrap Values ( ' 10/27/2005',DT , 0.44, 1 ,1)
Insert tblScrap Values ( ' 10/27/2006',Short , 0, 1 ,1)
Insert tblScrap Values ( ' 10/27/2007',Longs , 7.16, 1 ,1)
Insert tblScrap Values ( ' 10/27/2008',Bent , 1.84, 1 ,1)
Insert tblScrap Values ( ' 10/27/2009',NTA , 2.24, 1 ,1)
Insert tblScrap Values ( ' 10/27/2010',PIP , 0, 1 ,1)
Insert tblScrap Values ( ' 10/27/2011',Caps , 3.92, 1 ,1)
Insert tblScrap Values ( ' 10/27/2012',Paper , 7.86, 1 ,1)
Insert tblScrap Values ( ' 10/27/2013',NAPS , 1.76, 1 ,1)

4. Registered User
Join Date
Oct 2003
Location
Sweden
Posts
45
I'm not sure I fully understood what you are looking for.
This query groups by date and category with the percentage for each category in relation to the total of the day.

Code:
```SELECT	thaDate,
Category,
SUM(lbs) ScrapLbs,
FROM tblScrap TS
Hope this helps.

Cheers,
Robert

5. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
clinel,

Still not sure what you mean. sum(Category)? Category is a character field. Also, what date ranges?
This should get you started:

select LineCatTotals.LineNum, LineCatTotals.Category, LineCatTotals.LineCatlbs/LineTotals.Linelbs LineCatPercent
from (select LineNum, sum(lbs) Linelbs from tblScrap group by LineNum) LineTotals
inner join (select LineNum, Category, sum(lbs) LineCatlbs from tblScrap group by LineNum, Category) LineCatTotals
on LineTotals.LineNum = LineCatTotals.LineNum

Add groupings by date or daterange if you want them.

blindman

6. Registered User
Join Date
Oct 2002
Location
Western part of Georgia, USA
Posts
123
I'm sorry,
Category is the label for each category of scrap. I want to sum the lbs of scrap or each category would be a better term. Then I want to sum all lbs of scrap by line to get a line total and then divide the category total (for that line) by the line total to get the percent that each category contributes to the line total. Whew!

As far as date range goes, I will give the user the ability to give a beginning and ending date and I want to find the percentage for that date range.

My bad on the sum of category; I see now how I took a confusing thing and made it even more so.

Thanks for both the patience and help,
Lee
Last edited by clinel; 11-03-03 at 18:16.

7. Registered User
Join Date
Oct 2002
Location
Western part of Georgia, USA
Posts
123
Thanks to all,
It appears that what Blindman had sent me is what I needed. I was actually able to figure out where to set the critera for the date range. Now I just need to figure out what is going on because I have several reports that I think that this type query will fit the need.

Thanks again,
Lee

8. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
If you'd like, post your final query and we can make sure you implemented the date-range criteria in the most efficient manner.

blindman

9. Registered User
Join Date
Oct 2002
Location
Western part of Georgia, USA
Posts
123
As I am new to this and have had no formal and very little time to read very much, this is how I handled what you gave me.

I created a stored procedure (so I could set the critera for date range easily) and a I am allowing the user to set the begin and end and call it from an asp.

Here is how I handled the date range.

@begdate smalldatetime, @enddate smalldatetime
AS

select LineCatTotals.ProLine,
LineCatTotals.ScrapCat,
LineCatTotals.LineCatlbs/LineTotals.Linelbs LineCatPercent
from (select ProLine, sum(Scraplbs) Linelbs from clinel.otbl_SAAA_d_HSMainScrap WHERE ProDateTime BETWEEN @begdate AND @enddate group by ProLine) LineTotals
inner join (select ProLine, ScrapCat, sum(Scraplbs) LineCatlbs from clinel.otbl_SAAA_d_HSMainScrap WHERE ProDateTime BETWEEN @begdate AND @enddate group by ProLine, ScrapCat) LineCatTotals
on LineTotals.ProLine = LineCatTotals.ProLine Order by LineCatTotals.ProLine
GO

Now that you are looking over this, is it possible to select a total from another table and divide the Line total (scraplbs) by the production total from another table (Select Sum(Production) From tblProduction Where EntryDate Between @begdate AND @enddate) LineCatTotals.LineCatlbs/Sum(Production) ? Both tables could be linked on ProLine.

10. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595