I have the following query:
SELECT a.* FROM
(SELECT
moncallAdd.FirstListing,
max (Dateadd(MINUTE, moncalladd.addtime,
DateAdd(Day,moncalladd.adddate,'12/31/1899'))) as 'Adddate',
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,
'Added' AS Activity
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 = 'capital neph') a
LEFT JOIN
(SELECT
moncallDelete.FirstListing,
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,
'Deleted' AS Activity
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 = 'capital neph') b
ON a.FirstListing = b.FirstListing
and a.oncallstart = b.oncallstart
and a.oncallend = b.oncallend
and it produces this dataset:
CALL THERESA AT 320-0963 BEFORE CHANGING 2011-05-02 12:43:00.000 2011-08-24 08:00:00.000 2011-08-24 17:00:00.000 Added
BRACK & HEALTH S.- MAIDMENT 2011-07-19 10:07:00.000 2011-08-24 07:00:00.000 2011-08-24 18:00:00.000 Added
ST DAVIDS - ERKO 2011-07-19 10:21:00.000 2011-08-24 12:00:00.000 2011-08-24 18:00:00.000 Added
RRMC/SUMMIT/RELIANT/GT-MIDID 2011-07-19 11:04:00.000 2011-08-24 13:00:00.000 2011-08-24 18:00:00.000 Added
SETON HAYS-KYLE - PEREZ 2011-07-19 11:38:00.000 2011-08-24 07:00:00.000 2011-08-24 18:00:00.000 Added
SETON & CORNERST MAIN- MOORE 2011-07-19 09:46:00.000 2011-08-24 07:00:00.000 2011-08-24 18:00:00.000 Added
SAMC, WESTLAKE, SETON SW - SIMMONS 2011-07-19 11:14:00.000 2011-08-24 07:00:00.000 2011-08-24 18:00:00.000 Added
SAMC, WESTLAKE, SETON SW - SIMMONS 2011-07-19 11:23:00.000 2011-08-24 13:00:00.000 2011-08-24 18:00:00.000 Added
SAMC, WESTLAKE, SETON SW - SIMMONS 2011-08-04 16:04:00.000 2011-08-24 07:00:00.000 2011-08-24 18:00:00.000 Added
NAMC - MIDIDDODI 2011-07-19 10:05:00.000 2011-08-24 12:00:00.000 2011-08-24 18:00:00.000 Added
ST DAVIDS - ERKO 2011-07-19 10:26:00.000 2011-08-24 07:00:00.000 2011-08-24 18:00:00.000 Added
NAMC - MIDIDDODI 2011-07-19 10:41:00.000 2011-08-24 13:00:00.000 2011-08-24 18:00:00.000 Added
RRMC/SUMMIT/RELIANT/GT-LYSON 2011-07-19 10:54:00.000 2011-08-24 13:00:00.000 2011-08-24 18:00:00.000 Added
NAMC - LYSON 2011-08-02 14:09:00.000 2011-08-23 13:00:00.000 2011-08-24 18:00:00.000 Added
NAMC - LYSON 2011-07-19 09:59:00.000 2011-08-24 12:00:00.000 2011-08-24 18:00:00.000 Added
HEART HOSP - MOORE 2011-07-19 11:26:00.000 2011-08-24 07:00:00.000 2011-08-24 18:00:00.000 Added
as you can see there are duplicate entries, but I only need the “lastest” entries when there are duplicates. For example:
SAMC, WESTLAKE, SETON SW - SIMMONS 2011-07-19 11:14:00.000 2011-08-24 07:00:00.000 2011-08-24 18:00:00.000 Added
SAMC, WESTLAKE, SETON SW - SIMMONS 2011-07-19 11:23:00.000 2011-08-24 13:00:00.000 2011-08-24 18:00:00.000 Added
SAMC, WESTLAKE, SETON SW - SIMMONS 2011-08-04 16:04:00.000 2011-08-24 07:00:00.000 2011-08-24 18:00:00.000 Added
I would only need the entry with the “add date” of 2011-08-04 as it is the newest entry (the bold example.) I know that I’ll need to use a max and then a group by clause but I’ve tried a couple of different variations and can’t seem to get a working query. Can someone give me an example of how to use the max and groupby in this instance?
Thank you
Doug