Link to home
Start Free TrialLog in
Avatar of darkpomix
darkpomix

asked on

access sentence to SQL SERVER

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;
ASKER CERTIFIED SOLUTION
Avatar of David Todd
David Todd
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
PS Note that this doesn't set the from and to date ...

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
Actually its equality with both ends of the bounds... and   2006-04-30 is the start of the day not the end of the day...
'2006-04-30 23:59:49.997'

where a between b and c

b <= a <= c