(avg(i.BuyersPrice/i.Quantity)
-avg(q.Cost)*1.175
-avg(i.BuyersPrice/i.Quantity)*0.15
+ CASE mediatype
WHEN 1 then 1.1
when 2 then 1
...
END
AS AvgMargin
FROM SomeTables
--- James Smith <[EMAIL PROTECTED]> wrote:
> OK, this time I really am stuck. I have removed
> quite a lot from this query
> to make it easier to read, normally selects more
> fields and has WHERE, GROUP
> BY and ORDER BY clauses.
>
> <cfquery name="BestSellers" datasource="Intranet"
> maxrows="100">
> SELECT d.ItemID, d.Title, d.MediaType,
> (avg(i.BuyersPrice/i.Quantity)
> -avg(q.Cost)*1.175
> -avg(i.BuyersPrice/i.Quantity)*0.15
>
>
+#listgetat("1.1,1,1.74,1.83,1.63,2.33,4.51,4.51,3.57",MediaType)#)
> AS
> AvgMargin
> FROM SomeTables
> </cfquery>
>
> The query above needs to know the media type to
> calculate the margin as
> postage costs vary depending on this. The MediaType
> is a number from 1-9 so
> it made sense to use
>
listgetat("1.1,1,1.74,1.83,1.63,2.33,4.51,4.51,3.57",MediaType)
> to get the
> relevant costing however the MediaType variable is
> not defined as the query
> is not finished.
>
> Is there anyway to reference this field in this way?
>
> --
> James Smith
> [EMAIL PROTECTED]
>
>
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
