Join table with ID and date between 2 date

[Suvijak]

Hi Friend,

I have question about script,

- I have 2 table with OrderRecord and EmployeeStatus

- I want to join those table together and with condition EmployeeID must same and Order_date must between Start date and last date

- Calculate the work experience by [Order_date - Start_date] 

- If employee have change position and change back, measure the experience from lasted time only

Input Table: OrderRecord

Order_ID	Order_date	Employee_ID
1	01-Jan-19	2222
2	01-Feb-19	1111
3	01-Aug-19	1111
4	01-Aug-19	2222

EmployeeStatus

Employee_ID	Position	Start_date	Last_date
1111	Sale	Jun-19	-
1111	Support	Mar-19	Jun-19
1111	Sale	Jan-18	Jun-19
2222	Sale	Jan-17	Sep-19

* No Last date mean they still working on this position

Expected output table

Output_table	Order_date	Employee_ID	Position	Work_experience
1	01-Jan-19	2222	Sale	24
2	01-Feb-19	1111	Sale	1
3	01-Aug-19	1111	Sale	2
4	01-Sep-19	2222	Sale	32

I a bit concern with performance as the OrderRecord have  more than 1M record and Employee table is around 20k record

Additional Question, I don't want to create another topic.

- Can I create sort order for every table?

- Can I join table to existing table without create new table

- If i need to load new data every time, is it needed to create QVD file?

Sorry for my poor English.

Thank you,

Suvijak.

[Yousef_Amarneh]

Hi,

For your first question, you can use intervalmatch function, just search on google and you will find good examples matching your requirements.

For the additional Question, below are the answers:

- Can I create sort order for every table?

Yes, you can use order by "field" or rowno() function and use it for sort, or you can use rank also, depends on your requirements.

- Can I join table to existing table without create new table

yes you can using join(table_name)

- If i need to load new data every time, is it needed to create QVD file?

it's good to have QVDs for all the tables specially if the table are being used in multiple applications or the data is huge and you are using incremental load, but if you need to refresh the data directly from the data source yes you can load without storing the data in QVD file.