SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Guru SharifTK's Avatar
    Join Date
    Jan 2004
    Location
    New York, NY
    Posts
    621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Really Confusing Query!

    Ok I probably know how to do this (in the back of my head) but can't seem to just get it constructed.

    Here is the database table i'm working with (named products):
    id - primary key, auto increment
    title - text, title of product
    rd - date, date product will be released
    rd_type - smallint(1), type of release date (real date, quarter: ex. Q4 2004)
    quarter - text, quarter release date (ex. Q4 2004, Q2 2006)

    id|title|rd|rd_type|quarter

    This script is basically to list products coming out this year. I'd like to do this, list all the products that have a real date (where rd_type = 1) first, then ones with a quarter date (where rd_type = 2). And the main thing is, products whose release date is before the current date, cannot be displayed, so if a product is to be released on May 11, 2004, and the current date is May 12, 2004, it will not show up.

    For the real dates, I'd like to do it like:
    June 2004
    - Product 1
    - Product 2

    July 2004
    - Product 3
    - Product 4

    and lets make believe there are no products for this year after July, so we continue on to next year:

    January 2005
    - Blah..

    Then the quarter dates

    Q1 2004
    - Product

    Q2 2004
    - Product

    Q3 2004
    - Product

    lets pretend there is nothing for Q4 2004, then we start listing for Q1 2005 and so on.

    I will probably do this in two seperate queryies but my problem is I can't figure out how to get it to list how I want it to. I realize this is probably something complicated but please, help if you can. Thank you for your time.
    SK

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    select rd_type
         , case when rd_type=1
                then date_format(rd,"%Y%m%d")
                else concat(substring(quarter,4,4)
                           ,substring(quarter,2,1))
            end as sortme
         , case when rd_type=1
                then date_format(rd,"%M %Y")
                else quarter
            end as printme
      from products
     where (
           rd_type=1
       and rd
        >= current_date
           )
        or (
           rd_type=2
       and concat(substring(quarter,4,4)
                 ,substring(quarter,2,1))
        >= concat('',year(current_date)
                 ,floor(month(current_date)/3)+1)       )
    order
        by rd_type
         , sortme
    do yourself a favour and use VARCHAR for TITLE and QUARTER

    TEXT is a 65K field
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,706
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Rudy

    Can you elaborate a bit on how/why this works?

    Thanks,
    Dave

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    sure

    sortme is a sort field, used to ensure that the result set is in the proper calendar order

    printme is the date/quarter to display

    in the WHERE clause, either the date or the quarter is constrained to be in the future
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru SharifTK's Avatar
    Join Date
    Jan 2004
    Location
    New York, NY
    Posts
    621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow, that had to be THE biggest query I have ever seen and do not understand what the heck is going on at all. Guru, you have to explain things to us slow people...
    SK

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by SharifTK
    you have to explain things
    didn't i just do that?

    further explanation? i'd be happy to give more details, except obviously i don't know which parts of it you're having trouble understanding
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru SharifTK's Avatar
    Join Date
    Jan 2004
    Location
    New York, NY
    Posts
    621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Like everything after:

    select rd_type

    SK

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    you were kidding, right?

    no? okay, let's take them one expression at a time

    do you see what the first CASE is doing? it produces a string which is formatted in such a way that dates will be sorted correctly, because the way you have it, with month names first, april comes before january, and with quarter numbers first, q1 2004 comes before q4 2003, so the result of the CASE is a sortable date, depending on rd_type, which is fine, because rd_type is the major sort key anyway

    would you care to have a go at the second CASE?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,706
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'll take a stab at the second CASE.

    Here you are determining if it is a quarter or an actual date, if so you only need to format it to month, year, this will be when printing you can list the MONTH, YEAR then Quater whatever underneath.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    pretty close, except that there's no "underneath" if you mean that the printable dates are intermingled with the quarters

    the first column in the ORDER BY is rd_type, which means that the output is in two halves, first all the dates, then all the quarters, and within each half, the rows are properly sorted
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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