Its just a query but not required to post

Question

1.00/5 (2 votes)

See more:

SQL-Server-2014

SQL

M(  "Cost_Fact3". "CostAmount" ) AS  "C8"
FROM  "PalsDW". "dbo". "Cost_Fact" "Cost_Fact3"
WHERE  "Cost_Fact3". "CurrencySID" =3
GROUP BY  "Cost_Fact3". "PlanningResourceCenterSID",  "Cost_Fact3". "JDEAccountSID",  "Cost_Fact3". "CarrierGroupSID", 
"Cost_Fact3". "CurrencySID", "Cost_Fact3". "CalendarMonthSID",  "Cost_Fact3". "CalendarYear", 
CASE  "Cost_Fact3". "CalendarMonthSID"
WHEN 1 
THEN  '01'
WHEN 2 
THEN  '02'
WHEN 3 
THEN  '03'
WHEN 4 
THEN  '04'
WHEN 5 
THEN  '05'
WHEN 6 
THEN  '06'
WHEN 7 
THEN  '07'
WHEN 8 
THEN  '08'
WHEN 9 
THEN  '09'
WHEN 10 
THEN  '10'
WHEN 11 
THEN  '11'
WHEN 12 
THEN  '12'
END , LTRIM( RTRIM( CONVERT( CHAR,  "Cost_Fact3". "CalendarYear" ) ) ) + LTRIM( RTRIM( 
CASE  "Cost_Fact3". "CalendarMonthSID"
WHEN 1 
THEN  '01'
W
THEN  '07'
WHEN 8 
THEN  '08'
WHEN 9 
THEN  '09'
WHEN 10 
THEN  '10'
WHEN 11 
THEN  '11'
WHEN 12 
THEN  '12'
END ) ) )  "T0"

What I have tried:

can we improve this query performance with out using index. i don't have permission to create one

Posted 20-Jun-17 8:25am

BinnyVishwant

Updated 21-Jun-17 8:51am

v4

Add a Solution

Comments

[no name] 20-Jun-17 14:35pm

First idea: Remove your CASE blabla to convert a number to a string. Then the SQL becomes most probably also more nice to read. In the current form I don't like even to have a look to it to get an idea what it should do ;)

ZurdoDev 20-Jun-17 15:08pm

Stop all the crazy WHENs. Google how to get a 2 digit number.

ZurdoDev 20-Jun-17 15:08pm

But no, we can't tell you how to make it faster because we can't see any of the important stuff.

Michael_Davies 20-Jun-17 15:09pm

What timings are you getting at the moment and over how many records?

One thing you might consider doing would be to create a temporary table or view for the nested select then apply the main select to that and see how the timings compare.

PIEBALDconsult 20-Jun-17 16:48pm

Avoid string-manipulation in SQL -- that should be done elsewhere.
Look into the FORMAT function
https://docs.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql

Not only should you not be allowed to create an index, you probably shouldn't be allowed to write code.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2017-06-21T08:51:00

If you absolutely need to return a formatted string then use the FORMAT function - it's been around since SQL Server 2012. Failing that use a combination of REPLICATE and CONVERT.

Speaking of CONVERT if you use CONVERT(VARCHAR, CalendarYear ) rather than CONVERT(CHAR, CalendarYear ) then you won't need the LTRIM(RTRIM( on the result. You didn't need the LTRIM(RTRIM( on the CASE section at all.

You also need to look up the use of OVER[^] with Aggregate functions like SUM - It's a lot easier to use than awkward GROUP BY clauses like yours, the SQL is easier to read and it is also far more efficient.

Your whole query is over-complicated - there is no reason for the sub-query or changing aliases on columns. The whole thing appears to be as simple as the following (note - untested as you didn't give us any sample data)

SQL

SELECT  PlanningResourceCenterSID,  JDEAccountSID, 
	CarrierGroupSID,  CurrencySID,  CalendarMonthSID, 
	CalendarYear,  CalendarMonthSID AS Calendar_Month_Local_Time,  
	FORMAT(CalendarMonthSID, '00') AS  Month_C, 
	CONVERT(VARCHAR,  CalendarYear ) + FORMAT(CalendarMonthSID, '00') AS  Year_Month_con,  
	SUM(CostAmount) OVER (PARTITION BY   PlanningResourceCenterSID,  JDEAccountSID,  CarrierGroupSID, CurrencySID, CalendarMonthSID,  CalendarYear)  AS  CostAmount
	FROM  Cost_Fact
	WHERE  CurrencySID =3

CC_12497356 · Answer 2 · 2017-06-20T23:15:00

Solution 2

SQL

Select REPLICATE(0,2-len(<column_name>) + <column_name>

Posted 20-Jun-17 23:15pm

CC_12497356

Comments

CHill60 21-Jun-17 14:42pm

Rather than posting two solutions to the same question you should use the Improve solution link to update your initial solution