September 9, 2011 at 2:45 pm
I have the following query:
SELECT FirstListing,OnCallStart,OnCallEnd, Initials
FROM
(
SELECT
moncallAdd.FirstListing,
Dateadd(minute, mOncallAdd.addtime,
Dateadd(day, mOnCallAdd.adddate, '12/31/1899')) as AddStart,
DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')) AS OnCallStart,
DATEADD(MINUTE, mOnCallAdd.duration,
DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899'))) AS OnCallEnd,
Dateadd(second, moncalladd.Addtime,
Dateadd (Minute, moncalladd.AddTime,
Dateadd(Day, moncalladd.Adddate, '12/31/1899'))) as Added,
'Added' As Activity,
Initials
FROM
mdr.dbo.mOnCallAdd
WHERE DATEADD(MINUTE, mOnCalladd.StartOnCallTime,
DATEADD(DAY, mOnCalladd.StartOnCallDate, '12/31/1899')) < GETDATE()
AND
DATEADD(MINUTE, mOnCalladd.duration,
DATEADD(MINUTE, mOnCalladd.StartOnCallTime,
DATEADD(DAY, mOnCalladd.StartOnCallDate, '12/31/1899'))) > GETDATE() and
mOnCallAdd.schedname =@schedname
UNION
SELECT
moncallDelete.FirstListing,
Dateadd(minute, mOnCallDelete.addtime,
Dateadd(day, mOnCallDelete.adddate, '12/31/1899')) as AddStart,
DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,
DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899')) AS OnCallStart,
DATEADD(MINUTE, mOnCallDelete.duration,
DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,
DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899'))) AS OnCallEnd,
Dateadd(second,moncalldelete.Addtime,
Dateadd (Minute, moncalldelete.AddTime,
Dateadd(Day, moncalldelete.Adddate, '12/31/1899'))) as Added,
'Deleted' as Activity,
Initials
FROM
mdr.dbo.mOnCallDelete
WHERE DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,
DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899')) < GETDATE()
AND
DATEADD(MINUTE, mOnCallDelete.duration,
DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,
DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899'))) > GETDATE() and
mOnCallDelete.schedname = @schedname
)t
GROUP BY FirstListing,OnCallStart,OnCallEnd, Initials
HAVING SUM(CASE WHEN Activity='Added' THEN 1 ELSE 0 END) >0
AND SUM(CASE WHEN Activity='Deleted' THEN 1 ELSE 0 END) =0
and while this catches almost all results, the one thing that I noticed is that if something is listed as added and it's the most recent version of that, that my query isn't capturing that information. What I need to do is to use a MAX on the AddDate field and can't seem to get that to work. Any assistance would be welcome.
Thank you
Doug
September 10, 2011 at 4:11 am
Looking at the code, one thing I can think of is that maybe you are trying to use MAX in one of the components of the union, instead of in the very first select list in the code, or are trying some bizarre means to avoid selecting the max values *(along with the identifying elements for teh groups) into a seperate derived table. I guess that maybe what you have an extra cerived table in the query which contains the maximum values plus the grouping columns that you need to determine whether something is "latest", then you could compare individual adddates with the matching maxadddate to produce a "latest/not/atest" column in your result.
Without test data, expected result, and a means of generating the tables involved the only way anyone can do more than make a possibly unhelpful remark like that is to try to reconstruct the table definitions from the code you have shown and invent some test data and guess what results you are trying for and then try someme code and see if it looks as if it might work; since that process will very often get you the wrong answer, people are generally unwilling to do it because it's quite likely to be wasted effort and not help you at all. So if the above comment about where MAX should go and having another derived table doesn't lead you to a solution I suggest you read the excellent article Forum Etiquette: How to post data/code on a forum to get the best help[/url] and post some scripts that will enable people to help you.
Tom
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply