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-03-05 : 09:05:34
|
I have a query that I have to expand one more level to get the results that I need, I started with this query:SELECT CAST(detail_record.ticket_number AS int) AS TicketNumber, cmDelvTo.customer_number, cmBillTo.customer_number, CAST(detail_record.pickup_dt AS datetime) AS PickupDate, CAST(detail_record.deliver_dt AS datetime) AS DeliverDate,detail_record.division AS Division, detail_record.pickup_weight AS PickupPounds, detail_record.ddp_weight AS LoadPounds, detail_record.hauler_number AS HaulerNumberFROM detail_record INNER JOIN customer_master AS cmBillTo ON detail_record.customer_bill_to = cmBillTo.customer_number INNER JOIN customer_master AS cmDelvTo ON detail_record.customer_delv_to = cmDelvTo.customer_numberWHERE (detail_record.pickup_date BETWEEN @start_date AND @end_date) AND (detail_record.customer_delv_to = @customer_number) OR (detail_record.pickup_date BETWEEN @start_date AND @end_date) AND (detail_record.customer_bill_to = @customer_number)ORDER BY detail_record.pickup_dt and the results were:I then modified the query to this to get rid of the repeating rows in the Division column:SELECT CAST(detail_record.ticket_number AS int) AS TicketNumber, cmDelvTo.customer_number AS DeliverToNumber, cmBillTo.customer_number AS BillToNumber, CAST(detail_record.pickup_dt AS datetime) AS PickupDate, CAST(detail_record.deliver_dt AS datetime) AS DeliverDate,detail_record.division AS Division, SUM(detail_record.pickup_weight) AS PickupPounds,SUM(DISTINCT detail_record.ddp_weight) AS LoadPounds, detail_record.hauler_number AS HaulerNumberFROM detail_record INNER JOIN customer_master AS cmBillTo ON detail_record.customer_bill_to = cmBillTo.customer_number INNER JOIN customer_master AS cmDelvTo ON detail_record.customer_delv_to = cmDelvTo.customer_numberWHERE (detail_record.pickup_date BETWEEN @start_date AND @end_date) AND (detail_record.customer_delv_to = @customer_number) OR (detail_record.pickup_date BETWEEN @start_date AND @end_date) AND (detail_record.customer_bill_to = @customer_number)GROUP BY cmBillTo.customer_number, cmDelvTo.customer_number, detail_record.pickup_dt, detail_record.ticket_number, detail_record.division, detail_record.deliver_dt, detail_record.hauler_numberORDER BY detail_record.pickup_dt And the results were:I now need to get the DISTINT SUM of the LoadPounds for the PickupDate. Example;PickupDate LoadPounds2009-01-31 177492009-01-31 177492009-01-31 430762009-01-31 43076DistinctSum 60825Thanks for the helpCoachBarker |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-03-05 : 09:18:33
|
[code]DECLARE @Sample TABLE ( i INT )INSERT @SampleSELECT 100 UNION ALLSELECT 100 UNION ALLSELECT 200 UNION ALLSELECT 200 UNION ALLSELECT 300SELECT SUM(DISTINCT i)FROM @Sample[/code] E 12°55'05.63"N 56°04'39.26" |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-05 : 13:02:55
|
you last posted query is not correct. please post corrected query |
|
|
CoachBarker
Posting Yak Master
170 Posts |
Posted - 2009-03-05 : 13:34:41
|
I know it isn't, that was why I was trying to see how a union would be used.All we need to do is get the ticket number and the loads pounds and separate them out and still be able to use them in the report.Thanks for the helpCoachBarker |
|
|
CoachBarker
Posting Yak Master
170 Posts |
Posted - 2009-03-06 : 13:00:44
|
So I have my original querySELECT CAST(detail_record.ticket_number AS int) AS TicketNumber, cmDelvTo.customer_number AS DeliverToNumber, cmDelvTo.customer_name AS DeliverToName, cmDelvTo.city AS DeliverToCity, cmBillTo.customer_number AS BillToNumber, cmBillTo.customer_name AS BillToName, cmBillTo.city AS BillToCIty, CAST(detail_record.pickup_dt AS datetime) AS PickupDate, CAST(detail_record.deliver_dt AS datetime) AS DeliverDate, detail_record.division AS Division, SUM(detail_record.pickup_weight) AS PickupPounds, SUM (DISTINCT detail_record.ddp_weight) AS LoadPounds,detail_record.hauler_number AS HaulerNumberFROM detail_record INNER JOIN customer_master AS cmBillTo ON detail_record.customer_bill_to = cmBillTo.customer_number INNER JOIN customer_master AS cmDelvTo ON detail_record.customer_delv_to = cmDelvTo.customer_number WHERE (detail_record.pickup_date BETWEEN @start_date AND @end_date) AND (detail_record.customer_delv_to = @customer_number) OR (detail_record.pickup_date BETWEEN @start_date AND @end_date) AND (detail_record.customer_bill_to = @customer_number) GROUP BY cmBillTo.customer_number, cmBillTo.customer_name, cmBillTo.city, cmDelvTo.customer_number, cmDelvTo.customer_name, cmDelvTo.city, detail_record.pickup_dt, detail_record.ticket_number,detail_record.division, detail_record.deliver_dt, detail_record.hauler_number, detail_record.pickup_date, detail_record.ddp_weightOrder BY detail_record.pickup_dt And I have done thisCREATE TABLE distinct_loadpounds( ticket_number int, ddp_weight int)INSERT INTO distinct_loadpoundsSELECT ticket_number,SUM(Distinct ddp_weight)FROM detail_recordGroup By ticket_number How do I use them together?Thanks for the helpCoachBarker |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-07 : 13:40:17
|
join on to main query on ticket_number field |
|
|
CoachBarker
Posting Yak Master
170 Posts |
Posted - 2009-03-08 : 11:28:35
|
How I haven't been able to find any examples of how to do that.Thanks for the helpCoachBarker |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-08 : 12:52:24
|
something likeSELECT CAST(detail_record.ticket_number AS int) AS TicketNumber, cmDelvTo.customer_number AS DeliverToNumber, cmDelvTo.customer_name AS DeliverToName, cmDelvTo.city AS DeliverToCity, cmBillTo.customer_number AS BillToNumber, cmBillTo.customer_name AS BillToName, cmBillTo.city AS BillToCIty, CAST(detail_record.pickup_dt AS datetime) AS PickupDate, CAST(detail_record.deliver_dt AS datetime) AS DeliverDate, detail_record.division AS Division, SUM(detail_record.pickup_weight) AS PickupPounds, SUM (DISTINCT detail_record.ddp_weight) AS LoadPounds,detail_record.hauler_number AS HaulerNumber,MAX(ddp_weight) AS ddp_weightFROM detail_record INNER JOIN customer_master AS cmBillTo ON detail_record.customer_bill_to = cmBillTo.customer_number INNER JOIN customer_master AS cmDelvTo ON detail_record.customer_delv_to = cmDelvTo.customer_number INNER JOIN distinct_loadpounds dl ON dl.ticket_number= detail_record.ticket_number WHERE (detail_record.pickup_date BETWEEN @start_date AND @end_date) AND (detail_record.customer_delv_to = @customer_number) OR (detail_record.pickup_date BETWEEN @start_date AND @end_date) AND (detail_record.customer_bill_to = @customer_number) GROUP BY cmBillTo.customer_number, cmBillTo.customer_name, cmBillTo.city, cmDelvTo.customer_number, cmDelvTo.customer_name, cmDelvTo.city, detail_record.pickup_dt, detail_record.ticket_number,detail_record.division, detail_record.deliver_dt, detail_record.hauler_number, detail_record.pickup_date, detail_record.ddp_weightOrder BY detail_record.pickup_dt |
|
|
CoachBarker
Posting Yak Master
170 Posts |
Posted - 2009-03-08 : 13:25:13
|
I will break out my work laptop and see how that works, I see the logic in it, but sometimes queriies seem logical but still don't compile.Thanks for the helpCoachBarker |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-08 : 13:27:13
|
let me know how you got on |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2009-03-08 : 14:18:37
|
What's the primary key of detail_record? |
|
|
CoachBarker
Posting Yak Master
170 Posts |
Posted - 2009-03-08 : 14:46:34
|
Actually the PK of the detail_record is detail_number. Why? It isn't in the query because it isn't required in the report. The relationship is one detail_number can have many ticket_numbers.Thanks for the helpCoachBarker |
|
|
CoachBarker
Posting Yak Master
170 Posts |
Posted - 2009-03-09 : 09:05:15
|
The query as it is returns the same values as the original LoadPounds column, maybe I did not explain correctly, if so I am sorry. SUM of the MAX LoadPounds For the PickupDate. If I am wording it correctly.Like this example: TicketNumberPickupDate LoadPounds567435 2009-01-31 17749567435 2009-01-31 17749567634 2009-01-31 43076567634 2009-01-31 43076DistinctSum 60825Thanks for the helpCoachBarker |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2009-03-09 : 10:10:30
|
So if you want a result that has one row per ticket_number, why are you including division in the columns that the query is grouped by, when ticket_number does not uniquely determine the value of division?quote: Originally posted by CoachBarker Actually the PK of the detail_record is detail_number. Why?
I was hoping it might shed some light on what it is you're trying to group rows in detail_record on to get your sums of weights.I suppose I could ask where detail_numbers come from, and whether there are any other unique constraints on detail_record, but I fear the answers will be "it's an IDENTITY" and "no".You see, there are a number of things I don't get about detail_record on the evidence of the data you've posted. The values of several columns (customer_bill_to, customer_delv_to, pickup_date (or is it pickup_dt?), deliver_dt, ddp_weight, hauler_number) seem to be determined by the ticket_number. Moreover, ddp_weight is the sum of the pickup_weights for that ticket_number. Is that always true? If it is, either detail_record is a really a view that joins two (or more) other tables or detail_record is massively denormalized. |
|
|
CoachBarker
Posting Yak Master
170 Posts |
Posted - 2009-03-09 : 10:49:23
|
Because in the report Division is the lowest grouping, and unfortunately as far as the design of the tables I have no control over that.Tables;1. detail_record2. customer_master(cmDelv and cmBillTo)In my report I need to get the PickupPounds(which is the sum of the pickup_weight in the detail_ record based on the number of the same ticket_numbers)Then I also need the LoadPounds(which is the SUM of the ddp_pounds for the same ticket_number) and there is where the problem is, there can be multiple LoadPounds because there are mutiple ticket_numbers. Like this example: TicketNumberPickupDate LoadPounds567435 2009-01-31 17749567435 2009-01-31 17749567634 2009-01-31 43076567634 2009-01-31 43076So we need to get the sum of 17749 and 43076 which is 60825. If your confused guess how I feel Or would it be the MAX of LoadPounds each ticket_number and/or pickup_dtThanks for the helpCoachBarker |
|
|
CoachBarker
Posting Yak Master
170 Posts |
Posted - 2009-03-09 : 12:46:34
|
Can the last bit of this problem be done in the report using an expression to sum up the load pounds (getting rid of the repeating numbers)???Now shouldn't the part of the query in red be returning the SUM of the ddp_weight filed minus the repeating numbers? And they are being put in a temp table called dl? SELECT CAST(detail_record.ticket_number AS int) AS TicketNumber, cmDelvTo.customer_number AS DeliverToNumber, cmDelvTo.customer_name AS DeliverToName, cmDelvTo.city AS DeliverToCity, cmBillTo.customer_number AS BillToNumber, cmBillTo.customer_name AS BillToName, cmBillTo.city AS BillToCIty, CAST(detail_record.pickup_dt AS datetime) AS PickupDate, CAST(detail_record.deliver_dt AS datetime) AS DeliverDate, detail_record.division AS Division, SUM(detail_record.pickup_weight) AS PickupPounds, detail_record.hauler_number AS HaulerNumber, SUM(DISTINCT dl.ddp_weight) AS MaxLoadPounds FROM detail_record INNER JOIN customer_master AS cmBillTo ON detail_record.customer_bill_to = cmBillTo.customer_number INNER JOIN customer_master AS cmDelvTo ON detail_record.customer_delv_to = cmDelvTo.customer_number INNER JOIN distinct_loadpounds AS dl ON dl.ticket_number = detail_record.ticket_numberWHERE (detail_record.pickup_date BETWEEN @start_date AND @end_date) AND (detail_record.customer_delv_to = @customer_number) OR (detail_record.pickup_date BETWEEN @start_date AND @end_date) AND (detail_record.customer_bill_to = @customer_number)GROUP BY cmBillTo.customer_number, cmBillTo.customer_name, cmBillTo.city, cmDelvTo.customer_number, cmDelvTo.customer_name, cmDelvTo.city, detail_record.pickup_dt, detail_record.ticket_number, detail_record.division, detail_record.deliver_dt, detail_record.hauler_number, detail_record.pickup_date, detail_record.ddp_weight, dl.ddp_weightORDER BY detail_record.pickup_dt Thanks for the helpCoachBarker |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-10 : 03:19:22
|
the last bit can be done in report using IIF() expression. It doesnt subtract repeating numbers, it just sums up distinct values in ddp_weight field, so result wont alwyas be what you're looking at. you may be btter of returning another bit field to distinguish unique values of group and then sum based on it |
|
|
CoachBarker
Posting Yak Master
170 Posts |
Posted - 2009-03-10 : 06:14:52
|
An example if you would please.Thanks for the helpCoachBarker |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-10 : 06:23:42
|
suppose if your bit to identify distinct records is dist(you need to return it in your query), then in report just doSUM(IIF(Fields!dist.value=1,Fields!ddp_weight.value,0)) |
|
|
CoachBarker
Posting Yak Master
170 Posts |
Posted - 2009-03-10 : 06:31:35
|
So are you saying to count the rows of the bit such as COUNT(ddp_weight) then base the IIF off that?Thanks for the helpCoachBarker |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-10 : 06:36:17
|
sorry didnt understand what you're telling. for count you need thisCOUNT(IIF(Fields!dist.value=1,Fields!ddp_weight.value,Nothing)) |
|
|
Next Page
|
|
|
|
|