SQL query help 1

[liquidtox]

Hi,

I am having problems making a query that will return the rows for a specified date AND the preceding N number of days (not necessarily just date - N days).

I.e.

table date column consists of:

Date Column
...
Aug 5,2002
Aug 7,2002
Aug 8,2002
Aug 10,2002
Aug 12,2002
...

If i want to query Aug, 11, 2002 and the previous 2 days before it the select statement should return the rows:

Aug 10, 2002
Aug 8, 2002
Aug 7, 2002

How would i accomplish this ?? TIA.

 

[barbarul2001]

table a1

ss

20.08.2002 10:23:34
18.08.2002 10:23:34
16.08.2002 10:23:34
14.08.2002 10:23:34
12.08.2002 10:23:34
10.08.2002 10:23:34
08.08.2002 10:23:34
06.08.2002 10:23:34
04.08.2002 10:23:34
02.08.2002 10:23:34

SQL:

select * from a1
where ss<='07.08.2002' and
3>(select count(*) from a1 d
where d.ss<a1.ss)

and i have

SS
06.08.2002 10:23:34
04.08.2002 10:23:34
02.08.2002 10:23:34



I think it works.... web/sql developer

 

[liquidtox]

Thanks for help. But that doesn't seem to do exactly what I wanted. It works in the case of the example because the first 3 rows are the correct ones, however when there are more rows preceding them the wrong ones would be returned

table a1

ss

20.08.2002 10:23:34
18.08.2002 10:23:34
16.08.2002 10:23:34
14.08.2002 10:23:34
12.08.2002 10:23:34
10.08.2002 10:23:34
08.08.2002 10:23:34
06.08.2002 10:23:34
04.08.2002 10:23:34
02.08.2002 10:23:34
01.08.2002 10:23:34
28.07.2002 10:23:34

Would return
02.08.2002 10:23:34
01.08.2002 10:23:34
28.07.2002 10:23:34

which is not right...

 

[Jocsta]

I don't know of a pure ANSI way of doing this, but for a db specific version try (probably not the best queries in the world, I'm still learning.....)

SQL SERVER:-
select top 3 *
from a1
where ss <= '11.08.2002'


ORACLE:-
select *
from( select * from a1
where ss <= '11-AUG-00'
order by ss desc
)
where rownum <= 3

OTHERS:-
you're on your own 8)

<< JOC >>

 

[angiole]

You did not specifiy the DBMS, but here's the Oracle version anyway:

What you arasking for is the records with a date between the range of
(Input date - Input days back) and (Input date), as

Select    col1,
            col2,
            date1
From    TableA
Where  date1 between (&&Input_Date - &&Input_Days) and &&Input_Date

(The && notation simply invokes the user variable input)

There's a lot more to consider here.
What is the default date format permitted for data entry?
Is this standalone SQL, procedural, fed from the web?

I'm sure you get the idea.


AA 8~)

 

[liquidtox]

My apologies. I forgot to mention I am using MS Sql server 2000.

SQL SERVER:-
select top 3 *
from a1
where ss <= '11.08.2002'

This almost works but it returns the first 3 rows in my table instead of the 3 preceding 11.08.2002 ... is there a corresponding BOTTOM function??? i couldn't find one. TIA>

 

[Jocsta]

OK, I think there's 2 ways to do this.
Change the query to:-

select top 3 *
from a1
where ss <= '11.08.2002'
order by a1 desc

OR

use the original query and change the direction of the index on the ss column from ascending to descending.

<< JOC* >>

 

[liquidtox]

thanks all. Works great.