Solved

# Double Filter

Posted on 2011-05-04
161 Views
Ok, I have three tables.  The main table (FDH_MAIN_VIEW) has data similar to the following:
``````Comp_Cd  Job_Code    Cost_Ctr  GL_Month  GL_Year
ITC      1101058TC1  TOW       03         11
ITC      1102049TC1  TOW       04         11
``````
The second table (FDH_INV) has data similar to this:
``````Comp_Cd  Job_Code    Cost_Ctr  Post_Month  Post_Year  LE_4000  LE_2205
ITC      1101058TC1  TOW       03           11         0.00     1500.00
ITC      1102049TC1  TOW       04           11         0.00     5000.00
``````
And the third table (FDH_PB) like this:
``````Comp_Cd  Job_Code    Cost_Ctr  Post_Month  Post_Year  PB_4000  PB_2205
ITC      1101058TC1  TOW       04           11         1500.00  -1500.00
ITC      1102049TC1  TOW       04           11         5000.00  -5000.00
``````
What I'm trying to do is display the main record with the values from FDH_INV and FDH_PB if they meet certain criteria (have the proper Post_Month and Post_Year).  The closest I've come so far is with the following query:
``````SELECT DISTINCT
FDH_MAIN_VIEW.Comp_cd,
FDH_MAIN_VIEW.Job_Code,
FDH_MAIN_VIEW.Cost_Ctr,
CASE
WHEN
FDH_INV.GL_Month = FDH_MAIN_VIEW.GL_Month
AND
FDH_INV.GL_Year = FDH_MAIN_VIEW.GL_YEAR
THEN
FDH_INV.LE_4000
ELSE
0
END AS LE_4000,
CASE
WHEN
FDH_INV.GL_Month = FDH_MAIN_VIEW.GL_Month
AND
FDH_INV.GL_Year = FDH_MAIN_VIEW.GL_YEAR
THEN
FDH_INV.LE_2205
ELSE
0
END AS LE_2205,
CASE
WHEN
FDH_PB.GL_Month = FDH_MAIN_VIEW.GL_Month
AND
FDH_PB.GL_Year = FDH_MAIN_VIEW.GL_YEAR
THEN
FDH_PB.PB_2205
ELSE
0
END AS PB_2205,
CASE
WHEN
FDH_PB.GL_Month = FDH_MAIN_VIEW.GL_Month
AND
FDH_PB.GL_Year = FDH_MAIN_VIEW.GL_YEAR
THEN
FDH_PB.PB_4000
ELSE
0
END AS PB_4000,
FROM
FDH_MAIN_VIEW
LEFT OUTER JOIN FDH_PB
ON FDH_MAIN_VIEW.Comp_Cd=FDH_PB.Comp_Cd
AND FDH_MAIN_VIEW.Job_Code=FDH_PB.Job_Code
AND FDH_MAIN_VIEW.Cost_Ctr=FDH_PB.Cost_Ctr
LEFT OUTER JOIN FDH_INV
ON FDH_MAIN_VIEW.Comp_Cd=FDH_INV.Comp_Cd
AND FDH_MAIN_VIEW.Job_Code=FDH_INV.Job_Code
AND FDH_MAIN_VIEW.Cost_Ctr=FDH_INV.Cost_Ctr
``````
But that gives me incorrect values.

Trying to filter on Month 04 and year 11, I get the following:
``````Comp_Cd  Job_Code    Cost_Ctr  LE_4000   LE_2205   PB_2205   PB_4000
ITC      1101058TC1  TOW       0.00      1500.00   0.00      0.00
ITC      1102049TC1  TOW       0.00      5000.00   -5000.00  5000.00
``````
Should Look Like (for filter month 04 and year 11):
``````Comp_Cd  Job_Code    Cost_Ctr  LE_4000   LE_2205   PB_2205   PB_4000
ITC      1101058TC1  TOW       0.00      0.00      -1500.00  1500.00
ITC      1102049TC1  TOW       0.00      5000.00   -5000.00  5000.00
``````

Should Look Like (for filter month 03 and year 11):
``````Comp_Cd  Job_Code    Cost_Ctr  LE_4000   LE_2205   PB_2205   PB_4000
ITC      1101058TC1  TOW       0.00      1500.00   0.00      0.00
``````

What, pray tell, am I doing wrong?
0
Question by:Clif

LVL 9

Expert Comment

check this query.

declare @FilterMonth nvarchar(50)
declare @FilterYear nvarchar(50)

set @FilterMonth  = '03'
set @FilterYear =  '11'

