I was checking the other day whether data had been archived from the production environment. The easiest way I could think of to do this was to summarise the relevant tables by year, and see how many rows were added for each of the years.
The very simple queries below were used on all of the tables that were in the archive set. Note that if you were doing this you’d probably be checking the F0911… Strange that I did not need to!
The only interesting thing about this post is the round command really, allowing you to group by year julian date style
select count(1), round(rpupmj, -3) from uadta.f03b11 group by round(rpupmj, -3) order by 2 desc;
select count(1), round(rzupmj, -3) from uadta.f03b14 group by round(rzupmj, -3) order by 2 desc;
select count(1), round(ryupmj, -3) from uadta.f03b13 group by round(ryupmj, -3) order by 2 desc;
1 comment:
Great tip Shannon!
I dressed it up just a little:
select int(substr(rpupmj,1,3))+1900 recordyear, count(1) recordCount from proddta.f03b11 group by int(substr(rpupmj,1,3))+1900 order by 1 desc;
select int(substr(ryupmj,1,3))+1900 recordyear, count(1) recordCount from proddta.f03b13 group by int(substr(ryupmj,1,3))+1900 order by 1 desc;
select int(substr(rzupmj,1,3))+1900 recordyear, count(1) recordCount from proddta.f03b14 group by int(substr(rzupmj,1,3))+1900 order by 1 desc;
Post a Comment