How do I create dynamic column names in a WHILE DATE Loop query?

Question

Here is my working query:

DECLARE @StartDate AS DATETIME
DECLARE @EndDate AS DATETIME
DECLARE @CurrentDate AS DATETIME

SET @StartDate = '2021-01-01'
SET @EndDate = GETDATE()
SET @CurrentDate = @StartDate

WHILE (@CurrentDate < @EndDate)
    BEGIN

        SELECT
        SUM(CASE WHEN [CreatedDateTime] BETWEEN @CurrentDate AND @CurrentDate + 6 THEN 1 ELSE 0 END)

        FROM Table
        WHERE OwnedBy = 'Service Desk'
        
        SET @CurrentDate = convert(varchar(30), dateadd(day,6, @CurrentDate), 101);
        
    END

which produces the following result:

I'm trying to create a dynamic column name that shows the Date range for each result.

For example:
The 1st result should have the column name: "Jan 1-Jan 7"
The 2nd result should have the column name: "Jan 8-Jan 14"
etc.

Is there a way to do this?

Thanks.

As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. — Dale K
– Dale K, Commented Feb 10, 2022 at 21:15
For dynamic column names you need dynamic SQL. Why not do that in your front end? Much easier. — Dale K
– Dale K, Commented Feb 10, 2022 at 21:18

Joel Coehoorn · Accepted Answer · 2022-02-10 21:38:49Z

This is a very unusual way to approach this kind of database problem. You might instead consider using two columns, without loops, like this:

DECLARE @StartDate AS DATETIME = '20210101';
WITH e1(n) AS
(
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)-- 10*1
,e2(n) AS (SELECT 1 FROM e1 CROSS JOIN e1 AS b) -- 10*10
,e3(n) AS (SELECT 1 FROM e1 CROSS JOIN e2) -- 10*100
,Dates as (
    SELECT DATEADD(d, ROW_NUMBER() over (ORDER BY n)-1, @StartDate) AS BaseDate 
    FROM e3 
)
SELECT d.BaseDate As PeriodStart -- datetime value
    , FORMAT(d.BaseDate, 'MMM dd')+ '-' + FORMAT(DATEADD(d, 6, d.BaseDate), 'MMM dd') as PeriodRange -- string value
    , COUNT(t.*) As TicketCount
FROM Dates d
INNER JOIN [Table] t ON t.[CreatedDateTime] >= d.BaseDate 
    AND t.[CreatedDateTime] < DATEADD(d, 7, d.BaseDate)
    AND t.OwnedBy='Service Desk'
WHERE d.BaseDate < CURRENT_TIMESTAMP
GROUP BY d.BaseDate;

This is likely to run many times faster, and will generally be easier to consume in client code and reporting tools.

One other notable change in here is instead of a BETWEEN expression for six days in the future, I used two separate conditions where the ending condition is an exclusive upper bound for seven days in the future, which is also generally the better way to handle date ranges.

Collectives™ on Stack Overflow

How do I create dynamic column names in a WHILE DATE Loop query?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related