SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to repeat a view for differnt years in a time
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

goligol
Posting Yak Master

128 Posts

Posted - 11/12/2012 :  11:49:56  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/12/2012 :  12:20:05  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 1.69 seconds. Powered By: Snitz Forums 2000