Stored Procedure missing results

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Stored Procedure missing results

Author

Topic

dougancil
Posting Yak Master

217 Posts

Posted - 2011-09-01 : 17:29:26

I have the following query that I use as a Stored Procedure:


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 while it does pull data, I'm noticing that it's not pulling it all. Here is the dataset I get when I used this query:


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 a query this query:



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

you can see that the first 2 entries on my second dataset were missed by my query, and I don't know why. Can anyone offer me a reason as to why these two entries may have been missed?

Thank you

Doug

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-02 : 00:54:01

would be much better if you can atleast format your query output so that its easier for somebody to make out whats getting missed

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dougancil
Posting Yak Master

217 Posts

Posted - 2011-09-02 : 11:42:15

visakh,

My apologies. Here's my first 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 my second 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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-02 : 12:49:01

and what should be your output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dougancil
Posting Yak Master

217 Posts

Posted - 2011-09-02 : 12:52:52

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources