SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 JOIN on 2 tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nietzky
Yak Posting Veteran

72 Posts

Posted - 05/21/2013 :  15:02:28  Show Profile  Reply with Quote
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

Edited by - nietzky on 05/21/2013 15:04:49

MuMu88
Aged Yak Warrior

547 Posts

Posted - 05/21/2013 :  15:32:30  Show Profile  Reply with Quote
Try

SELECT DISTINCT * FROM TABLE1
UNION
SELECT DISTINCT * FROM TABLE2

Edited by - MuMu88 on 05/21/2013 15:34:44
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 05/21/2013 :  16:21:37  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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/
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 05/21/2013 :  17:55:16  Show Profile  Reply with Quote
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!
Go to Top of Page

nietzky
Yak Posting Veteran

72 Posts

Posted - 05/21/2013 :  21:15:54  Show Profile  Reply with Quote
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

Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 05/22/2013 :  00:57:56  Show Profile  Reply with Quote
Here you go:


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
Go to Top of Page

nietzky
Yak Posting Veteran

72 Posts

Posted - 05/22/2013 :  17:26:03  Show Profile  Reply with Quote
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.
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 05/22/2013 :  23:53:53  Show Profile  Reply with Quote
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:


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];

Go to Top of Page

nietzky
Yak Posting Veteran

72 Posts

Posted - 05/24/2013 :  11:27:04  Show Profile  Reply with Quote
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:


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];






Thank you
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000