Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2011
    Posts
    8

    Unanswered: Stored procedure not producing all results

    have the following query that is a stored procedure:


    Code:
    SELECT FirstListing,OnCallStart,OnCallEnd
    FROM
    (
    SELECT
        moncallAdd.FirstListing,
        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'
     
    UNION    
    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'
    )t
    GROUP BY FirstListing,OnCallStart,OnCallEnd
    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 when I run that I get the following dataset:

    HEART HOSP - MOORE 2011-09-01 07:00:00.000 2011-09-01 18:00:00.000
    NAMC - LYSON 2011-09-01 07:00:00.000 2011-09-01 18:00:00.000
    SAMC, WESTLAKE, SETON SW - SIMPSON 2011-09-01 07:00:00.000 2011-09-01 18:00:00.000
    RRMC/SUMMIT/RELIANT/GT-LYSON 2011-09-01 07:00:00.000 2011-09-01 18:00:00.000
    SETON & CORNERST MAIN- MOORE 2011-09-01 07:00:00.000 2011-09-01 18:00:00.000
    SETON HAYS-KYLE - PEREZ 2011-09-01 07:00:00.000 2011-09-01 18:00:00.000
    ST DAVIDS - ROSEN 2011-09-01 13:00:00.000 2011-09-01 18:00:00.000



    and when I run this query:
    Code:
    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,
    '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'
     
    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,
    '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'
    and after I remove the duplicates and the deletes from my table, I am left with this dataset:

    BRACK & HEALTH S. - ROSEN 2011-07-22 10:22:00.000 2011-09-01 13:00:00.000 2011-09-01 18:00:00.000 Added
    CALL THERESA AT 320-0963 BEFORE CHANGING 2011-07-22 10:01:00.000 2011-09-01 08:00:00.000 2011-09-01 17:00:00.000 Added
    HEART HOSP - MOORE 2011-07-19 11:26:00.000 2011-09-01 07:00:00.000 2011-09-01 18:00:00.000 Added
    RRMC/SUMMIT/RELIANT/GT-LYSON 2011-07-22 10:25:00.000 2011-09-01 07:00:00.000 2011-09-01 18:00:00.000 Added
    SETON HAYS-KYLE - PEREZ 2011-07-19 11:38:00.000 2011-09-01 07:00:00.000 2011-09-01 18:00:00.000 Added
    NAMC - LYSON 2011-09-01 07:48:00.000 2011-09-01 07:00:00.000 2011-09-01 18:00:00.000 Added
    SAMC, WESTLAKE, SETON SW - SIMPSON 2011-07-22 10:26:00.000 2011-09-01 07:00:00.000 2011-09-01 18:00:00.000 Added
    SETON & CORNERST MAIN- MOORE 2011-07-19 09:46:00.000 2011-09-01 07:00:00.000 2011-09-01 18:00:00.000 Added
    ST DAVIDS - ROSEN 2011-07-22 10:24:00.000 2011-09-01 13:00:00.000 2011-09-01 18:00:00.000 Added

    The second set of data has the "complete" set of data but that's been filtered.You can see the difference between the two queries that I ran so the second query gives me ALL data, both adds and deletes. The other query only gives me the "adds" that don't have a matching delete, but obviously it's not catching all of that information. As you can see, the second dataset's first two entries aren't in the first dataset.

    Can anyone help me figure out why?

    thank you
    Doug

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    dougancil, your second output has ADDED on all the rows.

    Regardless, the HAVING clause only returns rows where ADDED exists and DELETED does not exist. Since a row can only be Added or DELETED (not both) you are only getting ADDED rows.
    Code:
    HAVING SUM(CASE WHEN Activity='Added' THEN 1 ELSE 0 END) >0
    AND SUM(CASE WHEN Activity='Deleted' THEN 1 ELSE 0 END) =0

  3. #3
    Join Date
    Feb 2011
    Posts
    8
    Stealth,

    Actually I am now close to getting all of my results but I'm stuck with a max clause.

    I have this now as my query:
    Code:
    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 the one thing I have left to discern is the latest of the adds and I think I need to use a max clause on the Addstart field, but have no idea how to do that. Can someone please assist?

    Thanks

    Doug

  4. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Dougancil, In your first post, you asked why there were no Deleted rows and I showed you. In you second post, you has how to do something so the query will work. But you have never described what it is you are trying to do.

    You appear to have 2 identical tables (from the columns in the query) that contain data for ADD and DELETE data.

    Beyond that, I have do idea what you are doing or what you output should be.

    By the way, the AddStart and Added generated column values don't seem to do anything as they are not used anywhere. Not sure why they are there?

  5. #5
    Join Date
    Feb 2011
    Posts
    8
    Stealth,

    What I'm trying to do is as follows:

    I have a web page that is built around this query that I'm using as a stored procedure. The query is supposed to check a few things:

    1. To match all adds with corresponding deletes (if any) and present ONLY the ones that have no matching deletes)
    2. To match all schedules that start before the current date/time and end after the current date/time.

    What I discovered in working with this is that even matching those criteria, I wasn't getting all of the correct data, so I had to throw the value of "initials" in there as a variable to check as well. Then Friday, I noticed that I was missing an entry that matched all of my criteria but for some reason wasn't showing up:

    Code:
    NAMC - WELCH 9/9/2011 7:07:00 AM 9/9/2011 6:00:00 PM 8/2/2011 3:22:07 PM Added   ZC
    
    NAMC - WELCH 9/9/2011 7:07:00 AM 9/9/2011 6:00:00 PM 8/2/2011 3:22:07 PM Deleted ZC
    
    NAMC - WELCH 9/9/2011 7:07:00 AM 9/9/2011 6:00:00 PM 8/2/2011 3:23:08 PM Added   ZC
    
    NAMC - WELCH 9/9/2011 7:07:00 AM 9/9/2011 6:00:00 PM 8/2/2011 3:23:08 PM Deleted ZC
    
    NAMC - WELCH 9/9/2011 7:07:00 AM 9/9/2011 6:00:00 PM 8/3/2011 11:27:16 AM Added  ZC
    and you can see here that the only valid entry is the last one, which is why I think that by using the MAX added, that it may show the latest valid value, which is what I would need. That's the only value I can see that I'm not checking for that would make this a "valid" entry, or am I missing something?

    Thank you

    Doug

  6. #6
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    dougancil, If I follow what you want, it looks like there may be an easier way. If you select from the ADD table and use NOT EXISTS subquery with the DELETE table, you should only return rows from the ADD table that do NOT have a matching DELETE row (as you seem to want). This is a skeleton of what that could look like. In the Subquery I am assuming FirstListing and StartOnCallTime uniquely pairs up the Adds and Deletes. If not change to what would.

    Code:
    SELECT MonCallADD.FirstListing
         , MOnCallADD.StartOnCallDate
         , MonCallADD.Initials
    FROM MonCallADD
    WHERE DATEADD(MINUTE, mOnCalladd.StartOnCallTime
                        , DATEADD(DAY   , mOnCalladd.StartOnCallDate
                                        , '12/31/1899')) < GETDATE()
      AND mOnCallAdd.schedname =@schedname
      AND NOT EXISTS (SELECT *
                      FROM MonCallDELETE
                      WHERE MonCallADD.FirstListing  = MonCallDELETE.FirstListing
                        AND MonCallADD.StartOnCallDate = MonCallDELETE.StartOnCallDate
    
                        AND 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
                     )
    Once you have it cleaned up, it should only return the one non-paired ADD row in you sample and you may not need a MAX function.

    This also eliminates the Group By / Having clauses.

Posting Permissions

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