0

I have a table of calendar events. I am trying to write a query that, for every row, returns 3 calculated rows for the event and days after it.

For example, if my table looks like:

Event People
Birthday 10
Christmas 15
4th of July 10

I'm trying to construct a query that returns something like:

Event People Day
Birthday 10 1
Birthday 8 2
Birthday 7 3
Christmas 15 1
Christmas 13 2
Christmas 12 3
4th of July 10 1
4th of July 8 2
4th of July 7 3

I'm not a SQL beginner but I haven't run into anything before where I had to generate additional rows in a single statement. Can anyone point me in the right direction? I don't know if I need loops or CTEs or a 2nd table or where to start.

4
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
    – Community Bot
    Jul 5, 2023 at 21:26
  • 2
    Are you using Microsoft SQL Server or MySQL? Your question is tagged with both, which conflict.
    – Dan Guzman
    Jul 5, 2023 at 21:32
  • 1
    It is not obvious where the numbers in the sample output come from.
    – Rick James
    Jul 5, 2023 at 22:12

2 Answers 2

2

Well there's many ways to solve your problem, based on the limited information you've provided. One way is to create a temporary numbers table with the Days (maybe this exists as a concrete table already in your database 🤷‍♂️) and then cross join it to your CalendarEvents table like so:

DROP TABLE IF EXISTS #Days;
CREATE TABLE #Days (DayNumber INT PRIMARY KEY CLUSTERED);
INSERT INTO #Days
VALUES (1), (2), (3);

SELECT
    CE.Event,
    IIF(D.DayNumber = 1, CE.People, CE.People - D.DayNumber) AS People
    D.DayNumber AS [Day]
FROM CalendarEvents AS CE
CROSS JOIN #Days AS D
ORDER BY CE.Event, D.DayNumber;

This assumes the function that describes your People column in your expected results dataset is People - Day, except on the first day where it's just People. Hard to say if that's true for your entire dataset, with only the limited example provided. (Feel free to explain what the actual function is otherwise.)

Of course you'll need to load more rows into the #Days temp table if you have more days you need to show too. Alternative sources for a list of days that you can use too are either a date dimensions table, which is globally helpful for a multitude of other problems, or a CTE based on an existing table using the ROW_NUMBER() window function like so:

WITH _Days AS
(
    SELECT 
        ROW_NUMBER() OVER 
        (
            ORDER BY 
                object_id,
                column_id
        ) AS DayNumber
    FROM sys.columns
)

SELECT
    CE.Event,
    IIF(D.DayNumber = 1, CE.People, CE.People - D.DayNumber) AS People
    D.DayNumber AS [Day]
FROM CalendarEvents AS CE
CROSS JOIN _Days AS D
WHERE D.DayNumber <= 3
ORDER BY CE.Event, D.DayNumber;
0
select eventTable,people-x.day,x.day
from event,
(select 1 as day union all select 2 union all select 3 as day) as x
2
  • Can you explain what this does so someone can understand your code and answer better? Jul 26, 2023 at 22:33
  • It just does a cartesian join with 3 rows generated from an inline "table" (the union query X). Because there is no join condition, each row in event is repeated for each row in "X". Jul 26, 2023 at 23:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.