Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Rochester, NY, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need help with MySQL date-based query

    I am having trouble coming up with a query I want to turn into a MySQL view.

    I have a table that lists elected positions held by our members and the start and end dates of those positions. Some of the positions are 2 year terms. Some positions are held for multiple terms.

    I want to create a view that lists the persons ID and the description of the position held grouped by year

    I need to include every year that the person held a position.

    The problem I am having is that the records show the span of years from start to end, sometimes spanning several years, and I need the view to show all of the in-between years.

    The 1st picture shows what my end result should be. The problem area is in the case of Director 1 where his records show a start date of 5/1/1998 and an end date of 4/30/2000. How do I get the query to include and show the line for 1999?

    The 2nd picture shows the table I am working with.

    QueryResult.jpg table.jpg

    There's probably an easy solution but I have been scratching my head and scouring the internet for 2 days and haven't come up with a way to do it.

  2. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Hmmm... I don't see why a query grouping by year and Id wouldn't work?

    Something like.

    SELECT Year, ID, Position
    FROM ...
    GROUP BY Year, ID, Position (basically all the fields used in the select that are not aggregation functions)
    Rui
    -------
    R4

  3. #3
    New Lounger
    Join Date
    Dec 2009
    Location
    Rochester, NY, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I don't understand how that would extract the year 1999 from a record that has a start date of 5/1/1998 and an end date of 4/30/2000 and create a result row.
    Are you mistaking my desired results for the actual table I'm working with?

  4. #4
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Yes, I misinterpreted your desired result with the starting table.

    For that situation, I don't see a way out other than using cursors. Cursors in MySQL are not that fun, tbh and I think I wrote just one in my entire life . I suggest you take a look at cursors (and stored procedures) to see how that could be done.

    The idea is to have a stored procedure that would run a cursor that picks each of the records in the start table and would loop through them, adding records to a temporary table. At the end, the stored procedure would simply return all the records in the table.
    Rui
    -------
    R4

  5. #5
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Manchester, United Kingdom
    Posts
    116
    Thanks
    8
    Thanked 17 Times in 16 Posts
    Sorry to barge in late, but unlike ruirib I've never ever used a cursor - there's always a better way.

    For these sort of queries a calendar table is usually the answer. It is just a table with one date field, starting as early as you need and going on until after you're likely to retire (that way you won't have to field the complaints when it runs out of entries!). Creating a calendar table can be done several ways, but the easiest may be to create a single column Excel sheet, save as CSV and import into MySQL using "load data infile" (make sure you format the Excel dates for MySQL).

    Then your query is easy. Something like:

    select
    year(calDate)
    ,praPerson
    ,rnkName
    from tblCalendar
    inner join tblPersRank on calDate >= praStartDate and calDate <= praEndDate
    inner join tblRanks on praRank = rnkID
    group by year(calDate), rnkID, praPerson

    Substitute your own table and field names above, those are from a similar query in my library. Note that you could use distinct on the select instead of group by, but then you'd need to sort, and I've found group by is faster. Also, in the year a post changes you'll see two incumbents if the change occurs during the year. You could refine the query to use company years instead of calendar years if posts always change at the AGM. In fact that is a field you could add to the calendar table - it can be a very useful table (add company reporting period numbers, holidays, etc.)

    Ian.

  6. #6
    New Lounger
    Join Date
    Dec 2009
    Location
    Rochester, NY, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for that suggestion Ian. I'll give that a try. I did find a way to get what I needed in a round about way within my primary view but it amounts to just a yes/no value based on an IF statement and an EXISTS clause in the query. It works but I would rather have a way to get the office title and your method appears that it would allow that. My only problem would be the additional table since it's a vendor supported database and I would run the risk of having to recreate the table if for some reason they deleted it.

  7. #7
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Manchester, United Kingdom
    Posts
    116
    Thanks
    8
    Thanked 17 Times in 16 Posts
    Hi

    I've had similar issues with adapting commercial products. I'd be surprised if they applied updates by wiping the database and reloading their data, that can be risky and time consuming. Most likely they just run an SQL script to update the things they know about - that's how I do it. But the simple solution is to recreate the calendar table if they delete it. You can use the "mysqldump" facility to save and restore a backup.

    Safest solution is to create a new MySQL database (schema) and put the calendar table and your new view in there. You'd need to preface all table/view references with the new database name (newdb.tblCalendar) rather than just using the default database, and your view would then need to similarly preface all references to objects in the original database.

    Ian.

Posting Permissions

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