SQL Exercise – 13

SQL Exercise:

You are given a table of ticket data that includes the TicketID, OpenDate, and CloseDate. The task is to generate a new row for each day the ticket was open. The output should include TicketID, OpenDate, and ActiveDate (a date representing each day the ticket was active).

Create table script:

CREATE TABLE Tickets (
TicketID INT PRIMARY KEY,
OpenDate DATE NOT NULL,
CloseDate DATE NOT NULL
);

Data insertion script:

INSERT INTO Tickets (TicketID, OpenDate, CloseDate)
VALUES
(1, '2023-12-01', '2023-12-03'),
(2, '2023-12-02', '2023-12-04'),
(3, '2023-12-05', '2023-12-07');

 

Solution:

WITH RecursiveDates AS (
SELECT 
TicketID, 
OpenDate, 
OpenDate AS ActiveDate,
CloseDate
FROM 
Tickets

UNION ALL

SELECT 
TicketID, 
OpenDate, 
DATEADD(DAY, 1, ActiveDate) AS ActiveDate,
CloseDate
FROM 
RecursiveDates
WHERE 
ActiveDate < CloseDate
)

SELECT 
TicketID,
OpenDate,
ActiveDate
FROM 
RecursiveDates
ORDER BY 
TicketID, ActiveDate;

Output:

 

 

Explaination:

The above query is using recursive CTE to achieve the result.

1. Anchor Member:
   • Retrieves the initial row for each ticket, setting ActiveDate equal to OpenDate.
2. Recursive Member:
   • Continues to generate rows by adding 1 day to the ActiveDate until it reaches the CloseDate.
3. Final Output:
   • The SELECT query extracts the TicketID, OpenDate, and ActiveDate and orders the result for readability.