Self Join,Blank values for record doesn't exist ?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Self Join,Blank values for record doesn't exist ??

Author

Topic

avinash.natekar
Starting Member

5 Posts

Posted - 2012-08-03 : 03:58:08

Hi, I have 4 columns in my table .Date, Version , Machine, Duration.
(There are few runs on the different machines with different verions on different dates and Run Logs are stored in my DB )
I need data returned like below

Date - version - Duration on Host1 - Duration on Host2
1-Aug - 300 - 200 - <blank>
1-Aug - 301 - <blank>- 100
2-Aug - 300 - 200 - 120
2-Aug - 402 - 120 - 240

The thing is ( as result shows ) , if there are no run on host2 for 1 Aug and version 300 , there won't be any record in the result table so that value should be blank in result ( I am confused about how can I get the duration for some host where I didn't run my application so there is no such row in the DB )

Can you please help ? thanks in advance

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-08-03 : 06:32:34

EDIT: NM, not enough coffee yet

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-08-03 : 06:55:33

ok had some tea. here is a nice, terrible cludge:




create table #Host1 ([Date] char(5), [Version] int, Duration int) 
create table #Host2 ([Date] char(5), [Version] int, Duration int) 

insert into #Host1 
	([Date],[Version],Duration)
Values
	('1-Aug',300,200)
	,('2-Aug',300,200)
	,('2-Aug',402,120)

insert into #Host2 
	([Date],[Version],Duration)
Values
	('1-Aug',301,100)
	,('2-Aug',300,120)
	,('2-Aug',402,240)

select * from #Host1 
select * from #Host2

select
	[date],[version],max([Dur 1]) as [Dur 1],max([Dur 2]) as [Dur 2]
FROM
(select 
	[date],[version]
	,case tbl when 'h1' then Duration else null end as [Dur 1]
	,case tbl when 'h2' then Duration else null end as [Dur 2]
FROM
(select 
	[Date],[Version],Duration,'h1' as TBL
From
	#Host1 h1
union all
	select 
	[Date],[Version],Duration, 'h2' as TBL
From
	#Host2 h2) a
) x
group by 
	[date],[version]
order by [date]

--drop table #host1
--drop table #host2

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

avinash.natekar
Starting Member

5 Posts

Posted - 2012-08-03 : 07:28:16

Thanks DonAtWork , but in My DB, I have only one table available ( don't have 2 table for 2 hosts as you created)
I have one table say network table which exactly looks like below

Date - Version - Host - Duration
1 Aug - 300 - Host1 - 200
1 Aug - 301 - Host2 - 100
2 Aug - 300 - Host1 - 200
2 Aug - 300 - Host2 - 120
2 Aug - 402 - Host1 - 120
2 Aug - 402 - Host2 - 240

now I need the resultset as mentioned in the post previously

Avinash
-QA(Automation)

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-08-03 : 09:11:38

Easy peasy


create table #nwk([Date] char(5), [Version] int,Host varchar(10), Duration int) 


insert into #nwk
	([Date],[Version],Host,Duration)
Values
	('1-Aug',300,'Host1',200)
	,('2-Aug',300,'Host1',200)
	,('2-Aug',402,'Host1',120)
	,('1-Aug',301,'Host2',100)
	,('2-Aug',300,'Host2',120)
	,('2-Aug',402,'Host2',240)

select * from #nwk


select
	[date],[version],max([Dur 1]) as [Dur 1],max([Dur 2]) as [Dur 2]
FROM
(select 
	[date],[version]
	,case host when 'Host1' then Duration else null end as [Dur 1]
	,case host when 'Host2' then Duration else null end as [Dur 2]
FROM
#nwk) a

group by 
	[date],[version]
order by [date]

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

avinash.natekar
Starting Member

5 Posts

Posted - 2012-08-06 : 04:19:53

Thanks DonAtWork ! It worked

Avinash
-QA(Automation)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources