Created
June 8, 2021 18:34
-
-
Save sblack4/a65992068d54539bd8e623c37daa420b to your computer and use it in GitHub Desktop.
insert a column with the last year of dates in it. This can be adjusted to any contiguous period but must be specified relative to the present.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
insert into myschema.mytable | |
with ten_numbers as (select 1 as num union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 0) | |
,generted_numbers AS | |
( | |
SELECT (1000*t1.num) + (100*t2.num) + (10*t3.num) + t4.num-5000 as gen_num | |
FROM ten_numbers AS t1 | |
JOIN ten_numbers AS t2 ON 1 = 1 | |
JOIN ten_numbers AS t3 ON 1 = 1 | |
JOIN ten_numbers AS t4 ON 1 = 1 | |
) | |
select (getdate()::date - gen_num)::date from generted_numbers | |
where gen_num between 0 and 365 | |
order by 1; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment