#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    3
    Rep Power
    0

    Query with Multiple Date Ranges


    Hi,
    First off, I am in the process of learning SQL so apologies in advance if I have trouble understanding or am not clear in my communication.

    I have two tables (see below)

    Table 1

    Columns (the combination of Account ID and Contract Start Date is unique)
    1) Account ID
    2) Contract Start Date (different for each Account ID)
    3) Contract End Date (different for each Account ID)
    4) Contract Amount

    Table 2
    Columns
    1) Date (MM/DD/YYYY format)
    2) Account ID
    3) # of Sales

    I want to know if it's possible to run a query that will sum up all sales by Account ID but only if the sales came in between the contract start and end dates unique to that Account ID. Is this doable?

    Appreciate any help and thanks in advance!

    Cheers!
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by ParadiseCity310
    Is this doable?
    depends

    what's the actual datatype of the datetime column?

    also, what database is this?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    3
    Rep Power
    0
    Appreciate the fast response!

    I am using SQL Server 2008 and the data type of the column is (date, not null)
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Code:
    SELECT table1.[Account ID]
         , SUM(table2.[# of Sales]) AS total_sales
      FROM table1
    INNER
      JOIN table2
        ON table2.[Account ID] = table1.[Account ID]  
       AND table2.[Date] BETWEEN table1.[Contract Start Date]
                             AND table1.[Contract End Date]
    GROUP
        BY table1.[Account ID]
    also, thread moved to SQL Server forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    3
    Rep Power
    0
    Thank You!

IMN logo majestic logo threadwatch logo seochat tools logo