Can this be done? - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Can this be done?

Author

Topic

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-02-18 : 08:55:24

I have 2 tables, table detail_records and table customer_master.

In table detail records I have these fields;

detail_records.ticket_number varchar(10)
detail_records.customer_bill_to varchar(10) (customer_master.customer_number)
detail_records.customer_delv_to varchar(10) (customer_master.customer_number)
detail_records.pickup_dt char(8)
detail_records.deliver_dt char(8)
detail_records.pickup_weight int

in customer_master I have these fields;

customer_master.customer_number varchar(10)
customer_master.customer_name varchar(60)
customer_master.city varchar(30)

I know I can use a duplicate of table customer_master to get customer bill to and customer deliver to, call them customer_master_bill_to and customer_master_deliver_to. So I have a query that is like this;

SELECT detail_record.detail_number, detail_record.ticket_number, CAST(detail_record.pickup_dt AS datetime) AS PickupDate, CAST(detail_record.deliver_dt AS datetime) AS DeliverDate, detail_record.customer_delv_to, detail_record.customer_bill_to,
customer_master_bill_to.customer_number AS bill_to_number, customer_master_bill_to.customer_name AS bill_to_name, customer_master_bill_to.city AS bill_to_city,
customer_master_delv_to.customer_number AS deliver_to_number, customer_master_delv_to.customer_name AS deliver_to_name, customer_master_delv_to.city AS deliver_to_city,
detail_record.pickup_weight
FROM detail_record INNER JOIN
customer_master AS customer_master_Bill_To ON detail_record.customer_bill_to = customer_master_Bill_To .customer_number INNER JOIN
customer_master AS customer_master_Delv_To ON detail_record.customer_delv_to = customer_master_Delv_To.customer_number

WHERE (detail_record.pickup_date BETWEEN @start_date AND @end_date) ,,,,,,,,,,,,,,,,,,,

now what I need is the pickup_weight for the customer_delv_to and the customer customer_bill_to to be separated out. I am looking for something like customer_bill_to_weight and customer_deliver_to_weight

Sample selection of the table detail_record I am using

		Table detail_record			
ticket	  customer	customer 	pickup 
number	  bill_to	deliver_to	weight
488082	  900101	   421	         4112
488082	  900101	   421	         3978
488082	  900101	   421	         2351
488082	  900101	   421	         2077
363987	  900021	  9311	         5160
363987	  900021	  9311	        31801
363987	  900021	  9311	         8183
7559	  421	         13070	         1704
7560	  421	         13070	         3926
7560	  421	         13070	         4074

Thanks for the help
CoachBarker

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-18 : 09:01:44

how will you know which weight goes to customer to and which to customer from?

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-02-18 : 09:18:42

I guess that is what I am trying to figure out how to accomplish or am I trying to do something that can not be done. It might be that I am looking at the report I am supposed to create the wrong way. Basically I have paper copies of sveral reports generated from a HP system, and from looking at the fields on the report I am supposed to geneerate the same reort using SSRS and Sql Server.NOt really a lot of information to go on.

Thanks for the help
CoachBarker

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-18 : 09:26:35

but what i cant understand is whether each row pickupweight value contains delivery to & froms accumulated value or does they exist in seperate rows?

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-02-18 : 09:43:36

In each row you have the customer billed to, the customer delivered to and then the pickup weight.

I think so that I am not wasting your time and efforts on this problem I will see if someone can explain to me precisely what I am supposed to be figuring out. I will post back on this thread when I have an answer to your questions.

Thanks for the help
CoachBarker

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-18 : 09:46:59

ok...thats fine

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources