T-SQL Query Help

Question

T-SQL Query Help

SSCertifiable

Points: 6532

December 25, 2019 at 5:03 am

Hi Guys,
Here is my sample table and data.

Create  table #TempInvoice
(
id int identity (1,1),
Tkey int,
Status varchar(25),
Amount float
)

Insert into #TempInvoice (Tkey,Status,Amount)
VALUES (2331,'SecondTry',23.00),
  (2331,'ThirdTry',21.00),
  (2331,'Transfer',18.00)

Select * from #TempInvoice

Drop table #TempInvoice

However, Here what I want in my select statement.

ID,Tkey,Status,Amount
1,2331,SecondTry,23
2,2332,ThirdTry,21
3,2331,Transfer,18
4,2331,First,23

I want to just add last row with same information just changing the status to "First" any advice? Amount could be coming from Top 1

December 27, 2019 at 5:03 pm

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 28 total)

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

Jonathan AC Roberts SSCoach Points: 18339 More actions · Answer 1

Insert into #TempInvoice (Tkey,Status,Amount)
SELECT TOP(1) Tkey,'First',Amount FROM #TempInvoice

Jeff Moden SSC Guru Points: 1004302 More actions · Answer 2

I also suspect that you want this to be conditional to check if there already is a "First Try" or not and add the extra row if there isn't. I also suspect that there is more than one TKey in that same table that would also need the same conditional treatment.

Is that correct?

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

Jeff Moden SSC Guru Points: 1004302 More actions · Answer 3

jcelko212 32090 wrote:

