removing redudant data

[Rabbit]

If I had a table with the fields companyName and dateAdded, to get the lastest dateAdded, I would do

SELECT companyName, MAX(dateAdded) AS dateAdded
FROM tableName
GROUP BY companyName

[dougancil]

Rabbit,
What I have is this:

SELECT a.* FROM 
 (SELECT
     moncallAdd.FirstListing,
      max (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,
      max (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
group by firstlisting

and that gives me the error of "Ambiguous column name firstlisting" and I've tried several different variations of that. That's what I'm stuck on.

[Rabbit]

You're grouping by firstlisting but you don't specify which one.

[dougancil]

When I try this:
group by moncalladd.firs tlisting

I get this error:

The column prefix 'moncalladd' does not match with a table name or alias name used in the query.

when I try a.firstlisting

I get this error:

Column 'a.AddStart' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

[Rabbit]

All fields not used in an aggregate function must be in the group by clause.

[dougancil]

Ok I'm obviously missing something simple then with my syntax,

I now have this as my group by clause:
group by mdr.dbo.mOnCall Add.FirstListin g,mdr.dbo.mOnCa llAdd.StartOnCa llDate,
mdr.dbo.mOnCall Add.StartOnCall Time, mdr.dbo.mOnCall Add.StartOnCall Date,
mdr.dbo.mOnCall Add.StartOnCall Time, mdr.dbo.mOnCall Add.Duration

and get this error:
The column prefix 'mdr.dbo.mOnCal lAdd' does not match with a table name or alias name used in the query.

What am I missing? Those are all the fields that are not used in the aggregate function.

[Rabbit]

When you give a table an alias, you can no longer refer to it using it's original name. You have to use the alias from that point forward.

[dougancil]

Ok here's the query as it is now, but I'm still missing something because now I get the 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'.

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

[Rabbit]

I don't see anything that jumps out as incorrect. You should go through and make sure all the parentheses are closed off in the right areas.

I do see something else though that is unrelated to the AS problem. You don't have to subquery out your first query like that. And you don't have to calculate max on the second query, since you're only wanting the max on the first one anyways.

[dougancil]

what would be a better way to subquery out the first query then? Also I've removed the max after the left join. Now my query looks like this:

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
     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 I get the error:
Server: Msg 156, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'AS'.
Server: Msg 170, Level 15, State 1, Line 35
Line 35: Incorrect syntax near 'b'.

[Rabbit]

What I meant was that you don't have to subquery it. It might actually cause more problems.

Regarding the AS error, did you go through and double check all the parentheses?

[dougancil]

I did check my parentheses. I can't see any mismatched pairs. As far as the subquery, all I'm concerned with right now is getting it to work. I can go back and fix it later.

[Rabbit]

What I'm saying is it probably won't work if you subquery it that way.

As for your AS error, I think I see where the problem is. You're trying to give the group by aliases, you can't do that. So take those aliases out of the group by.

[dougancil]

Rabbit,

I've fixed the syntax error, but I'm still seeing duplicate data. I've tried union, left join, inner join and I feel like I'm close but I'm still missing something. What this query needs to do is to compare the moncalladd table to the moncalldelete table for exact matches of oncallstart, oncallend, firstlisting, schedname, and then ONLY show the adds with the latest datetime added that are between sometime in the past and the current time. I don't know what I'm missing as to why this isn't producing correct results. Any idea?

[ck9663]

Can you post some sample data from both table and show us what you're expecting your query to show as a result? If the table has many columns, just include a couple that is not included in the comparison then the one you mentioned above.

Also, do you need to include those records that exist in only one of the tables? Or you only those that exists on both but have different values on the columns you mentioned.


~~ CK