January 31, 2008 at 9:42 am
i have 2 tables. one is conference attendance and the other is conference info. one table has seperate date and time fields and the other has one datetime field. i am having trouble with matching the time portion. if one table has 5:00 and the other has 4:58 and 5:03, i need to match but with an hour time range.
SELECT COUNT(tblConferences.Division) AS Total, tblConferences.Subdivision, tblConfAtt.ConfDate,tblConfAtt.CustomerID
FROM tblConfAtt INNER JOIN
tblConferences ON tblConfAtt.Conference = tblConferences.Room AND MONTH(tblConfAtt.ConfDate) = MONTH(tblConferences.ConfDate) AND
DAY(tblConfAtt.ConfDate) = DAY(tblConferences.ConfDate) AND YEAR(tblConfAtt.ConfDate) = YEAR(tblConferences.ConfDate) AND
{ fn HOUR(tblConfAtt.ConfDate) } = { fn HOUR(tblConferences.TimeBegin) }
WHERE (tblConferences.ConfDate >= '8/15/2007') AND (tblConferences.Subdivision = N'Nuclear Medicine')
GROUP BY tblConferences.Subdivision, tblConfAtt.CustomerID, tblConfAtt.ConfDate
January 31, 2008 at 9:58 am
try rounding both datetimes.
select '11:29',
dateadd(minute,
round(cast(datediff(minute, 0, '11:29') as float) / 60,0) * 60, 0)
January 31, 2008 at 10:03 am
You could do something like this:
DECLARE @tbl1 table(
Col1 varchar(10),
dt varchar(15),
ti varchar(10)
)
declare @tbl2 table(
Col1 varchar(10),
dt datetime
)
insert into @tbl1
select 'test', '1/31/2008','11:00 AM'
insert into @tbl2
select 'test2','1/31/2008 11:30 AM'
select *
from @tbl1 a inner join
@tbl2 b on CONVERT(DATETIME,a.dt + ' ' + a.ti)
between CONVERT(DATETIME,a.dt + ' ' + a.ti)
AND DATEADD(hh,1,CONVERT(DATETIME,a.dt + ' ' + a.ti))
January 31, 2008 at 10:09 am
Please format your code 😀
SELECT
COUNT(tblConferences.Division) AS Total, tblConferences.Subdivision, tblConfAtt.ConfDate,tblConfAtt.CustomerID
FROM tblConfAtt
INNER JOIN tblConferences
ON tblConfAtt.Conference = tblConferences.Room AND
MONTH(tblConfAtt.ConfDate) = MONTH(tblConferences.ConfDate) AND
DAY(tblConfAtt.ConfDate) = DAY(tblConferences.ConfDate) AND
YEAR(tblConfAtt.ConfDate) = YEAR(tblConferences.ConfDate) AND
ABS( DateDiff(hour, tblConfAtt.ConfDate, tblConferences.TimeBegin) ) <= 1
WHERE (tblConferences.ConfDate >= '8/15/2007') AND (tblConferences.Subdivision = N'Nuclear Medicine')
GROUP BY tblConferences.Subdivision, tblConfAtt.CustomerID, tblConfAtt.ConfDate
If you have many rows (ie 1000s) then you may wish to look at storing a computed column in your tables which is solely the date and then include that column in an index.
January 31, 2008 at 10:33 am
also consider eliminating the month(), date(), year() comparisons
SELECT COUNT(tblConferences.Division) AS Total,
tblConferences.Subdivision, tblConfAtt.ConfDate,tblConfAtt.CustomerID
FROM tblConfAtt
INNER JOIN tblConferences
ON tblConfAtt.Conference = tblConferences.Room
and convert(char(10),tblConfAtt.ConfDate,120) = convert(char(10),tblConferences.ConfDate,120) -- date match
and datepart( hour,
dateadd(minute, round(cast(datediff(minute, 0, tblConfAtt.ConfDate) as float) / 60,0) * 60, 0) ) =
datepart( hour,
dateadd(minute, round(cast(datediff(minute, 0, tblConferences.TimeBegin) as float) / 60,0) * 60, 0) ) -- rounded time match
WHERE (tblConferences.ConfDate >= '8/15/2007') AND (tblConferences.Subdivision = N'Nuclear Medicine')
GROUP BY tblConferences.Subdivision, tblConfAtt.CustomerID, tblConfAtt.ConfDate
January 31, 2008 at 10:47 am
it works. thank you to everyone for helping. very quick responses!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply