SQL select statements - 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

SQL select statements

Author

Topic

Rayman
Starting Member

39 Posts

Posted - 2010-10-13 : 12:03:37

I have the following select statements. I am trying to condense these statements to perform as one command.

Statements:
SELECT database_name as [database name] FROM john
select convert(decimal(6,0),datebase_size)'database size'
FROM john
SELECT 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
FROM john

Task trying to complete:
Get database number from database_name
Get name of Month from database_date
AM- yes or no from database_date
PM- yes or no from databse_date
Database size rounded and converted to integer from database size
sql2000 yes or no from database_version
sql2005 yes of no from database_version

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

Formatted Data
Database Name Database Number Month AM PM Datbase Size SQL2K SQL2k5
databse Number 1 1 November yes no 55 yes no
database 2 2 December No Yes 2255 no yes
database 3 3 March yes no 12 yes no
datbase 143 4 January no yes 234 no yes

Terry Lynn King

X002548
Not Just a Number

15586 Posts

Posted - 2010-10-13 : 12:11:56

FROM John?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

Rayman
Starting Member

39 Posts

Posted - 2010-10-13 : 12:18:20

yes from the table john.
I have worked on this and have the online documentation securely attached to my browser. I hav epoured over the examples. Difficult time trying to decipher the errors in the query results. Is there a good reference for the error messages? I click on the error and it highlights the problem and then I attack from there.

quote:
Originally posted by X002548

FROM John?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

Terry Lynn King

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-13 : 12:19:07

Your question and sample data is not properly formatted.

I just framed the below select query with whatever I could understand by your post.If I misunderstood your concern, please
feel free to point it out.

Try it:

Select database_name as [database name],
convert(decimal(6,0),datebase_size)'database size',
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
CASE WHEN CHARINDEX('SQL2000', database_version) > 0 THEN 'YES' ELSE 'NO' END as SQL2K ,
CASE WHEN CHARINDEX('SQL2005', database_version) > 0 THEN 'YES' ELSE 'NO' END as SQL25
FROM JOHN

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

Rayman
Starting Member

39 Posts

Posted - 2010-10-13 : 12:35:21

script error
running your input provided this response

Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'CASE'.

in red highlighted letters. I click on the error and it places the cursor in front of the case statement.

CASE WHEN CHARINDEX('SQL2000', database_version) > 0 THEN 'YES' ELSE 'NO' END as SQL2K,

I review the documentation and do not see what could be the problem after revewing the example.

Terry Lynn King

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-13 : 12:44:50

Try it:

Select database_name as [database name],
convert(decimal(6,0),datebase_size)'database size',
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, --Comma is missing. I don't have sql box currently to compile the query so just posting from notepad.
CASE WHEN CHARINDEX('SQL2000', database_version) > 0 THEN 'YES' ELSE 'NO' END as SQL2K ,
CASE WHEN CHARINDEX('SQL2005', database_version) > 0 THEN 'YES' ELSE 'NO' END as SQL25
FROM JOHN

Regards,
Bohra

Rayman
Starting Member

39 Posts

Posted - 2010-10-13 : 13:14:48

Thank you this had made it simplier.
On the output if I want the database size to align on the right side of the column. For this to happen would it be a nested trim function?

Terry Lynn King

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-13 : 14:47:12

quote:
Originally posted by Rayman

Thank you this had made it simplier.
On the output if I want the database size to align on the right side of the column. For this to happen would it be a nested trim function?

Terry Lynn King

If possible, you do formatting (right align) on front end.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources