Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
goligol
Posting Yak Master
128 Posts |
Posted - 2012-11-12 : 11:49:56
|
Hi,I would like to repeat the following view for a time series data. instead of repeating the view and manually changing the year in the name of the view and in the content of the view, is the any way that I can use a simple command to create those views.instead of 1993 in this code, I want to use the years from 1994 to 2011.USE [BLS]GO/****** Object: View [dev].[A.nrg_prices_1993] Script Date: 11/12/2012 11:43:22 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE VIEW [dev].[A.nrg_prices_1993]ASSELECT a.source, a.end_use, price = CASE WHEN a.price = 0 THEN b.price ELSE a.price END FROM (SELECT source= case when SUBSTRING(msn,1,2)='ES' then 'ET' else SUBSTRING(msn,1,2) end, SUBSTRING(msn,3,2) AS end_use, state, [1993] AS price FROM EIA.dev.eia_pr_us WHERE SUBSTRING(msn,5,1) = 'D') a, (SELECT source= case when SUBSTRING(msn,1,2)='ES' then 'ET' else SUBSTRING(msn,1,2) end, State, [1993] AS price FROM EIA.dev.eia_pr_us ,dev.[B.nrg_price_by_finaluse_1993] WHERE SUBSTRING(msn,5,1) = 'D' AND SUBSTRING(msn,3,2) = 'TC' ) b WHERE a.source = b.source and a.State='US' and b.State = 'US'GO |
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-12 : 12:20:05
|
I can't think of a way to create the views automatically other than to use a smart (perhaps regex) editor to create the script for the views.Most experts would recommend against this type of database design, where you separate out the data into multiple tables with names containing the year (dev.[B.nrg_price_by_finaluse_1993]). Instead, what they suggest is that you create a single table, with year as another column. That would make querying, maintaining, and everything else easier.If you were to do that, you would simply have one view as well, and would query against that view with appropriate where clause to filter down to the year of interest.That said, I realize that more often than not, you have to work with what you are given - but if you have an opportunity to redesign the database, or if you are in the process of designing it consider doing it that way. |
|
|
|
|
|
|
|