Query help

Question

Query help

SQL_Surfer

SSCrazy Eights

Points: 8320
More actions
April 25, 2014 at 1:19 pm

#288261

I have a table that has field called total amount. And they have different amounts ranging from 1 mil to 30 mil….all u have to do is output in such a way that not two subsequent lines can have one line with over 10 mil
So for example if u have data like this: 30 mil, 20mil, 2 mil, 3 mil
U should out put like this
30mil, 2 mil, 20 mil, 3 mil (note that for every line that has a exceeding 10 mil, the next line is less than 10 mil)
How can I accomplish this?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Please post DDL and sample data in a consumable format.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

SQL_Surfer SSCrazy Eights Points: 8320 More actions · Answer 2

SQL_Surfer

SSCrazy Eights

Points: 8320

April 25, 2014 at 1:35 pm

#1708896

Just attached.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Maybe you could use something like this:

SELECT TotalAmt

FROM (

SELECT TotalAmt

,CAST( ROW_NUMBER() OVER(ORDER BY TotalAmt) AS decimal(8,1)) rn

FROM #temp

WHERE TotalAmt >= 10

UNION ALL

SELECT TotalAmt

,CAST( ROW_NUMBER() OVER(ORDER BY TotalAmt) AS decimal(8,1)) + .5 rn

FROM #temp

WHERE TotalAmt < 10)x

ORDER BY rn

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004410 More actions · Answer 4

What would you want in the following case?

drop table #temp

CREATE TABLE #temp

(

TotalAmt DECIMAL(10,0)

)

INSERT INTO #temp(TotalAmt) VALUES(30);

INSERT INTO #temp(TotalAmt) VALUES(40);

INSERT INTO #temp(TotalAmt) VALUES(60);

INSERT INTO #temp(TotalAmt) VALUES(30);

INSERT INTO #temp(TotalAmt) VALUES(40);

INSERT INTO #temp(TotalAmt) VALUES(60);

SELECT * FROM #temp

It will happen. 😉

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)