title: Using Calendar table to calculate fiscal week of year and business days in week
body: <p>I have a calendar table: </p>
  
  <pre><code>CREATE TABLE [dbo].[FIN_CalendarTable](
      [id] [int] IDENTITY(1,1) NOT NULL,
      [date] [datetime] NOT NULL,
      [fiscalStart] [datetime] NULL,
      [fiscalEnd] [datetime] NULL,
      [isHoliday] [int] NOT NULL,
      [isWorkday] [int] NOT NULL
   CONSTRAINT [PK_FIN_CalendarTable] PRIMARY KEY CLUSTERED 
  (
      [id] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  </code></pre>
  
  <p>I am wanting to calculate the qyt of business days in each week of the fiscal year.  I have the following query which returns the fiscal weeks and each business day of the fiscal period (250 records):</p>
  
  <pre><code>    SELECT  dbo.CalendarTable.fiscalStart
        , dbo.CalendarTable.fiscalEnd
        , COUNT(*)
        , dbo.CalendarTable.monthOfYearName
        , dbo.CalendarTable.monthStart
        , dbo.CalendarTable.monthEnd
        , DATEDIFF(DAY, dbo.CalendarTable.fiscalStart, [date]) / 7 + 1 WeekOfFiscalYear
  FROM    dbo.CalendarTable
  WHERE   dbo.CalendarTable.isHoliday=0
            AND dbo.CalendarTable.isWorkday=1
          AND ( dbo.CalendarTable.date  &gt;= CONVERT(DATETIME,'01/07/2012',103)
                AND dbo.CalendarTable.date &lt;= CONVERT(DATETIME,'30/06/2013',103) ) 
  GROUP BY dbo.CalendarTable.fiscalStart
        , dbo.CalendarTable.fiscalEnd
        , dbo.CalendarTable.monthOfYearName
        , dbo.CalendarTable.monthStart
        , dbo.CalendarTable.monthEnd
        , [dbo].[CalendarTable].[date]
  </code></pre>
  
  <p>I realise that this is occuring becuase the group by inlcudes the [dbo].[CalendarTable].[date] column.  Is there are better way to structure this query so that 52 (or when appropriate 53) records are returned with a total of the business days in each fiscal week?</p>
  
  <p>I could probably achieve the aggregate within the application but wanted to achieve a nicer set of results if at all possible.</p>