Create 2 sets of results using old school foxpro or dbase III dbf files
-
I have this report from a customer that gives away product samples, and uses 0.00 as the price. So I'm suppose to calculate the cost and the price, but there always different during the year. Anyways; when I calculate this one item, in which they sold 100, but 90 of them were free, the numbers skew and don't make sense. So I figured perhaps I'll create 2 result sets, 1 for no free items and 1 for all free items. What would you do?, 2 select statements and the reader will return 2 result sets, I'm not sure how to proceed here on this one.
" SELECT " & _
" v.FITEMNO " & _
", SUM(v.FSHIPQTY) " & _
", SUM(v.FCOST) / SUM(v.FSHIPQTY) " & _
", SUM(v.FPRICE) / SUM(v.FSHIPQTY) " & _
", (SELECT FDESCRIPT FROM ICITM01.dbf WHERE FITEMNO = v.FITEMNO) AS FREALDESC " & _
", SUM(v.FSHIPQTY * v.FPRICE - v.FSHIPQTY * v.FCOST)" & _
" FROM ARTRS01H.dbf v " & _
" WHERE " & _
" v.FSHIPDATE >= @startDate AND v.FSHIPDATE <= @stopDate " & _
" AND " & _
" FITEMNO = @FITEMNO " & _
" AND NOT " & _
" FPRICE = 0.00 " -
I have this report from a customer that gives away product samples, and uses 0.00 as the price. So I'm suppose to calculate the cost and the price, but there always different during the year. Anyways; when I calculate this one item, in which they sold 100, but 90 of them were free, the numbers skew and don't make sense. So I figured perhaps I'll create 2 result sets, 1 for no free items and 1 for all free items. What would you do?, 2 select statements and the reader will return 2 result sets, I'm not sure how to proceed here on this one.
" SELECT " & _
" v.FITEMNO " & _
", SUM(v.FSHIPQTY) " & _
", SUM(v.FCOST) / SUM(v.FSHIPQTY) " & _
", SUM(v.FPRICE) / SUM(v.FSHIPQTY) " & _
", (SELECT FDESCRIPT FROM ICITM01.dbf WHERE FITEMNO = v.FITEMNO) AS FREALDESC " & _
", SUM(v.FSHIPQTY * v.FPRICE - v.FSHIPQTY * v.FCOST)" & _
" FROM ARTRS01H.dbf v " & _
" WHERE " & _
" v.FSHIPDATE >= @startDate AND v.FSHIPDATE <= @stopDate " & _
" AND " & _
" FITEMNO = @FITEMNO " & _
" AND NOT " & _
" FPRICE = 0.00 "I think you are on the right track. Create 2 queries but use a union statement which will combine the result sets into one. Good luck. :thumbsup: