I have a currency converter table where I'm missing dates on weekends. Using this query solves this by adding Fridays value to the following Saturday and Sunday. I've created this query that returns the table I would like to be my new currency_converter table. How can I save it as currency_converter_new? my table looks like this right now:
currency_converter:
| time_period | obs_value | currency |
| ------------ | ------------ | ------------ |
| 20.02.2023 | 10,9683 | EUR |
| 20.02.2023 | 147,3 | DKK |
| 17.02.2023 | 11,015 | EUR |
| 17.02.2023 | 147,92 | DKK |
This is the output I would like to have in my other table. currency_converter_new:
time_period | obs_value | currency |
---|---|---|
20.02.2023 | 10,9683 | EUR |
20.02.2023 | 147,3 | DKK |
19.02.2023 | 11,015 | EUR |
19.02.2023 | 147,92 | DKK |
18.02.2023 | 11,015 | EUR |
18.02.2023 | 147,92 | DKK |
17.02.2023 | 11,015 | EUR |
17.02.2023 | 147,92 | DKK |
Using the statement below, I'm able to achieve what I want, but not sure how to get this to replace my currency_converter_new table.
with currency AS
(SELECT *, LEAD(time_period) OVER (PARTITION BY valuta ORDER BY time_period) as next_time_period
FROM currency_converter
)
SELECT c.day as time_period, t.obs_value, t.valuta
FROM dim_calendar c
JOIN currency t
ON c.day BETWEEN t.time_period and ISNULL(DATEADD(day, -1, t.next_time_period), t.time_period)
Any suggestions on how to solve this?
I have tried using the INSERT INTO, but can't seem to get that to work. I also couldn't get this syntax to work. tried adding INSERT INTO before my SELECT like this:
with currency AS
(SELECT *, LEAD(time_period) OVER (PARTITION BY valuta ORDER BY
time_period) as next_time_period
FROM currency_converter
);
INSERT INTO (currency_converter(time_period, obs_value, valuta)
SELECT * FROM (
SELECT c.day as time_period, t.obs_value, t.valuta
FROM dim_calendar c
JOIN currency t
ON c.day BETWEEN t.time_period and ISNULL(DATEADD(day, -1,
t.next_time_period), t.time_period)
)
I will truncate my currency_converter_new table before running this.
2条答案
按热度按时间h79rfbju1#
To be honest, it's not clear what you wanted to show, because this is some kind of torn-out request without context.
But if I understand correctly- do you want to insert or update from the query using the WITH expression?
See the example below:
PS: the temporary table is here for an example, you can use your table and your query, I showed you how to use the WITH expression in table insertion and update operations.
PPS: also using WITH for the MERGE statement
for some more detailed solution, you need to describe your question in detail and an example of the data
mlnl4t2r2#
It was as easy as just adding a INSERT INTO statement before the with clause.