Selecting from two tables containing different levels of date accuracy

Question

Selecting from two tables containing different levels of date accuracy

Jon-343748

SSChasing Mays

Points: 626
More actions
July 24, 2006 at 5:06 pm

#366940

I've got a select statement which selects data from two different tables based on a time stamp, except one of my tables has a value for each day, and the other has a value for every hour. So how do I do my WHERE piece so the date from the daily table matches that from the hourly table?
Here's what I've got so far...
SELECT
B.chrPlantAccro AS Plant,
B.vcName AS Block,
G.sdtT AS [Timestamp],
G.fltMeas AS Gas,
H.fltMeas AS [Power]
FROM
dbo._MatrixBlockToMarket AS M
INNER JOIN dbo._PRDBlocks AS B
ON M.chrPlantAccro = B.chrPlantAccro AND M.sintBlockNumb = B.sintBlockNumb,
dbo._GasData AS G,
dbo._PowerHourlyData AS H
WHERE
(G.sdtT >= '07/01/06') AND
(G.sdtT = H.sdtT) AND
(M.sintGasMarkID = G.sintGasMarkID) AND
(M.sintPowMarkID = D.sintPowMarkID) AND
(D.fltMeas IS NOT NULL)

The problem is with (G.sdtT = H.sdtT). The hour value in table 'G' is always 00:00 while the hour value in table H is for each hour of the day ( 00:00, 01:00, 02:00, etc).
To match these up, I was figuring I would just force the hour of the H table to zero only to see if the date matches between G and H. The problem is I can't find a T-SQL function that will do this for me.
Any ideas?
I already tried:
WHERE (Month(G.sdtT) = Month(H.sdtT)) AND (Day(G.sdtT) = Day(H.sdtT))
This didn't work.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Paul Mu SSCertifiable Points: 5547 More actions · Answer 1

To strip the hours from your table aliased as 'H', you can use the 'convert' function, eg. G.sdtT = convert(char(10),H.sdtT,102)

Refer to BOL for more info on the convert function.

Jon-343748 SSChasing Mays Points: 626 More actions · Answer 2

Thanks! That worked like a charm! Here's the final line:

(CONVERT(char,G.sdtT,101) = CONVERT(char,H.sdtT,101))

RyanRandall SSChampion Points: 13623 More actions · Answer 3

It's usually more efficient to avoid varchar when comparing dates, and to try to stick to the datetime format. The following should behave the same way, but give you better performance...

dateadd(day, datediff(day, 0, G.sdtT), 0) = dateadd(day, datediff(day, 0, H.sdtT), 0)

And here's a quick working example...

declare @d datetime

set @d = getdate()

select @d as Now, dateadd(day, datediff(day, 0, @d), 0) as StartOfToday

/*results

Now StartOfToday

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

2006-07-25 18:26:53.390 2006-07-25 00:00:00.000

*/

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Jon-343748 SSChasing Mays Points: 626 More actions · Answer 4

Ahh thanks, Ryan. That select was taking about a minute and a half covering about 500k rows, so I'm not sure if that's considered a good return time or not. I'll see how much difference I get with this code.

RyanRandall SSChampion Points: 13623 More actions · Answer 5

With optimisation, you should be able to get the time down to a lot less than that. See these articles if you want to try to do that...

http://www.sqlservercentral.com/columnists/jsack/sevenshowplanredflags.asp

http://www.sql-server-performance.com/query_execution_plan_analysis.asp

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.