Results 1 to 4 of 4

Thread: SQL Challenge - Can this even be done?

  1. #1

    Thread Starter
    PowerPoster Jenner's Avatar
    Join Date
    Jan 2008
    Location
    Mentor, OH
    Posts
    3,712

    Question SQL Challenge - Can this even be done?

    I got data in a Pervasive PSQL database that I need to pull for a Crystal Report. I've been pulling my hair out trying to figure out just the right line of SQL to pull my data the way I need it. At this point, there's a part of me that thinks it can be done in pure SQL and another part of me that says I need to write some backend data pre-processor to arrange this data in the way I need. I'm not even sure it can be done; but while I'm pretty good at SQL, I'm no master.

    Here's a simplified view of the data in the table I'm pulling from "TABLE_1":

    Job_Number (String) Part (String) Sequence (Integer) Operation (String) Start_Time (DateTime)
    1 Part A 1 SETUP 5/10/2013 8:00
    1 Part A 5 INSPECT 5/10/2013 8:10
    1 Part A 8 MILL 5/10/2013 9:00
    1 Part A 10 SETUP 5/10/2013 14:00
    1 Part A 15 CNC 5/11/2013 9:00
    1 Part A 50 SHIP 5/12/2013 16:30
    2 Part B 1 SETUP 5/11/2013 10:00
    2 Part B 2 INSPECT 5/11/2013 11:00
    2 Part B 5 SHIP 5/12/2013 8:45
    3 Part C 1 SETUP 5/10/2013 10:00
    3 Part C 3 CNC 5/10/2013 10:30
    3 Part C 5 SHIP 5/11/2013 12:30

    The data I need returned needs to look like this:
    Job Number Part Setup Start Operation
    1 Part A 5/10/2013 8:00 MILL
    1 Part A 5/10/2013 14:00 CNC
    3 Part C 5/10/2013 10:00 CNC

    The criteria:
    I only want record listings for each "MILL" or "CNC" operation in the Job.
    I need the Start Time record of the SETUP operation immediately preceeding the MILL or CNC operation.
    Some data records may contain a SETUP operation but no MILL or CNC operation.

    Can this even be done? Thanks in advance for any solutions/advice.
    My CodeBank Submissions: TETRIS using VB.NET2010 and XNA4.0, Strong Encryption Class, Hardware ID Information Class, Generic .NET Data Provider Class, Lambda Function Example, Lat/Long to UTM Conversion Class, Audio Class using BASS.DLL

    Remember to RATE the people who helped you and mark your forum RESOLVED when you're done!

    "Two things are infinite: the universe and human stupidity; and I'm not sure about the universe. "
    - Albert Einstein

  2. #2
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: SQL Challenge - Can this even be done?

    This should get you started - might even be exactly what you need...

    Code:
    Select S1.Operation,S1.Start_Time,S2.*
        From Table1 S1
        Left Join Table1 S2 on S2.JobNumber=S1.JobNumber
               and S2.Start_Time=(Select Max(S3.Start_Time) From Table1 S3 Where S3.JobNumber=S2.JobNumber and S3.Start_Time<S1.Start_Time and S3.Operation in ('SETUP'))
        Where S1.Operation in ('MILL','CNC')
    [editi] btw - I changed it from <S2.Start_Time to <S1.Start_Time in the sub-query [/edit]

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  3. #3

    Thread Starter
    PowerPoster Jenner's Avatar
    Join Date
    Jan 2008
    Location
    Mentor, OH
    Posts
    3,712

    Re: SQL Challenge - Can this even be done?

    Huh... never occurred to me to try a MAX() aggregate like that in the sub query, or a sub-query in the outer join. I don't think I tried anything where I'm calling the same table three different times.
    I'll give it a try this afternoon when I plan on taking another stab at it. Thanks much szlamany!
    My CodeBank Submissions: TETRIS using VB.NET2010 and XNA4.0, Strong Encryption Class, Hardware ID Information Class, Generic .NET Data Provider Class, Lambda Function Example, Lat/Long to UTM Conversion Class, Audio Class using BASS.DLL

    Remember to RATE the people who helped you and mark your forum RESOLVED when you're done!

    "Two things are infinite: the universe and human stupidity; and I'm not sure about the universe. "
    - Albert Einstein

  4. #4
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: SQL Challenge - Can this even be done?

    If pervasive doesn't like all that sub-query and what not you can always get to the same result with a couple of views that accomplish making the same sets of data.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

Tags for this Thread

Posting Permissions

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



Click Here to Expand Forum to Full Width