SQL how to select dates between 2 date parameters as columns in a stored procedure

Question

I need to write a stored procedure that takes 2 date parameters, sums up some data for that date, and returns a row with the dates inbetween as columns.

Im not sure where to start.

Lets say my stored procedure looks like this:

spGetAccountBalanceByDay(DateTime startDate, DateTime endDate)

I would like the column name to be formatted like this: F_{0}{1}{2} where {0} = year, {1} = Month, and {2} = Day.

so for the date 13/12/2014, my column would be called f_2014_12_13. I have a datasource that has dynamic properties which match ( as the grid in question can be run for any date range )

So in the SQL stored procedure, I want to loop between the 2 dates, sum the account balance for each date, and put the data in the column for that day.

So my table would look something like this returned by the stored procedure:

Account Ref | F_2014_12_13 | F_2014_12_14 | F_2014_12_15
------------------------------------------
ABB001        100            150            0

These queries can return one or more rows, I just need to know what function In SQL i should be looking to use, I know its possible to select columns dynamically just not sure how to do it.

Any advice would be appreciated.

Answer 1

Combining a few things I have found across the internet this is the solution I have come up with:

DECLARE @Columns VARCHAR(MAX)
DECLARE @StartDate AS DATETIME
DECLARE @EndDate AS DATETIME
DECLARE @Query AS VARCHAR(MAX)

SET @StartDate = '01 Jan 2012'
SET @EndDate = '31 Mar 2012'

;WITH dateRange as
(
    SELECT [Date] = DATEADD(dd, 1, DATEADD(dd, -1,@startDate))
    WHERE DATEADD(dd, 1, @startDate) < DATEADD(dd, 1, @endDate)
    UNION ALL
    SELECT DATEADD(dd, 1, [Date])
    FROM dateRange
    WHERE DATEADD(dd, 1, [Date]) < DATEADD(dd, 1,@endDate)
)

SELECT @Columns = COALESCE(@Columns, '[') + CONVERT(VARCHAR, [Date], 111) + '],['
FROM dateRange
OPTION (maxrecursion 0)

--delete last two chars of string (the ending ',[') and store columns in variable
SET @Columns = SUBSTRING(@Columns, 1, LEN(@Columns)-2)

SELECT @Columns

SET @Query = 
'
 SELECT *
 FROM 
 (
    SELECT 
                [PLSupplierAccount].[SupplierAccountNumber],
                [PLSupplierAccount].[SupplierAccountName],
                [PLPostedSupplierTran].[DueDate],
                [PLPostedSupplierTran].[GoodsValueInAccountCurrency] * [PLPostedSupplierTran].[DocumentToBaseCurrencyRate] AS [Value]
    FROM        [PLPostedSupplierTran]
    INNER JOIN  [PLSupplierAccount]
    ON          [PLSupplierAccount].[PLSupplierAccountID]
    =           [PLPostedSupplierTran].[PLSupplierAccountID]
    WHERE       [PLPostedSupplierTran].[DueDate]>= ''' + CONVERT(VARCHAR(50), @StartDate, 111) + ''' AND [PLPostedSupplierTran].[DueDate]<= ''' + CONVERT(VARCHAR(50), @EndDate, 111) + '''
 ) src
 PIVOT
 (
    SUM([Value])
    FOR src.[DueDate] IN (' + @Columns + ')
 ) AS PivotView
'

EXEC (@Query)