Results 1 to 15 of 15
  1. #1
    Join Date
    Jul 2013
    Posts
    8

    Unanswered: How to get MAX date from multi table query

    Hi,
    I'm new to joining, but use this forum often to get ideas on how to expand my SQL query knowledge.
    I've been attempting to try to write a statement that will return the latest effective date for a pricing inquiry. I've not been able to find any threads discussing how to execute this with 4 tables, so I do apologize if the info is out there.

    I'm pretty new with SQL language in the first place, so forgive me if this is ridiculously simple and I can't get my head around it.

    I attempted to execute the max effective date by grouping my items together and then adding MAX in front of the effective date, but this does nothing except take away the column header for the effective date.
    Here's my query. When I execute it, I still get a list with all of the effective dates in the list.

    SELECT VendorRate.vendorID, VendorRate.vendorEquipmentNumber, VendorRate.eroType, MAX (VendorRate.effectiveDate), VendorRate.hourly
    FROM Groundbreaker.dbo.Vendor Vendor, Groundbreaker.dbo.VendorEquipment VendorEquipment, Groundbreaker.dbo.VendorRate VendorRate
    WHERE Vendor.vendorID = VendorRate.vendorID AND VendorEquipment.vendorEquipmentNumber = VendorRate.vendorEquipmentNumber AND VendorEquipment.vendorID = Vendor.vendorID AND VendorEquipment.vendorID = VendorRate.vendorID AND ((Vendor.active=1) AND (VendorEquipment.available=1))
    GROUP BY VendorRate.vendorID, VendorRate.vendorEquipmentNumber, VendorRate.eroType, VendorRate.effectiveDate, VendorRate.hourly

    I hope this is enough information, but if not, I'll gladly pull anything else needed. If I can solve this, I just might be a hero in the office!
    Thanks so much in advance

  2. #2
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    I could probably create the requisite 3 tables and fabricate some data, but it would be very helpful if you could post the DDL for the 3 tables and some meaningful sample data (INSERT statements) for each. It would make it much easier to come up with a solution.

    Thanks.

  3. #3
    Join Date
    Jul 2013
    Posts
    8
    Thanks so much! I'll do my best to provide you with the info.
    The statement I'm creating is just a look up. My database consultant should have created an historical table to track equipment rate changes, but he didn't, so now in order to create a report for my users to see the most recent prices for supplier equipment, I'm stuck having to eliminate the unwanted data by query.
    I've attached a pdf of a sample of my data and my tables (hope that helps). I'm not sure what a DDL is, but I think the table data is what you need? If not, I'll try that again.
    Thanks!
    Attached Files Attached Files

  4. #4
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    DDL is Data Definition Language. It is used to describe your tables among other things. It would look something like:

    Code:
    CREATE TABLE Employees
    (
       Emp_ID int,
       Emp_Name char(50)
       etc...
    )
    Sample data should come in the form of:

    Code:
    INSERT Employees (Emp_ID, Emp_Name, etc..) VALUES
     (1, 'Name 1', other fields),
     (2, 'Name 2', other fields)
    This helps those who are trying to help you from having to type in all this information.

  5. #5
    Join Date
    Jul 2013
    Posts
    8
    Oh, I have no idea what any of that is, so I might be out of luck. I've never used an Insert statement before, I'm more looking to just do a look up, so I'm not sure how to translate that. Aside from the query I wrote, I don't have the knowledge to go any deeper than that - all of my queries begin with SELECT
    As far as DDL goes, maybe I shouldn't even waste your time, but here would be the revised statement with that information, I think:
    SELECT VendorRate.vendorID PK,FK,varchar(10), not null, VendorRate.vendorEquipmentNumber PK, FK, varchar(10), not null, VendorRate.eroType PK, FK, varchar(50), not null, MAX (VendorRate.effectiveDate PK, datetime, not null), VendorRate.hourly money, not null
    FROM Groundbreaker.dbo.Vendor Vendor, Groundbreaker.dbo.VendorEquipment VendorEquipment, Groundbreaker.dbo.VendorRate VendorRate
    WHERE Vendor.vendorID PK, varchar(10), not null = VendorRate.vendorID PK,FK,varchar(10), not null AND VendorEquipment.vendorEquipmentNumber PK, varchar(10), not null = VendorRate.vendorEquipmentNumber PK, FK, varchar(10), not null AND VendorEquipment.vendorID FK, varchar(10), not null = Vendor.vendorID PK, varchar(10), not null AND VendorEquipment.vendorID PK, varchar(10), not null = VendorRate.vendorIDPK, FK, varchar(10), not null AND ((Vendor.active int, not null=1) AND (VendorEquipment.available int, not null =1))
    GROUP BY VendorRate.vendorID PK,FK,varchar(10), not null, VendorRate.vendorEquipmentNumber PK, FK, varchar(10), not null, VendorRate.eroType PK, FK, varchar(50), not null, MAX (VendorRate.effectiveDate PK, datetime, not null), VendorRate.hourly money, not null

    The sample data written would look like:

    Select VendorRate (vendorID, vendorEquipmentNubmer, eroType, effectiveDate, hourly) VALUES
    (Vendor 1, Equipment Number 1, eroType regular, effective date June 1, 2012, hourly cost),
    (Vendor 1, Equipment Number 1, eroType regular, effective date June 1, 2013, hourly cost)
    (Vendor 1, Equipment Number 2, eroType trailer, effectve date Jan 10, 2013, hourly cost)
    (Vendor 1, Equipment Number 2, eroType trailer, effective date July 1, 2013, hourly cost

    I would like to just return into my report, for Vendor 1, Equipment Number 1 the June 1, 2013 line and for Vendor 1, Equipment Number 2, just the July 1, 2013 line.

    Hopefully this is somewhat close to what you're talking about. However, if I still haven't given you enough information, I'll just close this ticket and stop wasting your time.
    Thanks again for trying to help

  6. #6
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Try the following and see what is returned:

    Code:
    Select vendorID, VendorEquipmentNumber, MAX(effectiveDate) eDate
         from VendorRate 
         group by VendorID, VendorEquipmentNumber

  7. #7
    Join Date
    Jul 2013
    Posts
    8
    When i input that statement, I get exactly what I hope to see, which is a single line for each piece of equipment, with the most recent date added. YAY!
    So, I get this:
    Vendor 1, Equipment Number 1, ero type regular, June 1, 2013
    Vendor 1, Equipment Number 2, ero type trailer, July 1, 2013

    Unfortunately, as soon I try to addeither of the other 2 tables into the query, I get the same results as before.

    Again, thanks so much for helping me!

  8. #8
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Quote Originally Posted by lostgirl View Post
    I would like to just return into my report, for Vendor 1, Equipment Number 1 the June 1, 2013 line and for Vendor 1, Equipment Number 2, just the July 1, 2013 line.
    If all you need, as you stated, is the Vendor ID, Equipment Number and date, why do you need the rest of the query and other tables?

  9. #9
    Join Date
    Jul 2013
    Posts
    8
    The other parameters to exclude machines or vendors that are no longer 'active' in our system are located on the other tables as well as the actual equipment descriptions (which my laypeople need to understand what the actual piece of equipment is)

  10. #10
    Join Date
    Jul 2013
    Posts
    8
    Sorry, I meant to say originally, the return values showing only one line per piece of machine were good, but I still have to add the other parameters from the other tables to complete the report.

  11. #11
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Quote Originally Posted by lostgirl View Post
    I still have to add the other parameters from the other tables to complete the report.
    Which brings us back to my original request for data for all the tables! It is hard to come up with a solution without data to test it.

    You must have a MySql manager of some sort, ie. MySQL WorkBench, Toad, HeidiSQL, ems MySQL, to name a few, that would allow you to get create statements for your tables, run queries, export data, generate insert statements, etc...

    If you can come up with the DDL and data, come back and post it on this thread!

  12. #12
    Join Date
    Jul 2013
    Posts
    8
    I'm sorry, I thought I had provided that in the attachment, so I guess I still have a lot to learn. I'm using SQL Server Management Studio and Excel's database query editor to get my reports done and I don't seem to have access to DDL at all, so I'm probably right out of luck on this.
    Thanks anyway,

  13. #13
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    I have modified your original query.

    You don't want to group by the effectiveDate or the hourly rate.
    If you need the hourly rate, then a self join will be required which will of course complicate the query. I removed the hourly column from the select statement for now.

    Give it a try and see what happens.


    Code:
    SELECT VendorRate.vendorID
           , VendorRate.vendorEquipmentNumber,
           , VendorRate.eroType,
           , MAX (VendorRate.effectiveDate)
           --, VendorRate.hourly
    FROM Groundbreaker.dbo.Vendor Vendor,
         Groundbreaker.dbo.VendorEquipment VendorEquipment,
         Groundbreaker.dbo.VendorRate VendorRate
    WHERE Vendor.vendorID = VendorRate.vendorID
      AND VendorEquipment.vendorEquipmentNumber = VendorRate.vendorEquipmentNumber
      AND VendorEquipment.vendorID = Vendor.vendorID
      AND VendorEquipment.vendorID = VendorRate.vendorID
      AND Vendor.active=1
      AND VendorEquipment.available=1
    GROUP BY VendorRate.vendorID,
             VendorRate.vendorEquipmentNumber,
             VendorRate.eroType

  14. #14
    Join Date
    Jul 2013
    Posts
    8
    Aww, thanks for sticking with me I hope I'm not taking too much of your time.
    I tried that query and it worked. It returned a single line of data per vendor and equipment ID with the most recent effective date!

  15. #15
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Glad it is working for you.

Posting Permissions

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