Trouble JOINing table - 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 2008 Forums

Transact-SQL (2008)

Trouble JOINing table

Author

Topic

petersrj
Starting Member

20 Posts

Posted - 2011-01-21 : 13:22:26

My task is to return the starting time of a lookback period given a specific ending date. My Valid1Hour Table contains a record for every hour and I need to go back the last 100 hours where the temperature is greater than 200. The code below works perfectly for a single EndTime.

Now, I need to enhance this call. I have a new table called EndTimes for which I need to determine the start time for each of the Endtimes.
The endtimes are contiguous but might contain all 24 hours for a day. The Endtimes table contains a single column called DateInQuestion (DATETIME) and I need to JOIN it with the query below to get the start time for each record in Endtimes.

Thanks in advance.

DECLARE @EndTime Datetime
SET @EndTime = '9/1/2010 00:00'

SELECT Min(StartDate.TimeFrame) AS Mintime FROM
(
SELECT TOP 100 TimeFrame
FROM Valid1Hour
WHERE Temperature > 200
AND TimeFrame <= @EndTime
ORDER BY TimeFrame DESC
) AS StartDate

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-21 : 19:11:16

I think there is a better way..provide some sample data and expected results. What would the joining column be?

no idea for sure, but maybe something like this


SELECT TOP 100 
		 MIN(sd.TimeFrame) as MinStart
		,et.EndTimeInQuestion
FROM 
	Valid1Hour sd , EndTimes et
		 
WHERE Temperature > 200 
	AND TimeFrame >= dateadd(hh,-100,EndTimeInQuestion)
GROUP BY EndTimeInQuestion
ORDER BY MIN(sd.TimeFrame) DESC

Poor planning on your part does not constitute an emergency on my part.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-24 : 11:38:14

[code]
SELECT e.EndTime,v.MinStart
FROM EndTimes e
CROSS APPLY(
SELECT MIN(TimeFrame) AS MinStart
FROM Valid1Hour
WHERE Temperature > 200
AND TimeFrame <= e.EndTime
) AS v
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

petersrj
Starting Member

20 Posts

Posted - 2011-01-24 : 13:53:47

Dataguru and Visakh16, thanks for the help and guidance but we are not quite there. Here is code to create the tables and lets modify it slightly. Instead of going back 100 hours, lets go back 5 hours.

In my EndTimes Table, there are 6 DateInQuestion entries (hour 00 - hour 05). The end result should be the following:

DateInQuestion StartTime
1/24/2011 00:00 1/24/2011 04:00
1/24/2011 01:00 1/24/2011 05:00
1/24/2011 02:00 1/24/2011 07:00
1/24/2011 03:00 1/24/2011 08:00
1/24/2011 04:00 1/24/2011 09:00
1/24/2011 05:00 1/24/2011 14:00

Here is the code to create the Tables:

CREATE TABLE [dbo].[Valid1Hour] (
TimeFrame [Datetime] NOT NULL,
Temperature [float] NULL
) on [PRIMARY]
GO

INSERT INTO Valid1Hour Values ('1/24/2011 00:00', '220')
INSERT INTO Valid1Hour Values ('1/24/2011 01:00', '222')
INSERT INTO Valid1Hour Values ('1/24/2011 02:00', '224')
INSERT INTO Valid1Hour Values ('1/24/2011 03:00', '226')
INSERT INTO Valid1Hour Values ('1/24/2011 04:00', '228')
INSERT INTO Valid1Hour Values ('1/24/2011 05:00', '230')
INSERT INTO Valid1Hour Values ('1/24/2011 06:00', '10')
INSERT INTO Valid1Hour Values ('1/24/2011 07:00', '232')
INSERT INTO Valid1Hour Values ('1/24/2011 08:00', '234')
INSERT INTO Valid1Hour Values ('1/24/2011 09:00', '236')
INSERT INTO Valid1Hour Values ('1/24/2011 10:00', '20')
INSERT INTO Valid1Hour Values ('1/24/2011 11:00', '30')
INSERT INTO Valid1Hour Values ('1/24/2011 12:00', '40')
INSERT INTO Valid1Hour Values ('1/24/2011 13:00', '50')
INSERT INTO Valid1Hour Values ('1/24/2011 14:00', '240')
INSERT INTO Valid1Hour Values ('1/24/2011 15:00', '242')
INSERT INTO Valid1Hour Values ('1/24/2011 16:00', '244')
INSERT INTO Valid1Hour Values ('1/24/2011 17:00', '246')
INSERT INTO Valid1Hour Values ('1/24/2011 18:00', '248')
INSERT INTO Valid1Hour Values ('1/24/2011 19:00', '250')
INSERT INTO Valid1Hour Values ('1/24/2011 20:00', '252')
INSERT INTO Valid1Hour Values ('1/24/2011 21:00', '254')
INSERT INTO Valid1Hour Values ('1/24/2011 22:00', '256')
INSERT INTO Valid1Hour Values ('1/24/2011 23:00', '258')

CREATE TABLE [dbo].[EndTimes] (
DateInQuestion [Datetime] NOT NULL
) on [PRIMARY]
GO

INSERT INTO EndTimes Values ('1/24/2011 00:00')
INSERT INTO EndTimes Values ('1/24/2011 01:00')
INSERT INTO EndTimes Values ('1/24/2011 02:00')
INSERT INTO EndTimes Values ('1/24/2011 03:00')
INSERT INTO EndTimes Values ('1/24/2011 04:00')
INSERT INTO EndTimes Values ('1/24/2011 05:00')

Thank you again.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-24 : 14:46:25

What is the business rule or logic for connecting 1/24/2011 05:00 END TIME to 1/24/2011 14:00 START TIME

Poor planning on your part does not constitute an emergency on my part.

petersrj
Starting Member

20 Posts

Posted - 2011-01-24 : 16:33:23

Dataguru, I need to find the starting time which includes five hours of data where the temperature is > 200. Therefore, if the 05:00 hour is passed, it would return the 14:00 timeframe becasue the 05:00, 07:00, 08:00, 09:00 and 14:00 hour all have temperatures > 200.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-24 : 18:56:42

14:00 is more than 5 hours different.. I am not sure how I can get the 14:00 returned if the issue is 5 hours.

This is closer perhaps?


SELECT 
	et.DateInQuestion,MAX(TimeFrame) as LastEndTime
	 
FROM 
	Valid1Hour sd , EndTimes et
		 
WHERE  
Temperature > 200 
	AND DATEDIFF(HOUR,DateInQuestion,TimeFrame) <=5
	AND DATEDIFF(HOUR,DateInQuestion,TimeFrame) >=0
GROUP BY et.DateInQuestion
ORDER BY et.DateInQuestion ASC

Returns:
DateInQuestion LastEndTime
2011-01-24 00:00:00.000 2011-01-24 05:00:00.000
2011-01-24 01:00:00.000 2011-01-24 05:00:00.000
2011-01-24 02:00:00.000 2011-01-24 07:00:00.000
2011-01-24 03:00:00.000 2011-01-24 08:00:00.000
2011-01-24 04:00:00.000 2011-01-24 09:00:00.000
2011-01-24 05:00:00.000 2011-01-24 09:00:00.000

I know it isn't exact, but maybe you can clarify further. Based on your desired result, it appears you don't want any duplicate EndTimes returned (something like the max(enddate) not returned by a later startdate....)

Not sure..

Poor planning on your part does not constitute an emergency on my part.

petersrj
Starting Member

20 Posts

Posted - 2011-01-25 : 10:01:14

Thanks for the help. I worked with visakh16's approach using the CROSS APPLY function and was able to produce the results desired. Here is the completed code.

SELECT e.DateInQuestion,v.MinStart
FROM EndTimes e
CROSS APPLY(
SELECT MAX(TimeFrame) AS MinStart FROM
(Select Top 5 TimeFrame
FROM Valid1Hour
WHERE Temperature > 200
AND TimeFrame >= e.DateInQuestion
ORDER BY TimeFrame
) AS X
) AS v

Thanks again for all of your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources