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,Ro und(CRMOrd erItems.Su bTotal-[Di scount],2) ,0))) AS Taxed, CCur(Sum(IIf([Taxes]=0,Rou nd(CRMOrde rItems.Sub Total-[Dis count],2), 0))) AS NoTaxed, CCur(Sum(CRMOrderItems.Sub Total)) AS SubTotal, CCur(Sum([Discount])) AS DiscountTotal, CCur(Sum(CRMOrderItems.Sub Total-[Dis count])) AS SubTotalBeforeTaxes, CCur(Sum([Taxes])) AS TaxesTotal, CCur(Sum(CRMOrderItems.Tot al)) AS Total, CCur(Sum([Cost])) AS CostTotal, CCur(Sum([RepositionValue] )) AS RepositionValueTotal, Count(CRMOrderItems.OrderI temId) AS ItemsCount, Round(Sum(IIf([ServiceOrde rId]<>0,0, [Quantity] )),3) AS GoodsCount, Sum(IIf([ServiceOrderId]<> 0,[Quantit y],0)) AS ServicesCount
FROM CRMOrderItems INNER JOIN CRMOrders ON CRMOrderItems.OrderId = CRMOrders.OrderId
WHERE (((CRMOrders.ClosingDate)> =[pFromDat e] 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;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
'2006-04-30 23:59:49.997'
where a between b and c
b <= a <= c
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