Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
CoachBarker
Posting Yak Master
170 Posts |
 Posted - 2009-01-06 : 08:44:30
|
| Again this is the procedure I am using in my report, and through trial and error I am slowly putting it together.SELECT detail_record.division, detail_record.pickup_weight, detail_record.pickup_dt,detail_record.customer_delv_to, detail_record.customer_bill_to, customer_master.customer_number, customer_master.customer_name, customer_master.city, detail_record.pickup_date, detail_record.ticket_number FROM detail_record INNER JOIN customer_master ON detail_record.customer_bill_to = customer_master.customer_number AND detail_record.customer_delv_to = customer_master.customer_numberWHERE (detail_record.customer_bill_to = @customer_number) AND (detail_record.customer_delv_to = @customer_number) AND (detail_record.pickup_date BETWEEN @StartDate AND @EndDate)ORDER BY detail_record.division, detail_record.pickup_weightWhat I need the SPROC to do is get the customer_name based on the customer_number from the customer_master table being equal to detail_record.customer_bill_to anddetail_record.customer_delv_to (they are both cutomer_numbers)Thanks for the helpCoachBarker |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-06 : 08:54:43
|
| According to your explanation, your code looks fine. so will detail_record.customer_bill_to & detail_record.customer_delv_to always have same value? also whats the problem you're facing? |
 |
|
|
CoachBarker
Posting Yak Master
170 Posts |
Posted - 2009-01-06 : 09:08:24
|
| No BillTo and DelvTo can be different .I want to return the CustomerName based on the Number so I can set the value of the textboxes to CustomerNameThanks for the helpCoachBarker |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-06 : 09:39:08
|
quote:
Originally posted by CoachBarker
No BillTo and DelvTo can be different .I want to return the CustomerName based on the Number so I can set the value of the textboxes to CustomerNameThanks for the helpCoachBarker
then shouldnt you be returning two customer names one corresponding to each id. Also, whats the purpose of below where condition in which you're looking for only records with both values same as passed parameter value?WHERE (detail_record.customer_bill_to = @customer_number) AND (detail_record.customer_delv_to = @customer_number) |
|
|
|
CoachBarker
Posting Yak Master
170 Posts |
Posted - 2009-01-06 : 10:11:50
|
quote:
then shouldnt you be returning two customer names one corresponding to each id. Also, whats the purpose of below where condition in which you're looking for only records with both values same as passed parameter value?WHERE (detail_record.customer_bill_to = @customer_number) AND (detail_record.customer_delv_to = @customer_number)
I changed the WHERE clause back to the original right after I posted. That is what I am trying to figure, return 2 customer names from the existing query.Thanks for the helpCoachBarker |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-06 : 10:42:20
|
| [code]SELECT detail_record.division, detail_record.pickup_weight, detail_record.pickup_dt,detail_record.customer_delv_to, detail_record.customer_bill_to, cm1.customer_number, cm1.customer_name,cm1.city,cm2.customer_number, cm2.customer_name, cm2.city, detail_record.pickup_date, detail_record.ticket_numberFROM detail_record INNER JOINcustomer_master cm1 ON detail_record.customer_bill_to = cm1.customer_number INNER JOINcustomer_master cm2 ON detail_record.customer_delv_to = cm2.customer_number...........[/code] |
|
|
|
CoachBarker
Posting Yak Master
170 Posts |
Posted - 2009-01-06 : 14:50:26
|
| That works much better thanks, I forgot all about creating pseudo tables, I had only done it a few times in school.Thanks for the helpCoachBarker |
|
|
|
|
|
|
|
|
Creating Stored Procedure