i convert this sentence to sql server but put me an error , like, bad interpretation of CAST, so if anyone can help me with this i will very glad...

PARAMETERS pFromDate DateTime, pToDate DateTime;

SELECT CRMOrderItems.OrderId, CCur(Sum(Round([BasePrice]*[Quantity],2))) AS BasePriceTotal, CCur(Sum(IIf([Taxes]<>0,Round(CRMOrderItems.SubTotal-[Discount],2),0))) AS Taxed, CCur(Sum(IIf([Taxes]=0,Round(CRMOrderItems.SubTotal-[Discount],2),0))) AS NoTaxed, CCur(Sum(CRMOrderItems.SubTotal)) AS SubTotal, CCur(Sum([Discount])) AS DiscountTotal, CCur(Sum(CRMOrderItems.SubTotal-[Discount])) AS SubTotalBeforeTaxes, CCur(Sum([Taxes])) AS TaxesTotal, CCur(Sum(CRMOrderItems.Total)) AS Total, CCur(Sum([Cost])) AS CostTotal, CCur(Sum([RepositionValue])) AS RepositionValueTotal, Count(CRMOrderItems.OrderItemId) AS ItemsCount, Round(Sum(IIf([ServiceOrderId]<>0,0,[Quantity])),3) AS GoodsCount, Sum(IIf([ServiceOrderId]<>0,[Quantity],0)) AS ServicesCount

FROM CRMOrderItems INNER JOIN CRMOrders ON CRMOrderItems.OrderId = CRMOrders.OrderId

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

GROUP BY CRMOrderItems.OrderId

ORDER BY CRMOrderItems.OrderId;

PARAMETERS pFromDate DateTime, pToDate DateTime;

SELECT CRMOrderItems.OrderId, CCur(Sum(Round([BasePrice]

FROM CRMOrderItems INNER JOIN CRMOrders ON CRMOrderItems.OrderId = CRMOrders.OrderId

WHERE (((CRMOrders.ClosingDate)>

GROUP BY CRMOrderItems.OrderId

ORDER BY CRMOrderItems.OrderId;

try

set @pFromDate = '2006-04-01'

set @pToDate = '2006-04-30'

Instead of adding one to @pToDate, its better practice to use dateadd to add one day. Also check out the action of between - I'm pretty sure that it is like this

where a between b and c

b <= a < c

Regards

David

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

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