Dates returned as columns in SQL Select

Question

My user will submit a FromDate and a ToDate. What I want to happen is to select the dates that fall in between these dates, which I have accomplished with the script below. The dates will by dynamic.

DECLARE @fromDateParam DATETIME = '2022-01-24 00:00:00.000'
DECLARE @toDateParam DATETIME = '2022-01-29 00:00:00.000'

;WITH fnDateNow(DayOfDate) AS
(
    SELECT @fromDateParam AS TransactionDate

    UNION ALL

    SELECT DayOfDate + 1 
    FROM fnDateNow 
    WHERE DayOfDate < @toDateParam
) 
SELECT fnDateNow.DayOfDate AS TransactionDate 
FROM fnDateNow

This returns that dates as rows. What I am looking for is a way to make these dates return as the columns for a different script.

This table is called DailyTransactionHeader and it has a column [TransactionDate] and another one called [Amount].

There is the probability that their is not a DailyTransactionHeader with the specified Date for this I am looking to return 0.

So I am trying to have the data look like this (I formatted the date) There would be more than one row, but I just wanted to show an example of what I am trying to accomplish.

I appreciate any help,

Thanks

Answer 1

You can do it using dynamic sql. For example:

CREATE PROCEDURE [GET_DATE_TABLE] 
(
    @FROMDATE DATETIME, 
    @TODATE DATETIME
)
AS
DECLARE @PDATE DATETIME 
DECLARE @SQL VARCHAR(MAX) 
DECLARE @SEP VARCHAR(10)

    SET @PDATE = @FROMDATE
    SET @SQL = 'SELECT '
    SET @SEP = ''
    
    WHILE @PDATE < @TODATE
    BEGIN
        SET @SQL = @SQL + @SEP + 'NULL as [' + CONVERT(VARCHAR, CONVERT(DATE, @PDATE)) + ']'
        
        SET @PDATE = @PDATE + 1
        SET @SEP = ', '
    END;

    EXEC(@SQL)

Test Example:

DECLARE @fromDateParam DATETIME = '2022-01-24 00:00:00.000'
DECLARE @toDateParam DATETIME = '2022-01-29 00:00:00.000'
exec dbo.GET_DATE_TABLE @fromDateParam, @toDateParam