472,809 Members | 3,212 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,809 software developers and data experts.

removing redudant data

347 100+
I have the following SP.

Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.  moncallAdd.FirstListing,
  3.  DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
  4.  DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')) AS OnCallStart,
  5.  DATEADD(MINUTE, mOnCallAdd.duration,
  6.  DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
  7.  DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899'))) AS OnCallEnd
  8.  
  9. FROM
  10.  mdr.dbo.mOnCallAdd
  11.  WHERE DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
  12.  DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')) 
  13.  BETWEEN Convert(VARCHAR(10), GetDate(), 101) AND GetDate() and
  14.  mOnCallAdd.SchedName = @schedname
  15.  
  16. UNION 
  17. SELECT
  18.  moncallDelete.FirstListing,
  19.  DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,
  20.  DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899')) AS OnCallStart,
  21.  DATEADD(MINUTE, mOnCallDelete.duration,
  22.  DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,
  23.  DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899'))) AS OnCallEnd
  24.  
  25. FROM
  26.  mdr.dbo.mOnCallDelete
  27.  WHERE DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,
  28.  DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899')) 
  29.  BETWEEN Convert(VARCHAR(10), GetDate(), 101) AND GetDate() and 
  30.  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:

Expand|Select|Wrap|Line Numbers
  1.  'Added' AS Activity,
  2.  '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
Aug 18 '11 #1
29 2310
Rabbit
12,516 Expert Mod 8TB
Get rid of union and everything after it.
Aug 18 '11 #2
dougancil
347 100+
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.     mOnCallAdd.SchedName,
  3.     DATEADD(MINUTE, mOnCallAdd.AddTime,
  4.             DATEADD(DAY, mOnCallAdd.AddDate, '12/31/1899')) AS ActivityDate,
  5.     moncallAdd.Initials as [By],
  6.    'Added' AS Activity,
  7.     mOnCallAdd.FirstListing,
  8.     DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
  9.             DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')) AS OnCallStart,
  10.     DATEADD(MINUTE, mOnCallAdd.duration,
  11.             DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
  12.                     DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899'))) AS OnCallEnd
  13. FROM
  14.     mdr.dbo.mOnCallAdd
  15. WHERE
  16.     DATEADD(DAY, mOnCallAdd.AddDate, '12/31/1899') >= @sincedate AND
  17.     mOnCallAdd.SchedName = @schedname
  18. UNION    
  19. SELECT
  20.     mOnCallDelete.SchedName,
  21.     DATEADD(MINUTE, mOnCallDelete.AddTime,
  22.             DATEADD(DAY, mOnCallDelete.AddDate, '12/31/1899')) AS ActivityDate,
  23.     mOnCallDelete.Initials as [By],
  24.     'Deleted' AS Activity,
  25.     mOnCallDelete.FirstListing,
  26.     DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,
  27.             DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899')) AS OnCallStart,
  28.     DATEADD(MINUTE, mOnCallDelete.duration,
  29.             DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,
  30.                     DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899'))) AS OnCallEnd
  31. FROM
  32.     mdr.dbo.mOnCallDelete
  33. WHERE
  34.      DATEADD(DAY, mOnCallDelete.AddDate, '12/31/1899')  >= @sincedate AND
  35.     mOnCallDelete.SchedName = @schedname
  36. ORDER BY
  37.     ActivityDate DESC
and all I really need to see are the ones that are "added" and not the "deleted" entries.
Aug 18 '11 #3
Rabbit
12,516 Expert Mod 8TB
Just filter out the 'Deleted' Activity in the WHERE clause of your query.
Aug 18 '11 #4
dougancil
347 100+
I tried this:
Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.     moncallAdd.FirstListing,
  3.     DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
  4.             DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')) AS OnCallStart,
  5.     DATEADD(MINUTE, mOnCallAdd.duration,
  6.             DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
  7.                     DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899'))) AS OnCallEnd,
  8. 'Deleted' AS Activity
  9.  
  10. FROM
  11.     mdr.dbo.mOnCallAdd
  12.      WHERE DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
  13.     DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')) 
  14.     BETWEEN Convert(VARCHAR(10), GetDate(), 101) AND GetDate() and activity <>'deleted' and
  15.      mOnCallAdd.SchedName = 'arc im'
and get an error:
Invalid column name 'activity'.
Aug 19 '11 #5
Rabbit
12,516 Expert Mod 8TB
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.
Aug 20 '11 #6
dougancil
347 100+
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
Aug 22 '11 #7
Rabbit
12,516 Expert Mod 8TB
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()
Aug 22 '11 #8
dougancil
347 100+
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.
Aug 22 '11 #9
Rabbit
12,516 Expert Mod 8TB
I can't say whether or not that's correct without seeing the current SQL.
Aug 22 '11 #10
dougancil
347 100+
Rabbit,

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

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT
  3.     moncallAdd.FirstListing,
  4.     DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
  5.             DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')) AS OnCallStart,
  6.     DATEADD(MINUTE, mOnCallAdd.duration,
  7.             DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
  8.                     DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899'))) AS OnCallEnd
  9. FROM
  10.     mdr.dbo.mOnCallAdd
  11.      WHERE DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
  12.     DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')) < GETDATE() 
  13. AND 
  14.     DATEADD(MINUTE, mOnCallAdd.duration,
  15.             DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
  16.                     DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899'))) > Getdate()
  17. AND mOnCalladd.SchedName = @schedname
  18.  
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.
Aug 23 '11 #11
Rabbit
12,516 Expert Mod 8TB
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.
Aug 23 '11 #12
dougancil
347 100+
Rabbit,

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

Expand|Select|Wrap|Line Numbers
  1. SELECT a.* FROM 
  2. (SELECT
  3.     moncallAdd.FirstListing,
  4.     Dateadd(MINUTE, moncalladd.addtime,
  5.             DateAdd(Day,moncalladd.adddate,'12/31/1899')) as AddStart,
  6.     DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
  7.             DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')) AS OnCallStart,
  8.     DATEADD(MINUTE, mOnCallAdd.duration,
  9.             DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
  10.                     DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899'))) AS OnCallEnd,
  11. 'Added' AS Activity
  12. FROM
  13.     mdr.dbo.mOnCallAdd
  14.      WHERE DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
  15.     DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')) < GETDATE() 
  16. AND 
  17.     DATEADD(MINUTE, mOnCallAdd.duration,
  18.             DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
  19.                     DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899'))) >  GETDATE()
  20. AND  mOnCallAdd.SchedName = 'capital neph') a 
  21.  
  22. LEFT JOIN 
  23. (SELECT
  24.     moncallDelete.FirstListing,
  25.     Dateadd(MINUTE, moncalldelete.addtime,
  26.             Dateadd(DAY,moncalldelete.adddate,'12/31/1899')) as AddStart,
  27.     DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,
  28.             DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899')) AS OnCallStart,
  29.     DATEADD(MINUTE, mOnCallDelete.duration,
  30.             DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,
  31.                     DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899'))) AS OnCallEnd,
  32. 'Deleted' AS Activity
  33. FROM
  34.     mdr.dbo.mOnCallDelete
  35.   WHERE DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,
  36.     DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899')) < GETDATE() 
  37. AND 
  38.     DATEADD(MINUTE, mOnCallDelete.duration,
  39.             DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,
  40.                     DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899'))) >  GETDATE()
  41. AND  mOnCallDelete.SchedName = 'capital neph') b 
  42. ON a.FirstListing = b.FirstListing
  43. and a.oncallstart = b.oncallstart
  44. and a.oncallend = b.oncallend
  45.  
and the dataset that it produces:
Expand|Select|Wrap|Line Numbers
  1. 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
  2. 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
  3. 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
  4. 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
  5. 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
  6. 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
  7. 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
  8. 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
  9. 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
  10. 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
  11. NAMC - MIDIDDODI    2011-07-19 10:41:00.000    2011-08-24 13:00:00.000    2011-08-24 18:00:00.000    Added
  12. 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
  13. 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
  14. NAMC - LYSON    2011-08-02 14:09:00.000    2011-08-23 13:00:00.000    2011-08-24 18:00:00.000    Added
  15. NAMC - LYSON    2011-07-19 09:59:00.000    2011-08-24 12:00:00.000    2011-08-24 18:00:00.000    Added
  16. NAMC - MIDIDDODI    2011-07-19 10:05:00.000    2011-08-24 12:00:00.000    2011-08-24 18:00:00.000    Added
  17.  
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?
Aug 24 '11 #13
Rabbit
12,516 Expert Mod 8TB
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.
Aug 24 '11 #14
dougancil
347 100+
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:
Expand|Select|Wrap|Line Numbers
  1. 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
  2.  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
  3.  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
  4.  
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
Aug 24 '11 #15
Rabbit
12,516 Expert Mod 8TB
If I had a table with the fields companyName and dateAdded, to get the lastest dateAdded, I would do
Expand|Select|Wrap|Line Numbers
  1. SELECT companyName, MAX(dateAdded) AS dateAdded
  2. FROM tableName
  3. GROUP BY companyName
Aug 24 '11 #16
dougancil
347 100+
Rabbit,
What I have is this:
Expand|Select|Wrap|Line Numbers
  1. SELECT a.* FROM 
  2.  (SELECT
  3.      moncallAdd.FirstListing,
  4.       max (Dateadd(MINUTE, moncalladd.addtime,
  5.              DateAdd(Day,moncalladd.adddate,'12/31/1899'))) as AddStart,
  6.      DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
  7.              DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')) AS OnCallStart,
  8.      DATEADD(MINUTE, mOnCallAdd.duration,
  9.              DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
  10.                      DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899'))) AS OnCallEnd,
  11.  'Added' AS Activity
  12.  FROM
  13.      mdr.dbo.mOnCallAdd
  14.       WHERE DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
  15.      DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')) < GETDATE() 
  16.  AND 
  17.      DATEADD(MINUTE, mOnCallAdd.duration,
  18.              DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
  19.                      DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899'))) >  GETDATE()
  20.  AND  mOnCallAdd.SchedName = 'capital neph') a 
  21.  
  22.  LEFT JOIN 
  23.  (SELECT
  24.      moncallDelete.FirstListing,
  25.       max (Dateadd(MINUTE, moncalldelete.addtime,
  26.              Dateadd(DAY,moncalldelete.adddate,'12/31/1899'))) as AddStart,
  27.      DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,
  28.              DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899')) AS OnCallStart,
  29.      DATEADD(MINUTE, mOnCallDelete.duration,
  30.              DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,
  31.                      DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899'))) AS OnCallEnd,
  32.  'Deleted' AS Activity
  33.  FROM
  34.      mdr.dbo.mOnCallDelete
  35.    WHERE DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,
  36.      DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899')) < GETDATE() 
  37.  AND 
  38.      DATEADD(MINUTE, mOnCallDelete.duration,
  39.              DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,
  40.                      DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899'))) >  GETDATE()
  41.  AND  mOnCallDelete.SchedName = 'capital neph') b 
  42.  ON a.FirstListing = b.FirstListing
  43.  and a.oncallstart = b.oncallstart
  44.  and a.oncallend = b.oncallend
  45. group by firstlisting
  46.  
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.
Aug 24 '11 #17
Rabbit
12,516 Expert Mod 8TB
You're grouping by firstlisting but you don't specify which one.
Aug 25 '11 #18
dougancil
347 100+
When I try this:
group by moncalladd.firstlisting

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.
Aug 25 '11 #19
Rabbit
12,516 Expert Mod 8TB
All fields not used in an aggregate function must be in the group by clause.
Aug 25 '11 #20
dougancil
347 100+
Ok I'm obviously missing something simple then with my syntax,

I now have this as my group by clause:
group by mdr.dbo.mOnCallAdd.FirstListing,mdr.dbo.mOnCallAdd .StartOnCallDate,
mdr.dbo.mOnCallAdd.StartOnCallTime, mdr.dbo.mOnCallAdd.StartOnCallDate,
mdr.dbo.mOnCallAdd.StartOnCallTime, mdr.dbo.mOnCallAdd.Duration

and get this error:
The column prefix 'mdr.dbo.mOnCallAdd' 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.
Aug 25 '11 #21
Rabbit
12,516 Expert Mod 8TB
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.
Aug 25 '11 #22
dougancil
347 100+
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'.

Expand|Select|Wrap|Line Numbers
  1. SELECT a.FirstListing, a.AddStart, a.OnCallStart, a.OnCallEnd, a.Activity
  2. FROM 
  3.     (
  4.     SELECT    OCA.FirstListing,
  5.             MAX(Dateadd(MINUTE, OCA.addtime,DateAdd(Day,OCA.adddate,'12/31/1899'))) as AddStart,
  6.             DATEADD(MINUTE, OCA.StartOnCallTime,DATEADD(DAY, OCA.StartOnCallDate, '12/31/1899')) AS OnCallStart,
  7.             DATEADD(MINUTE, OCA.duration,DATEADD(MINUTE, OCA.StartOnCallTime,DATEADD(DAY, OCA.StartOnCallDate, '12/31/1899'))) AS OnCallEnd,
  8.             'Added' AS Activity
  9.      FROM     mdr.dbo.mOnCallAdd AS OCA
  10.     WHERE DATEADD(MINUTE, OCA.StartOnCallTime,     DATEADD(DAY, OCA.StartOnCallDate, '12/31/1899')) < GETDATE() 
  11.     AND    DATEADD(MINUTE, OCA.duration, DATEADD(MINUTE, OCA.StartOnCallTime, DATEADD(DAY, OCA.StartOnCallDate, '12/31/1899'))) >  GETDATE()
  12.     AND  OCA.SchedName = 'capital neph'
  13.     GROUP BY OCA.FirstListing,
  14.             DATEADD(MINUTE, OCA.StartOnCallTime,DATEADD(DAY, OCA.StartOnCallDate, '12/31/1899')) AS OnCallStart,
  15.             DATEADD(MINUTE, OCA.duration,DATEADD(MINUTE, OCA.StartOnCallTime,DATEADD(DAY, OCA.StartOnCallDate, '12/31/1899'))) AS OnCallEnd,
  16.     ) AS a 
  17.  
  18. LEFT JOIN 
  19.     (SELECT  d.FirstListing,
  20.             DATEADD(MINUTE, d.StartOnCallTime,DATEADD(DAY, d.StartOnCallDate, '12/31/1899')) AS OnCallStart,
  21.             DATEADD(MINUTE, d.duration, DATEADD(MINUTE, d.StartOnCallTime,DATEADD(DAY, d.StartOnCallDate, '12/31/1899'))) AS OnCallEnd,
  22.             'Deleted' AS Activity
  23.     FROM        mdr.dbo.mOnCallDelete AS d
  24.       WHERE DATEADD(MINUTE, d.StartOnCallTime,     DATEADD(DAY, d.StartOnCallDate, '12/31/1899')) < GETDATE() 
  25.     AND      DATEADD(MINUTE, d.duration, DATEADD(MINUTE, d.StartOnCallTime, DATEADD(DAY, d.StartOnCallDate, '12/31/1899'))) >  GETDATE()
  26.     AND  d.SchedName = 'capital neph'
  27.     ) AS b 
  28. ON a.FirstListing = b.FirstListing
  29. and a.oncallstart = b.oncallstart
  30. and a.oncallend = b.oncallend
  31. GROUP BY  a.FirstListing, a.AddStart, a.OnCallStart, a.OnCallEnd, a.Activity
  32.  
Aug 25 '11 #23
Rabbit
12,516 Expert Mod 8TB
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.
Aug 25 '11 #24
dougancil
347 100+
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT a.FirstListing, a.AddStart, a.OnCallStart, a.OnCallEnd, a.Activity
  2. FROM 
  3.     (
  4.     SELECT    OCA.FirstListing,
  5.             MAX(Dateadd(MINUTE, OCA.addtime,DateAdd(Day,OCA.adddate,'12/31/1899'))) as AddStart,
  6.             DATEADD(MINUTE, OCA.StartOnCallTime,DATEADD(DAY, OCA.StartOnCallDate, '12/31/1899')) AS OnCallStart,
  7.             DATEADD(MINUTE, OCA.duration,DATEADD(MINUTE, OCA.StartOnCallTime,DATEADD(DAY, OCA.StartOnCallDate, '12/31/1899'))) AS OnCallEnd,
  8.             'Added' AS Activity
  9.      FROM     mdr.dbo.mOnCallAdd AS OCA
  10.     WHERE DATEADD(MINUTE, OCA.StartOnCallTime,     DATEADD(DAY, OCA.StartOnCallDate, '12/31/1899')) < GETDATE() 
  11.     AND    DATEADD(MINUTE, OCA.duration, DATEADD(MINUTE, OCA.StartOnCallTime, DATEADD(DAY, OCA.StartOnCallDate, '12/31/1899'))) >  GETDATE()
  12.     AND  OCA.SchedName = 'capital neph'
  13.     GROUP BY OCA.FirstListing,
  14.             DATEADD(MINUTE, OCA.StartOnCallTime,DATEADD(DAY, OCA.StartOnCallDate, '12/31/1899')) AS OnCallStart,
  15.             DATEADD(MINUTE, OCA.duration,DATEADD(MINUTE, OCA.StartOnCallTime,DATEADD(DAY, OCA.StartOnCallDate, '12/31/1899'))) AS OnCallEnd,
  16.     ) AS a 
  17.  
  18. LEFT JOIN 
  19.  (SELECT
  20.      moncallDelete.FirstListing,
  21.      DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,
  22.              DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899')) AS OnCallStart,
  23.      DATEADD(MINUTE, mOnCallDelete.duration,
  24.              DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,
  25.                      DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899'))) AS OnCallEnd,
  26.  'Deleted' AS Activity
  27.  FROM
  28.      mdr.dbo.mOnCallDelete
  29.    WHERE DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,
  30.      DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899')) < GETDATE() 
  31.  AND 
  32.      DATEADD(MINUTE, mOnCallDelete.duration,
  33.              DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,
  34.                      DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899'))) >  GETDATE()
  35.  AND  mOnCallDelete.SchedName = 'capital neph') b 
  36.  ON a.FirstListing = b.FirstListing
  37.  and a.oncallstart = b.oncallstart
  38.  and a.oncallend = b.oncallend
  39.  
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'.
Aug 25 '11 #25
Rabbit
12,516 Expert Mod 8TB
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?
Aug 25 '11 #26
dougancil
347 100+
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.
Aug 25 '11 #27
Rabbit
12,516 Expert Mod 8TB
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.
Aug 25 '11 #28
dougancil
347 100+
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?
Aug 29 '11 #29
ck9663
2,878 Expert 2GB
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
Aug 29 '11 #30

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: En | last post by:
Any ideas what to do for the best? I have a table that records some transaction data. Each entry in the table has an auto gen primary key / ID. Somewhere along the way there are 1000's of...
5
by: PengYu.UT | last post by:
Hi, I'm searching for an implementation of set. I want to insert or delete elements. The set should have no redudant element. It seems linked list is one way for implementing set. But I don't...
1
by: FusionGuy | last post by:
I have a Windows Forms app with a datagrid that binds to an XML data island. Upon double-clicking a row in the grid, I want that row to simply disappear WITHOUT having to rebind. What's the...
3
by: kevin | last post by:
Is that even possible? I am creating a web service in .NET to expose some already created .NET programs to other groups. One group is writing the client in PERL, and thus wishes the wsdl schema...
22
by: Curious | last post by:
Hi, I am searching for a data structure that stores key-value pairs in it. This data structure is to hold large amounts of key-value pairs, and so needs to be efficient both in insertion and...
6
by: Niyazi | last post by:
Hi all, What is fastest way removing duplicated value from string array using vb.net? Here is what currently I am doing but the the array contains over 16000 items. And it just do it in 10 or...
0
by: Phil Barber | last post by:
I an a windows app where users enter diary data in to a MSWord component. this component supports all the various font options. the data is stored in a Text field in MS SQL 2000 db. I have a...
21
by: gurdz | last post by:
Does anyone know how to perform data validation in C? I have searched google for every possible result, and I either end up with data validation for C++ or nothing at all. I have also searched...
0
by: nkechifesie | last post by:
After writting the select statement below using the word Distinct the error Object doesn't support this property or method started appearing. I did a check by removing the data connection to this...
7
by: =?Utf-8?B?Sm9lbCBNZXJr?= | last post by:
I have created a custom class with both value type members and reference type members. I then have another custom class which inherits from a generic list of my first class. This custom listneeds...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
0
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
0
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
0
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
5
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
0
by: lllomh | last post by:
How does React native implement an English player?
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.