Left Outer Join Help
-
See query below. I need to be able to pull the data in by fiscal_wk regardless of if there was any data. This example pulls in 2 product lines (Blue and Metal). Blue has sales every week for both years. Metal started selling in June 2006 so no data for 2005 or early 2006. I've been playing with this for a while and am not getting the correct results back. If I run only for Metal I only get weeks 21-52 instead of 1-52, where weeks 1-20 should display as
Metal
select
reporting_year,
sec_prod_group,
b.fiscal_wk,
sum(qty_sold)
from
depot_buckets b
left outer join
(select
sec_prod_group,
p.date_start,
qty_sold
from
depot_pos_item_sum p
inner join depot_items i on i.idx_item = p.idx_item
where sec_prod_group in ('Metal','Blue')
 
poson b.date_start = pos.date_start
where reporting_year in (2005,2006)
group by
reporting_year,
sec_prod_group,
b.fiscal_wk
-
Can you post the DDL (data definition language) of the tables used, ie the CREATE statements? Only include the columns necessary if there's any issues with exposure. Also some example data from each table would be useful.
-
Is this what you're looking for:
CREATE TABLE [Depot_Items] (
[IDX_Item] [int] NOT NULL ,
[HD_SKU] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Prim_Prod_Group] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Sec_Prod_Group] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Tert_Prod_Group] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 
ON [PRIMARY]GO
=====================
CREATE TABLE [Depot_POS_Item_Sum] (
[Date_Start] [datetime] NOT NULL ,
[IDX_Item] [int] NOT NULL ,
[Qty_Sold] [int] NULL ,
CONSTRAINT [PK_wrk_Depot_POS_Item_Sum] PRIMARY KEY NONCLUSTERED
(
[Date_Start],
[IDX_Item]
 
WITH FILLFACTOR = 90 ON [PRIMARY] ,CONSTRAINT [IX_wrk_Depot_POS_Item_Sum] UNIQUE CLUSTERED
(
[Date_Start],
[IDX_Item]
 
WITH FILLFACTOR = 90 ON [PRIMARY]) ON [PRIMARY]
GO
===========================
CREATE TABLE [Depot_Buckets] (
[Date_Start] [datetime] NOT NULL ,
[Reporting_Year] [int] NULL ,
[Fiscal_Wk] [int] NULL ,
CONSTRAINT [PK_Depot_Buckets] PRIMARY KEY NONCLUSTERED
(
[Date_Start]
 
WITH FILLFACTOR = 90 ON [PRIMARY] ,CONSTRAINT [IX_Depot_Buckets] UNIQUE CLUSTERED
(
[Date_Start]
 
WITH FILLFACTOR = 90 ON [PRIMARY]) ON [PRIMARY]
GO
========================
Examples
depot_buckets
date_start month report_year firstdayofmonth fiscal_yr fiscal_wk
2006-10-02 00:00:00.000 10 2006 2006-10-01 00:00:00.000 2006 36
depot_pos_item_sum
date_start idx_item unit dollars onhand
2006-10-02 00:00:00.000 3874 134 2 4.0000
depot_items
idx_item sku primary secondary tertiary
3874 101491 Metal Bronze fc120
-
Okay, I think I've got it... Try this as your derived table:
(select
sec_prod_group,
p.date_start,
qty_sold
from
depot_items i
LEFT join depot_pos_item_sum p
on i.idx_item = p.idx_item
where sec_prod_group in ( 'Metal', 'Blue' )
) pos
There will be no Metal rows returned if there are no rows in the dpot_pos_item_sum table.
Let me know how this goes, I'll keep my eye on this post, but sadly I'm going home now... Good luck
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply