Results 1 to 10 of 10

Thread: Help with Query

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

    Unanswered: Help with Query

    Hello and Thanks in advance,

    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. #2
    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)
    SELECT yada, yada, yada UNION ALL
    SELECT yada, yada, yada UNION ALL
    SELECT yada, yada, yada

    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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    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
    {
    thaDate smalldatetime
    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. #4
    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, 
    	(SELECT NULLIF(SUM(lbs), 0) FROM tblScrap WHERE thaDate = TS.thaDate) SumScrapLbs, 
    	SUM(lbs) / (SELECT NULLIF(SUM(lbs), 0) WHERE thaDate = TS.thaDate) Percentage
    FROM tblScrap TS 
    GROUP BY thaDate, Category
    Hope this helps.

    Cheers,
    Robert

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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. #6
    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. #7
    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. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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. #9
    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. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Looks good to me.

    Yes, you can add more subqueries to do additional calculations. It is generally more efficient to run your process as a single query, but if the query gets too confusing then consider breaking it up into separate statements that load temporary tables or table variables with summarized data. Then finish with a query that links these temporary tables to get the answer you need.

    blindman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •