September 20, 2012 at 1:11 pm
I get the above message with this query:
select a.deliverydate, count (distinct (a.orderno)) as Orders, count (distinct(a.sku)) as Lines,
SUM(a.QuantityShipped/b.UoM) as Cases, SUM(a.QuantityShipped*b.length*b.width*b.height/12/12/12) as CubicFeet
from dbo.COI a, dbo.IM b
where
a.sku = b.SKUNumber
group by a.deliverydate, a.SKU
order by a.deliverydate
September 20, 2012 at 1:25 pm
jwillbanks (9/20/2012)
I get the above message with this query:select a.deliverydate, count (distinct (a.orderno)) as Orders, count (distinct(a.sku)) as Lines,
SUM(a.QuantityShipped/b.UoM) as Cases, SUM(a.QuantityShipped*b.length*b.width*b.height/12/12/12) as CubicFeet
from dbo.COI a, dbo.IM b
where
a.sku = b.SKUNumber
group by a.deliverydate, a.SKU
order by a.deliverydate
Need to see the CREATE TABLE statements for the two tables involved in this query.
September 20, 2012 at 1:48 pm
I imported these tables, but I'm not sure how to get this information
September 20, 2012 at 2:36 pm
jwillbanks (9/20/2012)
I imported these tables, but I'm not sure how to get this information
Take a look at the first link in my signature about best practices.
I am going to a shot in the dark as guess that b.UoM is a varchar field.
BTW, you should consider reworking your join syntax to the more modern join instead of the old style.
from dbo.COI a
inner join dbo.IM b on a.sku = b.SKUNumber
On a small dataset like this it isn't a big deal but at these get larger it is hard to tell what is a join condition and what is a row filter. Also, with the old style you can easily end up with a cross join if there is no where clause.
Another important point is your SUM may not do what you think it is going to do because of datatype issues.
SUM(a.QuantityShipped*b.length*b.width*b.height/12/12/12)
Depending on the datatypes you may have integer math here and not what you might be expecting. Consider the following:
select 12/12/12
What would you expect the result to be? Would you believe it if I told you it would be 0?
Given the names of your other columns they could easily all be integers in which case you will get truncated integer results.
Once you post up your ddl we can take a peek. 🙂
_______________________________________________________________
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 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply