从SQL存储过程返回工资核算日期表

I'm working with SQL Server Reporting Services 2008, which is somewhat new to me, as most of my experience is with LAMP development. In addition, moving most of the logic to SQL as stored procedures is something I'm not very familiar with, but would like to do. Any help or direction would be greatly appreciated.

我正在使用SQL Server Reporting Services 2008,这对我来说有点新鲜,因为我的大部分经验都是使用LAMP开发。另外,将大部分逻辑移动到SQL作为存储过程是我不太熟悉但想做的事情。任何帮助或方向将不胜感激。

I need a list of acceptable payroll dates in the form of a table to use as the allowed values for a report parameter. Ideally, the person will be able to select this payroll date from the drop-down provided by the report parameter, which will then be used in the dataset to pull data from a table. I would like the logic to be stored on the SQL server if possible, as this is something that will most likely be used on a few other reports.

我需要一个表格形式的可接受的工资核算日期列表,以用作报告参数的允许值。理想情况下,此人将能够从报告参数提供的下拉列表中选择此工资核算日期,然后将在数据集中使用该下拉列表从表中提取数据。如果可能的话,我希望逻辑存储在SQL服务器上,因为这很可能会用在其他一些报告上。

The logic to create the list of dates is rather simple. It starts with the oldest payroll date that is need by the system (sometime in 2007) and simply goes every two weeks from there. The procedure or function should return a table that contains all these dates up to and including the nearest upcoming payroll date.

创建日期列表的逻辑非常简单。它从系统需要的最早的工资日期开始(2007年的某个时间),并且从那里开始每两周一次。过程或函数应返回一个表,其中包含所有这些日期,包括最近的即将到来的工资日期。

It seems to me that the way to go about this would be a procedure or function that creates a temporary table, adds to it the list of dates, and then returns this table so that the report parameter can read it. Is this an acceptable way to go about it?

在我看来,解决这个问题的方法是创建临时表的过程或函数,向其添加日期列表,然后返回此表以便报表参数可以读取它。这是一种可以接受的方式吗?

Any ideas, examples, or thoughts would be greatly appreciated.

任何想法,例子或想法将不胜感激。

2 个解决方案

#1

I would use a CTE something like this one:

我会使用像这样的CTE:

;WITH PayPeriod AS (
    SELECT @DateIn2007 AS p UNION ALL
    SELECT DATEADD(dd, 14, p) as P FROM PayPeriod WHERE p < GetDate() )
SELECT p FROM PayPeriod
OPTION ( MAXRECURSION 500 )

The MAXRECURSION and/or where parameter limits the number of dates it will generate.

MAXRECURSION和/或where参数限制它将生成的日期数。

You can use a parameter to figure out the correct limit to get the correct last date still, of course.

当然,您可以使用参数来确定正确的限制,以获得正确的最后日期。

#2

try something like this:

尝试这样的事情:

;with AllDates AS
(
    SELECT CONVERT(datetime,'1/1/2007') AS DateOf
    UNION ALL
    SELECT DateOf+14
        FROM AllDates
    WHERE DateOf<GETDATE()+14
)
SELECT * FROM AllDates
OPTION (MAXRECURSION 500)

you can put this in a view or function.

你可以把它放在视图或功能中。

However, I would suggest that instead of presenting a select box of this many values, why not just have two text box fields: start date and end date and default them to reasonable values, just my 2 cents

但是,我建议不要只提供这么多值的选择框,为什么不只是有两个文本框字段:开始日期和结束日期,并将它们默认为合理的值,只是我的2美分

#1