如何在开始日期和结束日期之前获得一条记录

I want to get one record before start date and end date

我希望在开始日期和结束日期之前获得一条记录

DtpFrom means – 'date picker from
DtpTo means – 'date picker to

VB 6 CODE

VB 6代码

sdate = DateToString(dtpFrom)
edate = DateToString(dtpTo)

QUERY

Where DATE BETWEEN '" & sdate & "' AND '" & edate & "'"

I want to get one record before sdate and edate

我希望在sdate和edate之前获得一条记录

I tried this code

我试过这段代码

VB 6 CODE

VB 6代码

s1sdate = -sdate
e1edate= -edate

QUERY

Where DATE BETWEEN '" & s1date & "' AND '" & e1date & "'"

But it is going one day minus

但它有一天减去

Example

Selecting 03/05/2009 to 03/06/2009 from date picker, but it showing record from
02/05/2009 to 02/06/2009.

I want to display one record before from the selecting date and ending date, not one day before, because my table is not a continous date.

我想在选择日期和结束日期之前显示一条记录,而不是前一天,因为我的表格不是连续日期。

ADDITIONAL EXAMPLE:

If we have a table and rows [ ID(int) , Value(Money) ] and we have some rows in it

如果我们有一个表和行[ID(int),Value(Money)]并且我们有一些行

ID --Value
1------70 
2------100 
3------150 
8------200 
20-----250 
45-----280

and we want to make Query that get each row ID, Value and the previous Row Value in which data appear as follow

并且我们想要生成查询,以获取每个行ID,值和前面的行值,其中数据显示如下

ID --- Value ---Prev_Value 
1 ----- 70 ---------- 0 
2 ----- 100 -------- 70 
3 ----- 150 -------- 100 
8 ----- 200 -------- 150 
20 ---- 250 -------- 200 
45 ---- 280 -------- 250

i make the following query but i think it's so bad in performance in huge amount of data

我做了以下查询,但我认为它在大量数据中的表现非常糟糕

select  t1.id, t1.value, t2.value  from  table  t1 inner join table  t2  on t1.id = t2.id where t2.value =  (select max(value)  from  table t where t.value< t1.value and t.id = t1.id ) and  T1.value BETWEEN '" & sdate & "' AND '" & edate & "'

Need VB 6 CODE OR ACCESS QUERY HELP.

需要VB 6代码或访问查询帮助。

1 个解决方案

#1

SELECT * FROM whatever
WHERE DATE < sdate
ORDER BY DATE DESC
LIMIT 1

I don't think end date really matters if you want "the one before the records between start and end date"

如果您想要“开始和结束日期之间的记录之前”,我认为结束日期并不重要

But if you want the record right before start date AND the record right before end date you could just repeat the above query with edate instead of sdate.

但是如果你想要在开始日期之前的记录和在结束日期之前的记录,你可以用edate而不是sdate重复上述查询。

This is what the query actually means: Select every record with a DATE before this one (sdate). For all of those records, order them by DATE in a descending manner (DESC). Only return the first one (LIMIT 1).

这就是查询的实际含义:在此之前选择DATE的每个记录(sdate)。对于所有这些记录,请按DATE以降序(DESC)对它们进行排序。仅返回第一个(LIMIT 1)。

Note: Some implementations vary. You may need to use "TOP 1" after the word "SELECT" instead of "LIMIT 1"

注意:某些实现有所不同。您可能需要在“SELECT”之后使用“TOP 1”而不是“LIMIT 1”

#1

SELECT * FROM whatever
WHERE DATE < sdate
ORDER BY DATE DESC
LIMIT 1