createing view - SQL Server Forums

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

createing view

Author

Topic

khasim76
Starting Member

35 Posts

Posted - 2008-09-16 : 15:30:12

we have issuer table

We have another four tables

EQ_COMPANY with pk EQ_COMPANY
MF_PORTFOLIO with pk MF_PORTFOLIO
VA_PORTFOLIO with VA_PORTFOLIO
CE_COMPANY with CE_COMPANY

These four tables have company_names Column lists and these are listed in issuer table.

For example

Table a has mike
Table b has caty
Table c has tina
table d has matt

The issuer table will have

Mike
Caty
Tina
Matt

Company_name column changes by year in each of the tables and is versioned by columns issuer_version, company_version,portfolio_Version and company_version respectively in each of the tables.

The question
We need to company name changes (for example if company name changeed from mike to matt ) by year historical name

X002548
Not Just a Number

15586 Posts

Posted - 2008-09-16 : 15:52:35

We need sample data, DDL and expected results

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

khasim76
Starting Member

35 Posts

Posted - 2008-09-16 : 16:08:31

Expected RESULT IS

2005 2006 2007

mike matt katey

In this way, if the comapny name changes

DDL FOR TWO TABLES
USE [BILLING]
GO
/****** Object: Table [dbo].[EQ_COMPANY] Script Date: 09/16/2008 15:57:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[EQ_COMPANY](
[COMPANY_KEY] [int] NOT NULL,
[COMPANY_VERSION] [int] NOT NULL,
[GVKEY] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ADD1] [varchar](65) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ADD2] [varchar](65) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ADD3] [varchar](65) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ADD4] [varchar](65) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ADDZIP] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BUSDESC] [varchar](2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CITY] [varchar](24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CONM] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RECTYPE] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[COSTAT] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DLDTE] [datetime] NULL,
[DLRSN] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FAX] [varchar](14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FIC] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FYRC] [int] NULL,
[GGROUP] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[GIND] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[GSECTOR] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[GSUBIND] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IDBFLAG] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[INCORP] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LOC] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NAICS] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PHONE] [varchar](14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PRICAN] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PRIUSA] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SIC] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SPCINDCD] [int] NULL,
[SPCSECCD] [int] NULL,
[STATE] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SOURCE_KEY] [int] NULL,
[VALID_TO] [datetime] NULL,
[CURRENT_RECORD] [int] NULL,
[VALID_FROM] [datetime] NULL,
[DATE_CREATED] [datetime] NULL,
[DATE_MODIFIED] [datetime] NULL,
[DELETED_FROM_SOURCE] [int] NULL,
[CFO] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AUDITOR_KEY] [int] NULL,
[NAICS_SECTOR] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CIK] [int] NULL,
[ROWID] [uniqueidentifier] NOT NULL DEFAULT (newid()), CONSTRAINT [SYS_C0053678] PRIMARY KEY CLUSTERED (
[COMPANY_KEY] ASC,
[COMPANY_VERSION] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
USE [BILLING]
GO
ALTER TABLE [dbo].[EQ_COMPANY] WITH CHECK ADD CONSTRAINT [SYS_C0053951] FOREIGN KEY([SOURCE_KEY]) REFERENCES [dbo].[DIM_DATA_SOURCE] ([SOURCE_KEY])

--------------------------------------------------------------------------------------------------------

USE [BILLING]
GO
/****** Object: Table [dbo].[ISSUER] Script Date: 09/16/2008 15:58:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ISSUER](
[ISSUER_ID] [int] NOT NULL,
[ISSUER_VERSION] [int] NOT NULL,
[ISSUER_NAME] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ISSUER_CONTACT_NAME] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ISSUER_TYPE_ID] [int] NOT NULL,
[DATE_CREATED] [datetime] NULL,
[DATE_MODIFIED] [datetime] NULL,
[ISSUER_PARENT_ID] [int] NULL,
[ISSUER_FUND_PARENT_ID] [int] NULL,
[AUDITOR_KEY] [int] NULL,
[ROWID] [uniqueidentifier] NOT NULL CONSTRAINT [DF__ISSUER__ROWID__4BAC3F29] DEFAULT (newid()), CONSTRAINT [SYS_C0053715] PRIMARY KEY CLUSTERED (
[ISSUER_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
USE [BILLING]
GO
ALTER TABLE [dbo].[ISSUER] WITH CHECK ADD CONSTRAINT [SYS_C0053963] FOREIGN KEY([ISSUER_TYPE_ID]) REFERENCES [dbo].[DIM_ISSUER_TYPE] ([ISSUER_TYPE_ID])

SAMAPLE DATA FOR issuer table
100000,2,BLACK DOME ENERGY CORP,EDGAR J. HUFF, Treasurer,1,2003-12-15 08:25:05.000,2006-04-20 18:04:08.000,100000,NULL,9,A70EEBE8-D7FC-4845-B7B6-A6707EC765EF

for comapny
100000 1 002257 PO Box 4119 NULL NULL NULL 80437-4119 Operates an oil and gas concern that buys and sells producing oil and gas properties in the U.S. Co. also acquires interests in producing oil and gas leases for the purpose of resale of a portion of the working interest to industry participants, or for addition of reserves for its own account. Evergreen Black Dome Energy Corp I I 1999-12-13 00:00:00.000 03 NULL USA 12 1010 101020 10 10102020 D CO USA 211111 303-231-9059 NULL 01 1311 380 935 CO 2 2005-02-09 16:46:05.000 0 2003-01-01 00:00:00.000 2003-12-14 18:32:33.000 2005-02-09 17:46:22.000 0 EDGAR J. HUFF, Treasurer 9 Mining NULL D253A42C-AB79-43BB-944E-A3BFBE2A979C

khasim76
Starting Member

35 Posts

Posted - 2008-09-16 : 16:33:56

the four tables are versioned and will have all changed records

Issuer table has only current record

khasim76
Starting Member

35 Posts

Posted - 2008-09-16 : 16:48:28

i think i am dealing with multi versioned view.

khasim76
Starting Member

35 Posts

Posted - 2008-09-16 : 17:08:05

sorry the requirement is if the column name changed, then get the view with list of changed names