JOIN on 2 tables - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

JOIN on 2 tables

Author

Topic

nietzky
Yak Posting Veteran

75 Posts

Posted - 2013-05-21 : 15:02:28

I have 2 data sources which I formatted the same way. I would like to join these 2 tables in such a way I get unique hostnames per date and Object always showing SOURCE as OPD whenever possible first. if no OPD data is avilable for an object and/or hostname I would like to leverage table B and its information. There should be no dups.
I would imagine I need to use COALESCE somehow just not sure how to do this in most efficient way. Both tables are large approx 2 mln object each. The idea here is to get as many hostnames as possible with correspponding 3 counters' averages per date.

TABLE 1

HOSTNAME weekStart Object AverageValue_AVG SOURCE
host1 2013/05/13 Processor 1.911 OPD
host1 2013/05/13 Memory 25.031 OPD
host1 2013/05/13 LogicalDisk 46.467 OPD
host1 2013/05/06 Processor 2.312 OPD
host1 2013/05/06 Memory 26.795 OPD
host1 2013/05/06 LogicalDisk 47.341 OPD
host1 2013/04/29 Processor 1.64 OPD
host1 2013/04/29 Memory 25.073 OPD
host1 2013/04/29 LogicalDisk 47.623 OPD
host1 2013/04/22 Processor 0.728 OPD
host1 2013/04/22 Memory 25.01 OPD
host2 2013/05/13 Processor 1.005 OPD
host2 2013/05/13 Memory 40.992 OPD
host2 2013/05/13 LogicalDisk 34.832 OPD
host2 2013/05/06 Processor 1.911 OPD
host2 2013/05/06 Memory 48.485 OPD

TABLE 2

HOSTNAME weekStart Object AverageValue_AVG SOURCE
host1 2013/05/13 Processor 1.35 PRO
host1 2013/05/13 Memory 22.321 PRO
host1 2013/05/13 LogicalDisk 41.88 PRO
host1 2013/05/06 Processor 1.214 PRO
host1 2013/05/06 Memory 26.795 PRO
host1 2013/05/06 LogicalDisk 44.323 PRO
host1 2013/04/29 Processor 1.34 PRO
host1 2013/04/29 Memory 25.073 PRO
host1 2013/04/29 LogicalDisk 41.223 PRO
host1 2013/04/22 Processor 0.338 PRO
host1 2013/04/22 Memory 23.41 PRO
host2 2013/05/13 Processor 1.25 PRO
host2 2013/05/13 Memory 40.992 PRO
host2 2013/05/13 LogicalDisk 32.32 PRO
host2 2013/05/06 Processor 2.1 PRO
host2 2013/05/06 Memory 42.45 PRO
host2 2013/05/06 LogicalDisk 34.32 PRO
host3 2013/05/06 Processor 2.1 PRO
host3 2013/05/06 Memory 42.45 PRO
host3 2013/05/06 LogicalDisk 34.32 PRO

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-21 : 15:32:30

Try
[CODE]
SELECT DISTINCT * FROM TABLE1
UNION
SELECT DISTINCT * FROM TABLE2
[/CODE]

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2013-05-21 : 16:21:37

UNION will distinct the sets anyway -- no need for the DSTINCTS

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-21 : 17:55:16

quote:
Originally posted by Transact Charlie

UNION will distinct the sets anyway -- no need for the DSTINCTS

Transact Charlie
Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
http://nosqlsolution.blogspot.co.uk/

Oops!
Thank you Charlie!

nietzky
Yak Posting Veteran

75 Posts

Posted - 2013-05-21 : 21:15:54

quote:
Originally posted by MuMu88

quote:
Originally posted by Transact Charlie

UNION will distinct the sets anyway -- no need for the DSTINCTS

Transact Charlie
Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
http://nosqlsolution.blogspot.co.uk/

Oops!
Thank you Charlie!
I tried union already, I get dupes 2 processor statistics per date coming from 2 different tables, I just need memory, processor, logical disk per host name for particular date

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-22 : 00:57:56

Here you go:
[CODE]

DECLARE @TestStub TABLE (
weekStart Date,
HOSTNAME NVARCHAR(20),
[Object] NVARCHAR(20),
AverageValue_AVG NUMERIC(12,3),
SOURCE NVARCHAR(20)
);

DECLARE @TestStub1 TABLE (
weekStart Date,
HOSTNAME NVARCHAR(20),
[Object] NVARCHAR(20),
AverageValue_AVG NUMERIC(12,3),
SOURCE NVARCHAR(20)
);

INSERT INTO @TestStub(HOSTNAME, weekStart, [Object], AverageValue_AVG, SOURCE) VALUES
('host1', '2013/05/06', 'Memory', 26.795, 'OPD'),
('host1', '2013/05/06', 'LogicalDisk', 47.341, 'OPD'),
('host1', '2013/04/29', 'Processor', 1.64, 'OPD'),
('host1', '2013/04/29', 'Memory', 25.073, 'OPD'),
('host1', '2013/04/29', 'LogicalDisk', 47.623, 'OPD'),
('host1', '2013/04/22', 'Processor', 0.728, 'OPD'),
('host1', '2013/04/22', 'Memory', 25.01, 'OPD'),
('host2', '2013/05/13', 'Processor', 1.005, 'OPD'),
('host2', '2013/05/13', 'Memory', 40.992, 'OPD'),
('host2', '2013/05/13', 'LogicalDisk', 34.832, 'OPD'),
('host2', '2013/05/06', 'Processor', 1.911, 'OPD'),
('host2', '2013/05/06', 'Memory', 48.485, 'OPD');

INSERT INTO @TestStub1(HOSTNAME, weekStart, [Object], AverageValue_AVG, SOURCE) VALUES
('host1', '2013/05/13', 'Processor', 1.35, 'PRO'),
('host1', '2013/05/13', 'Memory', 22.321, 'PRO'),
('host1', '2013/05/13', 'LogicalDisk', 41.88, 'PRO'),
('host1', '2013/05/06', 'Processor', 1.214, 'PRO'),
('host1', '2013/05/06', 'Memory', 26.795, 'PRO'),
('host1', '2013/05/06', 'LogicalDisk', 44.323, 'PRO'),
('host1', '2013/04/29', 'Processor', 1.34, 'PRO'),
('host1', '2013/04/29', 'Memory', 25.073, 'PRO'),
('host1', '2013/04/29', 'LogicalDisk', 41.223, 'PRO'),
('host1', '2013/04/22', 'Processor', 0.338, 'PRO'),
('host1', '2013/04/22', 'Memory', 23.41, 'PRO'),
('host2', '2013/05/13', 'Processor', 1.25, 'PRO'),
('host2', '2013/05/13', 'Memory', 40.992, 'PRO'),
('host2', '2013/05/13', 'LogicalDisk', 32.32, 'PRO'),
('host2', '2013/05/06', 'Processor', 2.1, 'PRO'),
('host2', '2013/05/06', 'Memory', 42.45, 'PRO'),
('host2', '2013/05/06', 'LogicalDisk', 34.32, 'PRO'),
('host3', '2013/05/06', 'Processor', 2.1, 'PRO'),
('host3', '2013/05/06', 'Memory', 42.45, 'PRO'),
('host3', '2013/05/06', 'LogicalDisk', 34.32, 'PRO');

SELECT HOSTNAME, weekStart, [Object], AverageValue_AVG, SOURCE FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY HOSTNAME, weekStart, [Object] ORDER BY HOSTNAME, weekStart, [Object]) as RN, * FROM
(SELECT * from @TestStub
UNION
SELECT * from @TestStub1) A) B WHERE RN = 1 Order by HOSTNAME, weekStart, [Object];

RESULTS:
HOSTNAME, weekStart, [Object], AverageValue_AVG, SOURCE
host1 2013-04-22 Memory 25.010 OPD
host1 2013-04-22 Processor 0.728 OPD
host1 2013-04-29 LogicalDisk 47.623 OPD
host1 2013-04-29 Memory 25.073 OPD
host1 2013-04-29 Processor 1.640 OPD
host1 2013-05-06 LogicalDisk 47.341 OPD
host1 2013-05-06 Memory 26.795 OPD
host1 2013-05-06 Processor 1.214 PRO
host1 2013-05-13 LogicalDisk 41.880 PRO
host1 2013-05-13 Memory 22.321 PRO
host1 2013-05-13 Processor 1.350 PRO
host2 2013-05-06 LogicalDisk 34.320 PRO
host2 2013-05-06 Memory 48.485 OPD
host2 2013-05-06 Processor 1.911 OPD
host2 2013-05-13 LogicalDisk 34.832 OPD
host2 2013-05-13 Memory 40.992 OPD
host2 2013-05-13 Processor 1.005 OPD
host3 2013-05-06 LogicalDisk 34.320 PRO
host3 2013-05-06 Memory 42.450 PRO
host3 2013-05-06 Processor 2.100 PRO
[/CODE]

nietzky
Yak Posting Veteran

75 Posts

Posted - 2013-05-22 : 17:26:03

Thank you. I am very close now. I just want to make sure that if I have same hostname, weekStart and Object but different SOURCE, I always display OPD source first. Ex. if no OPD data is available then I will show PRO object with average for that date and hostname. Basically PRO table is my second choice.

Thx again.

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-22 : 23:53:53

quote:
Originally posted by nietzky

Thank you. I am very close now. I just want to make sure that if I have same hostname, weekStart and Object but different SOURCE, I always display OPD source first. Ex. if no OPD data is available then I will show PRO object with average for that date and hostname. Basically PRO table is my second choice.

Thx again.

Here is the modified query that gives you the average:
[CODE]

SELECT HOSTNAME, weekStart, [Object], Average, SOURCE FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY HOSTNAME, weekStart, [Object] ORDER BY HOSTNAME, weekStart, [Object]) as RN,
AVG(AverageValue_AVG) OVER(PARTITION BY HOSTNAME, weekStart, [Object]) as Average, * FROM
(SELECT * from @TestStub
UNION
SELECT * from @TestStub1) A) B WHERE RN = 1 Order by HOSTNAME, weekStart, [Object];

[/CODE]

nietzky
Yak Posting Veteran

75 Posts

Posted - 2013-05-24 : 11:27:04

quote:
Originally posted by MuMu88

quote:
Originally posted by nietzky

Thank you. I am very close now. I just want to make sure that if I have same hostname, weekStart and Object but different SOURCE, I always display OPD source first. Ex. if no OPD data is available then I will show PRO object with average for that date and hostname. Basically PRO table is my second choice.

Thx again.

Here is the modified query that gives you the average:
[CODE]

SELECT HOSTNAME, weekStart, [Object], Average, SOURCE FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY HOSTNAME, weekStart, [Object] ORDER BY HOSTNAME, weekStart, [Object]) as RN,
AVG(AverageValue_AVG) OVER(PARTITION BY HOSTNAME, weekStart, [Object]) as Average, * FROM
(SELECT * from @TestStub
UNION
SELECT * from @TestStub1) A) B WHERE RN = 1 Order by HOSTNAME, weekStart, [Object];

[/CODE]

Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources