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

    Join Date
    Oct 2005
    Posts
    137
    Rep Power
    11

    Distinct dates and aggregate


    I have this table of fund level policy data. It gets loaded daily, and each policy can have multiple lines per day. I am trying to get the summed value of all those lines per policy, on the earliest date that it entered the table. This is the query I have:

    Code:
    SELECT DISTINCT Fund1.PolicyNumber, Fund1.ValuationDate AS Earliest_Val_Date, SUM(Fund1.PolicyValue) AS Earliest_Policy_Value
    FROM         tbl_PolicyInfo AS Fund1
    WHERE     (Fund1.Valuation_Date IN
                              (SELECT     MIN(fund1.ValuationDate) AS Expr1
                                FROM          tbl_PolicyInfo AS fund1))
    GROUP BY PolicyNumber, ValuationDate
    ORDER BY PolicyNumber
    All that this is doing is returning the very first policy that entered, not the earliest date for every policy. Im struggling with this, can anyone help? This is where I am starting, but i also need to do the same query for the Maximum date as well. Thanks in advance
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,374
    Rep Power
    391
    Code:
    select Fund1.PolicyNumber,
           Fund1.ValuationDate as Earliest_Val_Date,
           SUM(Fund1.PolicyValue) as Earliest_Policy_Value
      from tbl_PolicyInfo as Fund1
     where Fund1.Valuation_Date in
          (select MIN(fund1.ValuationDate) 
             from tbl_PolicyInfo
            where tbl_PolicyInfo.PolicyNumber = Fund1.PolicyNumber)
     group
        by PolicyNumber,
           ValuationDate
     order
        by PolicyNumber
    The distinct attribute is superfluous in this case.

IMN logo majestic logo threadwatch logo seochat tools logo