Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
I think you will need to use IntervalMatch here... Look here:
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:
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
just keen to know
Have you tested a scenario where FromDate and ToDate are both same?
Thanks for trying to help. That didn't solve my problem.
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;
What would be the result you expect to see when these two tables are combined?
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.