使用日历表计算每周的财政周和工作日。

I have a calendar table:

我有一个日历表:

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]

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):

我想计算本财年每个星期的营业日数。我有以下查询，返回会计期间的会计周和每个工作日(250记录):

    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  >= CONVERT(DATETIME,'01/07/2012',103)
              AND dbo.CalendarTable.date <= 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]

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?

我意识到，这是由于组内的[dbo]。[日历]。[日期]列。是否有更好的方法来构造这个查询，以便在每个财政周中返回52条记录(或在适当的情况下是53条)，并返回总共的工作日?

I could probably achieve the aggregate within the application but wanted to achieve a nicer set of results if at all possible.

我可能可以在应用程序中实现聚合，但如果可能的话，我希望获得一组更好的结果。

2 个解决方案

#1

I’d rather try to create an UDF that would give me the number of work days or total number of weeks than trying to get this info from query itself. You have all these columns in GROUP BY statement only so you can count the number of records and you can probably simplify the query by using UDF.

我宁愿尝试创建一个UDF，它会给我工作天数或总数周，而不是试图从查询本身获取这个信息。所有这些列都在GROUP BY语句中，这样就可以计数记录的数量，并且可以使用UDF简化查询。

Here is a post that shows how to calculate the difference between two dates in workdays. How to calculate difference between two dates in workdays

这里有一篇文章展示了如何计算两个工作日之间的差异。如何计算两个工作日之间的差异

#2

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?

是否有更好的方法来构造这个查询，以便在每个财政周中返回52条记录(或在适当的情况下是53条)，并返回总共的工作日?

It's not entirely clear what you mean by business days, but if by that you mean work days, then I think you need something along these lines.

现在还不完全清楚你所说的工作日是什么意思，但如果你指的是工作日，那么我认为你需要一些类似的东西。

SELECT fiscalStart
     , fiscalEnd
     , sum(isWorkday)
FROM dbo.CalendarTable
WHERE date >= CONVERT(DATETIME,'01/07/2012',103)
  AND date <= CONVERT(DATETIME,'30/06/2013',103)
GROUP BY fiscalStart
       , fiscalEnd
ORDER BY fiscalStart

This assumes that, for each date, "fiscalStart" means the first day of the fiscal week and "fiscalEnd" means the last day of the fiscal week. If that's not what those columns mean, edit your question, paste a couple of weeks worth of INSERT statements, along with expected output.

这假设，对于每一个日期，“财政开始”意味着财政周的第一天，“财政年度”意味着财政周的最后一天。如果这不是这些列的意思，编辑您的问题，粘贴几个星期的插入语句，以及预期的输出。

#1