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

    Unanswered: Getting Totals Help

    I am using a web page to enter records into a table that tracks production on machine parts.

    I get two recordsets and loop through them using vbscript to get the totals.

    I use two because I first get all production data greater than the last date an event occurred. The second I get the totals for the day of because I have to back out shifts of production prior to the event, the day of the event (ie 3 shift per day, 3rd shift begins the day so a day looks like this 3, 1, 2. We do an event on shift 1. I have to back out shift 3 as it happened prior to 1. Once I have the two seperate totals I then write a record in the historical data table with the event, shift, production totals, etc.

    My question is can I get the production totals from within SQL or is looping through the rs's in vb better?

    the vb for the shift is similar to this
    If last_event_shift = 1 then
    DayProd = shift1prod + shift2prod
    elseif last_event_shift = 2 then
    DayProd = shift2prod
    elseif last_event_shift = 3 then
    DayProd = shift1prod + shift2prod + shift3prod
    else
    end if
    TotProd = DayProd + GreaterProd


    Here is the SQL for each rs
    Data Greater than last date
    Select Sum(Production)AS TotProd, dbo.Production.LineNum, EventType

    From dbo.Production INNER JOIN dbo.EventDate ON dbo.Production.LineNum = dbo.EventDate.LineNum AND dbo.Production.EntryDate > dbo.EventDate.EntryDate

    Where dbo.EventDate.LineNum = @Line
    Group By dbo.Production.LineNum, dbo.EventDate.EventType

    Data for the last date
    Select Max(dbo.EventDate.ShiftRemoved) AS RemovedShift, dbo.EventDate.Set_Pos, Max(dbo.EventDate.CutOff)AS CutOff, dbo.EventDate.EntryDate, Sum(Production)AS TotProd, dbo.Production.LineNum, EventType, Shift

    From dbo.Production INNER JOIN dbo.EventDate ON dbo.Production.LineNum = dbo.EventDate.LineNum AND dbo.Production.EntryDate = dbo.EventDate.EntryDate

    Where dbo.EventDate.LineNum = @Line

    Group By dbo.Production.LineNum, dbo.EventDate.EventType, Shift, dbo.EventDate.EntryDate, dbo.EventDate.Set_Pos

    Thanks in advance,
    Lee

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    First, it would be helpful to post the DDL for the underlying tables. That helps us help you by being able to set up the problem and test our answers before replying.

    Second, if what you are doing today works (and it seems to) and there doesn't seem to be an issue with performance, then by all means it is a "right" answer. Not that there's anything wrong (in my opinion) with trying to tweak it for improved performance, but hey it does work.

    In looking through the SQL you provided, I deduced the following DDL:

    [code]
    CREATE TABLE Production
    EntryDate DateTime,
    LineNum int,
    Production (some numeric)
    GO

    CREATE TABLE EventDate
    EntryDate DateTime,
    LineNum int,
    EventType,
    Set_Pos,
    CutOff
    GO

    Is this right so far?

    Could you simply do:

    SELECT
    EntryDate,
    LineNum,
    Sum(Production)
    FROM
    Production
    WHERE
    EntryDate BETWEEN @StartDate AND @EndDate

    Where you supply @StartDate and @EndDate. You could even make this a stored procedure and make @StartDate and @EndDate parameters. When I did something similar a while back, I had a separate table for shifts with a ShiftStartTime and a ShiftEndTime expressed as a decimal. With a little bit of manipulation, I was able to produce a script that dynamically pulled in the shift start and end times (we had 5 shifts working overlapping dates) to produce shift productivity reports.

    That was back when I could write SQL worth a dang. Sorry, I hope this was of some value to you. Post the actual DDL and an explanation of what an 'event' is and perhaps I can better understand what it is you are trying to do...

    Regards,

    hmscott

  3. #3
    Join Date
    Oct 2002
    Location
    Western part of Georgia, USA
    Posts
    123
    This is the table struture.

    Event is just a description of the task that was done (e.g. Tooling, Die, Gear), Set_Pos is a description. We have 3 shifts and they are ordered 3, 1, 2 all are on the same date.

    The reason I want to learn to do this is that I have several other reports that I would like to do this way (SQL) rather than a combination.

    CREATE TABLE Production
    EntryDate DateTime,
    LineNum int,
    Production int,
    Shift int
    GO

    CREATE TABLE EventDate
    EntryDate DateTime,
    LineNum int,
    EventType varchar(15),
    Set_Pos varchar(10),
    CutOff int,
    Shift int
    GO

Posting Permissions

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