August 27, 2012 at 3:20 am
SELECT @MSGOUT = @MSGOUT + '
<tr>
<td>' + ss.ServerName +'</td>
<td>' + ss.DBName +'</td>
<td>' + Occurrences +'</td>
</tr>
'
FROM DBINVENTORY ss
INNER JOIN
(SELECT ServerName,DBname,
cast(COUNT(dbname) as varchar(5))AS Occurrences
FROM DBINVENTORY d
GROUP BY ServerName,DBName
HAVING ( COUNT(DBName) > 1 )
)sq ON ss.DBName=sq.DBName
this giving me output like
----------------------------------------
Server_NameDbnameOccurences
XABC2
XABC2
YHIJ2
YHIJ2
YNOP2
YNOP2
ZBCD4
ZBCD4
ZBCD4
ZBCD4
ZJIK4
ZJIK4
ZJIK4
ZJIK4
ZJIK24
ZJIK24
ZJIK24
ZJIK24
AABC2
AABC2
ATUV2
ATUV2
BEFG2
BEFG2
DACD2
DACD2
---------------------------------------
Actual output required is
Server_NameDbnameOccurences
XABC2
YHIJ2
YNOP2
ZBCD4
ZBCD4
ZJIK4
ZJIK4
ZJIK24
ZJIK24
AABC2
ATUV2
BEFG2
DACD2
Please advise the join condition.....
Sagar Sonawane
** Every DBA has his day!!:cool:
August 27, 2012 at 3:56 am
Maybe you can give some sample data for table DBINVENTORY.
Why is it OK for some servername-dbname pairs to have 2 rows as output and for others isn't?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 27, 2012 at 4:24 am
Main objective is to find duplicate db's...... and out put is perfect..... which I am looking for....
Can you please help wi
Sagar Sonawane
** Every DBA has his day!!:cool:
August 27, 2012 at 4:41 am
Saga... (8/27/2012)
Main objective is to find duplicate db's...... and out put is perfect..... which I am looking for....
Can you please help wi
Sorry but it is difficult without the sample data and the actual requirement.Here none of these are not clear 🙁
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
August 27, 2012 at 5:30 am
Saga... (8/27/2012)
Main objective is to find duplicate db's...... and out put is perfect..... which I am looking for....
Can you please help wi
I asked two questions. You didn't answer any of them.
How much effort do you think I will put in your questions?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 27, 2012 at 5:33 am
SELECT @MSGOUT = @MSGOUT + '
<tr>
<td>' + ss.ServerName +'</td>
<td>' + ss.DBName +'</td>
<td>' + Occurrences +'</td>
</tr>
'
FROM /*DBINVENTORY ss --after removing this commented words...I got required output
INNER JOIN*/
(SELECT ServerName,DBname,
cast(COUNT(dbname) as varchar(5))AS Occurrences
FROM DBINVENTORY d
GROUP BY ServerName,DBName
HAVING ( COUNT(DBName) > 1 )
)sq ON ss.DBName=sq.DBName
Sagar Sonawane
** Every DBA has his day!!:cool:
August 27, 2012 at 7:39 am
Saga... (8/27/2012)
SELECT @MSGOUT = @MSGOUT + '<tr>
<td>' + ss.ServerName +'</td>
<td>' + ss.DBName +'</td>
<td>' + Occurrences +'</td>
</tr>
'
FROM /*DBINVENTORY ss --after removing this commented words...I got required output
INNER JOIN*/
(SELECT ServerName,DBname,
cast(COUNT(dbname) as varchar(5))AS Occurrences
FROM DBINVENTORY d
GROUP BY ServerName,DBName
HAVING ( COUNT(DBName) > 1 )
)sq ON ss.DBName=sq.DBName
So does this mean you solved your issue or not?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 27, 2012 at 7:55 am
You were joining your grouped results with the original table with duplicates. You can get an even simpler version of your code like this, no need of a subquery:
SELECT @MSGOUT = @MSGOUT + '
<tr>
<td>' + ServerName +'</td>
<td>' + DBName +'</td>
<td>' + cast(COUNT(dbname) as varchar(5)) +'</td>
</tr>
'
FROM DBINVENTORY
GROUP BY ServerName,DBName
HAVING ( COUNT(DBName) > 1 )
August 30, 2012 at 4:41 am
Luis Cazares (8/27/2012)
You were joining your grouped results with the original table with duplicates. You can get an even simpler version of your code like this, no need of a subquery:
SELECT @MSGOUT = @MSGOUT + '
<tr>
<td>' + ServerName +'</td>
<td>' + DBName +'</td>
<td>' + cast(COUNT(dbname) as varchar(5)) +'</td>
</tr>
'
FROM DBINVENTORY
GROUP BY ServerName,DBName
HAVING ( COUNT(DBName) > 1 )
Thanks luis....
Sagar Sonawane
** Every DBA has his day!!:cool:
August 30, 2012 at 4:43 am
Sean Lange (8/27/2012)
Saga... (8/27/2012)
SELECT @MSGOUT = @MSGOUT + '<tr>
<td>' + ss.ServerName +'</td>
<td>' + ss.DBName +'</td>
<td>' + Occurrences +'</td>
</tr>
'
FROM /*DBINVENTORY ss --after removing this commented words...I got required output
INNER JOIN*/
(SELECT ServerName,DBname,
cast(COUNT(dbname) as varchar(5))AS Occurrences
FROM DBINVENTORY d
GROUP BY ServerName,DBName
HAVING ( COUNT(DBName) > 1 )
)sq ON ss.DBName=sq.DBName
So does this mean you solved your issue or not?
Yes Sean...it solved my issue...
Sagar Sonawane
** Every DBA has his day!!:cool:
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply