inner join help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

inner join help

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-23 : 10:36:58

Hey guys
i have another inner join problem

here is one query

SELECT TOP 1000 [Fee_Code]
,[Description]
,[SalesMI_Group1]
,[SalesMI_Group2]
,[Card_Type]
,[Card_Type_Sub]
,[Product]
,[Premium_Product]
,[Scheme]
,[Q/NQ]
,[Sale_Refund]
,[Pence_Percent]
,[Card_Group1]
,[Sub_Product_Code]
,[Repeat_MSC]
FROM [FDMS].[dbo].[Dim_Fee_Codes]

SELECT TOP 1000 [FDMSAccountNo]
,[Fee_Sequence_Number]
,[Month_end_date]
,[Retail_amount]
,[Retail_tran_count]
,[Wholesale_amount]
,[Wholesale_tran_count]
FROM [FDMS].[dbo].[Fact_Fee_History]

I need to say that when

Fee_Code IN ('42B','42C','42D','42E') Then 'PCI'
Fee_Code = '00Y' Then 'Refund Transaction Charge'

i then need to inner join [FDMS].[dbo].[Dim_Fee_Codes] on Fee_Sequence_Number]

then somehow apply this query below, into the above

with cte as (
SELECT [fdmsaccountno],

Case when year(hst_date_processed) = '2011' then SUM ([msc]) else 0 end as [2011] ,
Case when year(hst_date_processed) = '2012' then SUM ([msc]) else 0 end as [2012]
,[MSC]
,[Refund Transaction Charge]
,[PCI]
,[Joining Fee]
,[Other]
FROM [Rm_Book_New].[dbo].[tmpbd1]

group by fdmsaccountno,
[MSC]
,[Refund Transaction Charge]
,[PCI]
,[Joining Fee]
,[Other],hst_date_processed )

select fdmsaccountno,SUM([2011]) as [2011 MSC],SUM([2012]) as [2012 MSC],SUM([2012]) - SUM([2011]) as [Comparision]
from cte
group by fdmsaccountno

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-23 : 10:51:19

my orginal query was

SELECT
a.FDMSAccountNo,
[Month_end_date] as hst_date_processed,

Case when Fee_Code IN ('42B','42C','42D','42E') Then 'PCI'

When Fee_Code = '00Y' Then 'Refund Transaction Charge'

Else salesMI_Group1 end as description,

Sum([Retail_amount]) As Amount,
Sum([Retail_tran_count]) As Trans_Count
FROM FDMS.dbo.Fact_Fee_History AS f INNER JOIN
dbo.Rm_accounts AS a ON a.FDMSAccountNo = f.FDMSAccountNo
INNER JOIN
FDMS.dbo.Dim_Fee_Codes D ON f.Fee_Sequence_Number = D.Fee_Code

where (a.FDMSAccountNo = '878020388889' )
and ([Month_end_date] > '20090101')
group by a.FDMSAccountNo,
[Month_end_date],SalesMI_Group1,Fee_Code

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-23 : 11:32:06

something like below

i dont know which column you need to link fee sequence number to so make sure you add correct condition


;with cte as (
SELECT [fdmsaccountno],

Case when year(hst_date_processed) = '2011' then SUM ([msc]) else 0 end as [2011] ,
Case when year(hst_date_processed) = '2012' then SUM ([msc]) else 0 end as [2012] 
,[MSC]
,[Refund Transaction Charge]
,[PCI]
,[Joining Fee]
,[Other]
FROM [Rm_Book_New].[dbo].[tmpbd1]

group by fdmsaccountno,
[MSC]
,[Refund Transaction Charge]
,[PCI]
,[Joining Fee]
,[Other],hst_date_processed )

select *
from
(
select fdmsaccountno,SUM([2011]) as [2011 MSC],SUM([2012]) as [2012 MSC],SUM([2012]) - SUM([2011]) as [Comparision]
from cte
group by fdmsaccountno
)t
inner join [FDMS].[dbo].[Fact_Fee_History] fh
on fh.FDMSAccountNo = t.FDMSAccountNo
inner join (SELECT CASE WHEN Fee_Code IN ('42B','42C','42D','42E') Then 'PCI' 
When Fee_Code = '00Y' Then 'Refund Transaction Charge' 
End AS Fee_Code,
,[Description]
,[SalesMI_Group1]
,[SalesMI_Group2]
,[Card_Type]
,[Card_Type_Sub]
,[Product]
,[Premium_Product]
,[Scheme]
,[Q/NQ]
,[Sale_Refund]
,[Pence_Percent]
,[Card_Group1]
,[Sub_Product_Code]
,[Repeat_MSC]
FROM [FDMS].[dbo].[Dim_Fee_Codes] 

)fc
ON required condition on feesequence number

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources