Join Query troubles - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Join Query troubles

Author

Topic

BenSwitzer
Yak Posting Veteran

72 Posts

Posted - 2006-11-14 : 08:02:32

Hi,

I am attempting to come up with a query which will give me the results I need. My current query results does not include those records that dont have a correlating record in another table. I thought a right outer would fix this, but I haven't been able to make it correct. Here is my current query:

Select distinct tempscrub.tname, tempscrub.ssn,tempscrub.cycle, tempscrub.uic, cyclemaster.paygrade, tempscrub.prate, isNull(progression.erate,''), 'X',tempscrub.pguic, 'Y'
		FROM             SerialAvailability 
		INNER JOIN       progression ON SerialAvailability.Rate = progression.erate 
		RIGHT OUTER JOIN CycleMaster 
		INNER JOIN       XrefCycleMasterToProgression ON CycleMaster.CYCPGID = XrefCycleMasterToProgression.CycPGID 
		INNER JOIN       tempscrub ON CycleMaster.Cycle = tempscrub.cycle AND CycleMaster.Paygrade = 'E' + LTRIM(STR(tempscrub.pgrade + 1)) 
				   ON SerialAvailability.Pgrade = CycleMaster.Paygrade AND SerialAvailability.Series = CycleMaster.AUTHSER1 AND progression.prate = tempscrub.prate
		 where 		tempscrub.cycle = @cycle and isSI = 'N'
		 AND                  ssn + uic + tempscrub.cycle not in(Select distinct ssn + uic +cycle from scrublist)
		 AND		tirdate <= Case(tempscrub.pgrade) 
					    when 3 
					    then 
						DateAdd(mm, -6,substring(CycleMaster.termeldt,5,2) + substring(CycleMaster.termeldt,1,2)+substring(CycleMaster.termeldt,3,2)) 
					    when 4
					    then
						DateAdd(yy, -1,substring(CycleMaster.termeldt,5,2) + substring(CycleMaster.termeldt,1,2)+substring(CycleMaster.termeldt,3,2)) 
					    when 5
					    then
						DateAdd(yy, -3,substring(CycleMaster.termeldt,5,2) + substring(CycleMaster.termeldt,1,2)+substring(CycleMaster.termeldt,3,2)) 
					    when 6
					    then
						DateAdd(yy, -3,substring(CycleMaster.termeldt,5,2) + substring(CycleMaster.termeldt,1,2)+substring(CycleMaster.termeldt,3,2)) 
					    end;

I am wanting the result to bring back records with from tempscrub which its prate may or may not be in progression but still passes the where part. If you need the DDLs let me know.

Thanks,
Ben

BenSwitzer
Yak Posting Veteran

72 Posts

Posted - 2006-11-14 : 08:13:39

Im posting the DDL for those that are interested


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CycleMaster]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CycleMaster]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SerialAvailability]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SerialAvailability]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[XrefCycleMasterToProgression]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[XrefCycleMasterToProgression]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[progression]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[progression]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_Addresses]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbl_Addresses]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tempscrub]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tempscrub]
GO

