sql query

Question

sql query

Tallur

Valued Member

Points: 63
More actions
September 1, 2013 at 3:15 pm

#395403

Thanks,
RT

Viewing 14 posts - 1 through 13 (of 13 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Homework? What have you tried so far? Where are you stuck?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Tallur Valued Member Points: 63 More actions · Answer 2

Yes it's a home work. I need an idea to start up and solve the query!!!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Here's an idea. Try it yourself. If you get stuck, ask for help. You learn nothing by having someone give you the answer.

Hint: Sum and Group By

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

kapil_kk SSC-Insane Points: 21316 More actions · Answer 4

can you please post the query that you have written so that we can see what you have tried and help you

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 5

ramya.usapp (9/1/2013)
Hello friends,
Please help me in writing a query!!
Question:
Write a query based off of the following data stored in the AccountingEntry table. The query needs to return running totals and should produce the exact results for each customer listed below.
Deposits – amounts added to an account
Charge – amounts should be subtracted from a deposit
Cancel/Refund – amount returned to a customer, should be subtracted from a deposit
AccountingEntryIDCustomerIDDateTypeAmount
111/1/2013Deposit400
231/23/2013Deposit900
3192/28/2013Deposit250
4233/15/2013Charge175
512/1/2013Charge350
6154/1/2013Deposit2000
732/23/2013Charge500
8154/1/2013Charge100
912/23/2013Deposit100
10233/15/2013Charge175
11154/1/2013Charge750
1212/15/2013Charge25
13154/1/2013Cancel/Refund1150
1431/25/2013Deposit100
15154/1/2013Deposit750
16193/28/2013Charge100
17154/1/2013Deposit100
1833/23/2013Charge500
19233/15/2013Deposit400
20194/28/2013Charge100
2113/15/2013Cancel/Refund125
Results should look like
AccountingEntryIDCustomerIDDateTypeAmountRunning DepositRunningAmount ChargedRunningAmt Remaining
111/1/2013Deposit4004000400
512/1/2013Charge35040035050
1212/15/2013Charge2540037525
912/23/2013Deposit100500375125
2113/15/2013Cancel/Refund1255003750
Thanks,
RT

Since this is homework, you must have an instructor. There are 3 typical ways of solving this problem in SQL Server 2008... Cursor/While Loop, Temp Table/While Loop, Correlated Sub-Query (which would form a "Triangular Join" which is very bad but the instructor may be teaching it).

There' also an atypical method known as the "Quirky Update" which will blow all 3 of those methods out of the water but it's not likely that your instructor taught it or even knows anything about it. Same goes for another atypical method (the "Multi-Pass" update) that a chap by the name of "Hugo" wrote.

With all that in mind, my question to you is what has your instructor recently covered out of the 5 things mentioned above so that we can play into what the instructor actually wants to see?

Also, if you want much better help, please see the first "Helpful Link" in my signature line below.

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

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 6

Jeff Moden (9/2/2013)
Same goes for another atypical method (the "Multi-Pass" update) that a chap by the name of "Hugo" wrote.

I assume that you mean set-based iteration. That is by no means an atypical method, but one a good SQL programmer should master.

Of course, for this particular problem, the correct solution is an ordered aggregate. No that is not possible on SQL 2008, but it's reasonable to assume that a class would teach from the most recent version. At least if the features are ANSI-compatible.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

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

Erland Sommarskog (9/2/2013)
I assume that you mean set-based iteration. That is by no means an atypical method, but one a good SQL programmer should master.

Yes and absolutely agreed. That's why I called it atypical though. 😉

Of course, for this particular problem, the correct solution is an ordered aggregate. No that is not possible on SQL 2008, but it's reasonable to assume that a class would teach from the most recent version. At least if the features are ANSI-compatible.

I guess "possible" depends a lot on whether or not you consider the "Quirky Update" to be a form of ordered aggregate or not but I do get your point. As reasonable as it sounds, though, I would't assume that any given class has been updated to the latest version epecially when the OP posted in a 2008 forum.

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

Dwain Camps SSC Guru Points: 86908 More actions · Answer 8

Jeff Moden (9/2/2013)
Same goes for another atypical method (the "Multi-Pass" update) that a chap by the name of "Hugo" wrote.

My curiosity has been piqued. Would this be Hugo Kornelski and where would this atypical method be elaborated?

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

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

dwain.c (9/2/2013)
Jeff Moden (9/2/2013)
Same goes for another atypical method (the "Multi-Pass" update) that a chap by the name of "Hugo" wrote.
My curiosity has been piqued. Would this be Hugo Kornelski and where would this atypical method be elaborated?

No. Different Hugo... Hugo Kornelis.

Here's his original post on the subject...

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

Here's the post where I made a suggestion that cut about a 1/3rd out of the duration (same thread)...

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

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

Dwain Camps SSC Guru Points: 86908 More actions · Answer 10

Jeff Moden (9/2/2013)
dwain.c (9/2/2013)
Jeff Moden (9/2/2013)
Same goes for another atypical method (the "Multi-Pass" update) that a chap by the name of "Hugo" wrote.
My curiosity has been piqued. Would this be Hugo Kornelski and where would this atypical method be elaborated?
No. Different Hugo... Hugo Kornelis.
Here's his original post on the subject...
http://www.sqlservercentral.com/Forums/FindPost816917.aspx
Here's the post where I made a suggestion that cut about a 1/3rd out of the duration (same thread)...
http://www.sqlservercentral.com/Forums/FindPost816964.aspx

Hah! Same Hugo but I forgot how to spell his name! :w00t:

Thanks Jeff!

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

iirc he also wrote a chapter in one of the MVP Deep Dives on the subject.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 12

Jeff Moden (9/2/2013)
I guess "possible" depends a lot on whether or not you consider the "Quirky Update" to be a form of ordered aggregate or not

If by "quirky update" you mean that you use the UPDATE statement and you get a desired solution when there are certain indexes in place, stars are aligned, I don't consider that to be an ordered aggregate or an acceptable solution at all for that matter.

As reasonable as it sounds, though, I would't assume that any given class has been updated to the latest version epecially when the OP posted in a 2008 forum.

The instructor that does not accept an ANSI-compatible solution that runs on the latest version is grossly incompetent.

That said, a good instructor should encourage his students to try different solutions, since not all platforms support ordered aggregates. There is certainly all reason to teach the solution with the correlated subquery, despite its dreadful performance. After all, the purpose of teaching SQL is not only to get the students to learn specific patterns, but also to learn the building blocks, and to that end the correlated subquery for running sums is an excellent exercise.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

Jeff Moden (9/2/2013)
ramya.usapp (9/1/2013)
Hello friends,
Please help me in writing a query!!
Question:
Write a query based off of the following data stored in the AccountingEntry table. The query needs to return running totals and should produce the exact results for each customer listed below.
Deposits – amounts added to an account
Charge – amounts should be subtracted from a deposit
Cancel/Refund – amount returned to a customer, should be subtracted from a deposit
AccountingEntryIDCustomerIDDateTypeAmount
111/1/2013Deposit400
231/23/2013Deposit900
3192/28/2013Deposit250
4233/15/2013Charge175
512/1/2013Charge350
6154/1/2013Deposit2000
732/23/2013Charge500
8154/1/2013Charge100
912/23/2013Deposit100
10233/15/2013Charge175
11154/1/2013Charge750
1212/15/2013Charge25
13154/1/2013Cancel/Refund1150
1431/25/2013Deposit100
15154/1/2013Deposit750
16193/28/2013Charge100
17154/1/2013Deposit100
1833/23/2013Charge500
19233/15/2013Deposit400
20194/28/2013Charge100
2113/15/2013Cancel/Refund125
Results should look like
AccountingEntryIDCustomerIDDateTypeAmountRunning DepositRunningAmount ChargedRunningAmt Remaining
111/1/2013Deposit4004000400
512/1/2013Charge35040035050
1212/15/2013Charge2540037525
912/23/2013Deposit100500375125
2113/15/2013Cancel/Refund1255003750
Thanks,
RT
Since this is homework, you must have an instructor. There are 3 typical ways of solving this problem in SQL Server 2008... Cursor/While Loop, Temp Table/While Loop, Correlated Sub-Query (which would form a "Triangular Join" which is very bad but the instructor may be teaching it).
There' also an atypical method known as the "Quirky Update" which will blow all 3 of those methods out of the water but it's not likely that your instructor taught it or even knows anything about it. Same goes for another atypical method (the "Multi-Pass" update) that a chap by the name of "Hugo" wrote.
With all that in mind, my question to you is what has your instructor recently covered out of the 5 things mentioned above so that we can play into what the instructor actually wants to see?
Also, if you want much better help, please see the first "Helpful Link" in my signature line below.

Hey Jeff! Don't forget the good ol' rCTE!

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden