Removing redudant data - TSQL 2000

Question

dougancil 0 Junior Poster in Training

13 Years Ago

I have the following stored procedure:

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')) 
    BETWEEN Convert(VARCHAR(10), GetDate(), 101) AND GetDate() and
     mOnCallAdd.SchedName = @schedname
 
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')) 
    BETWEEN Convert(VARCHAR(10), GetDate(), 101) AND GetDate() and
     mOnCallDelete.SchedName = @schedname

and what this query currently does is to return back values for oncallstart up and including the current time for anything that matches a schedname and what I'm needing to do is as follows:

Search for OnCallEnddate past todays date that has a OncallStartime before the current time, and also that shows only "added" as activity.

Can anyone offer a way to do that? Thank you

Doug

mssql

2 Contributors
14 Replies
128 Views
1 Week Discussion Span
Latest Post 13 Years Ago Latest Post by adam_k

adam_k 239 Master Poster

13 Years Ago

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'))) > convert(char(10),GETDATE(),101) -- 101 is US format, as is your '12/31/1899' value. Change it if necessary.

If you find it easier, you can use your original query as a derived table and query that instead.

adam_k 239 Master Poster

13 Years Ago

> convert(char(10),GETDATE(),101)

Change this (part of the last criteria, just before the comment) to

> convert(char(10),DATEADD(d,getdate(),1),101)

I guess I didn't understand your requirement correctly.

adam_k 239 Master Poster

13 Years Ago

Now you are making sense on the 'Added' part of your requirement.
How where you planning to filter out this kind of data in a datagridview (in your other post) or isn't this the same thing?

Anyway, since this isn't probably your query I'm keeping it simple, by using 2 derived tables and joining them to get you your result.

select a.* 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'))) >  convert(char(10),DATEADD(d,getdate(),1),101)
and  mOnCallAdd.SchedName = @schedname) 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'))) >  convert(char(10),DATEADD(d,getdate(),1),101)
and  mOnCallDelete.SchedName = @schedname) b 
on a.FirstListing = b.FirstListing
and a.OnCallStart = b.OnCallStart
and a.OnCallEnd = b.OnCallEnd
where b.FirstListing is null

Try it and let me know.

adam_k 239 Master Poster

13 Years Ago

I'm sorry, I got a bit confused. Are the deletes in the criteria or not? They way you've modified this query they are not considered at all.
Only when some field from deletes that shouldn't be null is found to be null then we can be sure that this addition hasn't been deleted.

If you run the query with the

b.FirstListing IS NULL

or any other field that should never be null in table moncallDelete do you get 3 results for SAMC, WESTLAKE ?

If you don't want the deletions to be part of the criteria, then we can select max( moncalladd.addtime) or something and only get one record per FirstListing with the latest datetime

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

dougancil 0 Junior Poster in Training · Answer 1 · 2011-08-23T02:19:03+00:00

Adam,

I tried your query and it seems that I'm still getting data that doesn't quite match what I'm looking for. For example here are some lines that are already "expired"

OLIVER 2011-08-21 08:00:00.000 2011-08-22 08:00:00.000
C-2 DOCTOR - 2011-08-15 08:00:00.000 2011-08-22 08:00:00.000
C-2 DOCTOR - 2011-08-15 08:00:00.000 2011-08-22 08:00:00.000
C-1 ONCALL - 2011-08-21 08:00:00.000 2011-08-22 08:00:00.000
C-2 ONCALL - 2011-08-21 08:00:00.000 2011-08-22 08:00:00.000

You can see the end time on those as being 8 am this morning.

dougancil 0 Junior Poster in Training · Answer 2 · 2011-08-23T21:05:13+00:00

adam

here's a sample of the data using your query:

BRACK & HEALTH S.- MAIDMENT 2011-08-23 07:00:00.000 2011-08-23 12:00:00.000
BRACK & HEALTH S.- MAIDMENT 2011-08-23 07:00:00.000 2011-08-23 13:00:00.000
BRACK & HEALTH S.- MAIDMENT 2011-08-23 07:00:00.000 2011-08-23 18:00:00.000

but if I do my original query for this, here's what you'll see:

BRACK & HEALTH S.- MAIDMENT 2011-08-23 07:00:00.000 2011-08-23 12:00:00.000 Deleted
BRACK & HEALTH S.- MAIDMENT 2011-08-23 07:00:00.000 2011-08-23 18:00:00.000 Added
BRACK & HEALTH S.- MAIDMENT 2011-08-23 07:00:00.000 2011-08-23 12:00:00.000 Added
BRACK & HEALTH S.- MAIDMENT 2011-08-23 07:00:00.000 2011-08-23 13:00:00.000 Added
BRACK & HEALTH S.- MAIDMENT 2011-08-23 07:00:00.000 2011-08-23 18:00:00.000 Deleted

You can see that there are 5 entries with 3 "added" and 2 "deletes" leaving me with one entry. That's what I'm trying to do, is to remove the deleted activity and just leave the outstanding "added" activity that doesn't match any "deleted" activity. Does that make sense? The line in bold should be the only line of data that is returned to me from your query.

dougancil 0 Junior Poster in Training · Answer 3 · 2011-08-24T21:44:22+00:00

Adam,

ok, I've tried your query and had to modify it a bit:

SELECT a.* 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') 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

but it still seems to be pulling some information that it doesnt need to. Here are my two data sets, with my original query (with the deletes removed)

HEART HOSP - MOORE	2011-08-24 07:00:00.000	2011-08-24 18:00:00.000	Added
ST DAVIDS - ERKO	2011-08-24 07:00:00.000	2011-08-24 18:00:00.000	Added
RRMC/SUMMIT/RELIANT/GT-CIPLEU	2011-08-24 07:00:00.000	2011-08-24 13:00:00.000	Added
SETON HAYS-KYLE - PEREZ	2011-08-24 07:00:00.000	2011-08-24 18:00:00.000	Added
CALL THERESA AT 320-0963 BEFORE CHANGING	2011-08-24 08:00:00.000	2011-08-24 17:00:00.000	Added
SETON & CORNERST MAIN- MOORE	2011-08-24 07:00:00.000	2011-08-24 18:00:00.000	Added
BRACK & HEALTH S.- MAIDMENT	2011-08-24 07:00:00.000	2011-08-24 18:00:00.000	Added
NAMC - CIPLEU	2011-08-24 07:00:00.000	2011-08-24 13:00:00.000	Added
NAMC - LYSON	2011-08-24 07:00:00.000	2011-08-24 12:00:00.000	Added
SAMC, WESTLAKE, SETON SW - MILLER	2011-08-24 07:00:00.000	2011-08-24 13:00:00.000	Added
SAMC, WESTLAKE, SETON SW - SIMMONS	2011-08-24 07:00:00.000	2011-08-24 18:00:00.000	Added
RRMC/SUMMIT/RELIANT/GT-LYSON	2011-08-24 07:00:00.000	2011-08-24 13:00:00.000	Added
ST DAVIDS - ROSEN	2011-08-24 07:00:00.000	2011-08-24 12:00:00.000	Added

and my dataset after I ran the modified query above:

SETON & CORNERST MAIN- MOORE	2011-08-24 07:00:00.000	2011-08-24 18:00:00.000	Added
NAMC - LYSON	2011-08-24 07:00:00.000	2011-08-24 12:00:00.000	Added
NAMC - CIPLEU	2011-08-24 07:00:00.000	2011-08-24 12:00:00.000	Added
BRACK & HEALTH S.- MAIDMENT	2011-08-24 07:00:00.000	2011-08-24 18:00:00.000	Added
SETON HAYS-KYLE - PEREZ	2011-08-24 07:00:00.000	2011-08-24 18:00:00.000	Added
CALL THERESA AT 320-0963 BEFORE CHANGING	2011-08-24 08:00:00.000	2011-08-24 17:00:00.000	Added
NAMC - CIPLEU	2011-08-24 07:00:00.000	2011-08-24 13:00:00.000	Added
SAMC, WESTLAKE, SETON SW - SIMMONS	2011-08-24 07:00:00.000	2011-08-24 18:00:00.000	Added
SAMC, WESTLAKE, SETON SW - SIMMONS	2011-08-24 07:00:00.000	2011-08-24 18:00:00.000	Added
NAMC - LYSON	2011-08-23 13:00:00.000	2011-08-24 18:00:00.000	Added
ST DAVIDS - ROSEN	2011-08-24 07:00:00.000	2011-08-24 12:00:00.000	Added
ST DAVIDS - ERKO	2011-08-24 07:00:00.000	2011-08-24 18:00:00.000	Added
RRMC/SUMMIT/RELIANT/GT-LYSON	2011-08-24 07:00:00.000	2011-08-24 13:00:00.000	Added
RRMC/SUMMIT/RELIANT/GT-CIPLEU	2011-08-24 07:00:00.000	2011-08-24 13:00:00.000	Added
SAMC, WESTLAKE, SETON SW - MILLER	2011-08-24 07:00:00.000	2011-08-24 13:00:00.000	Added
HEART HOSP - MOORE	2011-08-24 07:00:00.000	2011-08-24 18:00:00.000	Added

as you can see there are still some duplicates in the dataset that aren't being removed. When I ran the query you had posted, it looks like it removed some of the adds as well, which is why I had to modify the query a bit. This is by far the closest that I've gotten so far to the dataset that I need to have returned. Any idea as to why it's not discarding all of the duplicates?

dougancil 0 Junior Poster in Training · Answer 4 · 2011-08-25T00:39:45+00:00

Adam,

I've modified the query again:

SELECT a.* 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,
'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.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') b 
ON a.FirstListing = b.FirstListing
AND a.OnCallStart = b.OnCallStart
AND a.OnCallEnd = b.OnCallEnd
and a.addstart = b.addstart

and here is the data set that it returns:

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
NAMC - LYSON	2011-07-19 09:59:00.000	2011-08-24 12: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
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
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-MIDID	2011-07-19 11:04:00.000	2011-08-24 13: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
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
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
NAMC - LYSON	2011-08-02 14:09:00.000	2011-08-23 13: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
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
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
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

and as you can see, for the last entry (SAMC, WESTLAKE) there are 3 "adds" but I would only need the one with the latest date to actually be shown. How would I go about doing that?

dougancil 0 Junior Poster in Training · Answer 5 · 2011-08-25T21:43:02+00:00

Adam,

I want to use the Max (moncalladd.addtime) but when I try to run it and use group by Firstlisting, I get the error of "Ambiguous column name 'firstlisting'."

When I run the query with the b.firstlisting is NULL ... it was missing some entries.

adam_k 239 Master Poster · Answer 6 · 2011-08-26T02:58:27+00:00

The missing ones are with the corresponding 'Deleted' record.
The left join returns all records in the left side of the join ('Added' in your query) and only the records from the right table (left and right from the word join, so in your query the 'Deleted' table) that match the criteria specified with the keyword ON.

If the firstlisting has null values in the deleted table, then it is not the correct field to use. You need to replace b.firstlisting with another field from b that should never contain null.

Now for the max() function. If you are not going to use the is null then you don't need anything from left join onwards. If you don't want to remove or comment it out then group by a.firstlisting as there are 2 fields with the same name. You should also max(a.addtime) and not moncalladd.addtime, as you are running the max in the first select, so the query by now knows the mancalladd table as a (the derived table from the query inside the parenthesis).

dougancil 0 Junior Poster in Training · Answer 7 · 2011-08-26T03:10:40+00:00

Adam,

Ok I have the query as I need it to be except for one thing. How would I go about making sure that this checks for anytime before today when a schedule has been entered that hasn't expired yet due to oncallendtime?

SELECT a.FirstListing, a.AddStart, a.OnCallStart, a.OnCallEnd, a.Activity
FROM 
	(
	SELECT	OCA.FirstListing,
			MAX(Dateadd(second, OCA.Addtime,Dateadd(MINUTE, OCA.addtime,DateAdd(Day,OCA.adddate,'12/31/1899')))) as AddStart,
			DATEADD(MINUTE, OCA.StartOnCallTime,DATEADD(DAY, OCA.StartOnCallDate, '12/31/1899')) AS OnCallStart,
			DATEADD(MINUTE, OCA.duration,DATEADD(MINUTE, OCA.StartOnCallTime,DATEADD(DAY, OCA.StartOnCallDate, '12/31/1899'))) AS OnCallEnd,
			'Added' AS Activity
	 FROM	 mdr.dbo.mOnCallAdd AS OCA
	WHERE DATEADD(MINUTE, OCA.StartOnCallTime,	 DATEADD(DAY, OCA.StartOnCallDate, '12/31/1899')) < GETDATE()
	AND DATEADD(MINUTE, OCA.duration, DATEADD(MINUTE, OCA.StartOnCallTime, DATEADD(DAY, OCA.StartOnCallDate, '12/31/1899'))) > GETDATE()
	AND OCA.SchedName = 'capital neph'
	GROUP BY OCA.FirstListing,
			DATEADD(MINUTE, OCA.StartOnCallTime,DATEADD(DAY, OCA.StartOnCallDate, '12/31/1899')) ,
			DATEADD(MINUTE, OCA.duration,DATEADD(MINUTE, OCA.StartOnCallTime,DATEADD(DAY, OCA.StartOnCallDate, '12/31/1899'))) 
	) AS a 

LEFT JOIN 
	(SELECT d.FirstListing,
			DATEADD(MINUTE, d.StartOnCallTime,DATEADD(DAY, d.StartOnCallDate, '12/31/1899')) AS OnCallStart,
			DATEADD(MINUTE, d.duration, DATEADD(MINUTE, d.StartOnCallTime,DATEADD(DAY, d.StartOnCallDate, '12/31/1899'))) AS OnCallEnd,
			'Deleted' AS Activity
	FROM 	 mdr.dbo.mOnCallDelete AS d
	 WHERE DATEADD(MINUTE, d.StartOnCallTime,	 DATEADD(DAY, d.StartOnCallDate, '12/31/1899')) < GETDATE()
	AND 	 DATEADD(MINUTE, d.duration, DATEADD(MINUTE, d.StartOnCallTime, DATEADD(DAY, d.StartOnCallDate, '12/31/1899'))) > GETDATE()
	AND d.SchedName = 'capital neph'
	) AS b 
ON a.FirstListing = b.FirstListing
and a.oncallstart = b.oncallstart
and a.oncallend = b.oncallend
GROUP BY a.FirstListing, a.AddStart, a.OnCallStart, a.OnCallEnd, a.Activity

adam_k 239 Master Poster · Answer 8 · 2011-08-26T03:51:58+00:00

I believe that this happens with the following :

WHERE DATEADD(MINUTE, OCA.StartOnCallTime,
	 DATEADD(DAY, OCA.StartOnCallDate, '12/31/1899')) < GETDATE()
	AND DATEADD(MINUTE, OCA.duration, DATEADD(MINUTE, OCA.StartOnCallTime, DATEADD(DAY, OCA.StartOnCallDate, '12/31/1899'))) > GETDATE()

PS: You really don't need anything from left join till the end. If the moncalldelete contains a big number of records then you are slowing down your query for nothing.

dougancil 0 Junior Poster in Training · Answer 9 · 2011-08-29T21:59:45+00:00

Adam,

The query that you gave me is not filtering out the deleted items completely and I don't know why. I ran my query that shows all adds and deletes and get this dataset:

BRACK & HEALTH S.- MAIDMENT 2011-08-29 07:00:00.000 2011-08-29 12:00:00.000 Added
BRACK & HEALTH S.- MAIDMENT 2011-08-29 07:00:00.000 2011-08-29 13:00:00.000 Added
BRACK & HEALTH S.- MAIDMENT 2011-08-29 07:00:00.000 2011-08-29 18:00:00.000 Deleted
BRACK & HEALTH S.- MAIDMENT 2011-08-29 07:00:00.000 2011-08-29 12:00:00.000 Deleted
BRACK & HEALTH S.- MAIDMENT 2011-08-29 07:00:00.000 2011-08-29 18:00:00.000 Added

and as you can see, there is only one valid entry for Maidment. When I run your query, I am still seeing all 3 results. What in your query isn't working? Just for reference, here is the query that shows all results:

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')) 
    BETWEEN Convert(VARCHAR(10), GetDate(), 101) AND GetDate() and
     mOnCallAdd.SchedName = @schedname
 
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')) 
    BETWEEN Convert(VARCHAR(10), GetDate(), 101) AND GetDate() and
     mOnCallDelete.SchedName = @schedname

adam_k 239 Master Poster · Answer 10 · 2011-08-30T09:47:20+00:00

I am guessing that you use this query:

Adam,

Ok I have the query as I need it to be except for one thing. How would I go about making sure that this checks for anytime before today when a schedule has been entered that hasn't expired yet due to oncallendtime?

SELECT a.FirstListing, a.AddStart, a.OnCallStart, a.OnCallEnd, a.Activity
FROM 
	(
	SELECT	OCA.FirstListing,
			MAX(Dateadd(second, OCA.Addtime,Dateadd(MINUTE, OCA.addtime,DateAdd(Day,OCA.adddate,'12/31/1899')))) as AddStart,
			DATEADD(MINUTE, OCA.StartOnCallTime,DATEADD(DAY, OCA.StartOnCallDate, '12/31/1899')) AS OnCallStart,
			DATEADD(MINUTE, OCA.duration,DATEADD(MINUTE, OCA.StartOnCallTime,DATEADD(DAY, OCA.StartOnCallDate, '12/31/1899'))) AS OnCallEnd,
			'Added' AS Activity
	 FROM	 mdr.dbo.mOnCallAdd AS OCA
	WHERE DATEADD(MINUTE, OCA.StartOnCallTime,	 DATEADD(DAY, OCA.StartOnCallDate, '12/31/1899')) < GETDATE()
	AND DATEADD(MINUTE, OCA.duration, DATEADD(MINUTE, OCA.StartOnCallTime, DATEADD(DAY, OCA.StartOnCallDate, '12/31/1899'))) > GETDATE()
	AND OCA.SchedName = 'capital neph'
	GROUP BY OCA.FirstListing,
			DATEADD(MINUTE, OCA.StartOnCallTime,DATEADD(DAY, OCA.StartOnCallDate, '12/31/1899')) ,
			DATEADD(MINUTE, OCA.duration,DATEADD(MINUTE, OCA.StartOnCallTime,DATEADD(DAY, OCA.StartOnCallDate, '12/31/1899'))) 
	) AS a 

LEFT JOIN 
	(SELECT d.FirstListing,
			DATEADD(MINUTE, d.StartOnCallTime,DATEADD(DAY, d.StartOnCallDate, '12/31/1899')) AS OnCallStart,
			DATEADD(MINUTE, d.duration, DATEADD(MINUTE, d.StartOnCallTime,DATEADD(DAY, d.StartOnCallDate, '12/31/1899'))) AS OnCallEnd,
			'Deleted' AS Activity
	FROM 	 mdr.dbo.mOnCallDelete AS d
	 WHERE DATEADD(MINUTE, d.StartOnCallTime,	 DATEADD(DAY, d.StartOnCallDate, '12/31/1899')) < GETDATE()
	AND 	 DATEADD(MINUTE, d.duration, DATEADD(MINUTE, d.StartOnCallTime, DATEADD(DAY, d.StartOnCallDate, '12/31/1899'))) > GETDATE()
	AND d.SchedName = 'capital neph'
	) AS b 
ON a.FirstListing = b.FirstListing
and a.oncallstart = b.oncallstart
and a.oncallend = b.oncallend
GROUP BY a.FirstListing, a.AddStart, a.OnCallStart, a.OnCallEnd, a.Activity

This would probably return 3 records, as you've removed the b.FirstListing is NULL.
The is null is what removes the records with a matching delete record.

I'll ask again, does FirstListing allow or contain null values? If not, then try with the is null in the criteria and let me know how that works out.