(:
Snapshot Count: For each department, show the count 
of employees who worked in the department in year 1999.
:)

declare function local:overlap($manager, $tstart,$tend) {

if (xs:date($manager/@tstart) <= xs:date($tstart) and
      xs:date($manager/@tend) >= xs:date($tend))
  then (
     element mgrno {
	attribute tstart{$tstart},
	attribute tend{$tend},
	$manager/text()
     })

else if (xs:date($manager/@tstart) <= xs:date($tstart) and
      (xs:date($manager/@tend)) >= xs:date($tstart) and
      (xs:date($manager/@tend)) <= xs:date($tend))
  then (
     element mgrno {
	attribute tstart{$tstart},
	attribute tend{$manager/@tend},
	$manager/text()
     })
     
 else if (($manager/xs:date(@tend) >= xs:date($tend)) and
      ($manager/xs:date(@tstart) >= xs:date($tstart)) and
      ($manager/xs:date(@tstart) <= xs:date($tend)))
  then (
     element mgrno {
	attribute tstart{$manager/@tstart},
	attribute tend{$tend},
	$manager/text()
     }
  )
  else if (($manager/xs:date(@tstart) >= xs:date($tstart)) and
      ($manager/xs:date(@tend) <= xs:date($tend)))
  then (
    element mgrno {
	attribute tstart{$manager/@tstart},
	attribute tend{$manager/@tend},
	$manager/text()
     }
  )
     else ()
};

declare function local:get_all_emps_at($d) {
for $e in doc("v-emps.xml")/employees/employee/deptno[text()=$d/text()]
where local:overlap($e,"1999-12-31","1999-01-01")
return ($e)
};

declare function local:final() {
	for $r in doc("v-depts.xml")/departments/department
    let $emps := local:get_all_emps_at($r/deptno)
    return (element department{$r/deptno, element noemps{count($emps)}})
  };
  
  let $f := local:final() 
  return element departments{$f}