none
Unsure how to write this query

    Question

  • I have two tables.  One has a list of Items, and the other contains which of the items would be valid.  An item is only valid if the Type and the Date are between the Valid Date and the current time stamp.

    Here are the tables:

    --Create/Populate [#Items]:
    if object_id('tempdb..[#Items]','U') is not null drop table [#Items]
    go
    create table [#Items]
    (
     [itemid] int
     ,[itemname] varchar(50)
     ,[itemtype] int
     ,[itemtimestamp] datetime
    )
    go
    set nocount on
    insert [#Items] select 1, 'Widget1', 1, '2013-05-25T00:00:00'
    insert [#Items] select 2, 'Widget2', 1, '2013-06-07T00:00:00'
    insert [#Items] select 3, 'Widget3', 2, '2013-06-08T00:00:00'
    insert [#Items] select 4, 'Widget4', 3, '2013-06-09T00:00:00'
    insert [#Items] select 5, 'Widget5', 3, '2013-06-12T00:00:00'
    insert [#Items] select 6, 'Widget6', 1, '2013-06-13T00:00:00'
    insert [#Items] select 7, 'Widget7', 2, '2013-06-14T00:00:00'
    go
    --select * from [#Items]


    --Create/Populate [#ValidItems]:
    if object_id('tempdb..[#ValidItems]','U') is not null drop table [#ValidItems]
    go
    create table [#ValidItems]
    (
     [validid] int
     ,[validtype] int
     ,[validtimestamp] datetime
    )
    go
    set nocount on
    insert [#ValidItems] select 1, 1, '2013-06-01T00:00:00'
    insert [#ValidItems] select 2, 3, '2013-06-10T00:00:00'

    go
    --select * from [#ValidItems]

    So what I need to do is write a query that will return Widget2, Widget5, and Widget6.  

    Widget1 isn't valid because the valid date for Type 1 is 6/1.  Widget3 and Widget7 are not valid because Type 2 is not valid.  Widget4 isn't valid because the valid date for Type 3 is 6/10.

    Hopefully this makes sense.

    Wednesday, June 26, 2013 11:21 PM

Answers

  • Maybe this is what you are looking at.

    SELECT * 
    FROM [#Items] I
    	JOIN [#ValidItems] VI
    	  ON VI.validtype = I.itemtype
    	    AND VI.validtimestamp <= I.itemtimestamp
    ORDER BY 1

    Hope this helps................

    Ione

    • Marked as answer by MatMDIG Thursday, June 27, 2013 3:37 PM
    Thursday, June 27, 2013 1:57 PM

All replies

  • Hi, Could you please clarify what has ItemType got to do with ValidType to help you better.

    Thanks..........


    Ione

    Wednesday, June 26, 2013 11:32 PM
  • ItemType and ValidType is the type of Widget it is (Electronic(1), Mechanical(2), Magnetic(3), Stateless(4), etc).  It's actually represented by another table, and ItemType and ValidType are Foreign Keys for that table.  However, in the interest of keeping things simple, I didn't include it.
    Wednesday, June 26, 2013 11:45 PM
  • Maybe this is what you are looking at.

    SELECT * 
    FROM [#Items] I
    	JOIN [#ValidItems] VI
    	  ON VI.validtype = I.itemtype
    	    AND VI.validtimestamp <= I.itemtimestamp
    ORDER BY 1

    Hope this helps................

    Ione

    • Marked as answer by MatMDIG Thursday, June 27, 2013 3:37 PM
    Thursday, June 27, 2013 1:57 PM