select FDH_MAIN_VIEW.Comp_cd,
FDH_MAIN_VIEW.Job_Code,
FDH_MAIN_VIEW.Cost_Ctr, ISNULL(FDH_INV.LE_4000, 0) LE_4000, ISNULL(FDH_INV.LE_2205,0) LE_2205,
isnull(FDH_PB.PB_2205, 0) PB_2205, isnull(FDH_PB.PB_4000, 0)PB_4000
from FDH_MAIN_VIEW
LEFT OUTER JOIN FDH_PB
ON FDH_MAIN_VIEW.Comp_Cd=FDH_PB.Comp_Cd
AND FDH_MAIN_VIEW.Job_Code=FDH_PB.Job_Code
AND FDH_MAIN_VIEW.Cost_Ctr=FDH_PB.Cost_Ctr
AND FDH_MAIN_VIEW.GL_Month = FDH_PB.Post_Month
AND FDH_MAIN_VIEW.GL_Year= FDH_PB.Post_Year
LEFT OUTER JOIN FDH_INV
ON FDH_MAIN_VIEW.Comp_Cd=FDH_INV.Comp_Cd
AND FDH_MAIN_VIEW.Job_Code=FDH_INV.Job_Code
AND FDH_MAIN_VIEW.Cost_Ctr=FDH_INV.Cost_Ctr
AND FDH_MAIN_VIEW.GL_Month = FDH_INV.Post_Month
AND FDH_MAIN_VIEW.GL_Year= FDH_INV.Post_Year

where FDH_MAIN_VIEW.GL_Month = @FilterMonth  and FDH_MAIN_VIEW.GL_Year = @FilterYear
0

LVL 40

Expert Comment

for Job_Code 1101058TC1, GL_Month 03, there is no record in FDH_PB. Hence there is no PB_2205,PB_4000 values for that combination. Then why do you want Post_Month 04 values for GL_Month 03 record?
0

LVL 11

Author Comment

anillucky31 -
Not quite.  The filter for 03/11 returns correctly, but the filter for 04/11 only returns the record for Job_Code '1102049TC1'.  The record for Job_Code '1101058TC1' doesn't show up.

--
Sharath_123 -
I don't want to post 04 values for GL_Month 03.  If you look at my examples of what the proper  output should be, GL_Month 03 has 0's for the PB fields (since, as you pointed out, there is no PB record for '1101058TC1').  There is, however, a INV/LE value.

Basically I want to show a record from FDH_MAIN_VIEW when there is a record in FDH_INV OR FDH_PB for a specific month and year.

Or, to look at it another way, I want to display records from FDH_INV or FDH_PB filtered on a specified month and year and I want to join the record from FDH_MAIN_VIEW on the fields Comp_Cd, Job_No and Cost_Ctr.  If there is a record in FDH_INV or FDH_PB (or both) there will be a corresponding record in FDH_MAIN_VIEW
0

LVL 9

Accepted Solution

Hi Clif,

I have modified the code. This should work for you now

``````declare @FilterMonth nvarchar(50)
declare @FilterYear nvarchar(50)

set @FilterMonth  = '04'
set @FilterYear =  '11'

select B.* from (
select I.Comp_Cd, I.Job_Code, I.Cost_Ctr, I.Post_Month,I.Post_Year,
isnull(I.LE_4000, 0) LE_4000,isnull(I.LE_2205, 0) LE_2205, isnull(P.PB_2205, 0) PB_2205, isnull(P.PB_4000, 0) PB_4000  from FDH_INV I
left join FDH_PB P on
I.Comp_Cd=P.Comp_Cd
AND I.Job_Code=P.Job_Code
AND I.Cost_Ctr=P.Cost_Ctr
AND I.Post_Month = P.Post_Month
AND I.Post_Year = P.Post_Year

union
select P.Comp_Cd, P.Job_Code, P.Cost_Ctr, P.Post_Month,P.Post_Year,
isnull(I.LE_4000, 0) LE_4000,isnull(I.LE_2205, 0) LE_2205, isnull(P.PB_2205, 0) PB_2205, isnull(P.PB_4000, 0) PB_4000  from FDH_PB P
left join FDH_INV I on
I.Comp_Cd=P.Comp_Cd
AND I.Job_Code=P.Job_Code
AND I.Cost_Ctr=P.Cost_Ctr
AND I.Post_Month = P.Post_Month
AND I.Post_Year = P.Post_Year
) B

inner join FDH_MAIN_VIEW F on
B.Comp_Cd=F.Comp_Cd
AND B.Job_Code=F.Job_Code
AND B.Cost_Ctr=F.Cost_Ctr
where B.Post_Month = @FilterMonth and B.Post_Year=@FilterYear
``````
0

LVL 11

Author Closing Comment

Absolutely perfect.

0

## Featured Post

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be \$37.1B.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.