November 9, 2012 at 9:37 am
below query gives the totaldue based on quarter ,i have simple query where there is null i want to replace with TEXT 'No data'
WITH C AS
(
SELECT
YEAR(OrderDate) AS OrderYear,
DATENAME(Q,OrderDate) AS Quarter,
TotalDue
FROM Sales.SalesOrderHeader
)
SELECT
OrderYear,
STR(SUM(CASE WHEN Quarter=1 THEN TotalDue ELSE NULL END)) AS Quarter1 ,
STR(SUM(CASE WHEN Quarter=2 THEN TotalDue ELSE NULL END)) AS Quarter2,
STR(SUM(CASE WHEN Quarter=3 THEN TotalDue ELSE NULL END)) AS Quarter3 ,
STR(SUM(CASE WHEN Quarter=4 THEN TotalDue ELSE NULL END)) AS Quarter4
FROM C
GROUP BY OrderYear
ORDER BY OrderYear ASC
Following query gives result
2001NULLNULL 5850933 8476619
2002 7379686 8210285 13458206 10827327
2003 8550832 10749269 18220132 16787382
2004 14170983 17969751 56179NULL
Out put should look like
2001No Data No Data 5850933 8476619
2002 7379686 8210285 13458206 10827327
2003 8550832 10749269 18220132 16787382
2004 14170983 17969751 56179No Data
November 9, 2012 at 9:48 am
November 9, 2012 at 10:13 am
Yep, ISNULL or COALESCE, but...
In order to mix string "No Data" with numerics in the same columns, you will need to convert all of them into varchar. This sort of staff is better to be done in a client app or in a report.
November 9, 2012 at 10:16 am
Eugene Elutin (11/9/2012)
Yep, ISNULL or COALESCE, but...In order to mix string "No Data" with numerics in the same columns, you will need to convert all of them into varchar. This sort of staff is better to be done in a client app or in a report.
He's already converting to char using the STR function.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply