June 22, 2009 at 6:12 pm
I am using the SUM function that is built into sql server.
Here is my query code
SELECT @ExtendedCost = Sum(Cost) FROM dbo.utbQuote
WHERE
utbQuote.ShopperID = @ShopperID
and
utbQuote.ShopperAccepted=1
AND
utbQuote.Paid = 0
GROUP BY utbQuote.ShopperID
The problem is that the Sum is not returning the .00 cents portion of the money.
Actions Reference Name Request Date Cost
View Remove My Sliding Door OX06/21/2009 $6,782.23
View Remove2 06/22/2009 $4,876.97
View RemoveBoysenberry 06/22/2009 $9,742.34
Here what the above data return when i do a SUM on the cost column
SubTotal: $21,402.00
Shipping Cost: Included
Grand Total: $21,402.00
Here is what I should get: $21,401.54
Here is the table
CREATE TABLE [dbo].[utbQuote](
[QuoteID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](30) NOT NULL,
[DoorID] [int] NOT NULL,
[FileID] [int] NOT NULL,
[FinishID] [int] NOT NULL,
[Width] [varchar](10) NOT NULL,
[Height] [varchar](10) NOT NULL,
[GlassTypeID] [tinyint] NOT NULL,
[GlassSizeID] [tinyint] NOT NULL,
[NoteTo] [varchar](1500) NOT NULL,
[NoteFrom] [varchar](1500) NOT NULL,
[Cost] [money] NOT NULL,
[RequestDate] [datetime] NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
[GlassColorID] [tinyint] NOT NULL,
[ShipToZipcode] [int] NOT NULL,
[Pending] [bit] NOT NULL,
[ShopperAccepted] [bit] NOT NULL,
[EstimatorAccepted] [bit] NOT NULL,
[ShopperID] [uniqueidentifier] NOT NULL,
[Quantity] [tinyint] NOT NULL,
[ShopperReview] [bit] NOT NULL,
[Paid] [bit] NOT NULL,
CONSTRAINT [PK_utbQuote_QuoteID] PRIMARY KEY CLUSTERED
(
[QuoteID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Dam again!
June 22, 2009 at 8:17 pm
Whats @ExtendedCost declared as?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 23, 2009 at 7:26 am
MONEY sql datatype
decimal .net c# datatype
Dam again!
June 23, 2009 at 2:20 pm
It's not happening on my server.
Try executing this script and show us what the results are:
CREATE TABLE #utbQuote(
[Name] [varchar](30) NOT NULL,
[RequestDate] [datetime] NOT NULL,
[Cost] [money] NOT NULL
) ON [PRIMARY]
go
set nocount ON
set numeric_roundabort ON
exec sp_helplanguage @@language
go
--Actions Reference Name Request Date Cost
Insert into #utbQuote select 'My Sliding Door OX', '06/21/2009', 6782.23
Insert into #utbQuote select '2', '06/22/2009', 4876.97
Insert into #utbQuote select 'Boysenberry', '06/22/2009', 9742.34
go
declare @ExtendedCost as money
SELECT @ExtendedCost = Sum(Cost) FROM #utbQuote
Select @ExtendedCost
go
drop table #utbQuote
go
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply