T-SQL Query help

Question

T-SQL Query help

Viewing 13 posts - 16 through 27 (of 27 total)

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

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 1

If you right-click on the tables within the Object Explorer in SQL Server Management Studio, you can select an option that lets you create a script. That's what we're looking for here.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

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

forefj (7/7/2012)
I read Jeff's posting, especially the part about people posting for homework assignments or a job interview - neither apply in my case.
I am still somewhat new working with T-SQL, I am not sure what other sample data you need to be able to assist. It looks like the example in Jeff's article is talking about building a table.

Just a little constructive feedback on this...

I'll even help on homework and interviews if I see that the person is really trying to learn instead of just trying to get a quick answer so they don't actually have to think. 😉

And, yes... when someone puts the example data into readily consumable code that builds and populates test table(s), they stand a whole lot better chance of getting their answer quicker and more accurately than going back and forth with a thousand questions. The code than JLS posted is a good example of how to do that. It leaves nothing to be questioned about what the data actually looks like.

--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: 1004403 More actions · Answer 3

Grant Fritchey (7/8/2012)
If you right-click on the tables within the Object Explorer in SQL Server Management Studio, you can select an option that lets you create a script. That's what we're looking for here.

Heh... just so no one takes that literally for wide tables. We're looking for that kind of script but only include the columns that actually pertain to the problem being described.

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

forefj Mr or Mrs. 500 Points: 593 More actions · Answer 4

What I was looking for was what Grant Fritchey finally posted.

As I mentioned I don't want to post everything for privacy reasons and I would now have to go back and reword all the results and I think it would just make it even more confusing.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 5

forefj (7/8/2012)
What I was looking for was what Grant Fritchey finally posted.
As I mentioned I don't want to post everything for privacy reasons and I would now have to go back and reword all the results and I think it would just make it even more confusing.

we are not asking you to post "real" data...just an example set of data that represents your problem.

are you now able to do that...if you are still unsure, pls dont worry, ask for help here.

kind regards

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 6

forefj (7/8/2012)
What I was looking for was what Grant Fritchey finally posted.
As I mentioned I don't want to post everything for privacy reasons and I would now have to go back and reword all the results and I think it would just make it even more confusing.

Yeah, it's hard to stay completely private and get help in a public forum. Do what you can to help us out. It'll make easier to help you.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 7

forefj (7/8/2012)
I would now have to go back and reword all the results and I think it would just make it even more confusing.

Look at the post that "J Livingston SQL" posted on this thread and see how wrong that presumption actually is.

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

forefj Mr or Mrs. 500 Points: 593 More actions · Answer 8

Unfortunately I think posting the script info isn't going to help much.

I see other people posting about select into and then Union all.

What I am looking to do is something different. I don't have the list of values until the query runs so I can't readily list them in a select statement. Not sure if I can do this with a Subquery or temp table. I am probably going to have to research further - hopefully if I find the answer elsewhere I will report back the solution.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 9

http://www.sqlservercentral.com/Forums/FindPost1326539.aspx

did you have time to to take a look at my post above?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 10

just for clarification on my part...

in a previous post you mentioned

" It is SQL Server 2000 and I using Management Studio 2008 to connect. I search for some documentation but didn't finding anything specific to that table (only a Sales_Commision table) and it isn't related to the specific query I am looking to build."

can you actually see the relevant tables in SQL 2000 ?

do you have necessary permissions to access the table(s) we are asking you to document?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 11

forefj (7/8/2012)
Unfortunately I think posting the script info isn't going to help much.
I see other people posting about select into and then Union all.
What I am looking to do is something different. I don't have the list of values until the query runs so I can't readily list them in a select statement. Not sure if I can do this with a Subquery or temp table. I am probably going to have to research further - hopefully if I find the answer elsewhere I will report back the solution.

I don't believe you understand. The first part of the code that "J Livingston SQL" posted are just to build the test data. If you actually read the article you were asked to read, you'd probably have guessed that for yourself.

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

forefj Mr or Mrs. 500 Points: 593 More actions · Answer 12

J to answer your questions:

can you actually see the relevant tables in SQL 2000 ?

do you have necessary permissions to access the table(s) we are asking you to document?

Yes I can see the tables. When I first read your earlier post I thought you want me to INSERT test data in a database, that is where I was confused until Grant pointed out how to get the data.

forefj Mr or Mrs. 500 Points: 593 More actions · Answer 13

Go what I needed (see below). Thanks.

select c.name,c.customerid,c.linkid

,case when c.linkid=x.linkid then x.orderitem end as orderitem

,case when c.linkid=x.linkid then x.orderdate end as orderdate

from customer as c

Inner join (

Select C.CustomerID, C.LinkID, O.OrderItem,O.OrderDate

From Customer as C

JOIN Orders as O

ON C.CustomerKey=O.OrderKey

WHERE O.OrderDate='mm-dd-yyyy'

) as X

on c.customerid=x.customerid

order by c.customerid

,case when c.linkid=x.linkid then 0 else 1 end asc

,c.linkid