Dateadd in select - 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)

Dateadd in select

Author

Topic

memarcie
Yak Posting Veteran

91 Posts

Posted - 2006-12-12 : 16:12:52

Hi there -
I'm trying to run a query using dateadd to calculate a date to use in the where clause


declare @proc_date datetime
select @proc_date = dateadd(day, -5, getdate())

select a.claim, charge, price, proc_date
from hmpdw_staging.dbo.master a
join hmpdw_staging.dbo.detail b
on a.claim = b.claim and a.version = b.version
where  proc_date >= @proc_date

if I just enter in the date and not calculate it, it runs in 1 second vs a minute if I calculate the date at run time. Any suggestions?

Thanks,
Marcie

cognos79
Posting Yak Master

241 Posts

Posted - 2006-12-12 : 16:16:32

use datediff function instead...
where datediff(day, proc_date, @proc_date) > = 0

memarcie
Yak Posting Veteran

91 Posts

Posted - 2006-12-12 : 16:32:06

The datdiff function didn't reduce the time at all. Anything else?

cognos79
Posting Yak Master

241 Posts

Posted - 2006-12-12 : 16:43:33

are you using the above query in a loop?

memarcie
Yak Posting Veteran

91 Posts

Posted - 2006-12-12 : 16:52:52

Nope, not at all, but if I take the @proc_date and enter in '20061205' in its place it goes down to 1 second.

cognos79
Posting Yak Master

241 Posts

Posted - 2006-12-12 : 16:56:41

can you post here the table structure for both the tables...

memarcie
Yak Posting Veteran

91 Posts

Posted - 2006-12-12 : 17:04:43

I've never posted table structures before - but this is what I got through EM


CREATE TABLE [dbo].[master_combined] (
	[CLAIM] [int] NOT NULL ,
	[PROD_TYPE] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CLM_TYPE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[client_id] [int] NULL ,
	[TAX_ID] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[PROV_NUM] [int] NULL ,
	[NET_NUM] [int] NULL ,
	[DEAL_REC] [int] NULL ,
	[upin] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[LOC_REC] [int] NULL ,
	[PPO] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ins_ssn] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[pat_bd] [datetime] NULL ,
	[pat_sex] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ub_btype] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[disch_stat] [int] NULL ,
	[PROC_DATE] [datetime] NULL ,
	[onset_date] [datetime] NULL ,
	[diagcode1] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[diagcode2] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[diagcode3] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[diagcode4] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[diagcode5] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[proccode1] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[proccode2] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[proccode3] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[pat_fname] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[pat_lname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[pat_mname] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[pat_acct] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[notes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[status] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[retflags] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[SEND_DATE] [datetime] NULL ,
	[REVIEWED] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[SECOND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[OCCURCODE] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[DRG] [int] NULL ,
	[REC_DATE] [datetime] NULL ,
	[PROC_TIME] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[STAMP_NUM] [int] NULL ,
	[chi_note] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ENTEREDBY] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[datein] [datetime] NULL ,
	[dateout] [datetime] NULL ,
	[deal_type] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[WC] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[phy_num] [int] NULL ,
	[IN_OUT] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[PAY_TO] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[UNDER_REV] [tinyint] NULL ,
	[EOB] [tinyint] NULL ,
	[dup] [tinyint] NULL ,
	[KICKOUT] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[PROVNAME] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[PHYSICIAN] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[employee] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[SS] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[phyrec] [int] NULL ,
	[TPA_NUM] [int] NULL ,
	[accident] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[time_stamp] [datetime] NOT NULL ,
	[ediout] [tinyint] NULL ,
	[filename] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[sourcclaim] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[direct] [tinyint] NULL ,
	[deleted] [tinyint] NULL ,
	[last_modified_by] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[invnum] [int] NULL ,
	[member_id] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[medicare] [tinyint] NULL ,
	[siteid] [tinyint] NULL ,
	[version] [tinyint] NULL ,
	[send_date_ah] [datetime] NULL ,
	[prvinvnum] [int] NULL ,
	[origclaim] [int] NULL ,
	[autoprice] [tinyint] NULL ,
	[prvbilladd] [tinyint] NULL ,
	[nonnetwork] [tinyint] NULL ,
	[phy_lname] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[source] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[SYS_AUDIT_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[SYS_UPDATE_DATE] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [DATA] TEXTIMAGE_ON [DATA]
GO


CREATE TABLE [dbo].[detail_combined] (
	[claim] [int] NOT NULL ,
	[version] [tinyint] NOT NULL ,
	[pos] [tinyint] NULL ,
	[tos] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[code] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[servcode] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[charge] [decimal](11, 2) NOT NULL ,
	[price] [decimal](11, 2) NOT NULL ,
	[serv_date] [datetime] NULL ,
	[serv_date2] [datetime] NULL ,
	[reject] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[units] [decimal](9, 2) NOT NULL ,
	[diagptr] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[anesmin] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[paydeny] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[emer] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[cob] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[mods] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[pos2] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[time_stamp] [datetime] NOT NULL ,
	[last_modified_by] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[deleted] [tinyint] NULL ,
	[repriced] [tinyint] NULL ,
	[dup] [tinyint] NULL ,
	[siteid] [tinyint] NULL ,
	[lphylname] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[lphymname] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[lphyfname] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[lphysuffix] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[idnum] [bigint] NOT NULL ,
	[source] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[SYS_AUDIT_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[SYS_UPDATE_DATE] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [DATA]
GO

I know the table structures suck, I'm working with what I have at the moment. Can't rewrite an entire system in one day which was written by someone who doesn't know what normalized means.

:)

Thanks!
Marcie

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-12 : 17:20:19

Does this make a difference?

select		a.claim,
		<xyz>.charge,
		<xyz>.price,
		<xyz>.proc_date
from		hmpdw_staging.dbo.master as a
inner join	hmpdw_staging.dbo.detail as b on b.claim = a.claim and b.version = a.version
where		<xyz>.proc_date >= DATEADD(day, DATEDIFF(day, 5, GETDATE()), 0)

Replace <xyz> with proper table alias.

Peter Larsson
Helsingborg, Sweden

memarcie
Yak Posting Veteran

91 Posts

Posted - 2006-12-12 : 17:23:40

No difference Peter.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-12 : 17:26:53

I have a faint memory of this topic has been discussed before here at sqlteam...

Peter Larsson
Helsingborg, Sweden

memarcie
Yak Posting Veteran

91 Posts

Posted - 2006-12-12 : 17:30:57