Problem description & analysis

The sheet2 of an Excel file is a to-be-searched data area, where column A contains start dates and column B contains end dates, and the date periods of each row are not overlapped. Below is part of the data area:

By entering a date parameter, we get the corresponding row data, take columns from C to F, transpose the row data to column data, and populate values in C2:C5 of sheet1. Suppose the date parameter we entered is 2018/7/18, then sheet 1 will become as follows:

The algorithm involves sequence query and single-record transposition.

Solution & explanation

Copy data from the 3rd row in Excel sheet2 downwards and paste it to cell A1 in the esProc script. Then we write the following code:

A2: Split and parse A1’s string as a sequence of sequences. The @p option means to parse the data to an appropriate type automatically, which is date here. 

A3: Query sequences by a certain date period. Here pDate is a cellset parameter, whose value is, e.g. 2018/7/18. The date format should be consistent with that defined in Excel, which is yyyy/mm/dd.

A4: Get members of A3’s sequence from the 3rd one on.

After the above script is executed, copy and paste A4’s result to cell C2 in Excel sheet1 to complete the computation.

Q & A Collection

https://stackoverflow.com/questions/63840410/lookup-data-based-on-a-date-that-falls-between-two-dates