Hi,

Try something like this:

declare @pFromDate datetime

declare @pToDate datetime

SELECT

CRMOrderItems.OrderId,

Sum( Round( BasePrice * Quantity, 2 )) AS BasePriceTotal,

-- CCur(Sum(IIf([Taxes]<>0,Round(CRMOrderItems.SubTotal-[Discount],2),0))) AS Taxed,

sum(

case Taxes

when 0 then 0

else Round( CRMOrderItems.SubTotal - Discount, 2 )

end

) as Taxed,

-- CCur(Sum(IIf([Taxes]=0,Round(CRMOrderItems.SubTotal-[Discount],2),0))) AS NoTaxed,

sum (

cases Taxes

when 0 then Round( CRMOrderItems.SubTotal - Discount, 2 )

else 0

end

) as NoTaxed,

Sum( CRMOrderItems.SubTotal ) AS SubTotal,

Sum( Discount ) AS DiscountTotal,

Sum( CRMOrderItems.SubTotal - Discount ) AS SubTotalBeforeTaxes,

Sum( Taxes ) AS TaxesTotal,

Sum( CRMOrderItems.Total ) AS Total,

Sum( Cost ) AS CostTotal,

Sum( RepositionValue ) AS RepositionValueTotal,

Count( CRMOrderItems.OrderItemId ) AS ItemsCount,

-- Round(Sum(IIf([ServiceOrderId]<>0,0,[Quantity])),3) AS GoodsCount,

round( sum(

case ServiceOrderID

when 0 then Quantity

else 0

end

), 3 ) as GoodsCount,

-- Sum(IIf([ServiceOrderId]<>0,[Quantity],0)) AS ServicesCount

sum(

case ServiceOrderID

when 0 then 0

else Quantity

end

) as SericesCount

FROM CRMOrderItems

INNER JOIN CRMOrders

ON CRMOrderItems.OrderId = CRMOrders.OrderId

--WHERE (((CRMOrders.ClosingDate)>=[pFromDate] And (CRMOrders.ClosingDate)<=([pToDate]+1)))

where CRMOrders.CloseingDate between @pFromDate and @pToDate

GROUP BY CRMOrderItems.OrderId

ORDER BY CRMOrderItems.OrderId

Note that I've changed things a little with the case statements

the -- at the begining of the line comments it out - I've left the original line in some places.

I don't see the immediate need for the ccur - convert to currency if memory is correct, so I've deleted them. Easy enough to add back in if required.

HTH

David

Try something like this:

declare @pFromDate datetime

declare @pToDate datetime

SELECT

CRMOrderItems.OrderId,

Sum( Round( BasePrice * Quantity, 2 )) AS BasePriceTotal,

-- CCur(Sum(IIf([Taxes]<>0,Ro

sum(

case Taxes

when 0 then 0

else Round( CRMOrderItems.SubTotal - Discount, 2 )

end

) as Taxed,

-- CCur(Sum(IIf([Taxes]=0,Rou

sum (

cases Taxes

when 0 then Round( CRMOrderItems.SubTotal - Discount, 2 )

else 0

end

) as NoTaxed,

Sum( CRMOrderItems.SubTotal ) AS SubTotal,

Sum( Discount ) AS DiscountTotal,

Sum( CRMOrderItems.SubTotal - Discount ) AS SubTotalBeforeTaxes,

Sum( Taxes ) AS TaxesTotal,

Sum( CRMOrderItems.Total ) AS Total,

Sum( Cost ) AS CostTotal,

Sum( RepositionValue ) AS RepositionValueTotal,

Count( CRMOrderItems.OrderItemId ) AS ItemsCount,

-- Round(Sum(IIf([ServiceOrde

round( sum(

case ServiceOrderID

when 0 then Quantity

else 0

end

), 3 ) as GoodsCount,

-- Sum(IIf([ServiceOrderId]<>

sum(

case ServiceOrderID

when 0 then 0

else Quantity

end

) as SericesCount

FROM CRMOrderItems

INNER JOIN CRMOrders

ON CRMOrderItems.OrderId = CRMOrders.OrderId

--WHERE (((CRMOrders.ClosingDate)>

where CRMOrders.CloseingDate between @pFromDate and @pToDate

GROUP BY CRMOrderItems.OrderId

ORDER BY CRMOrderItems.OrderId

Note that I've changed things a little with the case statements

the -- at the begining of the line comments it out - I've left the original line in some places.

I don't see the immediate need for the ccur - convert to currency if memory is correct, so I've deleted them. Easy enough to add back in if required.

HTH

David