Still having problems with my Date functions

[eo]

A simple formula shows some data for the past 14 days. You will see the date is hard coded:

select sum (FSA_Unearned) AS 'b/forward',
DateAdd (dd, 1, [Print Date]) as 'Print Date'
from dbo.CAT7EA
where [Print Date] = '2006-10-28'
group by [Print Date]

But hardcoding a date is far from ideal, so I tried:

select sum (FSA_Unearned) AS 'b/forward',
DateAdd (dd, 1, [Print Date]) as 'Print Date'
from dbo.CAT7EA
where [Print Date] >= DateAdd (dd, -14, [Print Date])
group by [Print Date]

The first formula returns the results within seconds, the second is pretty much non-responsive. Is this because the table is indexed by [Print Date] and a hard coded date means more to SQL than 14 days ago, which it will go and have to calculate (there is approx 60 million records in this table - hence the non-responsiveness)?

Any ideas?

EO
Hertfordshire, England

 

[eo]

Sorry the first formula should read

select sum (FSA_Unearned) AS 'b/forward',
DateAdd (dd, 1, [Print Date]) as 'Print Date'
from dbo.CAT7EA
where [Print Date] >= '2006-10-28'
group by [Print Date]

EO
Hertfordshire, England

 

[bborissov]

With your second query you query almost WHOLE table. I am sure after first 14 dates ALL OTHER RECORDS have PrintDate >= PrintDate-14
What you want? To get All records that have PrintDate not older that 14 days ago? If so:

DECLARE @desireddate datetime
--- get current date W/o time portion
SET @desireddate = CONVERT(datetime,convert(varchar(8),GETDATE()-14,112))

select sum (FSA_Unearned) AS 'b/forward',
DateAdd (dd, 1, [Print Date]) as 'Print Date'
from dbo.CAT7EA
where [Print Date] >= @desireddate
group by [Print Date]

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[etienne123]

Thanks, I will try this soon. Related to this is a day of week question:

I want to generate data based on the day of the week.

If it is a Monday, then the rest of the statement will do one thing, if it is not a friday, then the statement should so something else:

You will see in the example, I have hardcoded the past Friday's date. How can this be automated?

If (Select Count ([Print Date]) from dbo.CAT7EA where [Print Date] = '2006-11-06') > 0
   Begin
	select sum (FSA_Unearned) AS 'new items',
	[Print Date]
	from dbo.CAT7EA
	where [Print Date] >= '2006-10-28'
	and [Entry_Date] = DateAdd(dd,-3,[Print Date])
	group by [Print Date]
   End

Else
If (Select Count ([Print Date]) from dbo.CAT7EA where [Print Date] <> '2006-11-06') > 0
   Begin
	select sum (FSA_Unearned) AS 'new items',
	[Print Date] as 'Print Date'
	from dbo.CAT7EA
	where [Print Date] >= '2006-10-28'
	and [Entry_Date] = DateAdd(dd,-1,[Print Date])
	group by [Print Date]
   End

The two if sections work seperate from each other, but not together, can you see anything obviously wrog in the statement...after which I will need to work your suggestion...

DECLARE @desireddate datetime
--- get current date W/o time portion
SET @desireddate = CONVERT(datetime,convert(varchar(8),GETDATE()-14,112))

select sum (FSA_Unearned) AS 'b/forward',
DateAdd (dd, 1, [Print Date]) as 'Print Date'
from dbo.CAT7EA
where [Print Date] >= @desireddate
group by [Print Date]

...into the equation