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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to repeat a view for differnt years in a time

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 ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dev].[A.nrg_prices_1993]
AS
SELECT 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.
Go to Top of Page
   

- Advertisement -