asked on

Double Filter

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

Open in new window

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

Open in new window

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

Open in new window

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

Open in new window

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

Open in new window

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

Open in new window

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

Open in new window

What, pray tell, am I doing wrong?

anillucky31

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

Sharath S

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?

Clif

ASKER

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

ASKER CERTIFIED SOLUTION

anillucky31

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Clif

ASKER

Absolutely perfect.

Thanks much for your help.