SELECT #_DateField# as OrderDate, #_columnNames# FROM qcf_orderDetail SELECT DistributorName, DistributorCode, ManufacturerName, ManufacturerCode, ItemCode, Description, orderdate, orderdate FDOW, SUM(Quantity) Quantity, SUM(NetWeight) NetWeight, SUM(LineNetAmount) LineNetAmount FROM qcf_orderDetail GROUP BY #_GroupBy#, ItemCode, Description, orderdate SELECT DistributorName, DistributorCode, ManufacturerName, ManufacturerCode, ItemCode, Description, FDOW, 0.0 w1Qty, 0.0 w1Nwt, 0.0 w1Amt, 0.0 w2Qty, 0.0 w2Nwt, 0.0 w2Amt, 0.0 w3Qty, 0.0 w3Nwt, 0.0 w3Amt, 0.0 w4Qty, 0.0 w4Nwt, 0.0 w4Amt, 0.0 w5Qty, 0.0 w5Nwt, 0.0 w5Amt, 0.0 w6Qty, 0.0 w6Nwt, 0.0 w6Amt, 0.0 w7Qty, 0.0 w7Nwt, 0.0 w7Amt, 0.0 w8Qty, 0.0 w8Nwt, 0.0 w8Amt, 0.0 w9Qty, 0.0 w9Nwt, 0.0 w9Amt, 0.0 w10Qty, 0.0 w10Nwt, 0.0 w10Amt, 0.0 w11Qty, 0.0 w11Nwt, 0.0 w11Amt, 0.0 w12Qty, 0.0 w12Nwt, 0.0 w12Amt, 0.0 w13Qty, 0.0 w13Nwt, 0.0 w13Amt FROM qmd_OrdersByItem WHERE 1=2 UNION SELECT DistributorName, DistributorCode, ManufacturerName, ManufacturerCode, ItemCode, Description, FDOW, SUM(Quantity) w1Qty, SUM(NetWeight) w1Nwt, SUM(LineNetAmount) w1Amt, 0.0 w2Qty, 0.0 w2Nwt, 0.0 w2Amt, 0.0 w3Qty, 0.0 w3Nwt, 0.0 w3Amt, 0.0 w4Qty, 0.0 w4Nwt, 0.0 w4Amt, 0.0 w5Qty, 0.0 w5Nwt, 0.0 w5Amt, 0.0 w6Qty, 0.0 w6Nwt, 0.0 w6Amt, 0.0 w7Qty, 0.0 w7Nwt, 0.0 w7Amt, 0.0 w8Qty, 0.0 w8Nwt, 0.0 w8Amt, 0.0 w9Qty, 0.0 w9Nwt, 0.0 w9Amt, 0.0 w10Qty, 0.0 w10Nwt, 0.0 w10Amt, 0.0 w11Qty, 0.0 w11Nwt, 0.0 w11Amt, 0.0 w12Qty, 0.0 w12Nwt, 0.0 w12Amt, 0.0 w13Qty, 0.0 w13Nwt, 0.0 w13Amt FROM qmd_OrdersByItem WHERE FDOW = #ListGetAt(_WeekList,1)# GROUP BY #_GroupBy#, ItemCode, Description, FDOW UNION SELECT DistributorName, DistributorCode, ManufacturerName, ManufacturerCode, ItemCode, Description, FDOW, 0.0 w1Qty, 0.0 w1Nwt, 0.0 w1Amt, SUM(Quantity) w2Qty, SUM(NetWeight) w2Nwt, SUM(LineNetAmount) w2Amt, 0.0 w3Qty, 0.0 w3Nwt, 0.0 w3Amt, 0.0 w4Qty, 0.0 w4Nwt, 0.0 w4Amt, 0.0 w5Qty, 0.0 w5Nwt, 0.0 w5Amt, 0.0 w6Qty, 0.0 w6Nwt, 0.0 w6Amt, 0.0 w7Qty, 0.0 w7Nwt, 0.0 w7Amt, 0.0 w8Qty, 0.0 w8Nwt, 0.0 w8Amt, 0.0 w9Qty, 0.0 w9Nwt, 0.0 w9Amt, 0.0 w10Qty, 0.0 w10Nwt, 0.0 w10Amt, 0.0 w11Qty, 0.0 w11Nwt, 0.0 w11Amt, 0.0 w12Qty, 0.0 w12Nwt, 0.0 w12Amt, 0.0 w13Qty, 0.0 w13Nwt, 0.0 w13Amt FROM qmd_OrdersByItem WHERE FDOW = #ListGetAt(_WeekList,2)# GROUP BY #_GroupBy#, ItemCode, Description, FDOW UNION SELECT DistributorName, DistributorCode, ManufacturerName, ManufacturerCode, ItemCode, Description, FDOW, 0.0 w1Qty, 0.0 w1Nwt, 0.0 w1Amt, 0.0 w2Qty, 0.0 w2Nwt, 0.0 w2Amt, SUM(Quantity) w3Qty, SUM(NetWeight) w3Nwt, SUM(LineNetAmount) w3Amt, 0.0 w4Qty, 0.0 w4Nwt, 0.0 w4Amt, 0.0 w5Qty, 0.0 w5Nwt, 0.0 w5Amt, 0.0 w6Qty, 0.0 w6Nwt, 0.0 w6Amt, 0.0 w7Qty, 0.0 w7Nwt, 0.0 w7Amt, 0.0 w8Qty, 0.0 w8Nwt, 0.0 w8Amt, 0.0 w9Qty, 0.0 w9Nwt, 0.0 w9Amt, 0.0 w10Qty, 0.0 w10Nwt, 0.0 w10Amt, 0.0 w11Qty, 0.0 w11Nwt, 0.0 w11Amt, 0.0 w12Qty, 0.0 w12Nwt, 0.0 w12Amt, 0.0 w13Qty, 0.0 w13Nwt, 0.0 w13Amt FROM qmd_OrdersByItem WHERE FDOW = #ListGetAt(_WeekList,3)# GROUP BY #_GroupBy#, ItemCode, Description, FDOW UNION SELECT DistributorName, DistributorCode, ManufacturerName, ManufacturerCode, ItemCode, Description, FDOW, 0.0 w1Qty, 0.0 w1Nwt, 0.0 w1Amt, 0.0 w2Qty, 0.0 w2Nwt, 0.0 w2Amt, 0.0 w3Qty, 0.0 w3Nwt, 0.0 w3Amt, SUM(Quantity) w4Qty, SUM(NetWeight) w4Nwt, SUM(LineNetAmount) w4Amt, 0.0 w5Qty, 0.0 w5Nwt, 0.0 w5Amt, 0.0 w6Qty, 0.0 w6Nwt, 0.0 w6Amt, 0.0 w7Qty, 0.0 w7Nwt, 0.0 w7Amt, 0.0 w8Qty, 0.0 w8Nwt, 0.0 w8Amt, 0.0 w9Qty, 0.0 w9Nwt, 0.0 w9Amt, 0.0 w10Qty, 0.0 w10Nwt, 0.0 w10Amt, 0.0 w11Qty, 0.0 w11Nwt, 0.0 w11Amt, 0.0 w12Qty, 0.0 w12Nwt, 0.0 w12Amt, 0.0 w13Qty, 0.0 w13Nwt, 0.0 w13Amt FROM qmd_OrdersByItem WHERE FDOW = #ListGetAt(_WeekList,4)# GROUP BY #_GroupBy#, ItemCode, Description, FDOW UNION SELECT DistributorName, DistributorCode, ManufacturerName, ManufacturerCode, ItemCode, Description, FDOW, 0.0 w1Qty, 0.0 w1Nwt, 0.0 w1Amt, 0.0 w2Qty, 0.0 w2Nwt, 0.0 w2Amt, 0.0 w3Qty, 0.0 w3Nwt, 0.0 w3Amt, 0.0 w4Qty, 0.0 w4Nwt, 0.0 w4Amt, SUM(Quantity) w5Qty, SUM(NetWeight) w5Nwt, SUM(LineNetAmount) w5Amt, 0.0 w6Qty, 0.0 w6Nwt, 0.0 w6Amt, 0.0 w7Qty, 0.0 w7Nwt, 0.0 w7Amt, 0.0 w8Qty, 0.0 w8Nwt, 0.0 w8Amt, 0.0 w9Qty, 0.0 w9Nwt, 0.0 w9Amt, 0.0 w10Qty, 0.0 w10Nwt, 0.0 w10Amt, 0.0 w11Qty, 0.0 w11Nwt, 0.0 w11Amt, 0.0 w12Qty, 0.0 w12Nwt, 0.0 w12Amt, 0.0 w13Qty, 0.0 w13Nwt, 0.0 w13Amt FROM qmd_OrdersByItem WHERE FDOW = #ListGetAt(_WeekList,5)# GROUP BY #_GroupBy#, ItemCode, Description, FDOW UNION SELECT DistributorName, DistributorCode, ManufacturerName, ManufacturerCode, ItemCode, Description, FDOW, 0.0 w1Qty, 0.0 w1Nwt, 0.0 w1Amt, 0.0 w2Qty, 0.0 w2Nwt, 0.0 w2Amt, 0.0 w3Qty, 0.0 w3Nwt, 0.0 w3Amt, 0.0 w4Qty, 0.0 w4Nwt, 0.0 w4Amt, 0.0 w5Qty, 0.0 w5Nwt, 0.0 w5Amt, SUM(Quantity) w6Qty, SUM(NetWeight) w6Nwt, SUM(LineNetAmount) w6Amt, 0.0 w7Qty, 0.0 w7Nwt, 0.0 w7Amt, 0.0 w8Qty, 0.0 w8Nwt, 0.0 w8Amt, 0.0 w9Qty, 0.0 w9Nwt, 0.0 w9Amt, 0.0 w10Qty, 0.0 w10Nwt, 0.0 w10Amt, 0.0 w11Qty, 0.0 w11Nwt, 0.0 w11Amt, 0.0 w12Qty, 0.0 w12Nwt, 0.0 w12Amt, 0.0 w13Qty, 0.0 w13Nwt, 0.0 w13Amt FROM qmd_OrdersByItem WHERE FDOW = #ListGetAt(_WeekList,6)# GROUP BY #_GroupBy#, ItemCode, Description, FDOW UNION SELECT DistributorName, DistributorCode, ManufacturerName, ManufacturerCode, ItemCode, Description, FDOW, 0.0 w1Qty, 0.0 w1Nwt, 0.0 w1Amt, 0.0 w2Qty, 0.0 w2Nwt, 0.0 w2Amt, 0.0 w3Qty, 0.0 w3Nwt, 0.0 w3Amt, 0.0 w4Qty, 0.0 w4Nwt, 0.0 w4Amt, 0.0 w5Qty, 0.0 w5Nwt, 0.0 w5Amt, 0.0 w6Qty, 0.0 w6Nwt, 0.0 w6Amt, SUM(Quantity) w7Qty, SUM(NetWeight) w7Nwt, SUM(LineNetAmount) w7Amt, 0.0 w8Qty, 0.0 w8Nwt, 0.0 w8Amt, 0.0 w9Qty, 0.0 w9Nwt, 0.0 w9Amt, 0.0 w10Qty, 0.0 w10Nwt, 0.0 w10Amt, 0.0 w11Qty, 0.0 w11Nwt, 0.0 w11Amt, 0.0 w12Qty, 0.0 w12Nwt, 0.0 w12Amt, 0.0 w13Qty, 0.0 w13Nwt, 0.0 w13Amt FROM qmd_OrdersByItem WHERE FDOW = #ListGetAt(_WeekList,7)# GROUP BY #_GroupBy#, ItemCode, Description, FDOW UNION SELECT DistributorName, DistributorCode, ManufacturerName, ManufacturerCode, ItemCode, Description, FDOW, 0.0 w1Qty, 0.0 w1Nwt, 0.0 w1Amt, 0.0 w2Qty, 0.0 w2Nwt, 0.0 w2Amt, 0.0 w3Qty, 0.0 w3Nwt, 0.0 w3Amt, 0.0 w4Qty, 0.0 w4Nwt, 0.0 w4Amt, 0.0 w5Qty, 0.0 w5Nwt, 0.0 w5Amt, 0.0 w6Qty, 0.0 w6Nwt, 0.0 w6Amt, 0.0 w7Qty, 0.0 w7Nwt, 0.0 w7Amt, SUM(Quantity) w8Qty, SUM(NetWeight) w8Nwt, SUM(LineNetAmount) w8Amt, 0.0 w9Qty, 0.0 w9Nwt, 0.0 w9Amt, 0.0 w10Qty, 0.0 w10Nwt, 0.0 w10Amt, 0.0 w11Qty, 0.0 w11Nwt, 0.0 w11Amt, 0.0 w12Qty, 0.0 w12Nwt, 0.0 w12Amt, 0.0 w13Qty, 0.0 w13Nwt, 0.0 w13Amt FROM qmd_OrdersByItem WHERE FDOW = #ListGetAt(_WeekList,8)# GROUP BY #_GroupBy#, ItemCode, Description, FDOW UNION SELECT DistributorName, DistributorCode, ManufacturerName, ManufacturerCode, ItemCode, Description, FDOW, 0.0 w1Qty, 0.0 w1Nwt, 0.0 w1Amt, 0.0 w2Qty, 0.0 w2Nwt, 0.0 w2Amt, 0.0 w3Qty, 0.0 w3Nwt, 0.0 w3Amt, 0.0 w4Qty, 0.0 w4Nwt, 0.0 w4Amt, 0.0 w5Qty, 0.0 w5Nwt, 0.0 w5Amt, 0.0 w6Qty, 0.0 w6Nwt, 0.0 w6Amt, 0.0 w7Qty, 0.0 w7Nwt, 0.0 w7Amt, 0.0 w8Qty, 0.0 w8Nwt, 0.0 w8Amt, SUM(Quantity) w9Qty, SUM(NetWeight) w9Nwt, SUM(LineNetAmount) w9Amt, 0.0 w10Qty, 0.0 w10Nwt, 0.0 w10Amt, 0.0 w11Qty, 0.0 w11Nwt, 0.0 w11Amt, 0.0 w12Qty, 0.0 w12Nwt, 0.0 w12Amt, 0.0 w13Qty, 0.0 w13Nwt, 0.0 w13Amt FROM qmd_OrdersByItem WHERE FDOW = #ListGetAt(_WeekList,9)# GROUP BY #_GroupBy#, ItemCode, Description, FDOW UNION SELECT DistributorName, DistributorCode, ManufacturerName, ManufacturerCode, ItemCode, Description, FDOW, 0.0 w1Qty, 0.0 w1Nwt, 0.0 w1Amt, 0.0 w2Qty, 0.0 w2Nwt, 0.0 w2Amt, 0.0 w3Qty, 0.0 w3Nwt, 0.0 w3Amt, 0.0 w4Qty, 0.0 w4Nwt, 0.0 w4Amt, 0.0 w5Qty, 0.0 w5Nwt, 0.0 w5Amt, 0.0 w6Qty, 0.0 w6Nwt, 0.0 w6Amt, 0.0 w7Qty, 0.0 w7Nwt, 0.0 w7Amt, 0.0 w8Qty, 0.0 w8Nwt, 0.0 w8Amt, 0.0 w9Qty, 0.0 w9Nwt, 0.0 w9Amt, SUM(Quantity) w10Qty, SUM(NetWeight) w10Nwt, SUM(LineNetAmount) w10Amt, 0.0 w11Qty, 0.0 w11Nwt, 0.0 w11Amt, 0.0 w12Qty, 0.0 w12Nwt, 0.0 w12Amt, 0.0 w13Qty, 0.0 w13Nwt, 0.0 w13Amt FROM qmd_OrdersByItem WHERE FDOW = #ListGetAt(_WeekList,10)# GROUP BY #_GroupBy#, ItemCode, Description, FDOW UNION SELECT DistributorName, DistributorCode, ManufacturerName, ManufacturerCode, ItemCode, Description, FDOW, 0.0 w1Qty, 0.0 w1Nwt, 0.0 w1Amt, 0.0 w2Qty, 0.0 w2Nwt, 0.0 w2Amt, 0.0 w3Qty, 0.0 w3Nwt, 0.0 w3Amt, 0.0 w4Qty, 0.0 w4Nwt, 0.0 w4Amt, 0.0 w5Qty, 0.0 w5Nwt, 0.0 w5Amt, 0.0 w6Qty, 0.0 w6Nwt, 0.0 w6Amt, 0.0 w7Qty, 0.0 w7Nwt, 0.0 w7Amt, 0.0 w8Qty, 0.0 w8Nwt, 0.0 w8Amt, 0.0 w9Qty, 0.0 w9Nwt, 0.0 w9Amt, 0.0 w10Qty, 0.0 w10Nwt, 0.0 w10Amt, SUM(Quantity) w11Qty, SUM(NetWeight) w11Nwt, SUM(LineNetAmount) w11Amt, 0.0 w12Qty, 0.0 w12Nwt, 0.0 w12Amt, 0.0 w13Qty, 0.0 w13Nwt, 0.0 w13Amt FROM qmd_OrdersByItem WHERE FDOW = #ListGetAt(_WeekList,11)# GROUP BY #_GroupBy#, ItemCode, Description, FDOW UNION SELECT DistributorName, DistributorCode, ManufacturerName, ManufacturerCode, ItemCode, Description, FDOW, 0.0 w1Qty, 0.0 w1Nwt, 0.0 w1Amt, 0.0 w2Qty, 0.0 w2Nwt, 0.0 w2Amt, 0.0 w3Qty, 0.0 w3Nwt, 0.0 w3Amt, 0.0 w4Qty, 0.0 w4Nwt, 0.0 w4Amt, 0.0 w5Qty, 0.0 w5Nwt, 0.0 w5Amt, 0.0 w6Qty, 0.0 w6Nwt, 0.0 w6Amt, 0.0 w7Qty, 0.0 w7Nwt, 0.0 w7Amt, 0.0 w8Qty, 0.0 w8Nwt, 0.0 w8Amt, 0.0 w9Qty, 0.0 w9Nwt, 0.0 w9Amt, 0.0 w10Qty, 0.0 w10Nwt, 0.0 w10Amt, 0.0 w11Qty, 0.0 w11Nwt, 0.0 w11Amt, SUM(Quantity) w12Qty, SUM(NetWeight) w12Nwt, SUM(LineNetAmount) w12Amt, 0.0 w13Qty, 0.0 w13Nwt, 0.0 w13Amt FROM qmd_OrdersByItem WHERE FDOW = #ListGetAt(_WeekList,12)# GROUP BY #_GroupBy#, ItemCode, Description, FDOW UNION SELECT DistributorName, DistributorCode, ManufacturerName, ManufacturerCode, ItemCode, Description, FDOW, 0.0 w1Qty, 0.0 w1Nwt, 0.0 w1Amt, 0.0 w2Qty, 0.0 w2Nwt, 0.0 w2Amt, 0.0 w3Qty, 0.0 w3Nwt, 0.0 w3Amt, 0.0 w4Qty, 0.0 w4Nwt, 0.0 w4Amt, 0.0 w5Qty, 0.0 w5Nwt, 0.0 w5Amt, 0.0 w6Qty, 0.0 w6Nwt, 0.0 w6Amt, 0.0 w7Qty, 0.0 w7Nwt, 0.0 w7Amt, 0.0 w8Qty, 0.0 w8Nwt, 0.0 w8Amt, 0.0 w9Qty, 0.0 w9Nwt, 0.0 w9Amt, 0.0 w10Qty, 0.0 w10Nwt, 0.0 w10Amt, 0.0 w11Qty, 0.0 w11Nwt, 0.0 w11Amt, 0.0 w12Qty, 0.0 w12Nwt, 0.0 w12Amt, SUM(Quantity) w13Qty, SUM(NetWeight) w13Nwt, SUM(LineNetAmount) w13Amt FROM qmd_OrdersByItem WHERE FDOW = #ListGetAt(_WeekList,13)# GROUP BY #_GroupBy#, ItemCode, Description, FDOW SELECT DistributorName, DistributorCode, ManufacturerName, ManufacturerCode, ItemCode, Description, '' DistributorItemCode, SUM(w1Qty) w1Qty, SUM(w1Nwt) w1Nwt, SUM(w1Amt) w1Amt, SUM(w2Qty) w2Qty, SUM(w2Nwt) w2Nwt, SUM(w2Amt) w2Amt, SUM(w3Qty) w3Qty, SUM(w3Nwt) w3Nwt, SUM(w3Amt) w3Amt, SUM(w4Qty) w4Qty, SUM(w4Nwt) w4Nwt, SUM(w4Amt) w4Amt, SUM(w5Qty) w5Qty, SUM(w5Nwt) w5Nwt, SUM(w5Amt) w5Amt, SUM(w6Qty) w6Qty, SUM(w6Nwt) w6Nwt, SUM(w6Amt) w6Amt, SUM(w7Qty) w7Qty, SUM(w7Nwt) w7Nwt, SUM(w7Amt) w7Amt, SUM(w8Qty) w8Qty, SUM(w8Nwt) w8Nwt, SUM(w8Amt) w8Amt, SUM(w9Qty) w9Qty, SUM(w9Nwt) w9Nwt, SUM(w9Amt) w9Amt, SUM(w10Qty) w10Qty, SUM(w10Nwt) w10Nwt, SUM(w10Amt) w10Amt, SUM(w11Qty) w11Qty, SUM(w11Nwt) w11Nwt, SUM(w11Amt) w11Amt, SUM(w12Qty) w12Qty, SUM(w12Nwt) w12Nwt, SUM(w12Amt) w12Amt, SUM(w13Qty) w13Qty, SUM(w13Nwt) w13Nwt, SUM(w13Amt) w13Amt FROM qmd_OrdersCrossTab GROUP BY #_GroupBy#, ItemCode, Description ORDER BY #_GroupBy#, ItemCode, Description