September 22, 2015 at 9:35 am
sqlizcool (9/22/2015)
Hi Guys,I am a sysadmin being asked to collect data from sql DBs on our app servers. Each app server has a group of customers that use a specific product. On these servers we store what is owed per customer. For each app server I am trying to satisfy the following requirements
1) list the customer and what they owe.
2) get a total aggregate amount of what is owed, or the total amount owed for all customers.
The database stores a customer ID and the amount of money they owe in 2 columns of table 1.
Table 1 has CUSTID and AMOUNT columns
So I can satisfy need 2, the aggregate amount owed for the server here by totaling the AMOUNT column.
I cannot satisfy requirement 1 of listing customer name because this table does not have the actual Name.
I have a second table that has the customer ID and the customer name .
Table 2 has CUSTID and CUSTNAME
So I am looking for a query that will correlate the CUSTID from table 1 to the CUSTNAME in table 2 and then output "CUSTNAME" and "AMOUNT"
Then ouput the total amount owed for each server or the totaled "AMOUNT" column from table 1.
I am able to do this in powershell but I query the table 1 for CUSTID and AMOUNT to a file then loop through and query table 2 for the "CUSTNAME" once for each "CUSTID" to associate the "CUSTID" to the "CUSTNAME". I am pretty sure this is inefficient and can be done all from an sql query.
Can you guys please help me?
Thanks!
The way you do this is with joins. This is the very basis of extracting relational data from a database. Here is an excellent visual on the topic. http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/%5B/url%5D
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 22, 2015 at 10:17 am
Thank you for teaching me how to fish, I really appreciate the tip. I was able to use join to grab the data. I am 100% more efficient than I was yesterday.
September 22, 2015 at 12:25 pm
sqlizcool (9/22/2015)
Thank you for teaching me how to fish, I really appreciate the tip. I was able to use join to grab the data. I am 100% more efficient than I was yesterday.
That is great and thanks for letting me know. This is not a super easy topic and you seem to have a great attitude towards it. Please come back and post again anytime you need some help. We are rather unique at this site that we like to teach you how to fish instead of handing a nicely cooked fillet. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply