| Till Westmann | 4c4ca1f | 2013-07-26 17:12:02 -0700 | [diff] [blame] | 1 | use dataverse tpch; |
| 2 | |
| 3 | for $l in dataset('LineItem') |
| 4 | for $o in dataset('Orders') |
| 5 | where $o.o_orderkey = $l.l_orderkey |
| 6 | and $l.l_commitdate < $l.l_receiptdate |
| 7 | and $l.l_shipdate < $l.l_commitdate |
| 8 | and $l.l_receiptdate >= '1994-01-01' |
| 9 | and $l.l_receiptdate < '1995-01-01' |
| 10 | and ($l.l_shipmode = 'MAIL' or $l.l_shipmode = 'SHIP') |
| 11 | group by $l_shipmode := $l.l_shipmode with $o |
| 12 | order by $l_shipmode |
| 13 | return { |
| 14 | "l_shipmode": $l_shipmode, |
| 15 | "high_line_count": sum( |
| 16 | for $i in $o |
| 17 | return |
| 18 | switch-case($i.o_orderpriority ='1-URGENT' or $i.o_orderpriority ='2-HIGH', |
| 19 | true, 1, false, 0) |
| 20 | ), |
| 21 | "low_line_count": sum( |
| 22 | for $i in $o |
| 23 | return switch-case($i.o_orderpriority ='1-URGENT' or $i.o_orderpriority ='2-HIGH', |
| 24 | true, 0, false, 1) |
| 25 | ) |
| 26 | } |
| 27 | |