• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 169
  • Last Modified:

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?
0
Clif
Asked:
Clif
  • 2
  • 2
1 Solution
 
anillucky31Commented:

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
 
SharathData EngineerCommented:
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
 
ClifAuthor Commented:
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
 
anillucky31Commented:
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

Open in new window

0
 
ClifAuthor Commented:
Absolutely perfect.

Thanks much for your help.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now