Return a Table of Payroll Dates from a SQL Stored Procedure

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.

  • Grantor does not have GRANT permission - issue
  • Get “next” row from SQL Server database and flag it in single transaction
  • Group DateTime into 5,15,30 and 60 minute intervals
  • Grouping by an alias
  • Database design to create tables on the fly
  • Folder Browser Dialog from remote machine perspective like the one SSMS uses
  • 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.

  • TSQL - How to URL Encode
  • how to structure an index for group by in Sql Server
  • Group by is throwing error in SQL Server
  • pagination in SQL server 2008
  • why varbinary instead of varchar
  • Parametric Sql Order
  • 2 Solutions collect form web for “Return a Table of Payroll Dates from a SQL Stored Procedure”

    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.

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

    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

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.