Additional Detail - 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

General SQL Server Forums

New to SQL Server Programming

Additional Detail

Author

Topic

Rayman
Starting Member

39 Posts

Posted - 2010-10-21 : 13:07:00

This script is working.
******************************************************************
SCRIPT:
Select database_name as [database name],

datename(month,database_date) as [ month ],

CASE WHEN datepart(hour,database_date) <12 THEN 'YES' ELSE 'NO' END as AM,

CASE WHEN datepart(hour,database_date) >=12 THEN 'YES' ELSE 'NO' END as PM,

convert(decimal(6,0),datebase_size)'database size',

CASE WHEN CHARINDEX('SQL2000', [datebase_version]) > 0 THEN 'YES' ELSE 'NO' END as [SQL 2000],

CASE WHEN CHARINDEX('SQL2005', [datebase_version]) > 0 THEN 'YES' ELSE 'NO' END as [SQL 2005]

FROM JOHN;
*******************************************************************
raw data
database_name datatbase_date database_version database_size database Number 1 11/30/08 1:52 AM SQL2000 ver 1.0 54.5
database 2 12/31/08 9:22 PM SQL2005 ver 2.11 2254.78
database 3 3/17/08 11:22 AM SQL2000 ver 1.023 12.4
database 143 1/4/09 11:22 PM SQL2005 ver 3.002 234.32
***************************************************************
need to add additional information from table

after database number I need to add a new column that states database number

desired results
**NEW Field**
Database Name Database Number Month AM PM db size SQL2k SQL5k
Database Number 1 1 Nov yes no 55 yes no
database 2 2 Dec no yes 2255 no yes
database 3 3 Mar yes no 12 yes no
database 143 43 Jan no yes 234 no yes

Terry Lynn King

Rayman
Starting Member

39 Posts

Posted - 2010-10-21 : 13:47:32

output should looked like this
Database Name Database number
database number 1 1
database 2 2
database 3 3
database 143 43

Terry Lynn King

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-23 : 02:15:00

why for 143 alone number value is 43?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sachin.Nand

2937 Posts

Posted - 2010-10-23 : 02:56:30

quote:
Originally posted by visakh16

why for 143 alone number value is 43?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

He never clarified this even in his previuos post..

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=151529

PBUH

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-24 : 02:23:28

Rayman, I got your email asking me to look at this thread. I've looked at it, and it's being handled appropriately by other members already. If you want them to help you further, then you need to explain why 143 gets changed to 43 as it doesn't make any sense currently.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Rayman
Starting Member

39 Posts

Posted - 2010-10-26 : 12:27:24

Sorry, It should be 143 sorry of the lack of understanding. Thank you,

quote:
Originally posted by tkizer

Rayman, I got your email asking me to look at this thread. I've looked at it, and it's being handled appropriately by other members already. If you want them to help you further, then you need to explain why 143 gets changed to 43 as it doesn't make any sense currently.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Terry Lynn King

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-26 : 12:31:58

Have a look in your old thread.
Michael Valentine Jones has given a solution.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=151529

No, you're never too old to Yak'n'Roll if you're too young to die.

Rayman
Starting Member

39 Posts

Posted - 2010-10-26 : 12:50:09

I can not use the union in the script

quote:
Originally posted by webfred

Have a look in your old thread.
Michael Valentine Jones has given a solution.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=151529

No, you're never too old to Yak'n'Roll if you're too young to die.

Terry Lynn King

Sachin.Nand

2937 Posts

Posted - 2010-10-26 : 14:46:14

MVJ used union to show some show some sample data & how to get the expected result.

Try this



--sample data

declare @tbl table(DB_Num varchar(20))
insert into @tbl
select 'database 1'	union all
	select'database 143'	union all
	select  'database 44'

--Below is the query
	
select
	*,
	convert(int,reverse(substring(reverse(DB_Num),1,charindex(' ',reverse(DB_Num))-1)))

from
	@tbl
order by
	DB_Num

PBUH

Rayman
Starting Member

39 Posts

Posted - 2010-10-26 : 18:26:29

Sorry for my ignorance. I modified the code to work as such

select
*,
convert(int,reverse(substring(reverse(database_name),1,charindex(' ',reverse(database_name))-1)))as [Column]

from
JOHN
order by
database_name

it returns the columns

Database_name
database 143
database 2
database 3
database number 1

Column
0
0
0
0

It should be
Column
1
2
3
43

It does not appear to be counting the database_names?

quote:
Originally posted by Sachin.Nand

MVJ used union to show some show some sample data & how to get the expected result.

Try this

--sample data

declare @tbl table(DB_Num varchar(20))
insert into @tbl
select 'database 1' union all
select'database 143' union all
select 'database 44'

--Below is the query

select
*,
convert(int,reverse(substring(reverse(DB_Num),1,charindex(' ',reverse(DB_Num))-1)))

from
@tbl
order by
DB_Num

PBUH

Terry Lynn King

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-27 : 13:38:00

[code]select
*,
STUFF(database_name,1,PATINDEX('%[0-9]%',database_name)-1,'') as [Column]

from
JOHN
order by
database_name
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources