Difficulty querying tables with none existing ID in table2

I need some assistance querying some data for reporting purposes. This
report will capture sale promises made by customers to buy x number of an
item and capture how much the've actually used(qty_shipped). The main part of
this report is to capture a sum of all usage when a promise is for in house
account (account xyz), but account xyz does not exist in the table that has
usage. Can anyone help with how to form this query.

*** Table1:
Customer,
Customer_ID,
Item,
TotalQty,
BeginDate,
EndDate

*** Table2:
[customer_id] [decimal](19, 0) NULL ,
[customer_name] [varchar] (255) COLLATE Latin1_General_CI_AS_KS_WS
NULL ,
[date_created] [datetime] NOT NULL ,
[qty_shipped] [decimal](19, 9) NOT NULL ,
[inv_mast_uid] [int] NULL

*** Table3:
[inv_mast_uid] [int] NOT NULL ,
[item_id] [varchar] (40) COLLATE Latin1_General_CI_AS_KS_WS NOT NULL ,
[item_desc] [varchar] (40) COLLATE Latin1_General_CI_AS_KS_WS NOT NULL
Thanks in advance.

On Thu, 20 Nov 2008 12:47:04 -0800, ITDUDE27 wrote:

>I need some assistance querying some data for reporting purposes. This
>report will capture sale promises made by customers to buy x number of an
>item and capture how much the've actually used(qty_shipped). The main part of
>this report is to capture a sum of all usage when a promise is for in house
>account (account xyz), but account xyz does not exist in the table that has
>usage. Can anyone help with how to form this query.

Hi ITDUDE27,

I think I can - but not with the data you supplied, as I have great
difficulty figuring out which table equates to what entity in your
description, and how the tables relate. So can you please post:

* The layout of your tables, **in the form of CREATE TABLE statements**.
Please include all constraints, properties, and indexes; you may omit
irrelevant columns though.

* A few well-chosen rows to illustrate the situation, including all
relevant possible variations and combinations. Please post this sample
data as INSERT statements that I can cut and paste into my database
after running the CREATE TABLE statements mentioned above (and try to
use consistent upper-/lowercase and nonambiguous date format, as I run
on a case sensitive server in a non-US country).

* The expected results, based on the sample data, along with an
explanation of any non-obvious results.

See www.aspfaq.com/5006 for more information.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis