AND DateDiff(dd,start_date,'5/21/2009')>=0
AND DateDiff(dd,'5/21/2009',end_date)>=0

AND start_date < '20090522'
AND end_date >= '20090521'

SELECT TOP 1 *
FROM #tCustomerPayments
WHERE customer_id = 1
AND active_record IS NULL
ORDER BY end_date DESC, customerpayment_id DESC

SELECT TOP 1 * FROM #tCustomerPayments c
WHERE customer_id = 1
AND active_record IS NULL
AND start_date < '20090522'
AND end_date >= '20090521'
ORDER BY customerpayment_id DESC

AND start_date < DATEADD(Day, 1, @CutoffDate)
AND end_date >= @CutoffDate

AND start_date < DATEADD(Day, DATEDIFF(Day, 0, @CutoffDate), 1)
AND end_date >= DATEADD(Day, DATEDIFF(DAy, 0, @CutoffDate), 0)

AND DateDiff(dd,start_date, @CutoffDate)>=0
AND DateDiff(dd,@CutoffDate, end_date)>=0

quote:

---

Originally posted by Kristen

"I am not concerned about day format as I have ODBC convert funciton wrapper before the date is applied as argument to stored procedures, functions or queries."

OK (but dates in string format, as examples on this forum for instance, will perform differently around the world )

Is your application sending Dates via ODBC as just the Date, or including a time also - i.e. a DateTime?

If it is just the date then

AND start_date < DATEADD(Day, 1, @CutoffDate)
AND end_date >= @CutoffDate

will include values for start_date (including any time) that is on, or before, @CutoffDate - that is to say earlier than midnight-following the cutoff date; and end_date is midnight on the @CutoffDate (i.e. midnight at the start of the cutoff date) or later.

If the parameters from your application include time components as well the parameters can be "rounded" to midnight to remove them:

AND start_date < DATEADD(Day, DATEDIFF(Day, 0, @CutoffDate), 1)
AND end_date >= DATEADD(Day, DATEDIFF(DAy, 0, @CutoffDate), 0)

both those methods should use indexes available for the Start/End date columns.

"As for DateDiff(), I know some advice against that, some... for."

I'd be interested to know anything against please.

In this context if you apply DateDiff() to the columns e.g.

AND DateDiff(dd,start_date, @CutoffDate)>=0
AND DateDiff(dd,@CutoffDate, end_date)>=0

indexes will not be used in the query plan, resulting in a scan of all rows in the table - which, for a large table, will be much slower than using a query plan which uses the indexes.

"The DateDiff() helps to establish date frame that relies on day only, I dont care about time and this is very important, especially its important because the system is used over few time zones."

OK, my examples did assume that the time was to be ignored

---

-- we need to get latest payment data for customer #1
-- query #18
SELECT * FROM #tCustomerPayments c
WHERE customer_id = 1
AND active_record IS NULL
AND customerpayment_id = 
(	
SELECT TOP 1 customerpayment_id
FROM #tCustomerPayments
WHERE customer_id = c.customer_id
AND active_record IS NULL
ORDER BY end_date DESC, customerpayment_id DESC
)

quote:

---

Originally posted by Transact Charlie

Hi gena_mak.

I was going to suggest a few improvements but I've read over what Kristen and Visakh have posted and everything they say is great advice for the information you posted.

This section:

-- we need to get latest payment data for customer #1
-- query #18
SELECT * FROM #tCustomerPayments c
WHERE customer_id = 1
AND active_record IS NULL
AND customerpayment_id = 
(	
SELECT TOP 1 customerpayment_id
FROM #tCustomerPayments
WHERE customer_id = c.customer_id
AND active_record IS NULL
ORDER BY end_date DESC, customerpayment_id DESC
)

Where visakh simplified this to get what he posted but this doesn't really make sense. Why would want to find the top 1 for a particular person this way?

Were you actually wanting the TOP 1 details per customer in the table ordered by some set of columns? If so that isn't what you said you wanted.

Kristen's advice on the date comparisons is gold and you seem to be completely ignoring it.

