Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Dec 2003
    Posts
    22

    Unanswered: Select statement/Schema/Pain-In-The-@ss Problem

    I have a table that stores pass information for coils in a steel mill. For each coil stored, I have a field that has the number of passes made, and then fields for the width and gauge of steel for each pass. (ie, it has pass1gauge, pass1feet, pass2gauge, pass2feet, etc. up to a max of 9 passes ... table was in place and in heavy usage long before I got here, so don't blame me for the problems with the schema ...

    I need to, in SQL, calculate the weight of the coil (width*last pass gauge*(last pass feet*12)*.283). I know from the pass field how many passes were made, and in VB, I could bring down the recordset, loop through the recordset and calculate this by checking the pass field, but there has to be a way to do this in SQL.

    The gist of my problem is I need to, if its a 5 pass coil, do calculations with pass5gauge and pass5feet. Maybe I could do a join back on the same table? But ... well I'm not too experienced with SQL.

    Anyone see how to do this? I can give a crap-ton of more details if needed. I am an admin (the only admin, as a matter of fact) of this db server, so I can do anything needed (ie, stored procedures for testing, views, whatever). I'd like to be able to avoid stored procedures, though, if possible.

  2. #2
    Join Date
    Feb 2004
    Location
    Chester Springs, PA
    Posts
    16

    Last Pass Query

    select top 1 width*gauge*(feet*12)*.283
    where coil_id = @coil_id /*assuming you use this to find the coil*/
    order by pass_number desc

    SQL Server's 'top' select operator is my favorite thing about their T-SQL implementation.

    In Sybase or Oracle, you would declare a cursor with the select statement sans the 'top 1' and then just grab the first row out of the recordset.

    In this case, you only have values from 1 to 9, but if you are doing double digits and the data (for whatever reason) are stored in a char or varchar field, the order by desc must be done as
    ... order by (cast pass_number as int)

    or another numeric datatype to avoid binary sort issues of char data. I'm assuming that your pass number is in a numeric field, though. But it never hurts to cover all bases.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm not quite sure what your end-goal is. Are you trying to compare weights between passes to find shrinkage? Are you trying to find only the weight of the final pass to see what you can sell? Did I miss your point completely?

    It would help a bunch if you could also post the CREATE TABLE syntax for the table(s) you are discussing. You can get this using:

    1) open EM (SQL Server Enterprise Mangler)
    2) open the server of interest
    3) open the databases collection
    4) open the database of interest
    5) open the tables collection
    6) right click the table of interest
    7) select All tasks
    8) select generate SQL script
    9) click the Ok button

    -PatP

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: Select statement/Schema/Pain-In-The-@ss Problem

    Originally posted by SaxMan101
    I'd like to be able to avoid stored procedures, though, if possible.
    Why? Me, I'd isolate the Business logic...

    Anyway...


    When you say 5, does that mean you need to reference 5 sets of Columns?

    Whats in 6 through 9? Null?
    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.

  5. #5
    Join Date
    Feb 2004
    Location
    Chester Springs, PA
    Posts
    16

    Coil Manufacture - OT

    Steel bands are rolled into coils. This can be done under specific tensions and using rollers of specific types to achieve a desired thickness, width, hardness, and coil set.

    It is important to track each rolling to address quality problems and to correctly identify the coil's properties. Our friend here has a data set that identifies the pass number, thickness, and width of the coil at the end of each rolling run.

    To calculate weight, it's volume x density

    In my original post, I erred in thinking that the data are normalized, and they're not (it has pass1gauge, pass1feet, pass2gauge, pass2feet, etc. up to a max of 9 passes), so my previous post is bogus.

    The kluge I'd do here is to create a view that selects the coil_id, passNgauge, passNfeet UNIONed nine times with the resultant view looking something like...

    coil_id
    pass_number
    gauge
    feet

    The create statement would look like..

    create view CoilVW as
    select coil_id, '1' as pass_number,
    pass1gauge as gauge, pass1feet as feet
    UNION
    select coil_id, '2', pass2gauge, pass2feet.

    etc.

    Then my previous suggestion would work(select top 1, order by desc), substituing the view for the table and adding 'where gauge is not null'. It would be wise to index (or clustered index) the source table by coil_id so that when you select from the view 'where coil_id = ' it won't do a table scan.

    My 2 cents.

  6. #6
    Join Date
    Dec 2003
    Posts
    22
    Attached is the sql for the table.

    I'd like to keep it as a view or basic SQL so I can do selects on different columns later. Maybe you can do that already, but as far as I know, you execute a stored procedure with parameters (ie, values to be used in the where clause for SPs I've done before) and get a recordset back. Ie. you simply execute and it returns whatever it returns w/o a way to subset the results (other than doing checks in code as you loop through the returned recordset). Correct me if I'm wrong, though.

    If its a 5 pass coil, then passgauge1 and passfeet1 through passgauge5 and passgauge5 have values, the rest are null (ie, passgauge6-9 and passfeet6-9 are null). I'm trying to calculate weight in SQL so I can do an aggregate sum (again, in SQL) for a given date range. The end result is going to be yield (on gauge vs. total weight), but the pre-process weight comes from somewhere else. It has to be last pass because that's how much on gauge steel the reversing mill is sending to the next process.

    To be clear, all I care about for this purpose is the last pass, which is designated by the passes column (integer 1-9). This tells me which columns my values should come from (passgauge[1-9] and passfeet[1-9]).

    If I'm still not making sense, let me know.
    Attached Files Attached Files

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How about

    Code:
    
    CREATE VIEW [dbo].[v_RMProduction]
    AS
    SELECT 	[IndexID]
    	, SUM(
    		  [PassFeet1]
    		+ [PassFeet2]
    		+ [PassFeet3]
    		+ [PassFeet4]
    		+ [PassFeet5]
    		+ [PassFeet6]
    		+ [PassFeet7]
    		+ [PassFeet8]
    		+ [PassFeet9]
    	    ) AS PassFeet
    	, SUM (
    		  [PassGauge1]
    		+ [PassGauge2]
    		+ [PassGauge3]
    		+ [PassGauge4]
    		+ [PassGauge5]
    		+ [PassGauge6]
    		+ [PassGauge7]
    		+ [PassGauge8]
    		+ [PassGauge9]
    	    ) AS PassGauge
      FROM RMProduction
    GROUP BY [IndexID]
    GO
    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.

  8. #8
    Join Date
    Dec 2003
    Posts
    22
    No because for a 3 pass coil (as an example), the pass 1 and 2 fields will have values. Doing a sum would work, if we didn't record values for previous passes (ie, they were null), but we do, so doing a sum will throw everything off.

    I'm thinking I'm going to have to just loop through the recordset and add them that way.

    What I'm going for is for each record, look at the pass field. If the pass field is 1, use passgauge1 and passfeet1 to calculate weight. if the pass field is 2, use passgauge2 and passfeet2 to calculate weight. And so on until 9. Don't know if this is possible in SQL, though.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by SaxMan101
    Attached is the sql for the table.

    I'd like to keep it as a view or basic SQL so I can do selects on different columns later. Maybe you can do that already, but as far as I know, you execute a stored procedure with parameters (ie, values to be used in the where clause for SPs I've done before) and get a recordset back. Ie. you simply execute and it returns whatever it returns w/o a way to subset the results (other than doing checks in code as you loop through the returned recordset). Correct me if I'm wrong, though.

    If its a 5 pass coil, then passgauge1 and passfeet1 through passgauge5 and passgauge5 have values, the rest are null (ie, passgauge6-9 and passfeet6-9 are null). I'm trying to calculate weight in SQL so I can do an aggregate sum (again, in SQL) for a given date range. The end result is going to be yield (on gauge vs. total weight), but the pre-process weight comes from somewhere else. It has to be last pass because that's how much on gauge steel the reversing mill is sending to the next process.

    To be clear, all I care about for this purpose is the last pass, which is designated by the passes column (integer 1-9). This tells me which columns my values should come from (passgauge[1-9] and passfeet[1-9]).

    If I'm still not making sense, let me know.
    Based on the DDL file you posted, those columns can't be NULL. Do you mean that they are zero, or something different?

    More important, how do you tell which Pass values you should use?

    -PatP

  10. #10
    Join Date
    Dec 2003
    Posts
    22
    Yeah. Zero's what I meant. Sorry about that.

    As far as which pass, to use, there's a Passes field that has how many passes were made for that coil. (1-9)

    The point of the passes is the reversing mill gets a coil from the pickle line at say .105. The ordered gauge is maybe .040. They have to make several passes (run the coil through the mill several times) to get the steel down to the ordered gauge.

    The table schema is whacked. I saw that as soon as I started working here, but there's already about 3 years of coils in there and several applications pulling data from it. Nothing really to be done about it, no matter how much I wish it could be.

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    True...assuming the columns default to 0

    How about

    Code:
    CREATE VIEW [dbo].[v_RMProduction]
    AS
    SELECT 	[IndexID]
    	, CASE  WHEN PassFeet9 <> 0 THEN PassFeet9
    		WHEN PassFeet8 <> 0 THEN PassFeet8
    		WHEN PassFeet7 <> 0 THEN PassFeet7
    		WHEN PassFeet6 <> 0 THEN PassFeet6
    		WHEN PassFeet5 <> 0 THEN PassFeet5
    		WHEN PassFeet4 <> 0 THEN PassFeet4
    		WHEN PassFeet3 <> 0 THEN PassFeet3
    		WHEN PassFeet2 <> 0 THEN PassFeet2
    		WHEN PassFeet1 <> 0 THEN PassFeet1
    				    ELSE 0
    	  END AS PassFeet
    	, CASE  WHEN PassGauge9 <> 0 THEN PassGauge9
    		WHEN PassGauge8 <> 0 THEN PassGauge8
    		WHEN PassGauge7 <> 0 THEN PassGauge7
    		WHEN PassGauge6 <> 0 THEN PassGauge6
    		WHEN PassGauge5 <> 0 THEN PassGauge5
    		WHEN PassGauge4 <> 0 THEN PassGauge4
    		WHEN PassGauge3 <> 0 THEN PassGauge3
    		WHEN PassGauge2 <> 0 THEN PassGauge2
    		WHEN PassGauge1 <> 0 THEN PassGauge1
    				     ELSE 0
    	  END AS PassGauge
      FROM RMProduction
    GO
    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.

  12. #12
    Join Date
    Dec 2003
    Posts
    22
    That may just work. I'll try it out in a few minutes and let you know. Thanks a ton.

  13. #13
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    PHP Code:
    select [IndexID], [Coil Weight] = (
        [
    Width] *
        
    coalesce(
            
    nullif(0, [PassGauge9]), 
            
    nullif(0, [PassGauge8]), 
            
    nullif(0, [PassGauge7]), 
            
    nullif(0, [PassGauge6]), 
            
    nullif(0, [PassGauge5]), 
            
    nullif(0, [PassGauge4]), 
            
    nullif(0, [PassGauge3]), 
            
    nullif(0, [PassGauge2]), 
            
    nullif(0, [PassGauge1])
            ) * (
            
    coalesce(
                
    nullif(0, [PassFeet1]),
                
    nullif(0, [PassFeet2]),
                
    nullif(0, [PassFeet3]),
                
    nullif(0, [PassFeet4]),
                
    nullif(0, [PassFeet5]),
                
    nullif(0, [PassFeet6]),
                
    nullif(0, [PassFeet7]),
                
    nullif(0, [PassFeet8]),
                
    nullif(0, [PassFeet9])
                ) * 
    12 ) * 0.283 )
        
    from [dbo].[RMProduction]
        
    where .... 
    Wow, I like this PHP...

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Uff-da, rdjabarov. That is pure evil incarnate!

    Based on
    As far as which pass, to use, there's a Passes field that has how many passes were made for that coil. (1-9)
    I'd like to suggest using:
    PHP Code:
    CREATE VIEW [dbo].[v_RMProduction2]
    AS
    SELECT [IndexID]
    ,  CASE
          
    WHEN 1 Passes THEN PassFeet1
          WHEN 2 
    Passes THEN PassFeet2
          WHEN 3 
    Passes THEN PassFeet3
          WHEN 4 
    Passes THEN PassFeet4
          WHEN 5 
    Passes THEN PassFeet5
          WHEN 6 
    Passes THEN PassFeet6
          WHEN 7 
    Passes THEN PassFeet7
          WHEN 8 
    Passes THEN PassFeet8
          WHEN 9 
    Passes THEN PassFeet9
          
    ELSE 0
    ,  CASE
          
    WHEN 1 Passes THEN PassGauge1
          WHEN 2 
    Passes THEN PassGauge2
          WHEN 3 
    Passes THEN PassGauge3
          WHEN 4 
    Passes THEN PassGauge4
          WHEN 5 
    Passes THEN PassGauge5
          WHEN 6 
    Passes THEN PassGauge6
          WHEN 7 
    Passes THEN PassGauge7
          WHEN 8 
    Passes THEN PassGauge8
          WHEN 9 
    Passes THEN PassGauge9
          
    ELSE 0
       END 
    AS PassGauge
      FROM RMProduction 
    -PatP

  15. #15
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Oh come on, Pat! And then you're gonna do the calculation based on the view? Why bother, look up, it's already calculated!

Posting Permissions

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