sql query -imaginary columns - 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

SQL Server 2005 Forums

Transact-SQL (2005)

sql query -imaginary columns

Author

Topic

josh777
Starting Member

12 Posts

Posted - 2007-03-15 : 13:05:35

I have a doubt regarding query, it is not that complicated but I am not aware of the syntax

in the northwind db , orders table

I want to declare a variable Testdate

If the Order date between 1/1/1996 to 1/1/1998

Then

Set testdate as 7/7/1997

If the Order date between 1/1/1998 to 1/1/2000

Then

Set testdate as 7/7/1999

Then I want to display the values of Testdate(as imaginary column)

Orderid Customerid Orderdate Ship date Testdate

How can i do this? if u can send the syntax it will be helpful.

Thank you,
josh

pootle_flump

1064 Posts

Posted - 2007-03-15 : 13:15:01

I would dump these into a table. Make sure you account for date 0 and date forever-in-the-future.

Then:

SELECT	Orderid
	, Customerid
	, Orderdate 
	, Shipdate
	, md.Testdate
FROM	dbo.orders AS o
INNER JOIN
	dbo.mydates AS md
ON	o.Orderdate BETWEEN md.lower_limt AND md.upper_limit

Or you could calculate mathematically if there is a rule.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-15 : 13:42:21

To just perform it as a simple calculation (pootle_flump's solution will be better if you have an ongoing need for mutiple date ranges)

SELECT Orderid, Customerid, Orderdate, Shippeddate, 
    CASE WHEN Orderdate BETWEEN '1/1/1996' AND '1/1/1998 23:59:59' THEN '7/7/1997'
    WHEN Orderdate BETWEEN '1/1/1998' AND '1/1/2000 23:59:59' THEN '7/7/1999'
    END AS Testdate
FROM Orders

josh777
Starting Member

12 Posts

Posted - 2007-03-15 : 13:43:17

Is it possible to create Testdate column based on the conditions without creating the table? thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-15 : 13:46:50

UPDATE Orders
SET TestDate = CASE WHEN OrderDate >= '19980101' AND OrderDate < '20000101' THEN '19990707' ELSE NULL END

Peter Larsson
Helsingborg, Sweden

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-15 : 13:53:15

quote:
Originally posted by josh777

Is it possible to create Testdate column based on the conditions without creating the table? thanks

Yes, that's what my query does.

josh777
Starting Member

12 Posts

Posted - 2007-03-15 : 13:55:56

thanks

pootle_flump

1064 Posts

Posted - 2007-03-15 : 14:05:06

quote:
Originally posted by josh777

Is it possible to create Testdate column based on the conditions without creating the table? thanks

This is good reading but in particular pay attention to third normal form:
http://r937.com/relational.html

EDIT - beg your pardon - I thought you were referring to a column in a table rather than a result set.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources