SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query on 2 tables do I need a 3rd?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

hbadministrator
Posting Yak Master

118 Posts

Posted - 11/07/2012 :  12:42:53  Show Profile  Reply with Quote
So so confused on how to fix this issue.

I have a query that takes info from a table called SABudget.

its Customer number, its Charge Customer the period and the amount. and another table called Customer that links the Customer number and allows me to match up the Address city st and zip. The issue is I also need the address for the Charge customer as well. Below is an example of query. I will also give you example of data and how i would like the data to look afterwards.

SELECT SaBudget.[Cust-no], customer.name, customer.Address, customer.City, customer.St, customer.[Zip-code], customer.Telephone, customer.[extra-field4], SaBudget.Amount, SaBudget.[Charge-cust], CONVERT(CHAR(10), SaBudget.CreateDate, 101) AS 'Create_Date', SaBudget.[sa-prd]
FROM customer AS customer INNER JOIN SaBudget AS SaBudget ON customer.[Cust-no] = SaBudget.[Cust-no]

010268, Tenant, 132 B St, Carlisle, PA, 17013-1907, 7175994582, 33.00, 010268M, 09/17/2012,10

010268, Tenant, 132 B St, Carlisle, PA, 17013-1907, 7175994582, 33.00, 010268M, 09/17/2012,11

010268, Tenant, 132 B St, Carlisle, PA, 17013-1907, 7175994582, 33.00, 010268M, 09/17/2012,12



Now notice that the customer number is 010268 and notice that the charge customer is 010268M. They are different They also have Different addresses. the Customer address is this 132 B St, Carlisle, PA, 17013-1907 the Charge customer address is this 444 B St, Carlisle, PA, 17013-1907. What I would like to see is the data show me the customers address and also the charge customer address on the same line. like this
010268, Tenant, 132 B St, Carlisle, PA, 17013-1907, 7175994582, 33.00, 010268M, 09/17/2012,10, 444 B St, Carlisle, PA, 17013-1907

010268, Tenant, 132 B St, Carlisle, PA, 17013-1907, 7175994582, 33.00, 010268M, 09/17/2012,11, 444 B St, Carlisle, PA, 17013-1907

010268, Tenant, 132 B St, Carlisle, PA, 17013-1907, 7175994582, 33.00, 010268M, 09/17/2012,12, 444 B St, Carlisle, PA, 17013-1907

Those fields could be called charge_add, charge_st, charge_zip


shilpash
Posting Yak Master

103 Posts

Posted - 11/07/2012 :  13:25:24  Show Profile  Reply with Quote

So where is this 444 B St, Carlisle, PA, 17013-1907 coming from.I am guessing from SABudget.If so,simply add those columns--

SELECT SaBudget.[Cust-no]
,customer.name
,customer.Address
,customer.City
,customer.St
,customer.[Zip-code]
,customer.Telephone
,customer.[extra-field4]
,SaBudget.Amount
,SaBudget.[Charge-cust]
,charge_add,=SaBudget.ADDRESS
,charge_st=SaBudget.St
,charge_zip =SaBudget.[Zip-code]
,CONVERT(CHAR(10),SaBudget.CreateDate,101) AS 'Create_Date'
,SaBudget.[sa-prd]

FROM customer AS customer
INNER JOIN SaBudget AS SaBudget
ON customer.[Cust-no] = SaBudget.[Cust-no]
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 11/07/2012 :  13:26:59  Show Profile  Visit chadmat's Homepage  Reply with Quote
Join to the customer table again using Charge-cust = [Cust-no], if I am understanding correctly, that should give you the Charge Customer Address.

-Chad
Go to Top of Page

hbadministrator
Posting Yak Master

118 Posts

Posted - 11/07/2012 :  14:09:15  Show Profile  Reply with Quote
I actually just did that lol. Now that I completed that they are asking for more and this part I am kinda confused. I think I might need to make a nested Select statement. I need to add another table that hold AR items. Now each Customer may have 50 records that are single line but I need to sum them up first and just need 2 fields from that table the Cust-no and the bal_amt. example
Table called AR_items
Data
Cust-No, bal_amt
010268, 57.00
010268, 57.00
010268, 103.00
010268, 234.00

I need these lines totaled up and then look like this.
010268, 451.00 so when I tie it to the query its already summed and will tie to the report.

SELECT SaBudget.[Cust-no], customer.name, customer.Address, customer.City, customer.St, customer.[Zip-code], customer.Telephone, customer.[extra-field4],
SaBudget.Amount, SaBudget.[Charge-cust], CONVERT(CHAR(10), SaBudget.CreateDate, 101) AS 'Create_Date', SaBudget.[sa-prd], customer_1.Address AS charge_add,
customer_1.City AS charge_city, customer_1.St AS charge_st, customer_1.[Zip-code] AS charge_zip, customer_1.name AS charge_name
FROM customer AS customer INNER JOIN
SaBudget AS SaBudget ON customer.[Cust-no] = SaBudget.[Cust-no] LEFT OUTER JOIN
customer AS customer_1 ON SaBudget.[Charge-cust] = customer_1.[Cust-no]

Now
Go to Top of Page

shilpash
Posting Yak Master

103 Posts

Posted - 11/07/2012 :  14:20:32  Show Profile  Reply with Quote
WITH Totalbalamt_cte
AS (SELECT [Cust-no]
,SUM(bal_amt) bal_amt
FROM AR_items
GROUP BY [Cust-no]
)
SELECT SaBudget.[Cust-no]
,customer.name
,customer.Address
,customer.City
,customer.St
,customer.[Zip-code]
,customer.Telephone
,customer.[extra-field4]
,SaBudget.Amount
,SaBudget.[Charge-cust]
,CONVERT(CHAR(10),SaBudget.CreateDate,101) AS 'Create_Date'
,SaBudget.[sa-prd]
,customer_1.Address AS charge_add
,customer_1.City AS charge_city
,customer_1.St AS charge_st
,customer_1.[Zip-code] AS charge_zip
,customer_1.name AS charge_name
,Totalbalamt_cte.bal_amt

FROM customer AS customer
INNER JOIN SaBudget AS SaBudget
ON customer.[Cust-no] = SaBudget.[Cust-no]
LEFT OUTER JOIN customer AS customer_1
ON SaBudget.[Charge-cust] = customer_1.[Cust-no]
LEFT OUTER JOIN Totalbalamt_cte
ON Totalbalamt_cte.[Cust-no]=SaBudget.[Cust-no]
Go to Top of Page

hbadministrator
Posting Yak Master

118 Posts

Posted - 11/07/2012 :  14:32:01  Show Profile  Reply with Quote
I actually created a view then linked it. Code looks like this.

SELECT SaBudget.[Cust-no], customer.name, customer.Address, customer.City, customer.St, customer.[Zip-code], customer.Telephone, customer.[extra-field4],
SaBudget.Amount, SaBudget.[Charge-cust], CONVERT(CHAR(10), SaBudget.CreateDate, 101) AS 'Create_Date', SaBudget.[sa-prd], customer_1.Address AS charge_add,
customer_1.City AS charge_city, customer_1.St AS charge_st, customer_1.[Zip-code] AS charge_zip, customer_1.name AS charge_name, ARBal.[bal-amt]
FROM customer AS customer INNER JOIN
SaBudget AS SaBudget ON customer.[Cust-no] = SaBudget.[Cust-no] LEFT OUTER JOIN
ARBal ON SaBudget.[Charge-cust] = ARBal.[Cust-no] LEFT OUTER JOIN
customer AS customer_1 ON SaBudget.[Charge-cust] = customer_1.[Cust-no]
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000