Click here to Skip to main content
15,867,594 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hello!

I have a problem with SQL query. Can anyone help me?

Here is a table T with field F(datetime).
SQL
-- format = DD.MM.YYYY
01.01.2012
02.01.2012
03.01.2012
...
10.01.2012
11.01.2012
...
31.01.2012

I need to make a simple select (without any subquery, etc) which show me the two fields result: 1st field - date from table, 2nd - the next minimum date like this:
SQL
01.01.2012 02.01.2012
02.01.2012 03.01.2012
...
10.01.2012 11.01.2012
...
31.01.20012 NULL

I repeat: query must not contain any subquery, joins, unions, etc.

Thank you.
Posted
Comments
fjdiewornncalwe 24-Jan-12 16:46pm    
You should really attempt your homework on your own. Show us what you have tried and we'll help you get it right, but we are not going to simply do your homework for you.
Shadow373 25-Jan-12 1:50am    
Marcus, I know how to make this query using subquery. It's simple:
SELECT T.f, (SELECT MIN(t1.f) FROM T t1 WHERE T.F < t1.f) AS MinF
FROM T
ORDER BY T.f ASC
-- Here are results:
f MinF
----------------------- -----------------------
2012-01-01 00:00:00.000 2012-01-02 00:00:00.000
2012-01-02 00:00:00.000 2012-01-03 00:00:00.000
2012-01-03 00:00:00.000 2012-01-04 00:00:00.000
2012-01-04 00:00:00.000 2012-01-05 00:00:00.000
2012-01-05 00:00:00.000 2012-01-06 00:00:00.000
2012-01-06 00:00:00.000 2012-01-07 00:00:00.000
2012-01-07 00:00:00.000 2012-01-08 00:00:00.000
2012-01-08 00:00:00.000 2012-01-09 00:00:00.000
2012-01-09 00:00:00.000 2012-01-10 00:00:00.000
2012-01-10 00:00:00.000 NULL

but i need remove this part
(SELECT MIN(t1.f) FROM T t1 WHERE T.F < t1.f) AS MinF
and make query without it.

I think about it but can't find solution. That's why I ask community help.
Chris Maunder 24-Jan-12 21:27pm    
Why can't you use any subquery, joins, unions, etc?
Shadow373 25-Jan-12 1:53am    
That's was a direct order. :(
Amir Mahfoozi 25-Jan-12 0:38am    
What about using CTEs ? If not maybe your instructor meant using CURSORs. If no other thing is accepted and only a simple select statement(without subqueries and joins) is acceptable then I should say that your instructor wants to make you all fail and searches for a reason :)

1 solution

If you're using TSQL see the documentation for DATEADD here.
 
Share this answer
 
Comments
Shadow373 25-Jan-12 2:03am    
Man, I really know about DATEADD. Your shot miss the target. See my comment to message from Markus.
Btw, DATEADD don't help if there are some holes between dates like this:
01.01.2012
03.01.2012
04.01.2012
21.02.2012
etc.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900