CODEWITHSUNDEEP
xquery
Daniel Garcia
Daniel Garcia

Reputation: 87

How to compare attributes on the same node xQuery

I got a problem trying to compare and count the attributes of the same node, for example, I need to filter the doctors who operated more than one time in the same day. My example xml is this:

<ops>
  <med_operation pac="p1" doctor="David" date="2017-01-01" />
  <med_operation pac="p2" doctor="David" date="2017-01-01" />
  <med_operation pac="p3" doctor="Ana" date="2017-01-02" />
</ops>

I would have to show only "David" is the only one who has done two operations on the same day. I got this poor query

let $m := distinct-values(
   let $dates := //med_operation/@date
   for $d in $dates
   let $ope := //med_operation[@date=$d]
   let $c := count(//med_operation[@date=$d])
   for $y in (1 to $c)
   where ($ope[$y]/@date) = ($ope[$y+1]/@date) and $c > 1
   return distinct-values($ope/@doctor)
   )
return $m

Thanks in advance and sorry for my English

Upvotes: 0

Views: 1104

Answers (2)

Michael Kay
Michael Kay

Reputation: 163322

In XQuery 3.0, use:

for $o in //med_operation
group by $doc := $o/@doctor, $date := $o/@date
where count($o) gt 1
return $doc

Upvotes: 1

wst
wst

Reputation: 11771

There are multiple ways to solve this problem, but one straightforward solution is to loop over distinct values of doctor names, then loop over distinct values of dates for that doctor, count the ones that match and store them in a sequence, and then finally constrain the result set using where to only to those doctors that have a value in that sequence greater than one.

for $doc in distinct-values(//med_operation/@doctor)
let $date-counts :=
  for $date in distinct-values(//med_operation[@doctor = $doc]/@date)
  return count(//med_operation[@doctor = $doc][@date= $date])
where ($date-counts[. gt 1])
return $doc

Upvotes: 1

Related Questions