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.

  • CREATE VIEW must be the only statement in the batch
  • Case statement for Order By clause with Desc/Asc sort
  • Code version control when developing in .NET/SQL Server 2005 on a Mac
  • DataGridView live display of datatable using virtual mode
  • Use Always Encrypted on decimal(18,2)
  • TSQL Averaging over datepart
  • 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.

  • SQL query help, conditional join
  • How to Ignore “Duplicate Key” error in T-SQL (SQL Server)
  • CASE WHEN THEN in hours ELSE in days
  • Array parameter in CFQL / stored procedure method
  • unresolved reference to object .
  • To create package in ssis for xml file and parse it and get required columns in the table sql
  • 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.