Join by date and time ranges

Question

I have two tables:

Invoice
Cost

I would like to find the cost record associated with the invoice record to create a margin report.

For example, order 1004 from invoice table, I would like to take the date and time from that file and find the cost based on that date and time (equal to or less then).

order 1004 has 04/15 time of 171543 which would link the cost table record of date 04/15 and time 171523.

See image below for details how I would like to see the output.

thanks in advance

Invoice file

Order1    Item1  Sales1     Date1   Time1
1001      | A1001   | 10.00     |04/15  |151025
1002      | A1001   | 12.00     |04/15  |151112
1003      | A1001   | 11.00     |04/15  |171235
1004      | A1001   | 14.00     |04/15  |171543
1005      | A1001   | 13.50     |04/15  |171855

Cost file

Item2   Cost2   Date2    Time2 
A1001   | 3.50  |04/14  |171255
A1001   | 4.20  |04/15  |151233
A1001   | 2.50  |04/15  |171523
A1001   | 4.00  |04/15  |171623

Output layout - Margin Report

Order   |Item    |Sales     |Cost   |Margin
1001    |A1001   |10.00     |3.50   | 6.50
1002    |A1001   |12.00     |3.50   | 8.50 
1003    |A1001   |11.00     |2.50   | 8.50 
1004    |A1001   |14.00     |2.50   | 11.50 
1005    |A1001   |13.50     |4.00   | 9.50

Please let us know what database system you're working with. — squillman, Apr 25 '12 at 14:47
By separating the dates from the times, you have made any query operations like this grotesquely more complex than they would be if you have a single DATE+TIME (aka TIMESTAMP) column. — Jonathan Leffler, Apr 25 '12 at 15:53

score 3 · Answer 1 · edited May 23 '17 at 12:21

3

This should work

select yourFields
  from invoice
       inner join cost on cost.item2 = invoice.item1
                      and cost.date2 = invoice.date1
                      and cost.time2 = (select max(cost_inner.time2)
                                          from cost as cost_inner
                                         where cost_inner.item2 = invoice.item1
                                           and cost_inner.date2 = invoice.date1
                                           and cost_inner.time2 <= invoice.time1)

There is a way to avoid the inner join with a more complex query, you can check that out here

edited May 23 '17 at 12:21

Community

1
1

answered Apr 25 '12 at 15:06

Alejandro B.

4,807
2
33
61

The general structure is right, but I would expect the answer to be based on the MAX(cost.time2) that comes before the invoice time1. The schema is complicated by the separation of date from time; that sort of thing is normally a mistake, but is not your fault. – Jonathan Leffler Apr 25 '12 at 15:52
@JonathanLeffler Right you are. Fixed. Feel free to edit yourself next time! – Alejandro B. Apr 25 '12 at 16:14

Join by date and time ranges

1 Answers1