Yeah it was just a quick rehash of the view, limiting the information
for demo purposes the actual view(vw_test) is below:
CREATE VIEW dbo.vw_test
AS
SELECT dbo.vw_Invoices_I.ID_Company_Number,
dbo.vw_Invoices_I.ID_Item_Code, dbo.vw_Invoices_I.I_Order_Number,
dbo.vw_Invoices_I.I_Order_Line_Number,
dbo.vw_Invoices_I.I_Invoice_Number, dbo.vw_Invoices_I.I_Customer_Number,
dbo.vw_Invoices_I.I_Delivery_Address_Code,
dbo.vw_Invoices_I.I_Line_Value *
dbo.tbl_Lagged_Sales.Percentage_Of_Sales AS Expr1,
dbo.vw_Invoices_I.I_VAT_Value,
dbo.vw_Invoices_I.I_Discount_Value, dbo.vw_Invoices_I.I_Standard_Cost,
dbo.vw_Invoices_I.I_Line_Cost_Value
FROM dbo.tbl_Lagged_Sales INNER JOIN
dbo.vw_Invoices_I ON
dbo.tbl_Lagged_Sales.Supplier_Code = dbo.vw_Invoices_I.ID_Company AND
dbo.tbl_Lagged_Sales.Lag_Product_Group =
dbo.vw_Invoices_I.ID_Product_GroupHi
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tbl_Invoices_I_DB_TEST]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tbl_Invoices_I_DB_TEST]
GO
CREATE TABLE [dbo].[tbl_Invoices_I_DB_TEST] (
[I_Company_Number] [char] (2) COLLATE Latin1_General_CI_AS NOT NULL ,
[I_Order_Number] [char] (7) COLLATE Latin1_General_CI_AS NOT NULL ,
[I_Order_Line_Number] [smallint] NOT NULL ,
[I_Invoice_Number] [char] (7) COLLATE Latin1_General_CI_AS NOT NULL ,
[I_Disp_Seq_No] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[I_Item_Code] [char] (15) COLLATE Latin1_General_CI_AS NULL ,
[I_Pack_Item_Code] [char] (15) COLLATE Latin1_General_CI_AS NULL ,
[I_Stockroom] [char] (2) COLLATE Latin1_General_CI_AS NULL ,
[I_Line_Value] [numeric](17, 2) NOT NULL ,
[I_VAT_Value] [numeric](17, 2) NULL ,
[I_Discount_Value] [numeric](17, 2) NULL ,
[I_Standard_Cost] [numeric](17, 2) NULL ,
[I_Line_Quantity] [numeric](13, 3) NOT NULL ,
[I_Week_Number] [int] NULL ,
[I_Period_Number] [int] NULL ,
[I_Transaction_Type] [smallint] NULL ,
[I_Transaction_Date] [int] NULL ,
[I_Customer_Number] [char] (8) COLLATE Latin1_General_CI_AS NULL ,
[I_Delivery_Address_Code] [char] (3) COLLATE Latin1_General_CI_AS NULL
,
[I_Line_Cost_Value] [numeric](17, 2) NOT NULL ,
[I_Status] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
[I_Parent_Line_Number] [smallint] NULL ,
[I_Reason_Code] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
[I_Print_Flag] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
[I_Sales_Analysis_Update_Flag] [char] (1) COLLATE Latin1_General_CI_AS
NULL ,
[I_Item_Type] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
[I_Sales_Type] [int] NULL ,
[I_Month_Number] [tinyint] NOT NULL ,
[I_Year_Number] [smallint] NOT NULL ,
[I_Transaction_Date_PC] [datetime] NOT NULL ,
[I_Gross_Margin] [numeric](17, 2) NULL ,
[I_Date_Downloaded] [datetime] NULL ,
[Flagged_for_Exception] [bit] NULL
) ON [PRIMARY]
GO
CREATE VIEW dbo.vw_Invoices_I
AS
SELECT dbo.vw_Item_Details_ID.*, dbo.tbl_Invoices_I_DB_TEST.*,
dbo.vw_Customer_Details.*
FROM dbo.tbl_Invoices_I_DB_TEST INNER JOIN
dbo.vw_Customer_Details ON
dbo.tbl_Invoices_I_DB_TEST.I_Company_Number =
dbo.vw_Customer_Details.CD_Company_Number AND
dbo.tbl_Invoices_I_DB_TEST.I_Customer_Number =
dbo.vw_Customer_Details.CD_Customer_Number AND
dbo.tbl_Invoices_I_DB_TEST.I_Delivery_Address_Code
= dbo.vw_Customer_Details.CD_Dseq INNER JOIN
dbo.vw_Item_Details_ID ON
dbo.tbl_Invoices_I_DB_TEST.I_Company_Number =
dbo.vw_Item_Details_ID.ID_Company_Number AND
dbo.tbl_Invoices_I_DB_TEST.I_Item_Code =
dbo.vw_Item_Details_ID.ID_Item_Code
CREATE VIEW dbo.vw_Item_Details_ID
AS
SELECT dbo.tbl_Item_Details_ID.ID_Company_Number,
dbo.tbl_Item_Details_ID.ID_Item_Code,
dbo.tbl_Item_Details_ID.ID_Description,
dbo.tbl_Item_Details_ID.ID_STD_Cost,
dbo.tbl_Item_Details_ID.ID_Product_Type,
dbo.tbl_ILU_Z_PTYP.Z_Product_Type_Description,
dbo.tbl_Item_Details_ID.ID_Company,
dbo.tbl_ILU_A_COMP.A_Company_Description,
dbo.tbl_Item_Details_ID.ID_Brand_Type,
dbo.tbl_ILU_B_BTYP.B_Brand_Type_Description,
dbo.tbl_Item_Details_ID.ID_Brand,
dbo.tbl_ILU_C_BRND.C_Brand_Description,
dbo.tbl_Item_Details_ID.ID_Range,
dbo.tbl_ILU_D_RANG.D_Range_Description, dbo.tbl_Item_Details_ID.ID_Item,
dbo.tbl_ILU_E_ITEM.E_Item_Description,
dbo.tbl_Item_Details_ID.ID_Function_Description,
dbo.tbl_ILU_F_DESC.F_Description_Description,
dbo.tbl_Item_Details_ID.ID_Det_Fuel,
dbo.tbl_ILU_G_DET_FUEL.G_Det_Fuel_Description,
dbo.tbl_Item_Details_ID.ID_Colour,
dbo.tbl_ILU_H_COLR.H_Colour_Description,
dbo.tbl_Item_Details_ID.ID_TypeCat,
dbo.tbl_ILU_I_TYPECAT.I_TypeCat_Description,
dbo.tbl_Item_Details_ID.ID_DetFunc,
dbo.tbl_ILU_J_DETFUNC.J_DETFUNC_Description,
dbo.tbl_Item_Details_ID.ID_Function,
dbo.tbl_ILU_K_FCTN.K_Function_Description,
dbo.tbl_Item_Details_ID.ID_Owner,
dbo.tbl_ILU_L_OWNR.L_Owner_Description,
dbo.tbl_Item_Details_ID.ID_Application,
dbo.tbl_ILU_M_APPL.M_Application_Description,
dbo.tbl_Item_Details_ID.ID_Planning_Group,
dbo.tbl_ILU_O_PLANG.O_Planning_Group_Description,
dbo.tbl_Item_Details_ID.ID_Planning_Sub_Group,
dbo.tbl_ILU_P_PLNSG.P_Planning_Sub_Group_Descripti on,
dbo.tbl_Item_Details_ID.ID_Product_Group,
dbo.tbl_ILU_O_GROP.O_PoductGroup_Description,
dbo.tbl_Item_Details_ID.ID_Top_Fuel,
dbo.tbl_ILU_N_TFUL.N_TopFuel_Description,
dbo.tbl_Item_Details_ID.ID_Date_Last_Manufactured,
dbo.tbl_Item_Details_ID.ID_Model, dbo.tbl_ILU_P_MODL.P_Model_Description
FROM dbo.tbl_Item_Details_ID INNER JOIN
dbo.tbl_ILU_P_MODL ON
dbo.tbl_Item_Details_ID.ID_Model = dbo.tbl_ILU_P_MODL.P_MODL_ID LEFT
OUTER JOIN
dbo.tbl_ILU_A_COMP ON
dbo.tbl_Item_Details_ID.ID_Company = dbo.tbl_ILU_A_COMP.A_COMP_ID LEFT
OUTER JOIN
dbo.tbl_ILU_I_TYPECAT ON
dbo.tbl_Item_Details_ID.ID_TypeCat = dbo.tbl_ILU_I_TYPECAT.I_TYPECAT_ID
LEFT OUTER JOIN
dbo.tbl_ILU_E_ITEM ON
dbo.tbl_Item_Details_ID.ID_Item = dbo.tbl_ILU_E_ITEM.E_ITEM_ID LEFT
OUTER JOIN
dbo.tbl_ILU_G_DET_FUEL ON
dbo.tbl_Item_Details_ID.ID_Det_Fuel =
dbo.tbl_ILU_G_DET_FUEL.G_DET_FUEL_ID LEFT OUTER JOIN
dbo.tbl_ILU_J_DETFUNC ON
dbo.tbl_Item_Details_ID.ID_DetFunc = dbo.tbl_ILU_J_DETFUNC.J_DETFUNC_ID
LEFT OUTER JOIN
dbo.tbl_ILU_N_TFUL ON
dbo.tbl_Item_Details_ID.ID_Top_Fuel = dbo.tbl_ILU_N_TFUL.N_TFUL_ID LEFT
OUTER JOIN
dbo.tbl_ILU_O_GROP ON
dbo.tbl_Item_Details_ID.ID_Product_Group = dbo.tbl_ILU_O_GROP.O_GROP_ID
LEFT OUTER JOIN
dbo.tbl_ILU_C_BRND ON
dbo.tbl_Item_Details_ID.ID_Brand = dbo.tbl_ILU_C_BRND.C_BRND_ID LEFT
OUTER JOIN
dbo.tbl_ILU_K_FCTN ON
dbo.tbl_Item_Details_ID.ID_Function = dbo.tbl_ILU_K_FCTN.K_FCTN_ID LEFT
OUTER JOIN
dbo.tbl_ILU_M_APPL ON
dbo.tbl_Item_Details_ID.ID_Application = dbo.tbl_ILU_M_APPL.M_APPL_ID
LEFT OUTER JOIN
dbo.tbl_ILU_P_PLNSG ON
dbo.tbl_Item_Details_ID.ID_Planning_Sub_Group =
dbo.tbl_ILU_P_PLNSG.P_PLNSG_ID LEFT OUTER JOIN
dbo.tbl_ILU_O_PLANG ON
dbo.tbl_Item_Details_ID.ID_Planning_Group =
dbo.tbl_ILU_O_PLANG.O_PLANG_ID LEFT OUTER JOIN
dbo.tbl_ILU_L_OWNR ON
dbo.tbl_Item_Details_ID.ID_Owner = dbo.tbl_ILU_L_OWNR.L_OWNR_ID LEFT
OUTER JOIN
dbo.tbl_ILU_H_COLR ON
dbo.tbl_Item_Details_ID.ID_Colour = dbo.tbl_ILU_H_COLR.H_COLR_ID LEFT
OUTER JOIN
dbo.tbl_ILU_F_DESC ON
dbo.tbl_Item_Details_ID.ID_Function_Description =
dbo.tbl_ILU_F_DESC.F_DESC_ID LEFT OUTER JOIN
dbo.tbl_ILU_D_RANG ON
dbo.tbl_Item_Details_ID.ID_Range = dbo.tbl_ILU_D_RANG.D_RANG_ID LEFT
OUTER JOIN
dbo.tbl_ILU_B_BTYP ON
dbo.tbl_Item_Details_ID.ID_Brand_Type = dbo.tbl_ILU_B_BTYP.B_BTYP_ID
LEFT OUTER JOIN
dbo.tbl_ILU_Z_PTYP ON
dbo.tbl_Item_Details_ID.ID_Product_Type = dbo.tbl_ILU_Z_PTYP.Z_PTYP_ID
CREATE VIEW dbo.vw_Customer_Details
AS
SELECT dbo.tbl_Customer_Details_CD.CD_Company_Number,
dbo.tbl_Customer_Details_CD.CD_Customer_Number,
dbo.tbl_Customer_Details_CD.CD_Dseq,
dbo.tbl_Customer_Details_CD.CD_Customer_Name,
dbo.tbl_Customer_Details_CD.CD_Customer_Address_1,
dbo.tbl_Customer_Details_CD.CD_Customer_Address_2,
dbo.tbl_Customer_Details_CD.CD_Customer_Address_3,
dbo.tbl_Customer_Details_CD.CD_Customer_Address_4,
dbo.tbl_Customer_Details_CD.CD_Customer_Address_5,
dbo.tbl_Customer_Details_CD.CD_Post_Code_1,
dbo.tbl_Customer_Details_CD.CD_Post_Code_2,
dbo.tbl_Customer_Details_CD.CD_Customer_Group_1,
dbo.tbl_Customer_Details_CD.CD_Customer_Group_2,
dbo.tbl_Customer_Details_CD.CD_Customer_Group_3,
dbo.tbl_Customer_Details_CD.CD_Customer_Group_4,
dbo.tbl_Customer_Details_CD.CD_Region,
dbo.tbl_Customer_Details_CD.CD_Credit_Limit,
dbo.tbl_Customer_Details_CD.CD_Customer_Contact,
dbo.tbl_Customer_Details_CD.CD_Phone_Number,
dbo.tbl_Customer_Details_CD.CD_Date_Account_Opened ,
dbo.tbl_Customer_Details_CD.CD_Bank_Account_Number ,
dbo.tbl_Customer_Details_CD.CD_Bank_Account_Name,
dbo.tbl_Customer_Details_CD.CD_Bank_Address_1,
dbo.tbl_Customer_Details_CD.CD_Bank_Address_2,
dbo.tbl_Customer_Details_CD.CD_Credit_Controller,
dbo.tbl_Credit_Controller.CC_Description,
dbo.tbl_Customer_Details_CD.CD_Customer_Group,
dbo.tbl_CLU_Z_CGT.Z_Customer_Group_Type_Descriptio n,
dbo.tbl_Customer_Details_CD.CD_Customer_Parent,
dbo.tbl_CLU_Y_CPT.Y_Customer_Parent_Type_Descripti on,
dbo.tbl_Customer_Details_CD.CD_Sales_Region,
dbo.tbl_CLU_A_SRGN.A_Sales_Region_Description,
dbo.tbl_Customer_Details_CD.CD_Business_Type,
dbo.tbl_CLU_B_BTYP.B_Business_Type_Description,
dbo.tbl_Customer_Details_CD.CD_Distribution_Channe l,
dbo.tbl_CLU_C_DCNL.C_Distribution_Channel_Descript ion,
dbo.tbl_Customer_Details_CD.CD_Distribution_Cluste r,
dbo.tbl_CLU_D_DCSR.D_Distribution_Cluster_Descript ion,
dbo.tbl_Customer_Details_CD.CD_Delivery_Type,
dbo.tbl_CLU_E_DTYP.E_Delivery_Type_Description,
dbo.tbl_Customer_Details_CD.CD_Marketing_Director,
dbo.tbl_CLU_F_MDR.F_Marketing_Director_Resposibili ty_Description,
dbo.tbl_Customer_Details_CD.CD_Sales_Director,
dbo.tbl_CLU_G_SDR.G_Sales_Director_Responsibility_ Description,
dbo.tbl_Customer_Details_CD.CD_Account_Manager,
dbo.tbl_CLU_H_AMR.H_Account_Manager_Responsibility _Description,
dbo.tbl_Customer_Details_CD.CD_Date_Account_Opened _PC,
dbo.tbl_Customer_Details_CD.CD_Consolidated_Custom er,
dbo.tbl_CLU_I_CNSL.I_Consolidated_Customer_Descrip tion
FROM dbo.tbl_Customer_Details_CD LEFT OUTER JOIN
dbo.tbl_CLU_I_CNSL ON
dbo.tbl_Customer_Details_CD.CD_Consolidated_Custom er =
dbo.tbl_CLU_I_CNSL.I_CNSL_ID LEFT OUTER JOIN
dbo.tbl_Credit_Controller ON
dbo.tbl_Customer_Details_CD.CD_Credit_Controller =
dbo.tbl_Credit_Controller.CC_ID LEFT OUTER JOIN
dbo.tbl_CLU_H_AMR ON
dbo.tbl_Customer_Details_CD.CD_Account_Manager =
dbo.tbl_CLU_H_AMR.H_AMR_ID LEFT OUTER JOIN
dbo.tbl_CLU_G_SDR ON
dbo.tbl_Customer_Details_CD.CD_Sales_Director =
dbo.tbl_CLU_G_SDR.G_SDR_ID LEFT OUTER JOIN
dbo.tbl_CLU_F_MDR ON
dbo.tbl_Customer_Details_CD.CD_Marketing_Director =
dbo.tbl_CLU_F_MDR.F_MDR_ID LEFT OUTER JOIN
dbo.tbl_CLU_E_DTYP ON
dbo.tbl_Customer_Details_CD.CD_Delivery_Type =
dbo.tbl_CLU_E_DTYP.E_DTYP_ID LEFT OUTER JOIN
dbo.tbl_CLU_D_DCSR ON
dbo.tbl_Customer_Details_CD.CD_Distribution_Cluste r =
dbo.tbl_CLU_D_DCSR.D_DCSR_ID LEFT OUTER JOIN
dbo.tbl_CLU_C_DCNL ON
dbo.tbl_Customer_Details_CD.CD_Distribution_Channe l =
dbo.tbl_CLU_C_DCNL.C_DCNL_ID LEFT OUTER JOIN
dbo.tbl_CLU_B_BTYP ON
dbo.tbl_Customer_Details_CD.CD_Business_Type =
dbo.tbl_CLU_B_BTYP.B_BTYP_ID LEFT OUTER JOIN
dbo.tbl_CLU_A_SRGN ON
dbo.tbl_Customer_Details_CD.CD_Sales_Region =
dbo.tbl_CLU_A_SRGN.A_SRGN_ID LEFT OUTER JOIN
dbo.tbl_CLU_Y_CPT ON
dbo.tbl_Customer_Details_CD.CD_Customer_Parent =
dbo.tbl_CLU_Y_CPT.Y_CPT_ID LEFT OUTER JOIN
dbo.tbl_CLU_Z_CGT ON
dbo.tbl_Customer_Details_CD.CD_Customer_Group =
dbo.tbl_CLU_Z_CGT.Z_CGT_ID
The above should give you some idea of the data, I haven't include the
scripts for the base tables as this would take considerable time
*** Sent via Developersdex
http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!