title: how to apply max(enddate) value to multiple lines based on company and calendar year
body: <p>I am writing a stored procedure to hold information about year / periods and dates but I am struggling with the last column I need to create.  It should effectively show the "end of year date".  </p>
  
  <p>The complication here is that sometimes company ID's are setup based on a calendar year, other times they aren't and the number of months/periods in each year can vary.  The duration of the first fiscal year also varies to subsequent years.  </p>
  
  <p>In the source table, there are entries per company, per year, per period/month, with an enddate field for each period/month.  I'd like my query to pull these lines into a new sp but include calculation of some additional fields to aide in the data selection and display.</p>
  
  <p>This is my sp so far:</p>
  
  <pre><code>SET ANSI_NULLS ON
  SET QUOTED_IDENTIFIER ON
  GO
  
  ALTER PROCEDURE [dbo].[GDM_GetCalDate2]
  (
    @FromPeriodMM INT ,
    @ToPeriodMM INT ,
    @company VARCHAR(12)
  )   AS 
      SET nocount ON
      SET dateformat dmy;
  
  
      SELECT DISTINCT
              p.yr ,
              p.period ,
              p.enddate AS Calenddate ,
              c.code AS Company ,
              c.yearcurt1 * 100 + c.percurt1 AS Current_YrPd_SecGrp1 ,
              c.yearcurt2 * 100 + c.percurt2 AS Current_YrPd_SecGrp2 ,
              c.yearcurt3 * 100 + c.percurt3 AS Current_YrPd_SecGrp3 ,
              YEAR(p.enddate) * 100 + MONTH(p.enddate) AS CalendarYrPd ,
              p.yr * 100 + p.period AS CodaYrPd ,
              @FromPeriodMM AS Codastartperiod ,
              @ToPeriodMM AS Codaendperiod ,
              ( @FromPeriodMM - 1 ) AS Codabegperiod ,
              CAST(LEFT(@ToPeriodMM, 4) - 1 AS NVARCHAR) + CAST(RIGHT(@ToPeriodMM, 2) AS NVARCHAR) AS 'Codayeperiod' ,
              LTRIM(STR(YEAR(Enddate)) + ' / ' + LEFT(DATENAME(m, enddate), 3) + ' / ' + '01') AS Calstartdate ,
              enddate AS Calenddate ,
              LTRIM(STR(YEAR(Enddate)) + ' / ' + LEFT(DATENAME(m, enddate), 3) + ' / ' + '01') AS Calbegdate
      --(                         
      --  SELECT  MAX(YEAR(p.enddate) * 100 + MONTH(p.enddate))
      --  FROM    oas_perlist p ,
      --          oas_company c
      --  WHERE   c.code = p.cmpcode
      --          AND c.code = @company
      --          AND YEAR(p.enddate) = YEAR(p.enddate) * 100 + MONTH(p.enddate)
      --          AND p.period NOT IN ( '9998', '9999' )
      --          AND p.lstseqno IN ( SELECT  MAX(p.enddate)
      --                              FROM    oas_perlist p ,
      --                                      oas_company c
      --                              WHERE   c.code = p.cmpcode
      --                                      AND c.code = @company
      --                                      AND p.period NOT IN ( '9998', '9999' )
      --                              GROUP BY YEAR(p.enddate) * 100 + MONTH(p.enddate) ,
      --                                      YEAR(p.enddate) ,
      --                                      p.enddate )
      --) 
      --  AS 'Calyedate'
      FROM    oas_company c
              INNER JOIN oas_perlist p ON c.code = p.cmpcode
      WHERE   c.code = @company
              AND p.period NOT IN ( '9998', '9999' )
      ORDER BY c.code ,
              CalendarYrPd
  </code></pre>
  
  <p>In my last column (commented out currently), I'd like to apply the enddate of the highest period for each year &amp; company combination to each line where the year of the enddate matches the year within the CalendarYrPd value (based on the company and year in question), and label as 'Calyedate'.  </p>
  
  <p>I've gotten as far as below which brings back the enddate values per company and year, but as I'm using the max for reference, it's only bringing back the max values and summarising (ie 6 lines - one for each year) </p>
  
  <pre><code>2009-06-30   (2009 Yearend date)
  2010-12-31   (2010 Yearend date)
  2011-12-31   (2011 Yearend date)
  2012-12-21   (2012 Yearend date)
  2013-12-31   (2013 Yearend date)
  2014-12-31   (2014 Yearend date)
  </code></pre>
  
  <p>The stored proceedure saves ok, but on running the full query I get an error:</p>
  
  <blockquote>
    <p>Msg 512, Level 16, State 1, Procedure GDM_GetCalDate2, Line 27
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, &lt;, &lt;= , >, >= or when the subquery is used as an expression.</p>
  </blockquote>
  
  <p>I tried to include the condition <code>Year(p.enddate) = year(CalendarYrPd)</code> in the first part of the select subquery but then I get a message "Invalid Column Name 'CalendarYrPd'.  I suspect this is cos it's not recognising this new column I've created earlier in the query. </p>
  
  <p>Could someone advise me how I should be looking at this to get what I need please?  I've tried all sorts but can't seem to get it.  I'm self taught SQL so it may well be something fundamental that I've missed / forgotten but eeitehr way, I'd be very grateful if someone could point me in the direction of home.</p>