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;
darkpomixAsked:
Who is Participating?
 
David ToddSenior DBACommented:
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
0
 
David ToddSenior DBACommented:
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
0
 
LowfatspreadCommented:
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
0
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.