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
29 2201
Get rid of union and everything after it.
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.
Just filter out the 'Deleted' Activity in the WHERE clause of your query.
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'.
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.
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
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()
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.
I can't say whether or not that's correct without seeing the current SQL.
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.
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.
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?
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.
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
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
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.
You're grouping by firstlisting but you don't specify which one.
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.
All fields not used in an aggregate function must be in the group by clause.
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.
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.
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
-
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.
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'.
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?
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.
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.
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?
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
Post your reply Sign in to post your reply or Sign up for a free account.
Similar topics
2 posts
views
Thread by En |
last post: by
|
5 posts
views
Thread by PengYu.UT |
last post: by
|
1 post
views
Thread by FusionGuy |
last post: by
|
3 posts
views
Thread by kevin |
last post: by
|
22 posts
views
Thread by Curious |
last post: by
|
6 posts
views
Thread by Niyazi |
last post: by
|
reply
views
Thread by Phil Barber |
last post: by
|
21 posts
views
Thread by gurdz |
last post: by
| |
7 posts
views
Thread by =?Utf-8?B?Sm9lbCBNZXJr?= |
last post: by
| | | | | | | | | | |