February 20, 2014 at 8:23 am
Hi all, hope in your help.
This is my query in SQL Server:
SELECT
(
[ENER1] + [ENER2]
) / 1000000 AS pG
FROM
[dbo].[doTable40]
WHERE
(
[ENER1] > 0
OR [ENER2] > 0
)
GROUP BY
[ENER1],
[ENER2]
HAVING
YEAR (MAX([theDate])) >= YEAR (GETDATE())
ORDER BY
pG DESC;
And this the output:
7.88528800
3.06978700
1.78070800
1.67984500
1.59402600
1.36468000
1.09013200
.96727700 ====> why not 0.97 ?
.91552000 ====> why not 0.91 ?
.82464700 ====> why not 0.98 ?
.80762800 ====> why not 0.81 ?
Can you help me?
Thanks in advance for any help.
February 20, 2014 at 8:27 am
Since we don't have the create statement or sample data for your table (hint, hint!), are you asking why is the data incorrect? or why isn't it rounding? If you want to round the values you can use ROUND. If the numbers are incorrect please add the create table statement along with some sample data.
February 20, 2014 at 8:31 am
The numbers of SUM are correct.
My question is in output of this SUM:
7.88528800 ====> I need 7.89
3.06978700 ====> I need 3.07
1.78070800 ====> I need 1.78
1.67984500 ...
1.59402600 ...
1.36468000 ...
1.09013200 ...
.96727700 ====> I need 0.97
.91552000 ====> I need 0.91
.82464700 ====> I need 0.98
.80762800 ====> I need 0.81
February 20, 2014 at 8:38 am
Read my post again about using ROUND. Here is a sample:select round('7.88528800',2)
select round('3.06978700',2)
select round('1.78070800',2)
select round('1.67984500',2)
select round('1.59402600',2)
select round('1.36468000',2)
select round('1.09013200',2)
select round('.96727700',2)
select round('.91552000',2)
select round('.82464700',2)
select round('.80762800',2)
February 20, 2014 at 8:44 am
I tried this but :
SELECT
ROUND((
[ENER1] + [ENER2]
) / 1000000,2) AS pG
FROM
[dbo].[doTable40]
WHERE
(
[ENER1] > 0
OR [ENER2] > 0
)
GROUP BY
[ENER1],
[ENER2]
HAVING
YEAR (MAX([theDate])) >= YEAR (GETDATE())
ORDER BY
pG DESC;
7.89000000
3.07000000
1.78000000
1.68000000
1.59000000
1.36000000
1.09000000
.97000000
.92000000
.82000000
.81000000
February 20, 2014 at 8:58 am
Probably just need to cast your calculation.
I used a formatter on this first so it was easier to read.
SELECT Cast(Round(( [ENER1] + [ENER2] ) / 1000000, 2) AS NUMERIC(9, 2)) AS pG
FROM [dbo].[doTable40]
WHERE ( [ENER1] > 0
OR [ENER2] > 0 )
GROUP BY [ENER1],
[ENER2]
HAVING Year (Max([theDate])) >= Year (Getdate())
ORDER BY pG DESC;
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 20, 2014 at 9:02 am
thank you, is better but still without a leading zero before the decimal point ...
7.89
3.07
1.78
1.68
1.59
1.36
1.09
.97
.92
.82
.81
.79
.79
February 20, 2014 at 9:09 am
cms9651 (2/20/2014)
thank you, is better but still without a leading zero before the decimal point ...7.89
3.07
1.78
1.68
1.59
1.36
1.09
.97
.92
.82
.81
.79
.79
You really should be doing this type of stuff in the front end. SQL is not a great formatting tool. In order to do that you will have to convert your numbers to strings.
This is a hack but it should produce the output you want. Note, this is NOT how I would do this. I do not like formatting output in sql. I like to return the data from sql. That allows whatever front end being used to do what it wants with the information.
SELECT
case when ([ENER1] + [ENER2]) / 1000000 > 1 then
cast(Cast(Round(( [ENER1] + [ENER2] ) / 1000000, 2) AS NUMERIC(9, 2)) AS VARCHAR(10))
else
'0' + cast(Cast(Round(( [ENER1] + [ENER2] ) / 1000000, 2) AS NUMERIC(9, 2)) AS VARCHAR(10))
end
AS pG
FROM [dbo].[doTable40]
WHERE ( [ENER1] > 0
OR [ENER2] > 0 )
GROUP BY [ENER1],
[ENER2]
HAVING Year (Max([theDate])) >= Year (Getdate())
ORDER BY pG DESC;
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 20, 2014 at 9:13 am
:doze:
7.89
3.07
1.78
1.68
1.59
1.36
1.09
00.97
00.92
00.82
00.81
00.79
00.79
00.77
February 20, 2014 at 9:22 am
cms9651 (2/20/2014)
:doze:7.89
3.07
1.78
1.68
1.59
1.36
1.09
00.97
00.92
00.82
00.81
00.79
00.79
00.77
What have you tried? Put some effort into this. Remember you are the one with the database where you can run this and you are the one collecting a paycheck for the output.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 20, 2014 at 9:25 am
I tried this:
SELECT
case when ([ENER1] + [ENER2]) / 1000000 > 1 then
cast(Cast(Round(( [ENER1] + [ENER2] ) / 1000000, 2) AS NUMERIC(9, 2)) AS VARCHAR(10))
else
'0' + cast(Cast(Round(( [ENER1] + [ENER2] ) / 1000000, 2) AS NUMERIC(9, 2)) AS VARCHAR(10))
end
AS pG
FROM [dbo].[doTable40]
WHERE ( [ENER1] > 0
OR [ENER2] > 0 )
GROUP BY [ENER1],
[ENER2]
HAVING Year (Max([theDate])) >= Year (Getdate())
ORDER BY pG DESC;
7.89
3.07
1.78
1.68
1.59
1.36
1.09
00.97
00.92
00.82
00.81
00.79
00.79
00.77
February 20, 2014 at 9:30 am
cms9651 (2/20/2014)
I tried this:
SELECT
case when ([ENER1] + [ENER2]) / 1000000 > 1 then
cast(Cast(Round(( [ENER1] + [ENER2] ) / 1000000, 2) AS NUMERIC(9, 2)) AS VARCHAR(10))
else
'0' + cast(Cast(Round(( [ENER1] + [ENER2] ) / 1000000, 2) AS NUMERIC(9, 2)) AS VARCHAR(10))
end
AS pG
FROM [dbo].[doTable40]
WHERE ( [ENER1] > 0
OR [ENER2] > 0 )
GROUP BY [ENER1],
[ENER2]
HAVING Year (Max([theDate])) >= Year (Getdate())
ORDER BY pG DESC;
7.89
3.07
1.78
1.68
1.59
1.36
1.09
00.97
00.92
00.82
00.81
00.79
00.79
00.77
No that is what I tried. Remember that I can't run this because you haven't posted ddl or sample data. I am shooting in the dark. This is some string manipulation that should be really easy. Unfortunately I can't see the results until I post it and you run it.
I doubt you need to use the case expression and casting as varchar because a numeric will add the 0 when the value is less than 1.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 20, 2014 at 11:44 am
We have tired to give you hints, but you need to give us the create table statement and the sample data if you want a better answer. Also your expected outcome keeps changing, so please read the article in my signature and try posting it again with all of the needed information and then you will get a better answer.
February 20, 2014 at 12:20 pm
cms9651 (2/20/2014)
I tried this:
SELECT
case when ([ENER1] + [ENER2]) / 1000000 > 1 then
cast(Cast(Round(( [ENER1] + [ENER2] ) / 1000000, 2) AS NUMERIC(9, 2)) AS VARCHAR(10))
else
'0' + cast(Cast(Round(( [ENER1] + [ENER2] ) / 1000000, 2) AS NUMERIC(9, 2)) AS VARCHAR(10))
end
AS pG
FROM [dbo].[doTable40]
WHERE ( [ENER1] > 0
OR [ENER2] > 0 )
GROUP BY [ENER1],
[ENER2]
HAVING Year (Max([theDate])) >= Year (Getdate())
ORDER BY pG DESC;
7.89
3.07
1.78
1.68
1.59
1.36
1.09
00.97
00.92
00.82
00.81
00.79
00.79
00.77
Cast the final ouput as MONEY to get the leading zero.
--Jeff Moden
February 20, 2014 at 12:29 pm
Jeff Moden (2/20/2014)
cms9651 (2/20/2014)
I tried this:
SELECT
case when ([ENER1] + [ENER2]) / 1000000 > 1 then
cast(Cast(Round(( [ENER1] + [ENER2] ) / 1000000, 2) AS NUMERIC(9, 2)) AS VARCHAR(10))
else
'0' + cast(Cast(Round(( [ENER1] + [ENER2] ) / 1000000, 2) AS NUMERIC(9, 2)) AS VARCHAR(10))
end
AS pG
FROM [dbo].[doTable40]
WHERE ( [ENER1] > 0
OR [ENER2] > 0 )
GROUP BY [ENER1],
[ENER2]
HAVING Year (Max([theDate])) >= Year (Getdate())
ORDER BY pG DESC;
7.89
3.07
1.78
1.68
1.59
1.36
1.09
00.97
00.92
00.82
00.81
00.79
00.79
00.77
Cast the final ouput as MONEY to get the leading zero.
If that works then I would go back about 2-3 of my posts. Prior to adding the case expression. There is no need to cast the results multiple times.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply