Decimal point for column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Decimal point for column

Author

Topic

anuraag205
Yak Posting Veteran

58 Posts

Posted - 2012-08-29 : 05:55:01

How can i set decimal Column for a part of query like this:::please help

Sum(NegotiatedSellPrice+ ( case when IsCancellationProtection = '1'
then ((CancellationProtectionAmt* (SiteToBaseConvRate/SiteToSalesConvRate)))
else 0 end ) ) AS [Total Sale Price]

I get an output like his

Total Sale Price
248.0000000000000000
132.1200002000000000
5412.000000000000000

i need it like
total sales price
204.00
132.12
3421.00

Please Help

Thanks

shaggy
Posting Yak Master

248 Posts

Posted - 2012-08-29 : 08:09:45

select CAST(248.0000000000000000 as decimal(22,2))

select CAST(Sum(NegotiatedSellPrice+ ( case when IsCancellationProtection = '1'
then ((CancellationProtectionAmt* (SiteToBaseConvRate/SiteToSalesConvRate)))
else 0 end ) ) as decimal(22,2))

anuraag205
Yak Posting Veteran

58 Posts

Posted - 2012-08-29 : 08:37:01

Thanks for your reply..

My actual Query is this and could not achive

plz help

:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
SELECT
DATENAME(Year,BookingDate) as Year, DATENAME(Month,BookingDate) as Month,
COUNT(InvoiceNumber) AS [Total Bookings],
Sum(NegotiatedSellPrice+ ( case when IsCancellationProtection = '1'
then ((CancellationProtectionAmt* (SiteToBaseConvRate/SiteToSalesConvRate)))
else 0 end ) ) AS [Total Sale Price]

FROM tblInvoice
WHERE BookingDate >= '2006-01-01'
AND BookingDate < '2020-02-01'
AND fk_StatusID in (3, 5)

GROUP BY DATENAME(Month,BookingDate) , DATENAME(Year,BookingDate),
DATEPART(MM,BookingDate),DATEPART(YYYY,BookingDate)
ORDER BY DATEPART(YYYY,BookingDate),DATEPART(MM,BookingDate)

error msg:::::::::::::::::::::::::::::::::::::::::::::::::::
Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'FROM'.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-29 : 13:08:49

remove unwanted braces


SELECT 
DATENAME(Year,BookingDate) as Year, DATENAME(Month,BookingDate) as Month,
COUNT(InvoiceNumber) AS [Total Bookings], 
Sum(NegotiatedSellPrice+ case when IsCancellationProtection = '1'
then (CancellationProtectionAmt* (SiteToBaseConvRate/SiteToSalesConvRate))
else 0 end ) AS [Total Sale Price]
FROM tblInvoice
WHERE BookingDate >= '2006-01-01'
AND BookingDate < '2020-02-01'
AND fk_StatusID in (3, 5)

GROUP BY DATENAME(Month,BookingDate) , DATENAME(Year,BookingDate),
DATEPART(MM,BookingDate),DATEPART(YYYY,BookingDate)
ORDER BY DATEPART(YYYY,BookingDate),DATEPART(MM,BookingDate)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anuraag205
Yak Posting Veteran

58 Posts

Posted - 2012-08-30 : 00:51:04

But still i could not achieve this....

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-30 : 01:26:04

quote:
Originally posted by anuraag205

But still i could not achieve this....

Thanks

its compiling fine for me

if its a part of bigger query post full part
otherwise we cant guess whats happening at your end

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anuraag205
Yak Posting Veteran

58 Posts

Posted - 2012-08-30 : 02:38:38

This is my full Query and could not get the "[Forward Sale Price]"value in 2 decimal system??? I tried your sugession, bu could not achieve...
:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
SELECT
DATENAME(YEAR, BOOKINGDATE) AS BookingYear,
DATENAME(MONTH, BOOKINGDATE) AS BookingMonth,
DATENAME(YEAR, COLLECTIONDATE) AS ForwardYear,
DATENAME(MONTH, COLLECTIONDATE) AS ForwardMonth,
SUM(NegotiatedSellPrice+ ( case when IsCancellationProtection = '1'
then ((CancellationProtectionAmt* (SiteToBaseConvRate/SiteToSalesConvRate)))
else 0 end ) ) AS [Forward Sale Price],

COUNT(InvoiceNumber) AS [Forward Bookings]
FROM
tblInvoice
WHERE
((fk_StatusID = 3) OR (fk_StatusID = 5))
AND BOOKINGDATE >= '20070401' AND BOOKINGDATE < '20150201'
AND COLLECTIONDATE >= '20070501' AND COLLECTIONDATE < '20150201'
GROUP BY
DATEPART(MM, BOOKINGDATE),
DATEPART(YYYY, BOOKINGDATE),
DATENAME(MONTH, COLLECTIONDATE),
DATENAME(YEAR, COLLECTIONDATE),
DATEPART(MM, COLLECTIONDATE),
DATEPART(YYYY, COLLECTIONDATE),
DATENAME(YEAR, BOOKINGDATE),
DATENAME(MONTH, BOOKINGDATE)
ORDER BY
DATENAME(YEAR, BOOKINGDATE),
DATENAME(MONTH, BOOKINGDATE),
DATEPART(YYYY, COLLECTIONDATE),
DATEPART(MM, COLLECTIONDATE)

::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

Thanks

shaggy
Posting Yak Master

248 Posts

Posted - 2012-08-30 : 06:24:49

Try this
SELECT
DATENAME(YEAR, BOOKINGDATE) AS BookingYear,
DATENAME(MONTH, BOOKINGDATE) AS BookingMonth,
DATENAME(YEAR, COLLECTIONDATE) AS ForwardYear,
DATENAME(MONTH, COLLECTIONDATE) AS ForwardMonth,
CAST(SUM(NegotiatedSellPrice+ ( case when IsCancellationProtection = '1'
then ((CancellationProtectionAmt* (SiteToBaseConvRate/SiteToSalesConvRate)))
else 0 end ) )as decimal(22,2))
AS [Forward Sale Price],

COUNT(InvoiceNumber) AS [Forward Bookings]
FROM
tblInvoice
WHERE
((fk_StatusID = 3) OR (fk_StatusID = 5))
AND BOOKINGDATE >= '20070401' AND BOOKINGDATE < '20150201'
AND COLLECTIONDATE >= '20070501' AND COLLECTIONDATE < '20150201'
GROUP BY
DATEPART(MM, BOOKINGDATE),
DATEPART(YYYY, BOOKINGDATE),
DATENAME(MONTH, COLLECTIONDATE),
DATENAME(YEAR, COLLECTIONDATE),
DATEPART(MM, COLLECTIONDATE),
DATEPART(YYYY, COLLECTIONDATE),
DATENAME(YEAR, BOOKINGDATE),
DATENAME(MONTH, BOOKINGDATE)
ORDER BY
DATENAME(YEAR, BOOKINGDATE),
DATENAME(MONTH, BOOKINGDATE),
DATEPART(YYYY, COLLECTIONDATE),
DATEPART(MM, COLLECTIONDATE)

anuraag205
Yak Posting Veteran

58 Posts

Posted - 2012-08-30 : 06:45:42

@ shaggy....u got it...am going wrong with the syntax every time.

@visakh16 thanks a ton for your support

Thanks

shaggy
Posting Yak Master

248 Posts

Posted - 2012-08-30 : 07:04:11

Glad it worked for u

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources