Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1

    Question Answered: How to determine if an item was available during a date range?

    I have a table that contains the SKU and a period of time the SKU was available to purchase (StartDtKey and EndDtKey). I am trying to write a query determine which skus were available during a given date range. The DtKeys correspond to a date in our calendar table and are in order. So DtKey 5 is 4 days after DtKey 1.

    Code:
    CREATE TABLE #SKU(
      [SKU] varchar(10) NULL,
      [StartDtKey] int NULL,
      [EndDtKey] int NULL);
    	
    INSERT INTO #SKU VALUES ('Sku1',1,9)
    	,('Sku2',2,6)
    	,('Sku3',10,20)
    	,('Sku4',5,9)
    	,('Sku5',25,30)
    Example:
    Which SKUs were available on at least one day between DtKey 7 and DtKey 9?

    Answer:
    Sku1, Sku4

  2. Best Answer
    Posted by Pat Phelan

    "
    Code:
    CREATE TABLE #SKU
    (  [SKU]        varchar(10) NULL
    ,  [StartDtKey] int         NULL
    ,  [EndDtKey]   int         NULL
    );
    	
    INSERT INTO #SKU
       VALUES
          ('Sku1',  1,  9), ('Sku2',  2,  6), ('Sku3', 10, 20)
    ,     ('Sku4',  5,  9), ('Sku5', 25, 30)
    
    SELECT *
       FROM #SKU
       WHERE  StartDtKey <= 9
          AND 7 <= EndDtKey
    I'll let you figure out how/why this works.

    -PatP"


  3. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    CREATE TABLE #SKU
    (  [SKU]        varchar(10) NULL
    ,  [StartDtKey] int         NULL
    ,  [EndDtKey]   int         NULL
    );
    	
    INSERT INTO #SKU
       VALUES
          ('Sku1',  1,  9), ('Sku2',  2,  6), ('Sku3', 10, 20)
    ,     ('Sku4',  5,  9), ('Sku5', 25, 30)
    
    SELECT *
       FROM #SKU
       WHERE  StartDtKey <= 9
          AND 7 <= EndDtKey
    I'll let you figure out how/why this works.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #3
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Thanks Pat!

Posting Permissions

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