January 25, 2010 at 9:12 am
I want to return data that represents sales for the current year and to show sales of the same product to the same customer on the same line. However, if a customer bought something last year but has not bought the same item this year I still want it to be displayed. The following sets up an example:
declare @sales table
(
id int,
customer char(10),
product char(20),
description char(20),
units numeric(18, 4),
price numeric(18, 4)
)
insert into @sales
select 1 as id, 'Company A' as customer, 'Product A' as product, 'Product A' as description,
10 as units, 50.00 as price
union all
select 2 as id, 'Company A' as customer, 'Product B' as product, 'Product B' as description,
10 as units, 50.00 as price
declare @archive_sales table
(
id int,
customer char(10),
product char(20),
description char(20),
units numeric(18, 4),
price numeric(18, 4)
)
insert into @archive_sales
select 1 as id, 'Company A' as customer, 'Product A' as product, 'Product A' as description,
3 as units, 15.00 as price
union all
select 2 as id, 'Company A' as customer, 'Product D' as product, 'Product D' as description,
10 as units, 50.00 as price
select s.customer, s.product, s.description, s.units, s.price, sa.units as 'ly units', sa.price as 'ly price'
from @sales s
left join @archive_sales sa on sa.customer = s.customer and sa.product = s.product
and sa.description = s.description
So in this example, Company A bought Product A last year and this year so the units, price, ly units and ly price fields contain data. The customer bought Product B this year but not last year so only data in units and price is shown but now I also want a seperate line showing that Company A bought Product D last year. The desired output would therefore show nothing (null)in units and price but values in ly units and ly price.
January 25, 2010 at 9:33 am
Full outer join, instead of left outer join, and then the s.customer, s.product, s.description columns need to coalese the current and previous years values
Thusly
COALESCE(s.customer, sa.customer) as Customer, ....
Generally a need for full outer join means that there's a problem with the DB design. In this case, are there tables for customer and product? If so, there are probably better ways to do this
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 25, 2010 at 9:44 am
Thank you for the quick response 🙂
The tables are in a third party db so I don't have the option to re-design them.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply