Professional Web Applications Themes

Complex query for all you SQL gurus out there - Microsoft SQL / MS SQL Server

Hi guys, I am stuck on how to approach this problem: I have a table of items, each item exists one or more times, with different data for different time periods, simplified in this structure: item_id, start_date, end_date, price e.g. item 1 can have different prices in different periods. This far no problem to select the right record(s) The situation is complicated by adding a "ShowNow" logical field, which is True for future records to be shown even if the period doesn't match, and the record for which the period actually matches, should then not be shown if there exists ...

  1. #1

    Default Complex query for all you SQL gurus out there

    Hi guys,

    I am stuck on how to approach this problem:

    I have a table of items, each item exists one or more times, with different
    data for different time periods, simplified in this structure:

    item_id, start_date, end_date, price

    e.g. item 1 can have different prices in different periods.

    This far no problem to select the right record(s)

    The situation is complicated by adding a "ShowNow" logical field, which is
    True for future records to be shown even if the period doesn't match, and
    the record for which the period actually matches, should then not be shown
    if there exists a future record with ShowNow set to True. Also, if there are
    more future records with shownow true, only the one with the highest date
    should be shown. (It is validated, that periods do not overlap)

    The structure of the simplified table now being:
    item_id, start_date, end_date, shownow, price
    Sample data for items 1 and 2:
    recno,item_id, start_date, end_date, shownow, price
    1, 1, 1/1/2002, 12/31/2002, False, 1000
    2, 1, 1/1/2003, 12/31/2003, False, 1100
    3, 1, 1/1/2004, 12/31/2004, True, 1200
    4, 1, 1/1/2005, 12/31/2005, True, 1300
    5, 2, 1/1/2002, 12/31/2002, False, 2000
    6, 2, 1/1/2003, 12/31/2003, False, 2100
    7, 2, 1/1/2004, 12/31/2004, False, 2200
    8, 2, 1/1/2005, 12/31/2005, False, 2300

    I need to submit the query: List prices as of 7/7/2003, and this should in
    this case return only rows 4 and 6:
    4, 1, 1/1/2005, 12/31/2005, True, 1300
    6, 2, 1/1/2003, 12/31/2003, False, 2100
    Because: row 4 has shownow true and is the record with the highest date for
    item 1, and item 2 doesn't have any future rows with shownow set to true,
    therefore row 6 where the period matches the sought date is returned.

    Any ideas on how best to approach this problem? Should I use a view, maybe a
    stored procedure and a temp.table, or is it at all possible to return the
    correct rows only in a select query?

    I would like to solve this server-side, as the code i have now, selects all
    records and then filters manually client-side.

    Hope there is somebody out there, who can solve this.



    Peter Guest

  2. #2

    Default Re: Complex query for all you SQL gurus out there

    Hi Peter,

    Try the following:

    set dateformat mdy
    CREATE TABLE PRICES (recno INT,item_id INT, start_date datetime, end_date
    datetime, shownow varchar(5), price money,
    CONSTRAINT PK_prices PRIMARY KEY (recno),
    CONSTRAINT UQ_item_start_date UNIQUE (item_id, start_date))

    GO
    INSERT INTO PRICES (recno,item_id, start_date, end_date, shownow, price)
    VALUES (1, 1, '1/1/2002', '12/31/2002', 'False', 1000)
    INSERT INTO PRICES (recno,item_id, start_date, end_date, shownow, price)
    VALUES (2, 1, '1/1/2003', '12/31/2003', 'False', 1100)
    INSERT INTO PRICES (recno,item_id, start_date, end_date, shownow, price)
    VALUES (3, 1, '1/1/2004', '12/31/2004', 'True', 1200)
    INSERT INTO PRICES (recno,item_id, start_date, end_date, shownow, price)
    VALUES (4, 1, '1/1/2005', '12/31/2005', 'True', 13000)
    INSERT INTO PRICES (recno,item_id, start_date, end_date, shownow, price)
    VALUES (5, 2, '1/1/2002', '12/31/2002', 'False', 2000)
    INSERT INTO PRICES (recno,item_id, start_date, end_date, shownow, price)
    VALUES (6, 2, '1/1/2003', '12/31/2003', 'False', 2100)
    INSERT INTO PRICES (recno,item_id, start_date, end_date, shownow, price)
    VALUES (7, 2, '1/1/2004', '12/31/2004', 'False', 2200)
    INSERT INTO PRICES (recno,item_id, start_date, end_date, shownow, price)
    VALUES (8, 2, '1/1/2005', '12/31/2005', 'False', 2300)

    GO

    SELECT t.recno, t.item_id, t.start_date, t.end_date, t.shownow, t.price
    FROM PRICES t
    INNER JOIN
    (SELECT item_id, MAX(start_date) AS max_start_date
    FROM PRICES
    WHERE shownow = 'TRUE'
    OR start_date < '7/7/2003'
    GROUP BY item_id) AS m
    ON t.item_id = m.item_id
    AND t.start_date = m.max_start_date
    GO
    DROP TABLE PRICES

    Can you btw next time you psot a question provide table create and data
    insert scripts as above? It saves us a lot of typing. You can script your
    tables from Enterprise manager and your data with this script from Vyas:
    http://vyaskn.tripod.com/code.htm#inserts

    I have designed a similar table recently and I didn't include a end_date
    column because the prices are continuous, so the end date of one price is
    when the next price starts. This saves doing validation and having redundant
    data in the database.

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Peter B.L. Rasmussen" <dk> wrote in message
    news:#phx.gbl... 
    different 
    are 
    for 

    all 


    Jacco Guest

  3. #3

    Default Re: Complex query for all you SQL gurus out there

    DECLARE dt DATETIME
    SET dt = '20030707'

    SELECT I.*
    FROM Itemprices AS I
    LEFT JOIN
    (SELECT item_id, MAX(start_date) AS show_date
    FROM Itemprices
    WHERE shownow = 'True'
    GROUP BY item_id) AS T
    ON I.item_id = T.item_id
    WHERE (show_date IS NULL AND dt BETWEEN start_date AND end_date)
    OR start_date = show_date

    Note that there is no Boolean datatype in SQLServer. You can create a column
    like this instead:

    CREATE TABLE ItemPrices (... shownow CHAR(5) NOT NULL CHECK (shownow IN
    ('True','False'))... )

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

  4. #4

    Default Re: Complex query for all you SQL gurus out there

    True. I dislike using BIT, partly because it's very non-standard but mainly
    because different environments and developers attribute different boolean
    meanings to 1 and 0 (and -1!). Microsoft's own applications are inconsistent
    on this point.

    If storage/performance is a concern I would rather use

    .... shownow CHAR(1) NOT NULL (CHECK shownow IN ('T','F')), ...

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

  5. #5

    Default Re: Complex query for all you SQL gurus out there

    You do have the BIT datatype with which you can store 0 for false and 1 for
    true and then use CASE in your select:

    CREATE TABLE ItemPrices (... ,shownow BIT NOT NULL, ... )

    SELECT .......,
    CASE shownow
    WHEN 1 THEN 'TRUE'
    ELSE 'FALSE
    END
    AS shownow
    FROM .......

    In this case, you don't need the check constraint and you save 4 Bytes per
    row.



    "David Portas" <org> wrote in message
    news:%23Yo%phx.gbl...
     
    column 


    Amy Guest

  6. #6

    Default Re: Complex query for all you SQL gurus out there

    Hi Jacco,

    Thanks for the quick response. This was just what I needed.
    You're right about the start/end date issue, but in this case I need the end
    date too, because items do not necessarily exist at all times, there can be
    "holes" inbetween periods, where items aren't available, and they are
    discontinued at some time. I have already modified your code to take this
    into account.

    Thanks again.

    Peter


    "Jacco Schalkwijk" <co.uk> skrev i en meddelelse
    news:phx.gbl... 
    redundant 
    > different [/ref]
    is [/ref]
    and [/ref]
    shown 
    > are [/ref]
    date [/ref]
    in 
    > for [/ref]
    true, [/ref]
    maybe [/ref]
    the 
    > all 
    >
    >[/ref]


    Peter Guest

  7. #7

    Default Re: Complex query for all you SQL gurus out there

    Hi Peter,

    I don't know exactly what your business requirements are, but I would model
    the status (available, not available, discontinued etc) of a product
    independently of the price. That allows for more flexibility.

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Peter B.L. Rasmussen" <dk> wrote in message
    news:eQw#phx.gbl... 
    end 
    be [/ref]
    end_date [/ref]
    your [/ref]
    is 
    > redundant 
    > > different [/ref][/ref]
    which [/ref]
    > and [/ref]
    > shown [/ref][/ref]
    there [/ref]
    > date [/ref][/ref]
    should [/ref][/ref]
    date [/ref]
    > true, [/ref]
    > maybe [/ref]
    > the [/ref][/ref]
    selects 
    > >
    > >[/ref]
    >
    >[/ref]


    Jacco Guest

Similar Threads

  1. Help! Complex query
    By RuBot in forum Coldfusion Database Access
    Replies: 5
    Last Post: March 7th, 12:47 AM
  2. Help for quite complex query.
    By _andrea.l in forum MySQL
    Replies: 3
    Last Post: January 1st, 04:22 PM
  3. Need SQL Query Help from Gurus
    By ctrl+alt+delete in forum Coldfusion Database Access
    Replies: 4
    Last Post: February 18th, 09:20 PM
  4. Help with complex query
    By Mike in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 17th, 08:19 AM
  5. A complex query
    By Venkatesan M in forum Microsoft SQL / MS SQL Server
    Replies: 5
    Last Post: July 1st, 02:27 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139