Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2002
    Western part of Georgia, USA

    Unanswered: Sp or something else?

    Could someone help me understand how to create a sp or would some other method be better?
    Thanks in advance for your help!!

    How is the the best way to accomplish the following? Mind you I do not understand how to loop in SQL (I could do this in vbScript, but I thought it better to process it via SQL.

    SELECT MAX(RemoveDate) AS RemoveDate, LineNum AS Line
    FROM Hata_Meter_Records
    WHERE (SetPosition = 'T1- 3.5') AND (EventType = 'Die') GROUP BY LineNum

    While theAboveRS is Not EOF

    SELECT LineNum, Sum(TotPiece) as Totals
    FROM Hata_Meter_Records
    WHERE (LineNum = @Line) AND (RemoveDate >= @RemoveDate) AND EventType = 'Die' GROUP BY LineNum
    ORDER BY LineNum

    Output LineNum, @RemoveDate, Totals

    Thanks again,
    Last edited by clinel; 11-08-04 at 07:42.

  2. #2
    Join Date
    Nov 2004
    Your question suggests the use of a cursor. However, I think you may be able to use a virtual view. Try something like this

    SELECT vw.RemoveDate,
    SUM(vw.TotPiece) AS Totals
    FROM (
    SELECT MAX(RemoveDate) AS RemoveDate,
    FROM Hata_Meter_Records
    WHERE SetPosition = 'T1- 3.5'
    AND EventType = 'Die'
    GROUP BY LineNum, TotPiece
    ) AS vw

    If you still need a cursor, the BOL is clear. Look for READ_ONLY cursor. From QA, you can also click on Object Browser (F8) and then on the Templates tab. Open the Using Cursors folder and drag the "Declare and use READ_ONLY cursor" element to your QA pane.

  3. #3
    Join Date
    Oct 2002
    Western part of Georgia, USA
    I'm sorry. I did poor job of explaining the details.

    1st details
    What I am trying to do is total the usage of a part. The part's life cycle is started at T1- 3.5 and continues to T2- 6.5. Each numeric value is a "move" of postion on the part. T1 is the first side. T2 is the second side. Typically the part is moved by .5 increments and would go T1- 3.5 to T1- 6.5 then rotated to T2- 3.5 to T2- 6.5. However, sometimes a position is skipped.

    2nd details
    Data is stored in the table with a entry of each move along with production totals for each move.
    Line = 1, DateRemoved = 10/27/04, SetPostion = T- 3.5, TotPiece = 10000
    Line = 1, DateRemoved = 11/1/04, SetPostion = T- 4.0, TotPiece = 20000
    Line = 1, DateRemoved = 11/5/04, SetPostion = T- 4.5, TotPiece = 30000

    3rd details
    What I have been asked to do is display how much total production each part that is running has on it to date. Thus the Max(RemoveDate) with SetPostion = T1- 3.5 (last time a new set was installed). For the above example, I would get a return of DateRemoved 10/27/04 and TotPiece of 60000 for Line 1.

    Thanks again for your help and patience,

  4. #4
    Join Date
    Jun 2003
    Provided Answers: 1


    No cursor necessary. Just a nested subquery:

    SELECT Hata_Meter_Records.Line, Last_Line_Changes.DateRemoved, SUM(Hata_Meter_Records.TotPiece) as TotPiece
    FROM Hata_Meter_Records
    (SELECT Line, Max(DateRemoved) DateRemoved
    FROM Hata_Meter_Records
    WHERE SetPostion = 'T- 3.5'
    GROUP BY Line) Last_Line_Changes
    on Hata_Meter_Records.Line = Last_Line_Changes.Line
    and Hata_Meter_Records.DateRemoved >= Last_Line_Changes.DateRemoved
    GROUP BY Hata_Meter_Records.Line, Last_Line_Changes.DateRemoved
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

  5. #5
    Join Date
    Oct 2002
    Western part of Georgia, USA
    Thanks again! That did the trick!

Posting Permissions

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