CODEWITHSUNDEEP
xquery
user8748135
user8748135

Reputation: 1

Finding out closest date from today

I have defined $today = current system date.

Objective: To find out the value of closest "date" element from today and get the corresponding "price_per_share" value based on below xml. FYI, it does not matter whether the date is in past or in future when calculating the "closest date".

How do I do this?

I was thinking of

  1. Calculating the difference between today and each "date" value
  2. Then adding "difference" as the child of "details" for each "details" node
  3. Then sort by the smallest "duration"
  4. Then extract the first occurrence of "price_per_share" value.

But somehow adding a child does not seem to work for me either.

<test_record>
<details>
    <date>2013-01-16</date>
    <currency>USD</currency>        
    <shares>48</shares>
    <price_per_share>20</price_per_share>
</details>
<details>
    <date>2018-05-28</date>
    <currency>USD</currency>
    <shares>49</shares>
    <price_per_share>30</price_per_share>
</details>
<details>
    <date>2018-10-25</date>
    <currency>USD</currency>
    <shares>50</shares>
    <price_per_share>40</price_per_share>
</details>
<details>
    <date>2018-05-02</date>
    <currency>USD</currency>
    <shares>51</shares>
    <price_per_share>60</price_per_share>
</details>

Can't use "map" as I am restricted to use xquery 1.0.

Upvotes: 0

Views: 108

Answers (1)

dirkk
dirkk

Reputation: 6218

The steps you describe sound good, except for the second one. There is no need to append anything as you don't actually want to modify the nodes. In fact, you can't do this with XQuery alone (XQuery Update is another specification which is intended for this). Also, there is no need to use a map here. Your logic sounds like you know procedural programming, but the concept of a functional programming language (XQuery being one) is quite different, so you might want to familiarize yourself with it.

Regarding you question: You first calculate the difference between the date and todays date. As you said it shouldn't matter whether this is in the past or the future, so we have to calculate the absolute value of the duration. To do this we first divide by seconds and thus get the number of seconds for this duration. As we now have a number we can get the absolute value in $abs-diff. We then order by this value, get the first element of the sequence and return price_per_share

let $today := fn:current-date()
return (
  for $detail in //details
  let $diff := xs:date($detail/date) - $today
  let $abs-diff := abs($diff div xs:dayTimeDuration('PT1S'))
  order by $abs-diff
  return $detail
)[1]/price_per_share

Upvotes: 1

Related Questions