CREATE TABLE [dbo].[CycleMaster] (
	[CYCPGID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
	[Cycle] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Paygrade] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ExamDate] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[TERMELDT] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[LIMITDT] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ADVDTIN1] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ADVDTIN2] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ADVDTIN3] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ADVDTIN4] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ADVDTIN5] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ADVDTIN6] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ADVDTIN7] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ADVDTIN8] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ADVDTIN9] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ADVDTIN10] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ADVDTIN11] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ADVDTIN12] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ADVDTIN13] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ADVDTIN14] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ADVDTIN15] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ALADVDTIN1] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ALADVDTIN2] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ALADVDTIN3] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ALADVDTIN4] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ALADVDTIN5] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ALADVDTIN6] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ALADVDTIN7] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ALADVDTIN8] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ALADVDTIN9] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ALADVDTIN10] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ALADVDTIN11] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ALADVDTIN12] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ALADVDTIN13] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ALADVDTIN14] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ALADVDTIN15] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[RAAUTH] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[PREVCYCL1] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[PREVCYCL2] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[PREVCYCL3] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[PREVCYCL4] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[PREVCYCL5] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[PREVCYCL6] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[FIDTADV] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[AUTHSER1] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[AUTHSER2] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[AUTHSER3] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[AUTHSER4] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[AUTHSER5] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[STKDATE] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[CRDATE] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[CRCUT] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[SBPER] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[PASSCURSCR] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[CounterCYCLE] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[EARQUOTA] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[EARPERCT] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[CHG_CDS_CUTOFF] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[EXAMHISTORY_CUTOFF] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[USNorRES] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[RESULTSVIEW] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[INHOUSE_OPENS] [datetime] NOT NULL ,
	[INHOUSE_CLOSES] [datetime] NOT NULL ,
	[SUBSTITUTE_OPENS] [datetime] NOT NULL ,
	[SUBSTITUTE_CLOSES] [datetime] NOT NULL ,
	[TIR_OPENS] [datetime] NOT NULL ,
	[TIR_CLOSES] [datetime] NOT NULL ,
	[TIR_PULLED] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[TIR_PULLDATE] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[TIR_PULLTIME] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[TIR_SUPCLOSES] [datetime] NOT NULL ,
	[TIR_SPULLDATE] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[TIR_SPULLTIME] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[CT_TIROPENS] [datetime] NOT NULL ,
	[CT_TIRCLOSES] [datetime] NOT NULL ,
	[CT_TIRPULLED] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[CT_TIRPULLDATE] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[CT_TIRPULLTIME] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[CT_TIRSUPCLOSES] [datetime] NOT NULL ,
	[CT_TIRSPULLDATE] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[CT_TIRSPULLTIME] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Quotas_Closed] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[SerialAvailability] (
	[Pgrade] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Rate] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Form] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Serial] [numeric](18, 0) NOT NULL ,
	[ESerial] [numeric](18, 0) NOT NULL ,
	[Series] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[isSI] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[MediaType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ActiveOnly] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Classification] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[XrefCycleMasterToProgression] (
	[CycPGID] [numeric](18, 0) NOT NULL ,
	[ProgID] [numeric](18, 0) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[progression] (
	[prate] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[erate] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ProgId] [numeric](18, 0) IDENTITY (1, 1) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tbl_Addresses] (
	[UIC] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[DCSID] [numeric](18, 0) NOT NULL ,
	[Title] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[AddressLine1] [varchar] (47) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[AddressLine2] [varchar] (47) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[AddressLine3] [varchar] (47) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[City] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[State] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Zip] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Country] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[PLAD] [varchar] (55) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[DMS_Address] [varchar] (69) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Decommissioned] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Parent_UIC] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Reserve_Admin] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Reserve_Ship_To] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Active_Ship_To] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Mail_Results] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[TCONum] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Is_ROB] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ChangedBy] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ReasonForChange] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Comments] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tempscrub] (
	[tname] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ssn] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[uic] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[tirdate] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[pgrade] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[prate] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[cycle] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[pguic] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[erate] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[earlies] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[addflag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[id] [numeric](18, 0) IDENTITY (1, 1) NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[CycleMaster] WITH NOCHECK ADD 
	CONSTRAINT [PK_CycleMaster] PRIMARY KEY  CLUSTERED 
	(
		[CYCPGID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[SerialAvailability] WITH NOCHECK ADD 
	CONSTRAINT [PK_SerialAvailability] PRIMARY KEY  CLUSTERED 
	(
		[Pgrade],
		[Rate],
		[Form],
		[Series]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tbl_Addresses] WITH NOCHECK ADD 
	CONSTRAINT [PK_tbl_Addresses] PRIMARY KEY  CLUSTERED 
	(
		[UIC]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tempscrub] WITH NOCHECK ADD 
	CONSTRAINT [PK_tempscrub] PRIMARY KEY  CLUSTERED 
	(
		[id]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[CycleMaster] WITH NOCHECK ADD 
	CONSTRAINT [DF_CycleMaster_CC_ExamDate] DEFAULT ('') FOR [ExamDate],
	CONSTRAINT [DF_CycleMaster_CC_TERMELDT] DEFAULT ('') FOR [TERMELDT],
	CONSTRAINT [DF_CycleMaster_CC_LIMITDT] DEFAULT ('') FOR [LIMITDT],
	CONSTRAINT [DF_CycleMaster_CC_ADVDTIN1] DEFAULT ('') FOR [ADVDTIN1],
	CONSTRAINT [DF_CycleMaster_CC_ADVDTIN2] DEFAULT ('') FOR [ADVDTIN2],
	CONSTRAINT [DF_CycleMaster_CC_ADVDTIN3] DEFAULT ('') FOR [ADVDTIN3],
	CONSTRAINT [DF_CycleMaster_CC_ADVDTIN4] DEFAULT ('') FOR [ADVDTIN4],
	CONSTRAINT [DF_CycleMaster_CC_ADVDTIN5] DEFAULT ('') FOR [ADVDTIN5],
	CONSTRAINT [DF_CycleMaster_CC_ADVDTIN6] DEFAULT ('') FOR [ADVDTIN6],
	CONSTRAINT [DF_CycleMaster_CC_ADVDTIN7] DEFAULT ('') FOR [ADVDTIN7],
	CONSTRAINT [DF_CycleMaster_CC_ADVDTIN8] DEFAULT ('') FOR [ADVDTIN8],
	CONSTRAINT [DF_CycleMaster_CC_ADVDTIN9] DEFAULT ('') FOR [ADVDTIN9],
	CONSTRAINT [DF_CycleMaster_CC_ADVDTIN10] DEFAULT ('') FOR [ADVDTIN10],
	CONSTRAINT [DF_CycleMaster_CC_ADVDTIN11] DEFAULT ('') FOR [ADVDTIN11],
	CONSTRAINT [DF_CycleMaster_CC_ADVDTIN12] DEFAULT ('') FOR [ADVDTIN12],
	CONSTRAINT [DF_CycleMaster_CC_ADVDTIN13] DEFAULT ('') FOR [ADVDTIN13],
	CONSTRAINT [DF_CycleMaster_CC_ADVDTIN14] DEFAULT ('') FOR [ADVDTIN14],
	CONSTRAINT [DF_CycleMaster_CC_ADVDTIN15] DEFAULT ('') FOR [ADVDTIN15],
	CONSTRAINT [DF_CycleMaster_CC_ALADVDTIN1] DEFAULT ('') FOR [ALADVDTIN1],
	CONSTRAINT [DF_CycleMaster_CC_ALADVDTIN2] DEFAULT ('') FOR [ALADVDTIN2],
	CONSTRAINT [DF_CycleMaster_CC_ALADVDTIN3] DEFAULT ('') FOR [ALADVDTIN3],
	CONSTRAINT [DF_CycleMaster_CC_ALADVDTIN4] DEFAULT ('') FOR [ALADVDTIN4],
	CONSTRAINT [DF_CycleMaster_CC_ALADVDTIN5] DEFAULT ('') FOR [ALADVDTIN5],
	CONSTRAINT [DF_CycleMaster_CC_ALADVDTIN6] DEFAULT ('') FOR [ALADVDTIN6],
	CONSTRAINT [DF_CycleMaster_CC_ALADVDTIN7] DEFAULT ('') FOR [ALADVDTIN7],
	CONSTRAINT [DF_CycleMaster_CC_ALADVDTIN8] DEFAULT ('') FOR [ALADVDTIN8],
	CONSTRAINT [DF_CycleMaster_CC_ALADVDTIN9] DEFAULT ('') FOR [ALADVDTIN9],
	CONSTRAINT [DF_CycleMaster_CC_ALADVDTIN10] DEFAULT ('') FOR [ALADVDTIN10],
	CONSTRAINT [DF_CycleMaster_CC_ALADVDTIN11] DEFAULT ('') FOR [ALADVDTIN11],
	CONSTRAINT [DF_CycleMaster_CC_ALADVDTIN12] DEFAULT ('') FOR [ALADVDTIN12],
	CONSTRAINT [DF_CycleMaster_CC_ALADVDTIN13] DEFAULT ('') FOR [ALADVDTIN13],
	CONSTRAINT [DF_CycleMaster_CC_ALADVDTIN14] DEFAULT ('') FOR [ALADVDTIN14],
	CONSTRAINT [DF_CycleMaster_CC_ALADVDTIN15] DEFAULT ('') FOR [ALADVDTIN15],
	CONSTRAINT [DF_CycleMaster_CC_RAAUTH] DEFAULT ('') FOR [RAAUTH],
	CONSTRAINT [DF_CycleMaster_CC_PREVCYCL1] DEFAULT ('') FOR [PREVCYCL1],
	CONSTRAINT [DF_CycleMaster_CC_PREVCYCL2] DEFAULT ('') FOR [PREVCYCL2],
	CONSTRAINT [DF_CycleMaster_CC_PREVCYCL3] DEFAULT ('') FOR [PREVCYCL3],
	CONSTRAINT [DF_CycleMaster_CC_PREVCYCL4] DEFAULT ('') FOR [PREVCYCL4],
	CONSTRAINT [DF_CycleMaster_CC_PREVCYCL5] DEFAULT ('') FOR [PREVCYCL5],
	CONSTRAINT [DF_CycleMaster_CC_PREVCYCL6] DEFAULT ('') FOR [PREVCYCL6],
	CONSTRAINT [DF_CycleMaster_CC_FIDTADV] DEFAULT ('') FOR [FIDTADV],
	CONSTRAINT [DF_CycleMaster_CC_AUTHSER1] DEFAULT ('') FOR [AUTHSER1],
	CONSTRAINT [DF_CycleMaster_CC_AUTHSER2] DEFAULT ('000') FOR [AUTHSER2],
	CONSTRAINT [DF_CycleMaster_CC_AUTHSER3] DEFAULT ('000') FOR [AUTHSER3],
	CONSTRAINT [DF_CycleMaster_CC_AUTHSER4] DEFAULT ('000') FOR [AUTHSER4],
	CONSTRAINT [DF_CycleMaster_CC_AUTHSER5] DEFAULT ('000') FOR [AUTHSER5],
	CONSTRAINT [DF_CycleMaster_CC_STKDATE] DEFAULT ('') FOR [STKDATE],
	CONSTRAINT [DF_CycleMaster_CC_CRDATE] DEFAULT ('') FOR [CRDATE],
	CONSTRAINT [DF_CycleMaster_CC_CRCUT] DEFAULT ('') FOR [CRCUT],
	CONSTRAINT [DF_CycleMaster_CC_SBPER] DEFAULT ('') FOR [SBPER],
	CONSTRAINT [DF_CycleMaster_CC_PASSCURSCR] DEFAULT ('') FOR [PASSCURSCR],
	CONSTRAINT [DF_CycleMaster_CC_CounterCYCLE] DEFAULT ('') FOR [CounterCYCLE],
	CONSTRAINT [DF_CycleMaster_CC_EARQUOTA] DEFAULT ('') FOR [EARQUOTA],
	CONSTRAINT [DF_CycleMaster_CC_EARPERCT] DEFAULT ('') FOR [EARPERCT],
	CONSTRAINT [DF_CycleMaster_CC_CHG_CDS_CUTOFF] DEFAULT ('') FOR [CHG_CDS_CUTOFF],
	CONSTRAINT [DF_CycleMaster_CC_EXAMHISTORY_CUTOFF] DEFAULT ('') FOR [EXAMHISTORY_CUTOFF],
	CONSTRAINT [DF_CycleMaster_USNorRES] DEFAULT ('') FOR [USNorRES],
	CONSTRAINT [DF_CycleMaster_RESULTSVIEW] DEFAULT ('NV') FOR [RESULTSVIEW],
	CONSTRAINT [DF_CycleMaster_INHOUSE_OPENS] DEFAULT ('') FOR [INHOUSE_OPENS],
	CONSTRAINT [DF_CycleMaster_INHOUSE_CLOSES] DEFAULT ('') FOR [INHOUSE_CLOSES],
	CONSTRAINT [DF_CycleMaster_SUBSTITUTE_OPENS] DEFAULT (0) FOR [SUBSTITUTE_OPENS],
	CONSTRAINT [DF_CycleMaster_SUBSTITUE_CLOSES] DEFAULT (0) FOR [SUBSTITUTE_CLOSES],
	CONSTRAINT [DF_CycleMaster_TIR_OPENS] DEFAULT ('') FOR [TIR_OPENS],
	CONSTRAINT [DF_CycleMaster_TIR_CLOSES] DEFAULT ('') FOR [TIR_CLOSES],
	CONSTRAINT [DF_CycleMaster_TIR_PULLED] DEFAULT ('') FOR [TIR_PULLED],
	CONSTRAINT [DF_CycleMaster_TIR_PULLDATE] DEFAULT ('') FOR [TIR_PULLDATE],
	CONSTRAINT [DF_CycleMaster_TIR_PULLTIME] DEFAULT ('') FOR [TIR_PULLTIME],
	CONSTRAINT [DF_CycleMaster_TIR_SUPCLOSES] DEFAULT ('') FOR [TIR_SUPCLOSES],
	CONSTRAINT [DF_CycleMaster_TIR_SPULLDATE] DEFAULT ('') FOR [TIR_SPULLDATE],
	CONSTRAINT [DF_CycleMaster_TIR_SPULLTIME] DEFAULT ('') FOR [TIR_SPULLTIME],
	CONSTRAINT [DF_CycleMaster_TIR_OPENS1] DEFAULT ('') FOR [CT_TIROPENS],
	CONSTRAINT [DF_CycleMaster_TIR_CLOSES1] DEFAULT ('') FOR [CT_TIRCLOSES],
	CONSTRAINT [DF_CycleMaster_TIR_PULLED1] DEFAULT ('') FOR [CT_TIRPULLED],
	CONSTRAINT [DF_CycleMaster_CT_TIRPULLDATE] DEFAULT ('') FOR [CT_TIRPULLDATE],
	CONSTRAINT [DF_CycleMaster_CT_TIRPULLTIME] DEFAULT ('') FOR [CT_TIRPULLTIME],
	CONSTRAINT [DF_CycleMaster_TIR_SUPCLOSES1] DEFAULT ('') FOR [CT_TIRSUPCLOSES],
	CONSTRAINT [DF_CycleMaster_TIR_SPULLDATE1] DEFAULT ('') FOR [CT_TIRSPULLDATE],
	CONSTRAINT [DF_CycleMaster_TIR_SPULLTIME1] DEFAULT ('') FOR [CT_TIRSPULLTIME],
	CONSTRAINT [DF_CycleMaster_Quotas_Closed] DEFAULT ('N') FOR [Quotas_Closed]
GO

ALTER TABLE [dbo].[SerialAvailability] WITH NOCHECK ADD 
	CONSTRAINT [DF_SerialAvailability_isSI] DEFAULT ('N') FOR [isSI],
	CONSTRAINT [DF_SerialAvailability_MediaType] DEFAULT ('P') FOR [MediaType],
	CONSTRAINT [DF_SerialAvailability_ActiveOnly] DEFAULT ('N') FOR [ActiveOnly],
	CONSTRAINT [DF_SerialAvailability_Classification] DEFAULT ('F') FOR [Classification]
GO

ALTER TABLE [dbo].[tbl_Addresses] WITH NOCHECK ADD 
	CONSTRAINT [DF_tbl_Addresses_DCSID] DEFAULT (0) FOR [DCSID],
	CONSTRAINT [DF_tbl_Addresses_Title] DEFAULT ('') FOR [Title],
	CONSTRAINT [DF_tbl_Addresses_AddressLine1] DEFAULT ('') FOR [AddressLine1],
	CONSTRAINT [DF_tbl_Addresses_AddressLine2] DEFAULT ('') FOR [AddressLine2],
	CONSTRAINT [DF_tbl_Addresses_AddressLine3] DEFAULT ('') FOR [AddressLine3],
	CONSTRAINT [DF_tbl_Addresses_City] DEFAULT (' ') FOR [City],
	CONSTRAINT [DF_tbl_Addresses_State] DEFAULT (' ') FOR [State],
	CONSTRAINT [DF_tbl_Addresses_Zip] DEFAULT (' ') FOR [Zip],
	CONSTRAINT [DF_tbl_Addresses_Country] DEFAULT ('') FOR [Country],
	CONSTRAINT [DF_tbl_Addresses_PLAD] DEFAULT ('') FOR [PLAD],
	CONSTRAINT [DF_tbl_Addresses_DMS_Address] DEFAULT ('') FOR [DMS_Address],
	CONSTRAINT [DF_tbl_Addresses_Decommissioned] DEFAULT ('N') FOR [Decommissioned],
	CONSTRAINT [DF_tbl_Addresses_Parent_UIC] DEFAULT ('') FOR [Parent_UIC],
	CONSTRAINT [DF_tbl_Addresses_Reserve_Admin] DEFAULT ('') FOR [Reserve_Admin],
	CONSTRAINT [DF_tbl_Addresses_Reserve_Ship_To] DEFAULT ('') FOR [Reserve_Ship_To],
	CONSTRAINT [DF_tbl_Addresses_Active_Ship_To] DEFAULT ('') FOR [Active_Ship_To],
	CONSTRAINT [DF_tbl_Addresses_Mail_Results] DEFAULT ('N') FOR [Mail_Results],
	CONSTRAINT [DF_tbl_Addresses_TCONum] DEFAULT ('') FOR [TCONum],
	CONSTRAINT [DF_tbl_Addresses_Is_ROB] DEFAULT ('N') FOR [Is_ROB],
	CONSTRAINT [DF_tbl_Addresses_ChangedBy] DEFAULT ('slb') FOR [ChangedBy],
	CONSTRAINT [DF_tbl_Addresses_ReasonForChange] DEFAULT ('') FOR [ReasonForChange],
	CONSTRAINT [DF_tbl_Addresses_Comments] DEFAULT ('') FOR [Comments]
GO

ALTER TABLE [dbo].[tempscrub] WITH NOCHECK ADD 
	CONSTRAINT [DF_tempscrub_pguic] DEFAULT ('') FOR [pguic],
	CONSTRAINT [DF_tempscrub_erate] DEFAULT ('') FOR [erate],
	CONSTRAINT [DF_tempscrub_earlies] DEFAULT ('') FOR [earlies]
GO

 CREATE  INDEX [IX_tbl_Addresses] ON [dbo].[tbl_Addresses]([UIC]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-14 : 08:16:08

Try again with a simple LEFT JOIN.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-14 : 08:18:26

Never never never never never never ever store dates as VARCHAR/CHAR!!!
Always store dates as DATETIME/SMALLDATETIME.

Peter Larsson
Helsingborg, Sweden

BenSwitzer
Yak Posting Veteran

72 Posts

Posted - 2006-11-14 : 08:52:39

quote:
Originally posted by Peso

Try again with a simple LEFT JOIN.

Peter Larsson
Helsingborg, Sweden

Change every Inner Join or a specific one?

Thanks

BenSwitzer
Yak Posting Veteran

72 Posts

Posted - 2006-11-14 : 08:54:44

quote:
Originally posted by Peso

Never never never never never never ever store dates as VARCHAR/CHAR!!!
Always store dates as DATETIME/SMALLDATETIME.

Peter Larsson
Helsingborg, Sweden

While I mostly agree, I had no choice. It was setup before I arrived. We also store dates numeric yyyymmdd, which I like better than char. I am trying to convert them to datetime.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-14 : 08:57:14

No, the RIGHT JOIN to a LEFT JOIN.
What you have written now is that CycleMaster is the main table, and all other records in all other tables will only be seen if there is a match in CycleMaster table.
If you change to a LEFT JOIN, all others records in all other tables are always seen, and with a LEFT JOIN to CycleMaster table, these records will be seen if there is a match.

Peter Larsson
Helsingborg, Sweden

BenSwitzer
Yak Posting Veteran

72 Posts

Posted - 2006-11-14 : 09:07:30

The Left join turned a 9sec query into a 5+minute query. I stopped it once it hit 5 minutes as thats much too long to execute.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-14 : 09:10:17

If all dates are stored varchar as yyyymmdd, there is no need for

when 3 then DateAdd(mm, -6, substring(CycleMaster.termeldt, 5, 2) + substring(CycleMaster.termeldt, 1, 2) + substring(CycleMaster.termeldt, 3, 2))
when 4 then DateAdd(yy, -1, substring(CycleMaster.termeldt, 5, 2) + substring(CycleMaster.termeldt, 1, 2) + substring(CycleMaster.termeldt, 3, 2))
when 5 then DateAdd(yy, -3, substring(CycleMaster.termeldt, 5, 2) + substring(CycleMaster.termeldt, 1, 2) + substring(CycleMaster.termeldt, 3, 2))
when 6 then DateAdd(yy, -3, substring(CycleMaster.termeldt, 5, 2) + substring(CycleMaster.termeldt, 1, 2) + substring(CycleMaster.termeldt, 3, 2))

Use this instead (universal date format)

when 3 then DateAdd(mm, -6, CycleMaster.termeldt)
when 4 then DateAdd(yy, -1, CycleMaster.termeldt)
when 5 then DateAdd(yy, -3, CycleMaster.termeldt)
when 6 then DateAdd(yy, -3, CycleMaster.termeldt)

Peter Larsson
Helsingborg, Sweden

BenSwitzer
Yak Posting Veteran

72 Posts

Posted - 2006-11-14 : 09:20:31

termeldt is stored as a char(6).

BenSwitzer
Yak Posting Veteran

72 Posts

Posted - 2006-11-14 : 09:51:40

Here is some DML of sample data for the tables.


INSERT INTO CYCLEMASTER ([Cycle],[Paygrade],[TERMELDT],[AUTHSER1],[USNorRES])
Select '195','E4','070107','195','U' UNION ALL
Select '195','E5','070107','195','U' UNION ALL
Select '195','E6','070107','195','U' 
GO

INSERT INTO [SerialAvailability] ([Pgrade] ,[Rate] ,[Form] ,[Serial],[ESerial],[Series],[isSI],[MediaType] ,[ActiveOnly] ,[Classification])
Select 'E6','ABE1','',1,8999,'195','N','P','N','C' UNION ALL
Select 'E5','ABE2','',1,8999,'195','N','P','N','C' UNION ALL
Select 'E4','ABE3','',1,8999,'195','N','P','N','C'
GO


insert into [progression] ([prate],[erate])
Select 'ABEAN','ABE3'  UNION ALL
Select 'ABE2','ABE1'  UNION ALL
Select 'ABE3','ABE2'  
GO

Insert into [XrefCycleMasterToProgression] ([CycPGID],[ProgID])
Select 1,2  UNION ALL
Select 2,3  UNION ALL
Select 3,1 

Insert into [tbl_Addresses] ([UIC])
Select '00001'
GO

Insert into [tempscrub] ([tname]  ,[ssn]  ,[uic]  ,[tirdate] ,[pgrade] ,[prate],[cycle] ,[pguic],[erate],[earlies],[addflag])
Select 'This Testy','000000001','00001','010701',3,'AN','195','00001','','','' UNION ALL
Select 'Tom Testy','000000002','00001','010701',4,'ABE3','195','00001','','','' UNION ALL
Select 'Tim Testy','000000003','00001','010701',5,'ABE2','195','00001','','',''

The query that I posted originally is returning


tname           ssn       cycle uic   paygrade prate            pguic      
--------------- --------- ----- ----- -------- ----- ----- ---- ----- ---- 
Tim Testy       000000003 195   00001 E6       ABE2  ABE1  X    00001 Y
Tom Testy       000000002 195   00001 E5       ABE3  ABE2  X    00001 Y

I am trying to get it to return


tname           ssn       cycle uic   paygrade prate            pguic      
--------------- --------- ----- ----- -------- ----- ----- ---- ----- ---- 
Tim Testy       000000003 195   00001 E6       ABE2  ABE1  X    00001 Y
Tom Testy       000000002 195   00001 E5       ABE3  ABE2  X    00001 Y
This Testy       000000003 195   00001 E6       AN    ''    X    00001 Y

How can i modify my original query to return that result?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-14 : 10:02:40

Move this part

quote:
tempscrub.cycle = @cycle and isSI = 'N'

from the WHERE part and add it to the JOIN part of TempScrub as

quote:
INNER JOIN tempscrub ON CycleMaster.Cycle = tempscrub.cycle AND CycleMaster.Paygrade = 'E' + LTRIM(STR(tempscrub.pgrade + 1))
ON SerialAvailability.Pgrade = CycleMaster.Paygrade AND SerialAvailability.Series = CycleMaster.AUTHSER1 AND progression.prate = tempscrub.prate
and tempscrub.cycle = @cycle and isSI = 'N'

You must learn how to treat NULLs correctly.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-14 : 10:06:36

Since you didn't provide any detail about SCRUBLIST table nor sample data, this is how the query would look like without them

declare @cycle int 

select @cycle = 195

Select distinct tempscrub.tname, tempscrub.ssn,tempscrub.cycle, tempscrub.uic, cyclemaster.paygrade, tempscrub.prate, isNull(progression.erate,''), 'X',tempscrub.pguic, 'Y'
		FROM             SerialAvailability 
		INNER JOIN       progression ON SerialAvailability.Rate = progression.erate 
		right OUTER JOIN CycleMaster 
		INNER JOIN       XrefCycleMasterToProgression ON CycleMaster.CYCPGID = XrefCycleMasterToProgression.CycPGID 
		INNER JOIN       tempscrub ON CycleMaster.Cycle = tempscrub.cycle AND CycleMaster.Paygrade = 'E' + LTRIM(STR(tempscrub.pgrade + 1)) 
				   ON SerialAvailability.Pgrade = CycleMaster.Paygrade AND SerialAvailability.Series = CycleMaster.AUTHSER1 AND progression.prate = tempscrub.prate
					and tempscrub.cycle = @cycle and isSI = 'N'
 		 where 	 --ssn + uic + tempscrub.cycle not in(Select distinct ssn + uic +cycle from scrublist)
		 	--and
				tirdate <= Case(tempscrub.pgrade) 
					    when 3 
					    then 
						DateAdd(mm, -6,substring(CycleMaster.termeldt,5,2) + substring(CycleMaster.termeldt,1,2)+substring(CycleMaster.termeldt,3,2)) 
					    when 4
					    then
						DateAdd(yy, -1,substring(CycleMaster.termeldt,5,2) + substring(CycleMaster.termeldt,1,2)+substring(CycleMaster.termeldt,3,2)) 
					    when 5
					    then
						DateAdd(yy, -3,substring(CycleMaster.termeldt,5,2) + substring(CycleMaster.termeldt,1,2)+substring(CycleMaster.termeldt,3,2)) 
					    when 6
					    then
						DateAdd(yy, -3,substring(CycleMaster.termeldt,5,2) + substring(CycleMaster.termeldt,1,2)+substring(CycleMaster.termeldt,3,2)) 
					    end

Peter Larsson
Helsingborg, Sweden

BenSwitzer
Yak Posting Veteran

72 Posts

Posted - 2006-11-14 : 11:14:45

Thanks for your help. Where did I go wrong with NULL?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-14 : 11:18:07

In the RIGHT JOIN and WHERE.
Since you have a RIGHT JOIN, some records will obviously be NULL, right?
And when you add a WHERE on the complete resultset, these NULLs are discarded because of the WHERE.
However, binding the WHERE as a new ON for the JOIN, makes the JOIN smaller, but allows NULLs to be returned.

Peter Larsson
Helsingborg, Sweden

BenSwitzer
Yak Posting Veteran

72 Posts

Posted - 2006-11-14 : 11:22:48

Thanks again. That makes sense. I was close, and had that at one time but the query execution took longer so I thought it was wrong.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-14 : 11:30:20

First time when query changes execution plan, it takes longer time.
After first time, depending on the cost, the query can be cached into memory and run faster second time.

Peter Larsson
Helsingborg, Sweden

BenSwitzer
Yak Posting Veteran

72 Posts

Posted - 2006-11-14 : 15:27:51

This query is consistently running 5+ minutes. Is there a better way to structure the query so that it executes more quickly?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-14 : 15:34:09

The problem is the concatenated JOINs. I'll bet there is not even an index for the column bindings in the JOIN.

This one is very costly (sql-wise)
.. ON CycleMaster.Cycle = tempscrub.cycle AND CycleMaster.Paygrade = 'E' + LTRIM(STR(tempscrub.pgrade + 1))

Same as this
ssn + uic + tempscrub.cycle not in (Select distinct ssn + uic +cycle from scrublist)

The last is the tirdate comparison that gets evaluated for every row.

Post a working query here, together with some sample data for ScrubList, and we'll see what we can come up with.

Peter Larsson
Helsingborg, Sweden

BenSwitzer
Yak Posting Veteran

72 Posts

Posted - 2006-11-14 : 16:32:27

Im using the changed query you instructed me to make. The original query I had was around 10 seconds. The new query take 5+ minutes. I will check on indexes, but Im pretty sure they have one. For this example I am running with scrublist empty.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-15 : 00:44:53

Yes, the query only took 10 seconds before, but that is because it filtered out many records who should been there, right?

Peter Larsson
Helsingborg, Sweden

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources