SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Removing redundant information

    I have the following query:

    Code:
    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:

    Code:
    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

  2. #2
    SitePoint Enthusiast
    Join Date
    Aug 2011
    Location
    OH, USA
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    something like
    select distinct max(mOnCallAdd.StartOnCallDate) as minDate, moncallAdd.FirstListing from mdr.dbo.mOnCal


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •