Removing redundant information

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)

Removing redundant information

Author

Topic

dougancil
Posting Yak Master

217 Posts

Posted - 2011-08-23 : 12:45:13

I have the following query:


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

and when I run this query, I'm shown the following data:


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 this result isn't correct. What I'm looking to do is to return back only activity that is listed as "added" that doesn't have a "deleted" to match it. If I run this query:


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 = 'arc im'
 
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 = 'arc im'

I see the following information:


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, in my first query, there are 3 results and there should only be one. What I'm not sure of, is how do I parse out just the matches based on "added" that don't have a corresponding "deleted?"

Any help would be appreciated.

Thanks

Doug

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-23 : 13:11:26

do you mean this?


SELECT FirstListing,OnCallStart,OnCallEnd
FROM
(
your union query
)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

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

dougancil
Posting Yak Master

217 Posts

Posted - 2011-08-24 : 10:48:14

Visakh,

I can't select oncallstart and oncallend from the table because those values I assign those values here:


    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,

when I try with this revised query:


SELECT
    moncallAdd.FirstListing,oncallstart, oncallend,
    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 = 'capital neph'
 
UNION    
SELECT
    moncallDelete.FirstListing, oncallstart, oncallend,
    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 = 'capital neph'

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

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'oncallstart'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'oncallend'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-24 : 12:18:42

you can select. see my query. i'm enclosing you query within another derived table like below


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

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

dougancil
Posting Yak Master

217 Posts

Posted - 2011-08-24 : 13:46:02

Visakh,

here's the query as I have it running currently, and it's still not eliminating all duplicates:


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

and here's the data result set that I'm getting:


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
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
ST DAVIDS - ERKO	2011-08-24 12: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
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-24 12:00:00.000	        2011-08-24 18:00:00.000	Added
NAMC - MIDIDDODI	2011-08-24 12: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 - 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

when I run the query that you have above here is the data set that I get:


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

so that doesnt appear to be pulling the complete data set. Any ideas as to why not?

Thank you

Doug

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-24 : 13:48:51

where's the original query with UNION?

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

dougancil
Posting Yak Master

217 Posts

Posted - 2011-08-24 : 13:51:49

the union query was producing everything but not removing the deletes and was not accounting for times before midnight yesterday and after midnight tonight.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-24 : 13:55:47

but you still have left join with deletes rite? why is it so? also you're not putting any where that means its not making any changes but it brings all records from first query regardless of match in second query

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

dougancil
Posting Yak Master

217 Posts

Posted - 2011-08-24 : 13:59:43

Visakh,

There is a where clause in the left joins. Between the start and end time, also then matching based on:
a.FirstListing = b.FirstListing
AND a.OnCallStart = b.OnCallStart
AND a.OnCallEnd = b.OnCallEnd

what do I need to add to the where clause? It seems that what I have would remove all duplicate data.

I feel that the data in the first dataset is closest to the result set I need, and I feel that the second set doesn't capture all of the data.

dougancil
Posting Yak Master

217 Posts

Posted - 2011-08-24 : 14:37:19

Ok and one more thing I've discovered. I have modified the query again because I'm seeing what may be part of the problem:


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

which produces this data set:


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

so for example you can see on the last entry, that there are three "added" dates and what I need to do is to only use the latest one. I think that may eliminate some of this issue.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-25 : 01:53:22

ok. for that group by common valued fields and take MAX() over your required date field

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

dougancil
Posting Yak Master

217 Posts

Posted - 2011-08-25 : 11:44:19

Visakh,

When I tried to use the max clause, and then group by firstlisting, I get the error:

Ambiguous column name 'firstlisting'." What am I doing incorrectly?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-25 : 13:12:24

you need to give it an alias. specify like a.firstlisting or b.firstlisting depending on from where you need it

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

dougancil
Posting Yak Master

217 Posts

Posted - 2011-08-25 : 14:30:51

Visakh,

I have tried using the columns of a. and also of listing the mdr.dbo.moncalladd, and none of that works. Here's what I get when I try it with the a columns:
Server: Msg 207, Level 16, State 3, Line 1 Invalid column name 'StartOnCallDate'. Server: Msg 207, Level 16, State 1, Line 1 Invalid column name 'StartOnCallTime'. Server: Msg 207, Level 16, State 1, Line 1 Invalid column name 'StartOnCallDate'. Server: Msg 207, Level 16, State 1, Line 1 Invalid column name 'StartOnCallTime'. Server: Msg 207, Level 16, State 1, Line 1 Invalid column name 'Duration'

dougancil
Posting Yak Master

217 Posts

Posted - 2011-08-25 : 14:54:13

I have this as my code:


SELECT a.FirstListing, a.AddStart, a.OnCallStart, a.OnCallEnd, a.Activity
FROM 
	(
	SELECT	OCA.FirstListing,
			MAX(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')) AS OnCallStart,
			DATEADD(MINUTE, OCA.duration,DATEADD(MINUTE, OCA.StartOnCallTime,DATEADD(DAY, OCA.StartOnCallDate, '12/31/1899'))) AS OnCallEnd,
	) 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

and I'm getting this error:

Server: Msg 156, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'AS'.
Server: Msg 156, Level 15, State 1, Line 27
Incorrect syntax near the keyword 'AS'.

what in my syntax is incorrect?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources