?
Solved

access sentence to SQL SERVER

Posted on 2006-04-17
3
Medium Priority
?
732 Views
Last Modified: 2011-09-20
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;
0
Comment
Question by:darkpomix
  • 2
3 Comments
 
LVL 35

Accepted Solution

by:
David Todd earned 1500 total points
ID: 16473906
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
 
LVL 35

Expert Comment

by:David Todd
ID: 16473917
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 16474950
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question