simple sql code without hard coded data

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

simple sql code without hard coded data

Author

Topic

Rayman
Starting Member

39 Posts

Posted - 2010-09-23 : 18:00:36

1. I am now trying to combine this code and make it one simple statement. I used a table and need to remove the table.
I have jumped between two test db's. i will stay on one db for this task.
The unions are not the proper way to completing this task. IT is hard coding the data in the script. I have the dates and time in
yyyy-mm-dd hh-mm-ss and I am trying to state the date.

2008-11-30 01:52:43.000
2008-12-31 21:22:43.000
2008-03-17 11:22:43.000
2009-01-04 23:22:43.000

By the following output:
Column Month
1 November
2 December
3 March
4 January

2. next I am trying to get database number from the table
database_name
database number 1
databse 2
database 3
database 143
trying to display in the format
Column database_name
database 1
database 2
database 3
database 4

current code

DECLARE @Table TABLE (database_date datetime)
insert into @Table
SELECT '11/30/08 1:52 AM' UNION
SELECT '12/31/08 9:22 PM' UNION
SELECT '3/17/08 11:22 AM' UNION
SELECT '1/4/09 11:22 PM'
SELECT ROUND(datebase_size,-1)'old column',convert(decimal(6,1),datebase_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 @table

Terry Lynn King

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-23 : 18:45:36

I would have answered your post in the other thread, but I can't figure out what you want. Please re-read what you posted and see if it makes sense to you. Remember that we can't see your system and don't understand your data, so you have to spell it out to us.

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-09-23 : 20:07:33

table: database_name

output:
Database_name
database number 1
database 2
database 3
database 143

Table: database_date
output:
database_date
2008-11-30 01:52:43.000
2008-12-31 21:22:43.000
2008-03-17 11:22:43.000
2009-01-04 23:22:43.000

Table: datebase_version
output:
Datebase_version
Sql2000 version 1.0223
MS Sql2005 ver 2.113 rev 1
Sql2000 version 1.0223
MS Sql2005 ver 3.0223 rev 2

Table: datebase_size
Output:
54.50
2254.78
12.40
234.32

Clean up script to produce
a. Database number from Database_name
b. get name of month of database_date
c. AM- Yes or no from database_date
d. PM- Yes or NO from database_date
databse size rounded from converted to integer rom database_size
e. sql200 - yes or no from database _version
f sql2005 - yes or no from database_version

example:
database 1 11/30/08 1:52 AM sql version 1.0223 54.5

produce this

database 1 1 November YES NO 55 YES NO

Terry Lynn King

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-09-24 : 11:38:06

The people that help out here are all un-paid volunteers. Providing the DDL (CREATE TABLE, CREATE INDEX, etc.), DML (INSERT statements) and Expected Output will go a long way in getting people to look at your issue and help you out. That way we can run our code against your data and the benefit to you is you get working code back. It's also a good idea to include code for what you have already tried.

This link can help your prepare your question including DDL and DML:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Rayman
Starting Member

39 Posts

Posted - 2010-09-24 : 11:48:30

This is it. Not pretty. I place snippets together as I could get it to work. But I am trying to streamline. I do not know enough to get all the functions and reserve words to work. I am scheduled for additional training this next month.

DECLARE @Table TABLE (database_date datetime)
insert into @Table
SELECT '11/30/08 1:52 AM' UNION
SELECT '12/31/08 9:22 PM' UNION
SELECT '3/17/08 11:22 AM' UNION
SELECT '1/4/09 11:22 PM'
SELECT ROUND(datebase_size,-1)'old column',convert(decimal(6,1),datebase_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 @table

Terry Lynn King

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources