query help on not exist - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

query help on not exist

Author

Topic

thanksfor help
Posting Yak Master

106 Posts

Posted - 2009-04-30 : 19:03:13

I need query help in populating the missing products.

Here is the sample product table

Product
ABCD
XYZ
PQRS
MMN

SALES data

Quarter Product Amount
Q1 ABCD 100
Q1 XYZ 200
Q2 PQRS 100
Q2 ABCD 300

I need query to generate the sales data with zero for the missing products

Quarter Product Amount
Q1 ABCD 100
Q1 XYZ 200
Q1 PQRS 0
Q1 MMN 0
Q2 PQRS 100
Q2 ABCD 300
Q2 XYZ 0
Q2 MMN 0

Any help is very much appreciated.

Thanks

singularity
Posting Yak Master

153 Posts

Posted - 2009-04-30 : 19:25:16

[code]
select a.quarter, a.product, isnull(b.amount,0) as amount
from (select distinct p.product, s.quarter
from product_data p
cross join sales_data s) a
left join sales_data b on a.product = b.product and a.quarter = b.quarter
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources