October 12, 2012 at 7:14 am
SELECT
YEAR(OrderDate) ASOrderYear,
SUM(CASE WHEN DATENAME(Q,OrderDate) =1 THEN TotalDue END)AS Q1,
SUM(CASE WHEN DATENAME(Q,OrderDate) =2 THEN TotalDue END)AS Q2,
SUM(CASE WHEN DATENAME(Q,OrderDate) =3 THEN TotalDue END)AS Q3,
SUM(CASE WHEN DATENAME(Q,OrderDate) =4 THEN TotalDue END)AS Q2
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate)
ORDER BY 1 ASC
Using above Query i got below report
OrderYear Q1 Q2 Q3 Q2
----------- --------------------- --------------------- --------------------- ---------------------
2001 NULL NULL 5850932.9483 8476619.278
2002 7379686.3091 8210285.1655 13458206.13 10827327.4904
2003 8550831.8702 10749269.374 18220131.5285 16787382.3141
2004 14170982.5455 17969750.9487 56178.9223 NULL
output should like this
OrderYear Q1 Q2 Q3 Q2
----------- --------------------- --------------------- --------------------- ---------------------
2001 NULL NULL 58,50932.9483 84,76619.278
2002 73,79686.3091 82,10285.1655 13,458206.13 10,827327.4904
2003 85,50831.8702 10,749269.374 18,220131.5285 16,787382.3141
2004 14,170982.5455 17,969750.9487 56,178.9223 NULL
October 12, 2012 at 7:44 am
Smash125 (10/12/2012)
SELECTYEAR(OrderDate) ASOrderYear,
SUM(CASE WHEN DATENAME(Q,OrderDate) =1 THEN TotalDue END)AS Q1,
SUM(CASE WHEN DATENAME(Q,OrderDate) =2 THEN TotalDue END)AS Q2,
SUM(CASE WHEN DATENAME(Q,OrderDate) =3 THEN TotalDue END)AS Q3,
SUM(CASE WHEN DATENAME(Q,OrderDate) =4 THEN TotalDue END)AS Q2
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate)
ORDER BY 1 ASC
Using above Query i got below report
OrderYear Q1 Q2 Q3 Q2
----------- --------------------- --------------------- --------------------- ---------------------
2001 NULL NULL 5850932.9483 8476619.278
2002 7379686.3091 8210285.1655 13458206.13 10827327.4904
2003 8550831.8702 10749269.374 18220131.5285 16787382.3141
2004 14170982.5455 17969750.9487 56178.9223 NULL
output should like this
OrderYear Q1 Q2 Q3 Q2
----------- --------------------- --------------------- --------------------- ---------------------
2001 NULL NULL 58,50932.9483 84,76619.278
2002 73,79686.3091 82,10285.1655 13,458206.13 10,827327.4904
2003 85,50831.8702 10,749269.374 18,220131.5285 16,787382.3141
2004 14,170982.5455 17,969750.9487 56,178.9223 NULL
This sort of formatting should be performed in the presentation layer, not the database layer.
If you insist on doing it in the database layer, it can be achieved like this: -
SELECT OrderYear,
CONVERT(VARCHAR(100), CAST(Q1 AS money), 1),
CONVERT(VARCHAR(100), CAST(Q2 AS money), 1),
CONVERT(VARCHAR(100), CAST(Q3 AS money), 1),
CONVERT(VARCHAR(100), CAST(Q4 AS money), 1)
FROM (SELECT
YEAR(OrderDate) AS OrderYear,
SUM(CASE WHEN DATENAME(Q,OrderDate) =1 THEN TotalDue END)AS Q1,
SUM(CASE WHEN DATENAME(Q,OrderDate) =2 THEN TotalDue END)AS Q2,
SUM(CASE WHEN DATENAME(Q,OrderDate) =3 THEN TotalDue END)AS Q3,
SUM(CASE WHEN DATENAME(Q,OrderDate) =4 THEN TotalDue END)AS Q4
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate)
) a
ORDER BY OrderYear;
October 12, 2012 at 8:12 am
Cadavre (10/12/2012)
If you insist on doing it in the database layer, it can be achieved like this:
Check again. OP's desired output doesn't actually have a comma as a thousands separator. It would appear that the OP wants a comma only after the 2nd character of the integer part of each number.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 12, 2012 at 8:15 am
Jeff Moden (10/12/2012)
Cadavre (10/12/2012)
If you insist on doing it in the database layer, it can be achieved like this:Check again. OP's desired output doesn't actually have a comma as a thousands separator. It would appear that the OP wants a comma only after the 2nd character of the integer part of each number.
Honestly, I assumed it was a typo in the same way as having two Q2 columns was a typo. If I'm wrong, well, you know what happens when you assume, right? 😀
October 12, 2012 at 2:20 pm
Check for STR, CONVERT, and REPLACE functions to additionally format the numbers as string.
October 14, 2012 at 7:04 am
Jeff Moden (10/12/2012)
Cadavre (10/12/2012)
If you insist on doing it in the database layer, it can be achieved like this:Check again. OP's desired output doesn't actually have a comma as a thousands separator. It would appear that the OP wants a comma only after the 2nd character of the integer part of each number.
Lakhs instead of thousands.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 14, 2012 at 7:05 am
Vedran Kesegic (10/12/2012)
Check for STR, CONVERT, and REPLACE functions to additionally format the numbers as string.
I can't work out how any of these three functions can help. Please show us.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 14, 2012 at 7:05 am
Vedran Kesegic (10/12/2012)
Check for STR, CONVERT, and REPLACE functions to additionally format the numbers as string.
I can't work out how any of these three functions can help. Please show us.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 14, 2012 at 7:50 am
ChrisM@home (10/14/2012)
Jeff Moden (10/12/2012)
Cadavre (10/12/2012)
If you insist on doing it in the database layer, it can be achieved like this:Check again. OP's desired output doesn't actually have a comma as a thousands separator. It would appear that the OP wants a comma only after the 2nd character of the integer part of each number.
Lakhs instead of thousands.
Maybe, but if so there seems to be also a rule that for amounts less than 1 lakh a comma eparates thousands: this is shown by the last number, where the sated form is 56,178.9223.
Tom
October 14, 2012 at 9:34 am
L' Eomot Inversé (10/14/2012)
ChrisM@home (10/14/2012)
Jeff Moden (10/12/2012)
Cadavre (10/12/2012)
If you insist on doing it in the database layer, it can be achieved like this:Check again. OP's desired output doesn't actually have a comma as a thousands separator. It would appear that the OP wants a comma only after the 2nd character of the integer part of each number.
Lakhs instead of thousands.
Maybe, but if so there seems to be also a rule that for amounts less than 1 lakh a comma eparates thousands: this is shown by the last number, where the sated form is 56,178.9223.
Fair point, Tom, but if you examine all of those "output example" figures and count the number of digits between the comma and the decimal point, this is what you get:
56,178.9223 -- 3
58,50932.9483-- 5
84,76619.278-- 5
73,79686.3091-- 5
82,10285.1655-- 5
85,50831.8702-- 5
10,749269.374-- 6
18,220131.5285-- 6
16,787382.3141-- 6
14,170982.5455-- 6
17,969750.9487-- 6
13,458206.13-- 6
10,827327.4904-- 6
I reckon the intent is lakh, and the rest is down to sloppiness. But it's a guess.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 14, 2012 at 10:13 am
ChrisM@home (10/14/2012)
Fair point, Tom, but if you examine all of those "output example" figures and count the number of digits between the comma and the decimal point, this is what you get:
56,178.9223 -- 358,50932.9483-- 5
84,76619.278-- 5
73,79686.3091-- 5
82,10285.1655-- 5
85,50831.8702-- 5
10,749269.374-- 6
18,220131.5285-- 6
16,787382.3141-- 6
14,170982.5455-- 6
17,969750.9487-- 6
13,458206.13-- 6
10,827327.4904-- 6
I reckon the intent is lakh, and the rest is down to sloppiness. But it's a guess.
You may be right, but separating only lakhs would be unusual. The common system is to separate all of thousand, lakh, crore, thousand crore, lakh crore, crore crore, and so on upwards (the pattern of number of digits before separator, reading right to left, is 3,2,2,3,2,2,3,2,2,...and so on). Maybe whoever suggested that the comma is meant to be after the first two digits was right after all? Maybe the original poster will appear and enlighten us on this.
Tom
October 14, 2012 at 3:51 pm
If you don't mind trailing zeroes, and you really want such an odd format (it's not a typo in your desired result) here is your solution.
Prepare the data:
create table #numbers(q money)
insert into #numbers(q) values
(56178.9223), -- 3
(5850932.9483),-- 5
(8476619.278),-- 5
(7379686.3091),-- 5
(8210285.1655),-- 5
(8550831.8702),-- 5
(10749269.374),-- 6
(18220131.5285),-- 6
(16787382.3141),-- 6
(14170982.5455),-- 6
(17969750.9487),-- 6
(13458206.13),-- 6
(10827327.4904)-- 6
There is conversion formula:
select stuff(convert(varchar, q, 2), 3, 0, ',')
from #numbers
And result is:
56,178.9223
58,50932.9483
84,76619.2780
73,79686.3091
82,10285.1655
85,50831.8702
10,749269.3740
18,220131.5285
16,787382.3141
14,170982.5455
17,969750.9487
13,458206.1300
10,827327.4904
You just have to handle numbers less than 100 and negative numbers.
You could create a function or use outer apply to avoid repetition of the formula.
Cheers,
Vedran
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply