Return a Table of Payroll Dates from a SQL Stored Procedure

Question

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.

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.

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.

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.

Answer 1

I would use a CTE something like this one:

;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. Edit: You can use a parameter to figure out the correct limit to get the correct last date still, of course ;)

Answer 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