Query Help Totals by Fields

1

Hi,
Can someone help me with this? I have 3 tables.

OINV - Invoice Header
Invoice # Customer Number, Invoice Total
10000 ABC1 1000.00

INV1 - Invoice Line Items
Item Quantity Amount
A1000 3 300.00
B101 4 600.00
C101 2 100.00

OITM - Item Master Data
Item Property
A1000 Y
B101 N
C101 Y

Desired Results
Invoice # Customer Number, Invoice Total Property Y Property N
10000 ABC1 1000.00 400.00 600.00

So in property Y, it should be the amount of item A1000 and C101 and in property N, it should be the amount of B101.

Any help would be greatly appreciated...

2

If you could post the data as tables, with CREATE TABLE and INSERT statement(s) -- rather than as a "splat" on the screen, which we have to convert to data to try to run a query, someone can code the query for you.

3

hi

hope this helps

create data script

declare @OINVInvoiceHeader table (Invoice# int, CustomerNumber varchar(20), InvoiceTotal decimal(10,2))
insert into @OINVInvoiceHeader select 10000 ,'ABC1', 1000.00

declare @INV1InvoiceLineItems table (Item varchar(20), Quantity int, Amount decimal(10,2))
insert into @INV1InvoiceLineItems select 'A1000' ,3, 300.00
insert into @INV1InvoiceLineItems select 'B101' ,4 ,600.00
insert into @INV1InvoiceLineItems select 'C101', 2 ,100.00

declare @OITMItemMasterData table (Item varchar(20), Property varchar(1))
insert into @OITMItemMasterData select 'A1000', 'Y'
insert into @OITMItemMasterData select 'B101', 'N'
insert into @OITMItemMasterData select 'C101', 'Y'

select * from @OINVInvoiceHeader a ,
( select 
     sum(case when Property = 'Y' then Amount else 0 end ) TotalPropertyY, 
     sum(case when Property = 'N' then Amount else 0 end ) TotalPropertyN
  from 
     @OITMItemMasterData a join @INV1InvoiceLineItems b 
        on a.item = b.item ) b

image
image2584×1001 83.6 KB