(invoice_nbr CHAR(4) NOT NULL,

Good lord, NO! That is so wrong in so many ways! I suggest you read any good book on RDBMSs, especially if your going to use SQL Server!

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

oogibah SSC Eights! Points: 827 More actions · Answer 4

Never in my life have I heard somebody call a pound sign an octothorp .. spell check doesn't even recognize it as a word.

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 5

I used to set type, decades ago. This is the actual name of what you probably call a hash. Or do you call it a pound sign (it weighs 5#) . Or do you call it a number sign (#5 machine screws).

You might want to get a copy of "Shady Characters" by Keith Houston (ISBN 980393064421). This book is a description of the history of various punctuation marks. We have the pilcrow, Interrobang, ampersand, and manicule among others.

Please post DDL and follow ANSI/ISO standards when asking for help.

oogibah SSC Eights! Points: 827 More actions · Answer 6

I call it a pound sign or a sharp sign. The original name for it was a pound sign.. the origins of the "octothorp" which, btw, isn't even recognized by spellcheck was some patent in 1973.

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 7

I'm assuming that an invoice number uses a nominal scale. This is not a magnitude or quantity. That means it has to be a character string, but perhaps we could have added the constraint to keep this string as all digits.

invoice_nbr CHAR(4) NOT NULL CHECK(invoice_nbr LIKE '[0-9][0-9][0-9][0-9]'),

I would prefer to have a check digit, but we don't have any specs as to what check digit method to use. Obviously the worst way to do this would be to make it an integer.

Please post DDL and follow ANSI/ISO standards when asking for help.

Jeff Moden SSC Guru Points: 1004302 More actions · Answer 8

jcelko212 32090 wrote:

I'm assuming that an invoice number uses a nominal scale. This is not a magnitude or quantity. That means it has to be a character string, but perhaps we could have added the constraint to keep this string as all digits.
invoice_nbr CHAR(4) NOT NULL CHECK(invoice_nbr LIKE '[0-9][0-9][0-9][0-9]'),
I would prefer to have a check digit, but we don't have any specs as to what check digit method to use. Obviously the worst way to do this would be to make it an integer.

I agree with your "assumption" that an invoice number is a "Nominal Scale". The column has a single purpose and that is to uniquely identify each row in the invoice table without regard to row content or rank of the row in any way, shape, or fashion. There isn't even a requirement for the numbers to be sequential nor contiguous (although sequential generation of the numbers makes life easy and so I recommend doing so). It's nothing more than an identification number (which meets the requirement to be a "Nominal Scale") and, although customers might be a little concerned if it were a negative number, it's starting value could be anything in the domain of a TinyInt, SmallInt, Int, or BigInt datatype and it could be generated in a positive direction, negative direction, can use any increment, and could, indeed, be randomly assigned so long as uniqueness is maintained in the column and that can even include a single NULL (although that's a really bad idea because NULL isn't a value, it's a non-joinable state). Again, the numbers have no underlying meaning that identifies any attribute of the data in the rows.

But, you are suggesting that numeric datatypes have a "value" and while that is certainly true (it IS a numeric value), the numeric value has no "meaning" in this case other than providing a convenient method of generating unique numbers. THERE IS NO RULE that numeric values being used in the capacity of "labels" mean that such "labels" (which are nothing more than "identifiers") must be character based, especially when it comes to computers and relational databases.

I'll even suggest that converting such numbers to character based representations (as you so adamantly suggest) is a violation of one of the "holy" rules of databases and that is "Thou shalt not permanently store formatted temporal or numeric data in relational database objects". That rule certainly applies here and for many good reasons including but not limited to unnecessarily decreasing the density of data y bincreasing the byte count of the data for no good reason. Further, if the uniqueness of this column is ensured by a (unique) Clustered Index, the ramifications of expanding the data by converting to character based representations of what were previously adequately and correctly expressed as much smaller numeric data types, you drastically further decrease the data density of every non-clustered index (which contains a copy of the Clustered Index key column(s)) on the table and every table that contains an FK column that points at the column(s) (the invoice_nbr column, in this case).

To restate to be sure, the numbers may have a "numeric value" but those values have no "meaning" that intentionally identifies any attributes of the data the related row contains, which meets all of the requirements of being a "Nominal Scale" even if they are stored in a numeric datatype. To wit, not all "labels" (which is just another name for "identifiers") have to be character based.

And we haven't even touched on how lower data density or character based lookups affect performance of queries or index maintenance.

Shifting gears a bit, I DO like the idea of a check digit on Invoice Numbers. It does add a bit of overhead to the original insert and can cost a bit of extra space when it comes to the domain of the Invoice Numbers if stored in the same column as a part of the Invoice Number but... even that doesn't require the use of character based unique row identifiers and neither do any of the calculations to support it. If the automatic generation of the base invoice number is important, the check digit could be stored in a separate TINYINT column, which might also reduce the size of the integer datatype of the invoice_nbr column, as well.

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

Jeff Moden SSC Guru Points: 1004302 More actions · Answer 9

For those interested in what "Nominal Scale" actually is, there's a quick tutorial from 1:46 to 3:30 in the following video. The two examples that they use to explain it are exceptionally clear.

https://www.youtube.com/watch?v=KIBZUk39ncI

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

rjhare Newbie Points: 3 More actions · Answer 10

I'm a little taken aback by the language used to the OP in answers on here, is this the standard?

Wow...

Criticism comes easier than craftsmanship

Neil Burton SSC-Insane Points: 22997 More actions · Answer 11

rjhare wrote:

I'm a little taken aback by the language used to the OP in answers on here, is this the standard?
Wow...

Absolutely not. Mr Celko, while undeniably hugely knowledgeable, seems to take a perverse delight in being as obnoxious as possible in order to get his points across. If you read some of Jeff's replies to other threads you'll see how things are usually done around here.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

x SSC-Insane Points: 23660 More actions · Answer 12

"Thou shalt not permanently store formatted temporal or numeric data in relational database objects"

Joe is making that column a character column, so this doesn't really apply as much as the text implies as far as I can tell from his viewpoint. But with that out of the way, a case could be made that since we increment order numbers to get each one in succession, maybe it SHOULD be considered numeric data.

Joe, do you convert back to numeric to get your next successive order number and then convert back to character data? With all the back and forth about STORING order numbers, what about generating them? At some point, we have to determine what order number to assign to a newly created order right?

rjhare Newbie Points: 3 More actions · Answer 13

Thanks Neil,

I assumed this level of disdain, to those less experienced, had disappeared twenty years ago from technical forums - apparently not.

I will not be asking too many questions in anticipation of such abuse...sadly.

Rich

Criticism comes easier than craftsmanship