Help with WHERE clause - 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)

Help with WHERE clause

Author

Topic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-27 : 13:33:11

I can't seem to figure this one out, so I'm asking for help. I need to write a query that when given a date and time will check to see if it (minus the time) is between two columns but only the date portion of the two columns. So if I'm given 10-16-2004 02:00 and the two columns' values are 10-16-2004 04:00 and 10-17-2004 06:00, then I should see this row as 10-16-2004 is between and including 10-16-2004 and 10-17-2004. I have an idea of a solution, but it wouldn't make use of indexes...


SET NOCOUNT ON

DECLARE @Date datetime

CREATE TABLE Table1 
(Column1 varchar(10) NOT NULL, StartTime datetime NOT NULL, EndTime datetime NOT NULL)

CREATE INDEX idx_Table1_StartTime_EndTime ON dbo.Table1 (StartTime, EndTime)

INSERT INTO Table1 (Column1, StartTime, EndTime)
SELECT 'Tara', '10-16-2004 00:00', '10-18-2004 16:00'
UNION ALL
SELECT 'Mike', '10-16-2004 08:00', '10-18-2004 23:00'
UNION ALL
SELECT 'Fred', '10-18-2004 02:00', '10-21-2004 09:00'
UNION ALL
SELECT 'Michelle', '10-19-2004 06:00', '10-20-2004 00:00'
UNION ALL
SELECT 'Laura', '10-19-2004 11:00', '10-26-2004 13:00'

SET @Date = '10-15-2004 08:30'

SELECT Column1
FROM Table1
WHERE ...

SET @Date = '10-16-2004 00:00'

SELECT Column1
FROM Table1
WHERE ...

SET @Date = '10-18-2004 02:00'

SELECT Column1
FROM Table1
WHERE ...

SET @Date = '10-20-2004 02:00'

SELECT Column1
FROM Table1
WHERE ...

DROP TABLE Table1

For 10-15-2004 08:30, no rows should be returned.
For 10-16-2004 00:00, Tara and Mike should be returned.
For 10-18-2004 02:00, Tara, Mike, and Fred should be returned.
For 10-20-2004 02:00, Fred, Michelle and Laura should be returned.

The WHERE clause would be the same for all of them above, I just duplicated it so that you can run it all at once to see if the results match up with what I have specified.

Tara

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2004-10-27 : 13:44:47

Is this a trick question?

CONVERT(int,@Date,112) BETWEEN CONVERT(int,StartTime,112) AND CONVERT(int,EndTime,112)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-27 : 13:49:59

Yours wouldn't make use of indexes though, which is why I need help. I had come up with that solution, but if you'll notice the actual execution plan of the query, table scans occur. I've changed my initial post to include the index creation on StartTime and EndTime.

Tara

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-27 : 14:06:14

Try:

WHERE StartTime < dateadd(d,0,datediff(d,0,@Date)+1) AND
EndTime >= dateadd(d,0,datediff(d,0,@Date))

The starttime must be less than the NEXT day after @Date at 12:00:00AM, and the EndTime must be greater than or equal to @Date at 12:00:00AM.

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-27 : 14:08:02

By the way -- thanks for the DDL, sample data and expected results!

- Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-27 : 14:14:38

Perfect, Jeff! I knew you'd be here to the rescue.

Wouldn't it be funny if I didn't post the DDL, DML and expected results, then I'd be the first reply to it saying please post it. Then I'd ask: What's DDL?

Tara

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2004-10-27 : 14:26:04

[code]
SELECT Column1
FROM Table1
WHERE DATEADD(d, DATEDIFF(d, 0, @Date), 1) > StartTime
AND DATEADD(d, DATEDIFF(d, 0, @Date), 0) <= EndTime
[/code]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-27 : 14:31:42

Those appear to be equivalent just switched around. Same execution plan.

Thanks Arnold.

Tara

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2004-10-27 : 14:39:01

quote:
Those appear to be equivalent just switched around. Same execution plan.

Yeah, I know. That's what the man with the strange legs shooting the Yak is for.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-27 : 14:44:00

I had typed my response but didn't hit submit until after you edited your post.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-27 : 14:44:42

Just noticed a problem with my data. Here's the real problem. I've got this in a UDF to concatenate together a CSV string. The problem is I don't want duplicates. Take a look at this:



SET NOCOUNT ON

CREATE TABLE Table1 (Column1 varchar(10) NOT NULL, StartTime datetime NOT NULL, EndTime datetime NOT NULL)

CREATE INDEX idx_Table1_StartTime_EndTime ON dbo.Table1 (StartTime, EndTime)

INSERT INTO Table1 (Column1, StartTime, EndTime)
SELECT 'Tara', '10-16-2004 00:00', '10-18-2004 16:00'
UNION ALL
SELECT 'Mike', '10-16-2004 08:00', '10-18-2004 23:00'
UNION ALL
SELECT 'Fred', '10-18-2004 02:00', '10-21-2004 09:00'
UNION ALL
SELECT 'Michelle', '10-19-2004 06:00', '10-20-2004 00:00'
UNION ALL
SELECT 'Laura', '10-19-2004 11:00', '10-26-2004 13:00'
UNION ALL
SELECT 'Tara', '10-17-2004', '10-19-2004'
GO

CREATE   FUNCTION udf_Convert_Column1_ToCSV
(@Date datetime)
RETURNS varchar(4000)
AS

BEGIN

DECLARE @s varchar(4000)

SELECT @s = COALESCE(@s + ',', '') + Column1
FROM Table1
WHERE 
	StartTime < DATEADD(d, 0, DATEDIFF(d, 0, @Date) + 1) AND
	EndTime >= DATEADD(d, 0, DATEDIFF(d, 0, @Date))

RETURN (@s)

END

GO

SELECT dbo.udf_Convert_Column1_ToCSV('10-17-2004 08:30')

DROP FUNCTION udf_Convert_Column1_ToCSV
DROP TABLE Table1

How would the UDF be modified so when called above, the output would be Tara,Mike. And not Tara,Mike,Tara.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-27 : 14:51:28

BTW, this may be a non-issue as the duplicates occur in our dev environment via manually added rows. It might not be a real world situation that these dups be in this table. I'm checking with our PM to find out, but I'd still like to cover it in the UDF just in case.

Tara

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2004-10-27 : 14:52:45

Probably get sniped again

, but the obvious answer is to use


SELECT @s = COALESCE(@s + ',', '') + Column1
FROM (
    SELECT DISTINCT Column1
    FROM Table1
    WHERE 
      StartTime < DATEADD(d, 0, DATEDIFF(d, 0, @Date) + 1) AND
      EndTime >= DATEADD(d, 0, DATEDIFF(d, 0, @Date))
  ) AS A

Or what Jeff says here, which is a bit shorter
|
v

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-27 : 14:54:05

Add a GROUP BY Column1 to the end of your SELECT which concatenates the string, in the UDF.

- Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-27 : 14:59:37

I had tried DISTINCT but didn't do it in the derived table. I didn't consider the GROUP BY as I just assumed I'd have a problem due to the variable in the SELECT. I'm making this too complicated in my head.

Thanks Jeff and Arnold!

Tara

Kristen
Test

22859 Posts

Posted - 2004-10-27 : 20:50:16

"Wouldn't it be funny if I didn't post the DDL, DML and expected results, then I'd be the first reply to it saying please post it. Then I'd ask: What's DDL?"

lol

Kristen

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-10-29 : 13:35:05

quote:
Originally posted by Arnold Fribble

quote:
Those appear to be equivalent just switched around. Same execution plan.

Yeah, I know. That's what the man with the strange legs shooting the Yak is for.

Hey Arnold, I thought those legs looked funny, too, but I think that's actually supposed to be a balcony that the sniper is standing on and leaning over. But hey, I don't think we've ever accused Rob of being a graphic artist.

-----------------------------------------------------
Words of Wisdom from AjarnMark, owner of Infoneering

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources