Results 1 to 2 of 2
  1. #1
    Join Date
    May 2013
    Posts
    1

    Unanswered: Date Relationships

    I am making a database to track telemarketers orders and calls

    Here is my problem

    In Table 1 I have...
    sales #/order amount/order volume/date

    In Table 2 I have...
    sales #/call length/# of calls/date

    I would like my query to output
    sales #/order amount(sum)/order volume(sum)/call length(sum)/# of calls(sum)/date(by day)

    Seems simple enough... but I can't seem to make this work

  2. #2
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    I have no idea if you have a 1 to many or a many to many relationship between these two tables. Nor do I have any idea of what the data would really be.

    So, I will take a chance and answer it with some guesses:

    Code:
    DECLARE @t1 TABLE (
         SaleNumber   int
       , OrderAmount  int
       , OrderVolume  int
       , OrderDate    datetime
       )
    
    DECLARE @t2 TABLE (
         SaleNumber   int
       , CallLength   int
       , NumberCalls  int
       )
       
    INSERT INTO @t1 (SaleNumber, OrderAmount, OrderVolume, OrderDate)
       VALUES
          (1, 100, 25, '2012-01-01'), 
          (2, 120, 10, '2012-03-05'),
          (3, 50,  5,  '2012-02-01');
          
    INSERT INTO @t2 (SaleNumber, CallLength, NumberCalls)
       VALUES
          (1, 50, 3), (1, 20, 4),
          (1, 10, 1), (1, 100, 8),
          (2, 25, 5), (2, 1, 1),
          (2, 99, 1), (2, 3, 4);
          
    SELECT t1.OrderDate, t1.SaleNumber, 
           MIN(T1.OrderAmount) OrderAmt, MIN(t1.OrderVolume) OrderVol, 
           SUM(t2.CallLength) CallLen, SUM(t2.NumberCalls) NumCalls
      FROM @t1 as t1
     INNER JOIN @t2 t2 on T2.SaleNumber = T1.SaleNumber
     GROUP BY T1.OrderDate, T1.SaleNumber

Posting Permissions

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