SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


how to write query


how to write query

Author
Message
amitsingh308
amitsingh308
SSC Veteran
SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)

Group: General Forum Members
Points: 217 Visits: 224
Hello

I have to generate a report in which amount should be 13 digits.

e.g 2000- convert it into paisa 200000 then 13 digit amount is 0000000200000

but I want it to do in SQl query..

Select (amount*100) from table
then need to add 0s as per count 13-len(amount*100) before the amount

please guide me..
amitsingh308
amitsingh308
SSC Veteran
SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)

Group: General Forum Members
Points: 217 Visits: 224
I got the solution

Use Replicate function
SELECT '200000'
SELECT REPLICATE('0',7)+'200000'
Erland Sommarskog
Erland Sommarskog
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5508 Visits: 875
SELECT replace(str(amt, 13), ' ', '0')

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: General Forum Members
Points: 222904 Visits: 42003
If you're interested in speed, STR isn't the way to do it. And since it does a FLOAT conversion in the process, it can slip into the same approximation "problems" as FLOAT (although not likely with only 13 digits). Then there's the rounding errors that it produces because of the approximations it does.

My recommendation is to avoid STR().

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: General Forum Members
Points: 222904 Visits: 42003
amitsingh308 (8/29/2013)
Hello

I have to generate a report in which amount should be 13 digits.

e.g 2000- convert it into paisa 200000 then 13 digit amount is 0000000200000

but I want it to do in SQl query..

Select (amount*100) from table
then need to add 0s as per count 13-len(amount*100) before the amount

please guide me..


One of the fastest ways of doing such a thing is with a little hardcoding. You might want to avoid things like REPLICATE to calculate a certain number of concatenated zero's because its just not as fast as a string literal. You should have good luck with the following...
 SELECT RIGHT('0000000000000' + CAST((Amt*100) AS VARCHAR(13)),13)
FROM dbo.SomeTable
;




Of course, such formatting should probably be done in the front-end, if there is one.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Erland Sommarskog
Erland Sommarskog
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5508 Visits: 875
Jeff Moden (8/30/2013)
If you're interested in speed, STR isn't the way to do it.


It's unlikely that the speed difference would be significant. Yes, if you need to do this on 10 million rows on straight SELECT from a single table, you might see something. If you instead query umpteen tables with a suboptimal query plan, this will be one your smallest problem.

And since it does a FLOAT conversion in the process, it can slip into the same approximation "problems" as FLOAT (although not likely with only 13 digits).


True, the argument will be converted to float, and this could pose problems with you are working with 16-digit bigint numbers you want to pad to 18 digits. An integer value is typically converted to float with the exponent as 0, meaning that there is no loss of precision.

I like str() + replace() because is short and concise.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: General Forum Members
Points: 222904 Visits: 42003
Erland Sommarskog (8/31/2013)
Jeff Moden (8/30/2013)
If you're interested in speed, STR isn't the way to do it.


It's unlikely that the speed difference would be significant. Yes, if you need to do this on 10 million rows on straight SELECT from a single table, you might see something. If you instead query umpteen tables with a suboptimal query plan, this will be one your smallest problem.

And since it does a FLOAT conversion in the process, it can slip into the same approximation "problems" as FLOAT (although not likely with only 13 digits).


True, the argument will be converted to float, and this could pose problems with you are working with 16-digit bigint numbers you want to pad to 18 digits. An integer value is typically converted to float with the exponent as 0, meaning that there is no loss of precision.

I like str() + replace() because is short and concise.


I used to think the same thing for a very long time. Like me, you might want to change you mind.

It may be "concise" but it's far from accurate and far from being performant.

It produces rounding errors when working with decimal places simply because FLOAT is based on binary math rather than decimal math and it can't actually precisely contain certain values with it's rather short Mantissa even on small numbers.

STR() is also more than twice as slow as RIGHT() concatenation method. While I agree that a 2,300ms difference in CPU time (3,800ms vs 1,500ms) for a single instance of the formula over a million rows on a relatively slow machine isn't much, just imagine if everyone on a project paid attention to such minor nuances... the end result would be code that ran twice as fast as the compeition's code. ;-) And, what does it cost? If you know how to use the RIGHT() concatenation method and even if you can't actually type quickly, the answer is measured as just a second or two to get twice the performance and much more accurate rounding.

Here's a link for some testing that I did that demonstrates the performance and accuracy differences. Again, it was a while back meaning that I did the testing on my really old desktop and that causes the appearance of a larger difference in time but that doesn't affect things like the rounding errors when working with decimal numbers.
http://www.sqlservercentral.com/articles/T-SQL/71565/

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Erland Sommarskog
Erland Sommarskog
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5508 Visits: 875
Jeff Moden (8/31/2013)
[quote][b]It produces rounding errors when working with decimal places simply because FLOAT is based on binary math rather than decimal math and it can't actually precisely contain certain values with it's rather short Mantissa even on small numbers.


That assumes decimal values. The case in point was numeric values. If you want to use str() to get right-aligned decimal values, yes, you can get errors, and if it essential that your rounding is consistent there may be a workaround:

SELECT str(1.23955, 10, 4) AS Wrong, convert(float, 1.23955) AS Why, 
str(round(1.23955, 4), 10, 4) AS Workaround



Note: you need to run the query from SQLCMD to see Why.

STR() is also more than twice as slow as RIGHT() concatenation method.


Frankly, I couldn't be bothered. I also use things like:

convert(char(6), getdate(), 112) + '01'

when I want the first day of the month, even if people claim other constructs are faster. I look at them and say, eh? I can do the most common date tricks with style 112 in my sleep.

Developer productivity counts some times. Performance in T-SQL is about greater scheme of things.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: General Forum Members
Points: 222904 Visits: 42003
I don't need to run it from SQLCMD to see why. I've already demonstrated "Why" in the article. ;-)

So far as "Developer Productivity" goes, I'm all for that. That's why I teach my Developers to learn the more performant methods... so they can do it in their sleep and won't have to rewrite code to fix performance problems that should have been avoided to begin with.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
dwain.c
dwain.c
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18477 Visits: 6431
Another way that should look familiar:


WITH Amt (Amt) AS (SELECT 2000)
SELECT RIGHT(10000000000000+Amt*100,13)
FROM Amt;





My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search