Inner join between dates

[flygstolen_fred]

Hi,

I'm trying to concatenate two tables when the dates conditions are met in the WHERE clause.

Everything works as it should except when OrderDate and the ToDate are the same date.

Any help would be much appreciated?!

WHERE OrderDate >= FromDate and OrderDate <= ToDate;

Order:

OrderId	Total	OrderDate
1234	330	2016-01-02
1235	105	2016-01-15

Markup:

Category	Code	Descr	CountryCode	Price	FromDate	ToDate
GENERAL	ALL	Text1 - internal	SE	17	2016-01-01	2016-01-02

[sunny_talwar]

I think you will need to use IntervalMatch here... Look here:

IntervalMatch

[flygstolen_fred]

Hi,

It works perfect when the OrderDate are between FromDate and ToDate but not when OrderDate are equal to ToDate.

Can I use IntervalMatch in the WHERE clause?

Here is how the load statement look, Swuehl helped me:

Re: Lines fetched repeats

[vinieme12]

if that is the only problem, can you add one more condition to your where clause and try

WHERE

(OrderDate >= FromDate and OrderDate <= ToDate)

OR

(OrderDate = ToDate);

Cheers

V

[vinieme12]

just keen to know

Have you tested a scenario where FromDate and ToDate are both same?

[flygstolen_fred]

Thanks for trying to help. That didn't solve my problem.

[flygstolen_fred]

The OrderDate also contains time that the date-function (Date(OrderDate)) didn't remove. When I changed to floor it solved the problem:

WHERE Floor(OrderDate) >= FromDate and Floor(OrderDate) <= ToDate;

[sunny_talwar]

What would be the result you expect to see when these two tables are combined?

[flygstolen_fred]

Hi Sunny T,

Thanks for helping out.
I'm trying to concatenate to tables (invoicelines, markup) when certain conditions match but the problem was that the OrderDate also contained time that wasn't removed with the Date-function. When I changed Date to Floor everything worked as it should.

Re: Lines fetched repeats