Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2008
    Posts
    2

    Unanswered: Insert and Update Records

    Good day to all, I am new here so i hope i am doing things correctly.

    The Company i work for make coils of shaped wire and work a 6 - 6 shift pattern

    I have a database that is updated from a data collection source (MS Access) at 06:00 every morning. This seems to be working ok, my problem is that most coils fit nicely into the 6 - 6 shift pattern, but some now and again drift over into the next shift. I have written a crystal report that picks up this data. at the moment the coils are put in the database as: [Coil Start Time], [Coil Finish Time], [Coil Start Weight], [Coil Finish Weight], etc.

    I have written (been helped to write) a SQL statement that will do the following:

    Step 1: If the Coil Finish time is greater than the shift end time, then set the shit end time to be coil end time and zero start and finish wheight.
    Step 2: The original Coil record is duplicated and Coil Start time set to start time of shift, all other data left alone.

    Example of code:

    -->>

    SELECT [Batch Name], [Batch Start], [Batch End], [Coil Start Weight], [Coil Finish Weight], [Product], [Shift], [Operator ID], [Works Order No]
    FROM dbo.tblCoilData
    WHERE (DATEPART(hour, [Batch Start]) >= 6 AND DATEPART(hour, [Batch End]) < 18) OR
    ((DATEPART(hour, [Batch Start]) < 6 OR
    DATEPART(hour, [Batch Start]) >= 18) AND (DATEPART(hour, [Batch End]) < 6 OR
    DATEPART(hour, [Batch End]) >= 18))
    UNION ALL
    SELECT [Batch Name], [Batch Start], DATEADD(hour, 17, DATEADD(minute, 59, CONVERT(char(10), [Batch End], 101))), 0, 0, [Product], [Shift], [Operator ID],
    [Works Order No]
    FROM dbo.tblCoilData
    WHERE DATEPART(hour, [Batch Start]) >= 6 AND DATEPART(hour, [Batch Start]) < 18 AND (DATEPART(hour, [Batch End]) < 6 OR
    DATEPART(hour, [Batch End]) >= 18)
    UNION ALL
    SELECT [Batch Name], DATEADD(hour, 18, CONVERT(char(10), [Batch Start], 101)), [Batch End], [Coil Start Weight], [Coil Finish Weight], [Product], [Shift],
    [Operator ID], [Works Order No]
    FROM dbo.tblCoilData
    WHERE DATEPART(hour, [Batch Start]) >= 6 AND DATEPART(hour, [Batch Start]) < 18 AND (DATEPART(hour, [Batch End]) < 6 OR
    DATEPART(hour, [Batch End]) >= 18)
    UNION ALL
    SELECT [Batch Name], [Batch Start], DATEADD(hour, 5, DATEADD(minute, 59, CONVERT(char(10), [Batch End], 101))), 0, 0, [Product], [Shift], [Operator ID],
    [Works Order No]
    FROM dbo.tblCoilData
    WHERE (DATEPART(hour, [Batch Start]) < 6 OR
    DATEPART(hour, [Batch Start]) >= 18) AND DATEPART(hour, [Batch End]) >= 6 AND DATEPART(hour, [Batch End]) < 18
    UNION ALL
    SELECT [Batch Name], DATEADD(hour, 6, CONVERT(char(10), [Batch Start], 101)), [Batch End], [Coil Start Weight], [Coil Finish Weight], [Product], [Shift],
    [Operator ID], [Works Order No]
    FROM dbo.tblCoilData
    WHERE (DATEPART(hour, [Batch Start]) < 6 OR
    DATEPART(hour, [Batch Start]) >= 18) AND DATEPART(hour, [Batch End]) >= 6 AND DATEPART(hour, [Batch End]) < 18


    <<--

    I have 2 options now

    option 1:
    Leave this as a SQL View and report from this

    option 2:
    Insert updated records to the tblCoilData table so that the data in the table is permanent

    I would prefer option 2 but am a bit of a nugget when it comes to writing update / insert statements, Could someone please help me with this

    Thank you very kindly


    Regards

    Steve Dyson

  2. #2
    Join Date
    Feb 2007
    Posts
    62
    It sounds like a business decision not a technical one. Either you need to record the actual times or you do not. If you go and overwrite the original data you will never know what actually happened, where as a view allows you to see both actual and massaged data. Whether deleting/hiding the actual data is acceptable is a buisness decision.
    Personally I would use a view until it has been proven that changing the data would be ok.

  3. #3
    Join Date
    Feb 2008
    Posts
    2
    Thanks for the reply,

    The original data is held in a backed up Access Database Table, The SQL data is an export from the original so that there are no messups, so either would be fine as i have a DTS import to update the database.


    Thanks, I think i will go with the view option, I just have one question.

    1: whilst creating this statement i concentrated so hard on the times of the coils i forgot about the Shift Colour and Shift Operator being changed, how would i change that to the following shift


    Regards

    Steve Dyson

  4. #4
    Join Date
    Feb 2007
    Posts
    62
    Quote Originally Posted by snoopsterg
    1: whilst creating this statement i concentrated so hard on the times of the coils i forgot about the Shift Colour and Shift Operator being changed, how would i change that to the following shift
    I have no idea what you are referring to. Sorry!

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    If you really want an answer (because it's hard to tell what you're looking for) read the sticky at the top of the forum, and post what it asks for
    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.

Posting Permissions

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