If you want accurate help then my advice is to split the problem in smaller pieces. Post the *full* (not simplified) version of those problems and someone will not be able to resist showing how kewl he or she is by optimising them.

Posting something that is different to what you actually want to fix is meaningless unless you get the root cause of the problem absolutely right. I think you are putting people off from helping you here.

Here are a few questions that you can answer which will help people help you:

---------------------------------

1) Which version of SQL Server are you using? -- this is important as there are many methods of optimisation that work well in 2005 or later that are not available in 2000. In particular it is often easy to eliminate sub selects with either ROW_NUMBER() or OUTER/CROSS APPLY

2) Please provide the full table structure (including any indices present) for the tables involved.

3) Please provide the *actual* sql that you want to improve. There's nothing more likely to turn someone off from helping than when they post a solution and the OP says -- actually that isn't relevant because....

4) If possible please provide some data. This doesn't have to be actual data and it's probably better from a security point of view that it isn't but it should be representative of your actual data.

----------------

If you do this then you'll get awesome help here. Your problems will vanish.

I'm not having a go at you, but if you stop for a moment and have a look at your posts I think you'll find that you aren't coming across very well.

Best of luck,


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

---

IF OBJECT_ID('tempDb..#testTable') IS NOT NULL DROP TABLE #testTable

CREATE TABLE #testTable (
	[Id] INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED
	, [startDate] DATETIME
	, [endDate] DATETIME
	)

CREATE CLUSTERED INDEX IX_ID_START_END ON #testTable ([startDate], [endDate])

GO

INSERT #testTable ([startDate], [endDate])
SELECT
	CAST(CAST(CAST(NEWID() AS VARBINARY(2)) AS INT) AS DATETIME)
	, CAST(CAST(CAST(NEWID() AS VARBINARY(2)) AS INT)  AS DATETIME)
FROM
	(SELECT 1 AS [1] UNION SELECT 2 AS [2] UNION SELECT 3 AS [3]) a
	CROSS JOIN (SELECT 1 AS [1] UNION SELECT 2 AS [2] UNION SELECT 3 AS [3]) b
	CROSS JOIN (SELECT 1 AS [1] UNION SELECT 2 AS [2] UNION SELECT 3 AS [3]) c
	CROSS JOIN (SELECT 1 AS [1] UNION SELECT 2 AS [2] UNION SELECT 3 AS [3]) d
	CROSS JOIN (SELECT 1 AS [1] UNION SELECT 2 AS [2] UNION SELECT 3 AS [3]) e
	CROSS JOIN (SELECT 1 AS [1] UNION SELECT 2 AS [2] UNION SELECT 3 AS [3]) f
	CROSS JOIN (SELECT 1 AS [1] UNION SELECT 2 AS [2] UNION SELECT 3 AS [3]) g
	CROSS JOIN (SELECT 1 AS [1] UNION SELECT 2 AS [2] UNION SELECT 3 AS [3]) h
GO

-- This must be the only command in the batch -- it shows you the execution plan and index use
SET SHOWPLAN_ALL ON
GO

-- DATEDIFF EXAMPLE
-- Find any rows with startdate of 2010-Jan-01
SELECT
	*
FROM
	#testTable
WHERE
	DATEDIFF(DAY, [startDate], '20100101') = 0
GO

-- >= < EXAMPLE
SELECT
	*
FROM
	#testTable
WHERE
	[startDate] >= '20100101'
	AND [startDate] < '20100102'

Msg 2714, Level 16, State 6, Line 3
There is already an object named '#testTable' in the database.

EstimateRows	EstimateIO	EstimateCPU	AvgRowSize
729	NULL	NULL	NULL
729	0.01868056	0.0073741	27

EstimateRows	EstimateIO	EstimateCPU	AvgRowSize
1	NULL	NULL	NULL
1	0.003125	0.0001581	27

quote:

---

SELECT
*
FROM
#testTable
WHERE
DATEDIFF(DAY, [startDate], '20100101') = 0
GO

-- >= < EXAMPLE
SELECT
*
FROM
#testTable
WHERE
[startDate] >= '20100101'
AND [startDate] < '20100102'
[/code]

---