Calculation of date in function

Question

   1      28/11/2011 ...

How do I write a function in SQL to implement the above pattern?

Answer 1

You could do something like this in SQL Server:

DECLARE @BaseDate DATE = '20111107';
DECLARE @EndDate DATE = GETDATE(); --Or the "end of dates in the database"
WITH RecursiveCTE AS (
    SELECT
        1 AS [Counter],
        @BaseDate AS [MyDate]
    UNION ALL
    SELECT
        [Counter] + 1,
        DATEADD(DAY, 7, MyDate)
    FROM
        RecursiveCTE
    WHERE
        MyDate < @EndDate)
SELECT * FROM RecursiveCTE OPTION (MAXRECURSION 0);

To handle dates that aren't exact and make this into a function you would do this:

--Function definition
CREATE FUNCTION SuperDuperDataCalculator (
    @BaseDate DATE = '20131016',
    @EndDate DATE = '20131020')
RETURNS @Results TABLE (
    [Counter] INT,
    [Date] DATE)
AS
BEGIN
    WITH RecursiveCTE AS (
        SELECT
            1 AS [Counter],
            @BaseDate AS [MyDate]
        UNION ALL
        SELECT
            [Counter] + 1,
            CASE WHEN DATEADD(DAY, 7, MyDate) > @EndDate THEN @EndDate ELSE DATEADD(DAY, 7, MyDate) END
        FROM
            RecursiveCTE
        WHERE
            MyDate < @EndDate)
    INSERT INTO
        @Results
    SELECT * FROM RecursiveCTE OPTION (MAXRECURSION 0);
    RETURN;
END;
GO

--Usage
SELECT * FROM SuperDuperDataCalculator('20131016', '20131020');

--Results
Counter Date
1   2013-10-16
2   2013-10-20

Note that we have to use a multi-statement table-valued function as there is a bug in SQL Server where it won't let you use OPTIONs in a simple table-valued function. The alternative would be to remove the OPTION (MAXRECURSION 0) from the function and remember to use this every time you reference it (i.e. a pretty poor alternative).

...and finally, if you wanted to just return the maximum counter value you could rewrite this as a scalar-valued function, i.e.:

--Function definition
CREATE FUNCTION SuperDuperDataCalculator (
    @BaseDate DATE = '20131016',
    @EndDate DATE = '20131020')
RETURNS INT
AS
BEGIN
    DECLARE @Results TABLE (
    [Counter] INT,
    [Date] DATE);
    DECLARE @ReturnValue INT;
    WITH RecursiveCTE AS (
        SELECT
            1 AS [Counter],
            @BaseDate AS [MyDate]
        UNION ALL
        SELECT
            [Counter] + 1,
            CASE WHEN DATEADD(DAY, 7, MyDate) > @EndDate THEN @EndDate ELSE DATEADD(DAY, 7, MyDate) END
        FROM
            RecursiveCTE
        WHERE
            MyDate < @EndDate)
    INSERT INTO
        @Results
    SELECT * FROM RecursiveCTE OPTION (MAXRECURSION 0);
    SELECT @ReturnValue = MAX([Counter]) FROM @Results;
    RETURN @ReturnValue;
END;
GO
SELECT dbo.SuperDuperDataCalculator('20131016', '20131020');

Answer 2

Try this - It will get all the weeks and assign a rownumber in the subquery. Then only select the records where row number = 1 because there might be more results for that week. So hence RowNo = 1

 SELECT ROW_NUMBER() OVER(ORDER BY RowNo) AS IncrementalWeek,dte
 FROM 
 (
      SELECT DISTINCT DATEPART(ww,CONVERT(VARCHAR(20),createdDate,111)) AS [week],
                      CONVERT(VARCHAR(20),createdDate,111) AS dte,
                      ROW_NUMBER() OVER(PARTITION BY DATEPART(ww,Convert(VARCHAR(20),createdDate,111)) ORDER BY DATEPART(ww,CONVERT(VARCHAR(20),createdDate,111))) AS RowNo 
      FROM YourTable

 ) AS tble
WHERE RowNo = 1
ORDER BY [week]