trying to select from two different databases

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)

trying to select from two different databases

Author

Topic

dougancil
Posting Yak Master

217 Posts

Posted - 2011-04-08 : 10:10:18

I am trying to check two databases to see if something is true and then show the results.

What I have are databases moncalladd and moncalldelete and what I'm trying to show is if a record falls within both a name and a datarange from either table that the results are shown. Currently the query I have is this:

SELECT OnCallAdd.OpName,mOnCallAdd.SchedName,mOnCallAdd.FirstListing, moncallAdd.Initials,
DATEADD(MINUTE, mOnCallAdd.AddTime, DATEADD(DAY, mOnCallAdd.AddDate, '12/31/1899')) as Added,
DATEADD(MINUTE, mOnCallAdd.StartOnCallTime, DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')) as OnCallDate,
DATEADD(MINUTE, mOnCallAdd.duration, DATEADD(MINUTE, mOnCallAdd.StartOnCallTime, DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899'))) as OnCallEnd,
DATEADD(n,mOnCallDelete.Timestamp,'12/31/1899')-(mOnCallDelete.Timestamp / 1000 / 60 / 1440) AS Deleted
FROM mdr.dbo.mOnCallAdd INNER JOIN mdr.dbo.mOnCallDelete
ON mOnCallAdd.SchedName = mOnCallDelete.SchedName AND mOnCallAdd.Timestamp = mOnCallDelete.Timestamp
WHERE DATEADD(MINUTE, mOnCallAdd.StartOnCallTime, DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')) >= '03-21-2011'
ORDER BY OnCallDate

what I'm looking to add to this query is an activity header that shows if something is either added or deleted. Any help would be appreciated.

Thank you

Doug

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-08 : 14:46:20

Can you post the DDL, some sample data to populate the tables and the output you are expect to get? Brett's page here has some info on how to get the DDL. http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

dougancil
Posting Yak Master

217 Posts

Posted - 2011-04-08 : 15:29:53

sunitabeck, I'm not sure if I've done this correctly but here is the DDL:


CREATE TABLE [dbo].[mOnCallAdd] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[RecID] [decimal](18, 0) NOT NULL ,
	[Timestamp] [int] NULL ,
	[SchedName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Rank] [int] NULL ,
	[StartOnCallDate] [int] NULL ,
	[StartOnCallTime] [int] NULL ,
	[Override] [int] NULL ,
	[FirstListing] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Duration] [decimal](18, 0) NULL ,
	[TimeDifference] [decimal](18, 0) NULL ,
	[AddDate] [int] NULL ,
	[AddTime] [int] NULL ,
	[Initials] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Opname] [nvarchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Comment] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Field0] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Field1] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Field2] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Field3] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Field4] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Field5] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Field6] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Field7] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Field8] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Field9] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Field10] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Field11] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[MainSchedName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

Here is a sample line of data:


1	70550	55426893	Schedname	0	38490	1020	0	Dr Doctor	840	0	38490	1293	TG	8199

the results I'm currently getting are like this:


ZACH	CCM ONCALL	PATRICE	ENM	2011-01-19 10:00:00.000	2011-03-21 06:00:00.000	2011-03-22 18:00:00.000	2011-01-19 10:00:00.000

and what I'd like to get is any activity either in the moncalladd or moncalldelete table that matches either a schedname or a "since date" and present is activity and when that activity took place (as determined by either when something is "added" or something is "deleted")

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources