(: ABC Sales Analysis :)
(: Qty-wise :)

(:COL1 - STOCKITEMNAME:)
(:COL2 - ACTUALQTY:)
(:COL3 - AMOUNT:)


(:XQuery begins here:)
<output>
<createtable>CREATE TABLE [sheet1$] ([STOCKITEM] char(255),[ACTUALQTY] FLOAT,[AMOUNT] FLOAT)</createtable>
{
for 
$stockitem in distinct-values(ENVELOPE/BODY/IMPORTDATA/REQUESTDATA/TALLYMESSAGE[@BASEVCHTYPE="Sales"]/VOUCHER/ALLINVENTORYENTRIES.LIST/STOCKITEMNAME)
let
$salesinventoryentries := (ENVELOPE/BODY/IMPORTDATA/REQUESTDATA/TALLYMESSAGE[@BASEVCHTYPE="Sales"]/VOUCHER/ALLINVENTORYENTRIES.LIST/STOCKITEMNAME[.=$stockitem]/parent::node()),
$salesqty := (round-half-to-even(sum($salesinventoryentries/number(substring-before(normalize-space(data(ACTUALQTY)),' '))),2)),
$salesamt := (round-half-to-even(sum($salesinventoryentries/AMOUNT),0)),
$fieldnames:= ("STOCKITEM","ACTUALQTY","AMOUNT"),
$fieldvalues:=($stockitem,if (fn:exists($salesqty)) then xs:decimal($salesqty) else 0,if (fn:exists($salesamt)) then $salesamt else 0)
order by xs:decimal(sum($salesamt)) descending
return
    sslib:sql-insert('[sheet1$]',$fieldnames,$fieldvalues)
}	
</output>