Returning too many rows....

• egnagey

  SSC Rookie

  Points: 41

  More actions

  July 22, 2004 at 7:26 am

  #109251

  What I am trying to do here is a little weird, but this is what my client wants.  I have a table of roofs and a capital expenditure table associated with the roofs table.  They want every roof to be returned with the next occuring cap exp and that date.  My problem is if there are more than one cap exp I am getting both returned.  How do I get only the first one to return?  I have tried top 1, but that only returns the very first roof not the first occurance, and I have tried distinct on the roofid, but that doesnt' work either.  Any help would be greatly appreciated!

   

  select tblroofs.roofid, cap.estimatedcost as CapCost, cap.estimateddate

  from tblcapitalexpenditures as cap RIGHT OUTER JOIN  tblroofs

     on cap.RoofID = tblRoofs.RoofID

  Where [tblRoofs].[CompanyID] = @CompanyID

   and datepart(yyyy, cap.estimateddate) >= datepart(yyyy, dateadd(yyyy, 1, getdate()))

  group by tblroofs.RoofID, cap.estimatedcost, cap.estimateddate

  example of what is being returned and I only want the first 1115 to be returned:

  1095   36800.00     2008-01-01 00:00:00.000

  1096   219450.00    2008-01-01 00:00:00.000

  1115   29500.00     2005-01-01 00:00:00.000 

  1115   110700.00   2008-01-01 00:00:00.000

• JourneymanProgrammer

  SSCommitted

  Points: 1546

  More actions

  July 22, 2004 at 9:19 am

  #515710

  hey egnagey,

  try adding the following HAVING clause to your SQL to see if that is what you are looking for:

  SELECT

    tblroofs.roofid, cap.estimatedcost as CapCost, cap.estimateddate

  FROM

    tblcapitalexpenditures as cap RIGHT OUTER JOIN  tblroofs ON cap.RoofID = tblRoofs.RoofID

  WHERE

    [tblRoofs].[CompanyID] = @CompanyID AND

    DATEPART(yyyy, cap.estimateddate) >= DATEPART(yyyy, DATEADD(yyyy, 1, GETDATE()))

  GROUP BY

    tblroofs.RoofID, cap.estimatedcost, cap.estimateddate

  HAVING

    cap.estimateddate = MIN(cap.estimateddate)

  JP

• Razvan Socol

  SSCarpal Tunnel

  Points: 4811

  More actions

  July 25, 2004 at 11:29 pm

  #516024

  JP, the HAVING clause has no effect in this query, because the estimateddate is part of the GROUP BY clause (and the MIN function is evaluated for each group and, of course, each group has only one date).

  I think the query should be written like this:

  SELECT r.roofid, c1.estimatedcost as CapCost, c1.estimateddate
  FROM tblcapitalexpenditures c1
  INNER JOIN tblroofs r ON c1.RoofID = r.RoofID
  WHERE r.CompanyID = @CompanyID AND YEAR(cap.estimateddate)> YEAR(GETDATE())+1
  AND c1.estimateddate = (
   SELECT MIN(c2.estimateddate) FROM tblcapitalexpenditures c2
   WHERE c1.RoofID = c2.RoofID
  )

  I have replaced the outer join with an inner join because an outer join seems unnecessary to me. If it is really needed (if it is possible that a "roof" has no "capital expenditures" and you want that "roof" selected), the query would be:

  SELECT r.roofid, c1.estimatedcost as CapCost, c1.estimateddate
  FROM tblcapitalexpenditures c1
  RIGHT JOIN tblroofs r ON c1.RoofID = r.RoofID
  WHERE r.CompanyID = @CompanyID AND YEAR(cap.estimateddate)> YEAR(GETDATE())+1
  AND (c1.estimateddate IS NULL OR c1.estimateddate = (
   SELECT MIN(c2.estimateddate) FROM tblcapitalexpenditures c2
   WHERE c1.RoofID = c2.RoofID
  ))

  Razvan