case problem - 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

case problem

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-09-05 : 11:14:45

Hey

i have encountered a problem , and i am hoping you will guys be able to help me with this.

i believe i need to create a case within my query (but not 100% sure )

I have built my query as follows

select ParentID,
SUM(Gross_Sales) as Sales_Annualised,
[FDMS].[dbo].[SalesBandRM](Sum(gross_sales)) as SalesBanding
Into #RM
--drop table #RM
--select * from #RM
from [FDMS].[dbo].[Dim_Outlet] o
inner join [FDMS].[dbo].[Fact_Financial_History_Annualised] fh on o.FDMSAccountNo = fh.FDMSAccountNo
group by ParentID
having SUM (fh.Gross_Sales) > 1999999
order by SUM(Gross_Sales) desc

Which provides me with three columns ParentId, Sales_annualises and salesbanding

my second query below, Which narrows my results from the 1st query down, as its meeting my criteria set

select
rm.ParentID,
Dba_Name,
Post_Code,
HO,
LBG_Account,
LBG_Status,
Account_Status,
Sales_Annualised,
SalesBanding
into #test
From #RM rm inner join [FDMS].[dbo].[Dim_Outlet] o on o.FDMSAccountNo = rm.ParentID
where LBG_Status <> 'accepted'
and Account_Status ='16'
AND iso_account = 'N'
AND Open_Date < dateadd(mm, -3, getdate())
and Agent_Chain_No not in ('878970059886', '878970013883')
AND fdmsaccountno NOT IN (SELECT [ta_mid] FROM fdms_partnerreporting.tmp.trade_assocations)
and Sales_Annualised > 1999999
order by DBA_Name

I somehow need to inner join [FDMS].[dbo].[Geo_PCA_Sellers] PCA onto my postcode column
i know it be along the lines of
WHEN Isnumeric(RIGHT(LEFT(post_code, 2), 1)) = '0' THEN
LEFT(post_code, 2)
ELSE LEFT(post_code, 1)
END AS 'NewPostcode',

and the inner join will be along the lines of
INNER JOIN [geo_pca_sellers] [pca] = NewPostcode

But where would i apply that ?

Hoping this makes sense

Looking forward to your response

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-09-05 : 12:57:10

One option is to use the case expression on the join (assuming the datatypes match?):

select 
    rm.ParentID,
    Dba_Name,
    Post_Code,
    HO,
    LBG_Account,
    LBG_Status,
    Account_Status,
    Sales_Annualised,
    SalesBanding
into 
    #test 
From 
    #RM rm 
inner join 
    [FDMS].[dbo].[Dim_Outlet] o 
    on o.FDMSAccountNo = rm.ParentID 
inner join 
    [FDMS].[dbo].[Geo_PCA_Sellers] PCA 
    ON PCA.pc = CASE 
                WHEN Isnumeric(RIGHT(LEFT(o.post_code, 2), 1)) = 0 
                    THEN LEFT(o.post_code, 2) 
                ELSE LEFT(o.post_code, 1) 
            END
where 
    LBG_Status <> 'accepted'
    and Account_Status ='16'
    AND iso_account = 'N'
    AND Open_Date < dateadd(mm, -3, getdate())
    and Agent_Chain_No not in ('878970059886', '878970013883')
    AND fdmsaccountno NOT IN (SELECT [ta_mid] FROM fdms_partnerreporting.tmp.trade_assocations)
    and Sales_Annualised > 1999999
order by 
    DBA_Name

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources