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

    Question Unanswered: Limiting results using Start and End Date from another table

    I have a pickle here that I cant figure out. I have a Sales table that has all the sales info including the date of the sale, and a separate table (Promo) that has date ranges (StartDate, EndDate) for given promotion periods. For example: Promo1, 10/1/2010, 10/31/2010. Promo2, 12/31/2010, 1/28/2011. etc etc.

    I need to find all sales for the given promo dates.

    I can't figure out how to use the start and end dates from the promo table in the where clause of the sales table for each of the promos. Ideas?
    Last edited by clawlan; 10-14-11 at 15:31.

  2. #2
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Did you mention that your Sales table contains the date of the sale???

    If so, then use that in a WHERE clause with the BETWEEN operator.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  3. #3
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by PracticalProgram View Post
    Did you mention that your Sales table contains the date of the sale???

    If so, then use that in a WHERE clause with the BETWEEN operator.
    The issue with this is that there is no way for me to link the two tables together, otherwise using between would be a no brainer.

    It's almost as if I need to grab the dates and store them as a variable and inject into my query code...but I have no clue how or if that is possible.

  4. #4
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    The use the BETWEEN operator as your join condition.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  5. #5
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by PracticalProgram View Post
    The use the BETWEEN operator as your join condition.
    Ah, i understand. That is getting me a little closer but the number are way too huge... i'll keep at it.

  6. #6
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    clawlan, you are probably joining with all promotions from the Promo table. Try limiting it to the one promotion you are dealing with. This should be on your JOIN's ON predicate along with the BETWEEN.

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    This is what PracticalProgram and Stealth_DBA meant:
    Code:
    CREATE TABLE #Sales (
    	ID	varchar(20)	not null,
    	DateOfSale	DATE not null
    )
    
    INSERT INTO #Sales(ID, DateOfSale) VALUES
    ('Pen', '20110101'), ('Paper', '20110201'), ('Scissors', '20110301'), 
    ('Rock', '20110401'), ('Rose', '20110501'), ('Flower', '20110601'), 
    ('Stone', '20110701'), ('Sand', '20110801')
    
    CREATE TABLE #Promo (
    	ID	int	not null,
    	StartDate	DATE not null,
    	EndDate	DATE not null
    )
    
    INSERT INTO #Promo (id, StartDate, EndDate) VALUES
    (1, '20110201', '20110228'), (2, '20110501', '20110731'), 
    (3, '20110801', '20120101')
    
    SELECT S.ID, S.DateOfSale, P.ID, P.StartDate, P.EndDate
    FROM #Sales as S
    	INNER JOIN #Promo as P ON
    		S.DateOfSale BETWEEN P.StartDate AND P.EndDate
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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