Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2005
    Posts
    3

    Unanswered: Query to display most active records

    Hello,

    I have two tables, Promotion and Promolocation. The Promotion table is used to set up promotions or sales, and consists of a PromoID, StartDate, and EndDate. Each PromoID is referenced in the Promolocation table, which is used to assign items to a promotion for various locations or stores. The Promolocation table consists of PromoID, LocID, SkuID, PromoPrice, and DiscLevel.

    There are times where an item or SkuID will exist in more than one promotion, however, our application is currently not intelligent enough to determine which promotion to use, so it sets the active promotion based on the StartDate being before other promotions' StartDate and the EndDate being after other promotions' EndDate.

    I want to find all promoid's where a sku exists in more than one promotion. I want to signify which promotion is active, using 1 as the first active promotion, 2 as the next active, 3 as the next, etc. To determine which promotion is the first active promotion, the StartDate must be before any of the other promotions' StartDate, and the EndDate must be after other promotions' EndDate. If the promotions' StartDate is after the other promotions' StartDate but not before the other promotions' EndDate, and the EndDate is before or on other promotions' EndDate, then that's the second active promotion. If the StartDate is the same as other promotions' StartDate, but the EndDate is before other promotions' EndDate, then that's the third active promotion.

    For example:

    PromoID StartDate EndDate
    ------- --------- -------
    PROMO1 1/1/2004 1/1/2006 (1st Active Promotion)
    PROMO2 2/1/2004 1/1/2006 (2nd Active Promotion)
    PROMO3 1/1/2004 12/1/2005 (3rd Active Promotion)

    Here's a query I am using to display all active promotions:

    select
    pl.promoid,
    pr.startdate,
    pr.enddate,
    pl.locid,
    pl.skuid,
    pl.promoprice,
    pl.disclevel
    from
    promolocation pl
    inner join
    promotion pr
    on
    pl.promoid = pr.promoid
    where
    pr.enddate >= getdate()

    Thanks for your help.

    D

  2. #2
    Join Date
    Jan 2005
    Location
    TempDb
    Posts
    228
    Provide DDL and sample data. For example, SKU is missing from your post.
    I love deadlines. I like the whooshing sound they make as they fly by. Douglas Adams

  3. #3
    Join Date
    Jan 2005
    Posts
    3
    Here's the DDL:

    CREATE TABLE [dbo].[Promotion] (
    [PromoID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [StartDate] [datetime] NOT NULL ,
    [EndDate] [datetime] NOT NULL ,
    [rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE [dbo].[PromoLocation] (
    [PromoID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [LocID] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [SkuID] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [PromoPrice] [money] NULL ,
    [DiscLevel] [float] NULL ,
    [rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
    ) ON [PRIMARY]
    GO

    And here's some of the output from my query:

    promoid startdate enddate locid skuid promoprice disclevel
    ---------- ------------------------------------------------------ ------------------------------------------------------ ---------------- ---------------- --------------------- -----------------------------------------------------
    SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 1 116 60.0000 0.20000000000000001
    SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 2 116 60.0000 0.20000000000000001
    SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 3 116 60.0000 0.20000000000000001
    SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 6 116 60.0000 0.20000000000000001
    20%_2 2004-12-06 00:00:00.000 2006-01-01 23:59:59.000 1 118B 99.0000 0.20000000000000001
    20%_2 2004-12-06 00:00:00.000 2006-01-01 23:59:59.000 2 118B 99.0000 0.20000000000000001
    20%_2 2004-12-06 00:00:00.000 2006-01-01 23:59:59.000 3 118B 99.0000 0.20000000000000001
    20%_2 2004-12-06 00:00:00.000 2006-01-01 23:59:59.000 6 118B 99.0000 0.20000000000000001
    SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 1 119 139.0000 0.20000000000000001
    SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 2 119 139.0000 0.20000000000000001
    SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 3 119 139.0000 0.20000000000000001
    SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 6 119 139.0000 0.20000000000000001
    Feb05Sale 2005-02-11 00:00:00.000 2005-02-15 23:59:59.000 1 125 135.0000 0.59999999999999998
    Feb05Sale 2005-02-11 00:00:00.000 2005-02-15 23:59:59.000 2 125 135.0000 0.59999999999999998
    Feb05Sale 2005-02-11 00:00:00.000 2005-02-15 23:59:59.000 3 125 135.0000 0.59999999999999998
    Feb05Sale 2005-02-11 00:00:00.000 2005-02-15 23:59:59.000 4 125 135.0000 0.59999999999999998
    Feb05Sale 2005-02-11 00:00:00.000 2005-02-15 23:59:59.000 6 125 135.0000 0.59999999999999998
    Feb05Sale 2005-02-11 00:00:00.000 2005-02-15 23:59:59.000 7 125 135.0000 0.59999999999999998
    Feb05Sale 2005-02-11 00:00:00.000 2005-02-15 23:59:59.000 8 125 135.0000 0.59999999999999998
    SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 1 13 195.0000 0.20000000000000001
    SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 2 13 195.0000 0.20000000000000001
    SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 3 13 195.0000 0.20000000000000001
    SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 6 13 195.0000 0.20000000000000001
    SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 1 20 220.0000 0.20000000000000001
    SepNov20% 2004-09-15 00:00:00.000 2006-01-01 23:59:59.000 2 20 220.0000 0.20000000000000001

    (25 row(s) affected)

  4. #4
    Join Date
    Jan 2005
    Posts
    3

    Unhappy

    Can somone help me with this problem?

    Thanks,

    D

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by uxphreak
    Can somone help me with this problem?
    what was the question?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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