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  New to Sql _ Trying to do Self Join

Author  Topic  gualm Starting Member 11 Posts Posted - 2007-12-26 : 14:40:21 I have to pull data from 5 tables. The acct# & contract# are from the Contract table. The Customer last and first name are from the Customer table. The Trans table is needed for the chg date. The Price table and the Contract Acct table. I was told to do a self join with one of the tables. (1) I must first determine accts who are currently in the status (A, H, N) and price is currently HGH and they have the ContractAcct chg_date populated. I was able to do this. (2)Then I must determine those who currently has the Price of ‘Gen’ whose accts are in status (A,H,N) and they have the ContractAcct chg_date populated but also have had the Price of ‘Spec’ and prior to that ‘Alt’. This is where I am having a problem. Please review my code below and give me any suggestions thanks.. CREATE PROCEDURE [dbo].[------] AS SET nocount ON CREATE TABLE #Old_Rate ( AccountNum varchar(35), Contract int, Last_Name varchar(55), First_Name varchar(25), Acct_Status Varchar(20), Gca_Status bit, Price varchar (10), Price_EffDate datetime, Price_EndDate datetime, ) INSERT #Old_Rate (AccountNum, Contract, Last_Name, First_Name, Acct_Status, Gca_Status, Price, Price_EffDate, Price_EndDate) SELECT distinct Contract_acctno, Contract_contractno, Customer_last, Customer_first, Trans_acctstat, ContractAcct_chg_date, Price_price, Price_eff_date, Price_exp_date from Contract inner join Customer on Customer_AccountNum = Contract_AccountNum inner join Trans on Trans_acctstat = Contract_acctstat inner join Price on Price_Contractno = Contract_Contractno inner join ContractAcct on Contract_Contractno = ContractAcct_Contractno where trans_acctstat in ('A', 'H', 'N') and ContractAcct chg_date = 1 --AND Contract_price = 'HGH' Group By Trans_acctstat Order By Contract_acctno SELECT * INTO #HGH_ON FROM #OLD_RATE WHERE Price_Price = 'Spec' SELECT * INTO #HGH_OFF FROM #OLD_RATE WHERE Price_eff_date is Null UPDATE #OLD_Rate SET HGH.Price_EndDate = LOW.Price_EffDate FROM Price LOW INNER JOIN Price HGH ON LOW.Price_Contractno = HGH.Price_Contractno INNER JOIN Price RED ON LOW. Price_Contractno = RED. Price_Contractno Where LOW.Price_Price = 'GEN' and LOW.Price_EndDate is null and HGH.Price = ‘Spec' and HGH.Price_EffDate < LOW.Price_EffDate and Red.Price = ‘Alt’ and Red.Price_EffDate < HGH.Price_EffDate