(:
   file:    freq_inst_by_numYr_v2.xq
   date:    23-June-2010
   author:  Gary Lewis
   purpose: Frequency distribution of number of institutions by the number of years that the
            institutions appear in the 25 IPEDS directory data files 1980 - 2008.
   notes:   1. This program replaces freq_inst_by_numYr.xq. Done when the 25 distinct_unitids_by_year.xml files
               (1 file per year in the time series) were eliminated.
   use:     Run program from path location where it resides.
   example: zorba -f -q freq_inst_by_numYr_v2.xq -o freq_inst_by_numYr_v2.xml -z indent=yes
:)

<distribution>
{
let $tmp1 :=
   ( 
   for $i in fn:doc("/home/gml/rwebdb/projects/ipeds/xml/metadata/ipedsData.xml")/data/file[@type = "xml"]
   for $j in fn:doc("/home/gml/rwebdb/projects/ipeds/xml/metadata/ipedsFiles.xml")/ipedsFiles/file[@dir_ind = "1"]
   let $name := $i/@name,
       $path := $i/@path,
       $xmlFile := concat($path, $name),
       $name_wo_ext := substring-before($name, "."),
       $csvName := $j/@csvName,
       $year := $j/@year
   where 
       $csvName = $name_wo_ext                         (: join between the two xml metadata files :)
   order by $year
   return <file year="{$year}" xmlFile="{$xmlFile}" />
   )
return 
let $tmp2 :=
    for $k in $tmp1
    let $year := $k/@year,
        $xmlFile := $k/@xmlFile
    for $unitid in distinct-values(fn:doc($xmlFile)/data/record/@unitid)
    return <inst year="{$year}" unitid="{$unitid}" />
return 
let $tmp3 :=
   for $l in $tmp2/@unitid
   let $cnt := 1
   group by $l
   return <inst unitid="{$l}" count="{sum($cnt)}" />
return 
let $tmp4 :=
    for $m in $tmp3/@count
    let $freq := 1
    group by $m
    order by xs:integer($m) ascending
    return <freq numYrs="{$m}" numInst="{sum($freq)}" />
return $tmp4
}
</distribution>