April 21, 2010 at 11:46 pm
I have 2 tables:
1. InvoiceLines_Monthly T1
2. Forecast_Monthly T2
I need to join the 2 together so that the output will have both the T1 and T1 rows on 1 line based on the join operation. The join between the 2 tables is on:
Customer_Group, Stock_Code, Whse_Code, YYYYMM.
Eg: T1 has 11 rows and T2 has 13 rows. The result I need is 13 rows.
CREATE TABLE [dbo].[T1](
[Customer_Group] [char](20) NULL,
[Stock_Code] [char](16) NOT NULL,
[Whse_Code] [char](4) NOT NULL,
[YYYYMM] [varchar](6) NULL,
[Shipped_Qty] [float] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[T2](
[Customer_Group] [char](23) NULL,
[Stock_Code] [char](43) NULL,
[Whse_Code] [char](15) NULL,
[YYYYMM] [int] NULL,
[FC_Qty] [int] NULL
) ON [PRIMARY]
I expect the resulting query to have these columns:
Customer_Group, Stock_Code, Whse_Code, YYYYMM, FC_Qty, Shipped_Qty
T1:
=====
JD WHSE 009040 S 2010041
JD WHSE 009040 B 20100499
T2:
======
JD WHSE 009040 M 20100422
JD WHSE 009040 P 20100411
JD WHSE 009040 S 2010049
My Expected Result:
================
JD WHSE 009040 M 20100422 0
JD WHSE 009040 P 20100411 0
JD WHSE 009040 S 2010049 1
JD WHSE 009040 B 2010040 99
The issue is when T1 may not have the matching records for T2 and vice-versa. I need this to calculate the Forecast accuracy.
ie, Absolute(Shipped_Qty - FC_Qty)/FC_Qty
Thanks in advance.
April 22, 2010 at 1:34 am
I guess you could use a FULL JOIN.
Please read the article linked in my signature, post some sample data and the query you came up with so far and I'll be glad to help you tweak it in the right direction.
-- Gianluca Sartori
April 22, 2010 at 5:00 pm
Hi Gianluca and All,
I have used the FULL join but resulta are not as expected.
Here are the sql for testing.
------Table #T1 (InvoiceLinesMonthly)
CREATE TABLE [dbo].[#T1](
[Customer_Group] [char](20) NULL,
[Stock_Code] [char](16) NOT NULL,
[Whse_Code] [char](4) NOT NULL,
[YYYYMM] [varchar](6) NULL,
[Shipped_Qty] [float] NULL
) ON [PRIMARY]
SELECT
'SELECT '
+ QUOTENAME(Customer_Group,'''')+','
+ QUOTENAME(Stock_Code,'''')+','
+ QUOTENAME(Whse_Code,'''')+','
+ QUOTENAME(YYYYMM,'''')+','
+ QUOTENAME(Shipped_Qty,'''')
+ ' UNION ALL'
FROM #T1
INSERT INTO #T1 (Customer_Group, Stock_Code, Whse_Code, YYYYMM, Shipped_Qty)
SELECT 'ALIGNED','009040','B','201004','25' UNION ALL
SELECT 'ALIGNED','009040','M','201004','40' UNION ALL
SELECT 'ALIGNED','009040','P','201004','10' UNION ALL
SELECT 'ALIGNED','009040','S','201004','5' UNION ALL
SELECT 'BUNNINGS','009040','B','201004','220' UNION ALL
SELECT 'BUNNINGS','009040','M','201004','410' UNION ALL
SELECT 'BUNNINGS','009040','P','201004','90' UNION ALL
SELECT 'BUNNINGS','009040','S','201004','295' UNION ALL
SELECT 'JD WHSE','009040','S','201004','0' UNION ALL
SELECT 'JD WHSE','009040','B','201004','99' UNION ALL
SELECT 'M10 WHSE','009040','B','201004','30' UNION ALL
SELECT 'M10 WHSE','009040','M','201004','30'
-----Table #T2 (ForecastMonthly)
CREATE TABLE [dbo].[#T2](
[Customer_Group] [char](20) NULL,
[Stock_Code] [char](16) NULL,
[Whse_Code] [char](4) NULL,
[YYYYMM] [int] NULL,
[FC_Qty] [int] NULL
) ON [PRIMARY]
SELECT
'SELECT '
+ QUOTENAME(rtrim(Customer_Group),'''')+','
+ QUOTENAME(rtrim(Stock_Code),'''')+','
+ QUOTENAME(rtrim(Whse_Code),'''')+','
+ QUOTENAME(YYYYMM,'''')+','
+ QUOTENAME(FC_Qty,'''')
+ ' UNION ALL'
FROM #T2
INSERT INTO #T2 (Customer_Group, Stock_Code, Whse_Code, YYYYMM, FC_Qty)
SELECT 'ALIGNED','009040','B','201004','35' UNION ALL
SELECT 'ALIGNED','009040','M','201004','36' UNION ALL
SELECT 'ALIGNED','009040','P','201004','73' UNION ALL
SELECT 'ALIGNED','009040','S','201004','143' UNION ALL
SELECT 'BUNNINGS','009040','B','201004','203' UNION ALL
SELECT 'BUNNINGS','009040','M','201004','327' UNION ALL
SELECT 'BUNNINGS','009040','P','201004','247' UNION ALL
SELECT 'BUNNINGS','009040','S','201004','293' UNION ALL
SELECT 'JD WHSE','009040','M','201004','22' UNION ALL
SELECT 'JD WHSE','009040','P','201004','11' UNION ALL
SELECT 'JD WHSE','009040','S','201004','9' UNION ALL
SELECT 'M10 WHSE','009040','B','201004','17' UNION ALL
SELECT 'M10 WHSE','009040','M','201004','18'
This is my expected output:
Customer_Group Stock_Code Whse_Code YYYYMM FC_QtyShipped_Qty
-------------------- ---------------- --------- ----------- ----------------------
ALIGNED 009040 B 201004 3525
ALIGNED 009040 M 201004 3640
ALIGNED 009040 P 201004 7310
ALIGNED 009040 S 201004 1435
BUNNINGS 009040 B 201004 203220
BUNNINGS 009040 M 201004 327410
BUNNINGS 009040 P 201004 24790
BUNNINGS 009040 S 201004 293295
JD WHSE 009040 M 201004 220
JD WHSE 009040 P 201004 110
JD WHSE 009040 S 201004 90
JD WHSE 009040 B 201004 099
M10 WHSE 009040 B 201004 1730
M10 WHSE 009040 M 201004 1830
Notice the rows for 'JD WHSE'.
T1 has 2 records for whse_code (S, B) and T2 has 3 records for whse_code(M, P, S).
My expected result should have 4 records covering the 4 whse_codes (S, M, P, B). FULL OUter JOIN gives NULL values.
Using Full Outer Join as per below gives me the corret # of records (ie, 14)but has NULLS.
SELECT T1.Customer_Group, T1.Stock_Code, T1.Whse_Code, T1.YYYYMM, T1.Shipped_Qty, T2.FC_Qty
FROM T1 FULL OUTER JOIN
T2 ON T1.Customer_Group = T2.Customer_Group AND T1.Stock_Code = T22.Stock_Code AND T11.Whse_Code = T2.Whse_Code AND T1.YYYYMM = T2.YYYYMM
Output of above sql:
=====================
Customer_Group Stock_Code Whse_Code YYYYMM Shipped_Qty FC_Qty
-------------------- ---------------- --------- ------ ---------------------- -----------
ALIGNED 009040 B 201004 25 35
ALIGNED 009040 M 201004 40 36
ALIGNED 009040 P 201004 10 73
ALIGNED 009040 S 201004 5 143
BUNNINGS 009040 B 201004 220 203
BUNNINGS 009040 M 201004 410 327
BUNNINGS 009040 P 201004 90 247
BUNNINGS 009040 S 201004 295 293
JD WHSE 009040 S 201004 0 9
JD WHSE 009040 B 201004 99 NULL
M10 WHSE 009040 B 201004 30 17
M10 WHSE 009040 M 201004 30 18
NULL NULL NULL NULL NULL 22
NULL NULL NULL NULL NULL 11
(14 row(s) affected)
Thx in advance.
April 22, 2010 at 5:32 pm
Select t1.Customer_Group,t1.Stock_Code,t1.Whse_Code,t1.YYYYMM
,ABS(ISNULL(ISNULL(t1.Shipped_Qty,0) - ISNULL(t2.FC_Qty,0)/t2.FC_Qty,0)) as FC_QTY
,t1.Shipped_Qty
,'T1 Left'
From #T1 t1
Left Outer Join #T2 t2
On t1.Customer_Group = t2.Customer_Group
And t1.Stock_Code = t2.Stock_Code
And t1.Whse_Code = t2.Whse_Code
And t1.YYYYMM = t2.YYYYMM
Where ISNULL(t2.customer_group,'') = ''
And ISNULL(t2.stock_code,'') = ''
And ISNULL(t2.whse_code,'') = ''
And ISNULL(t2.YYYYMM,'') = ''
Union
Select t2.Customer_Group,t2.Stock_Code,t2.Whse_Code,t2.YYYYMM
,ABS(ISNULL(ISNULL(t1.Shipped_Qty,0) - ISNULL(t2.FC_Qty,0)/t2.FC_Qty,0)) as FC_QTY
,ISNULL(t1.Shipped_Qty,0)
,'T2 Left'
From #T2 t2
Left Outer Join #T1 t1
On t2.Customer_Group = t1.Customer_Group
And t2.Stock_Code = t1.Stock_Code
And t2.Whse_Code = t1.Whse_Code
And t2.YYYYMM = t1.YYYYMM
Where ISNULL(t1.customer_group,'') = ''
And ISNULL(t1.stock_code,'') = ''
And ISNULL(t1.whse_code,'') = ''
And ISNULL(t1.YYYYMM,'') = ''
Union
Select t2.Customer_Group,t2.Stock_Code,t2.Whse_Code,t2.YYYYMM
,ABS(ISNULL(ISNULL(t1.Shipped_Qty,0) - ISNULL(t2.FC_Qty,0)/t2.FC_Qty,0)) as FC_QTY
,ISNULL(t1.Shipped_Qty,0)
,'T1 T2 Join'
From #T2 t2
Inner Join #T1 t1
On t2.Customer_Group = t1.Customer_Group
And t2.Stock_Code = t1.Stock_Code
And t2.Whse_Code = t1.Whse_Code
And t2.YYYYMM = t1.YYYYMM
There is a problem with your expected results. Your expected results do not match your forecast formula and thus create some confusion.
This query returns the records based on the formula and match your output with the exception of the few records where the forecast quantity in your results do not match your formula.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 22, 2010 at 5:46 pm
Thx Jason,
and Sorry to allif there was some confusion. I do not need to calculate the Forecast accuracy
(ie, Absolute(Shipped_Qty - FC_Qty)/FC_Qty ) at this stage. I just need all the records to appear on the final sql output. I will then take this output to do my Calculation.
Jason, if i modify your sql to remove the calculation part, will that work. i will give it a try and see as well.
Thx. in advance.
April 22, 2010 at 5:50 pm
If you remove the calculation and just replace with
Isnull(t2.FC_Qty,0)
That should do it for you. This is due to fc_qty not being in both tables.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 22, 2010 at 6:02 pm
Thx Jason for such a sqift response. It works as expected.
I still ahve a question though, will it work when there are missng records on either tables, ie,
You may sell a Product this month but the Product may not have any forecast.
or,
You may have a Product forecast this month but the Product was not sold at all this month.
Thx once again.
April 22, 2010 at 6:55 pm
rbajimaya (4/22/2010)
Thx Jason for such a sqift response. It works as expected.I still ahve a question though, will it work when there are missng records on either tables, ie,
You may sell a Product this month but the Product may not have any forecast.
or,
You may have a Product forecast this month but the Product was not sold at all this month.
Thx once again.
The first part of the query checks for product without a forecast. Second part checks for a forecast where no sales were done. And the third part checks for sales that have a forecast.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 22, 2010 at 8:18 pm
Thx once again Jason.
April 22, 2010 at 8:39 pm
rbajimaya (4/22/2010)
Thx once again Jason.
You're welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply