removing redudant data

[dougancil]

I have the following SP.

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

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

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

this SP was built on a pre existing SP that showed activity based on the value of "added" or "deleted," which was simply done by these two lines:

 'Added' AS Activity,
 'Deleted' AS Activity,

Table definitions are as follows:

StartDate Int
StartTime Int
Firstlisting nvarchar
duration decimal
adddate int
addtime int


and what I'm noticing is that when I run my SP that it's returning values for both the added and deleted. I need to just show the ones that have been added, but not deleted. What would be the best way to do that with the SP that I currently have?

Thank you

[Rabbit]

Get rid of union and everything after it.

[dougancil]

Rabbit,

I did that and it seems that it's giving more data than it was before. This SP was built off of this query:

SELECT
    mOnCallAdd.SchedName,
    DATEADD(MINUTE, mOnCallAdd.AddTime,
            DATEADD(DAY, mOnCallAdd.AddDate, '12/31/1899')) AS ActivityDate,
    moncallAdd.Initials as [By],
   'Added' AS Activity,
    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
FROM
    mdr.dbo.mOnCallAdd
WHERE
    DATEADD(DAY, mOnCallAdd.AddDate, '12/31/1899') >= @sincedate AND
    mOnCallAdd.SchedName = @schedname
UNION    
SELECT
    mOnCallDelete.SchedName,
    DATEADD(MINUTE, mOnCallDelete.AddTime,
            DATEADD(DAY, mOnCallDelete.AddDate, '12/31/1899')) AS ActivityDate,
    mOnCallDelete.Initials as [By],
    'Deleted' AS Activity,
    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
FROM
    mdr.dbo.mOnCallDelete
WHERE
     DATEADD(DAY, mOnCallDelete.AddDate, '12/31/1899')  >= @sincedate AND
    mOnCallDelete.SchedName = @schedname
ORDER BY
    ActivityDate DESC

and all I really need to see are the ones that are "added" and not the "deleted" entries.

[Rabbit]

Just filter out the 'Deleted' Activity in the WHERE clause of your query.

[dougancil]

I tried this:

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,
'Deleted' 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 activity <>'deleted' and
     mOnCallAdd.SchedName = 'arc im'

and get an error:
Invalid column name 'activity'.

[Rabbit]

Isn't that the query that your query is based on? You should put the where condition in your first query, the one you posted earlier.

[dougancil]

Rabbit,

Ok the requirements for this has changed again. What I'm needing to do now is to be able to do the following things:

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

Does that make sense?

Thank you

Doug

[Rabbit]

You'll just need to put that criteria into the WHERE clause. Here are some things to keep in mind.

• GETDATE() will return the current date and time.
• You will have to use that in conjunction with FORMAT() or DATEPART()

[dougancil]

I'm just testing this again, and the query doesn't seem to be filtering out if the endtime is before now. Here are some examples:

BRACK & HEALTH S.- 2011-08-22 07:00:00.000 2011-08-22 12:00:00.000 Added
BRACK & HEALTH S.- 2011-08-22 07:00:00.000 2011-08-22 13:00:00.000 Added

current time is 2:16 PM and I just ran that query. Those entries shouldn't have shown up.

[Rabbit]

I can't say whether or not that's correct without seeing the current SQL.

[dougancil]

Rabbit,

Ok so here's the new query, but it is still not giving me the correct data:

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

here is the data that I'm shown:
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

and of those results, only one should be showing. If I run the original query, here is the data that I'm shown:

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

so as you can see, there should only be one entry shown that is an "add" that has no matching delete. I can't seem to parse just that one entry though.

[Rabbit]

Did you change the requirements again? I thought you wanted calls that started before the current date and time and ends after the current date and time. Now it sounds like you want ones that are in the add table that aren't in the delete table.

I can't help if the requirements keep changing and I don't know what you want.

[dougancil]

Rabbit,

This query has been changing due to issues beyond my control. Here is the query as it stands currently:

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 the dataset that it produces:

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
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
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
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
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
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
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
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
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
NAMC - MIDIDDODI	2011-07-19 10:05:00.000	2011-08-24 12:00:00.000	2011-08-24 18:00:00.000	Added

and as you can see, this is now only producing the "added" schedules without the deletes. The problem is that since there are multiple adds for the same schedule, I had to add the "addstart" field to my query. Now all I need to do is to filter out the lastest among those times to provide the correct result. How would I go about doing that?

[Rabbit]

I understand that requirements change, but you need to let me know when they do. You can't just start talking about a query with new requirements and expect me to know that.

For the newest requirements, depending on what you mean by latest, you can use an aggregate query to take the min() or max() of the addstart field and group by the other fields.

[dougancil]

Rabbit,

Sorry I won't put you through that again. What I mean by the latest is this for example, in my last dataset I had these entries:

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 you can see the "latest" is the one "added" on 8-4-2011 at 16:04:00. That would be the only one I would need to show as a result. Can you give me an example of where to add the max