when 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

when problem

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-09-11 : 06:32:26

HI guys

I am hoping you will be able to help me

i am trying to create a coloumn called ‘spostcode’
I have inner joined my tables, however i am unsure where i would put the following peice of code in

The code is

WHEN Isnumeric(RIGHT(LEFT(post_code, 2), 1)) = '0' THEN
LEFT(post_code, 2)
ELSE LEFT(post_code, 1)
END AS 'sPostcode',

The code basically, looks at the value postcode and provides me with either 1 alphabetical letter or two alphabetical letters

For eg

if the postcode was cv15nr . The logic above would return CV

My query is

Declare @date varchar(10)
set @Date = (select dateadd(MM,-12,max(hst_date_processed))
from FDMS.dbo.Fact_Financial_History)

SELECT
o.ParentID,
SUM(f.hst_sales_amt) AS Rolling_12,
o.RM_Code,
o.Account_Status,
o.DBA_Name,
o.LBG_Status,
o.Post_Code,
Geo_PCA_Sellers.RMSC
INTO [#Rolling12]
FROM
Geo_PCA_Sellers INNER JOIN
Dim_Outlet AS o ON Geo_PCA_Sellers.PCA = o.Post_Code FULL OUTER JOIN
Fact_Financial_History AS f ON o.FDMSAccountNo_First9 = f.hst_merchnum
WHERE(f.hst_date_processed > @date)
AND (o.Account_Status = '16')
AND (o.ISO_Account = 'n')
AND (o.LBG_Status <> 'accepted')
AND (o.RM_Account = 'n')
AND (o.Agent_Chain_No NOT IN ('878970059886', '878970013883')) AND (o.FDMSAccountNo NOT IN
(SELECT TA_MID FROM FDMS_PartnerReporting.tmp.trade_assocations))

GROUP BY o.ParentID,
o.RM_Code,
o.Account_Status,
o.DBA_Name,
o.Post_Code,
o.LBG_Status,
Geo_PCA_Sellers.RMSC
having SUM (f.hst_sales_amt) > 1999999
order by SUM(f.hst_sales_amt) desc

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-11 : 06:49:41

If you format the code nicely, it may be more obvious where to put it. The "WHEN" needs the keyword "CASE" before it. You may also want to qualify the post_code (i.e., use o.post_code instead of post_code).

DECLARE @date VARCHAR(10)
SET @Date = (
        SELECT DATEADD(MM, -12, MAX(hst_date_processed))
        FROM   FDMS.dbo.Fact_Financial_History
    ) 

SELECT o.ParentID,
       SUM(f.hst_sales_amt)   AS Rolling_12,
       o.RM_Code,
       o.Account_Status,
       o.DBA_Name,
       o.LBG_Status,
       o.Post_Code,
       Geo_PCA_Sellers.RMSC,
       CASE 
            WHEN ISNUMERIC(RIGHT(LEFT(o.post_code, 2), 1)) = '0' THEN LEFT(o.post_code, 2)
            ELSE LEFT(o.post_code, 1)
       END                    AS 'sPostcode'
INTO [#Rolling12]
FROM   Geo_PCA_Sellers
       INNER JOIN Dim_Outlet  AS o
            ON  Geo_PCA_Sellers.PCA = o.Post_Code
       FULL OUTER JOIN Fact_Financial_History AS f
            ON  o.FDMSAccountNo_First9 = f.hst_merchnum
WHERE  (f.hst_date_processed > @date)
       AND (o.Account_Status = '16')
       AND (o.ISO_Account = 'n')
       AND (o.LBG_Status <> 'accepted')
       AND (o.RM_Account = 'n')
       AND (o.Agent_Chain_No NOT IN ('878970059886', '878970013883'))
       AND (
               o.FDMSAccountNo NOT IN (SELECT TA_MID
                                       FROM   FDMS_PartnerReporting.tmp.trade_assocations)
           )
GROUP BY
       o.ParentID,
       o.RM_Code,
       o.Account_Status,
       o.DBA_Name,
       o.Post_Code,
       o.LBG_Status,
       Geo_PCA_Sellers.RMSC
HAVING SUM(f.hst_sales_amt) > 1999999
ORDER BY
       SUM(f.hst_sales_amt) DESC

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-09-11 : 07:48:32

Hi sunitabeck

My inner joins where incorrect so the query didnt work :(
My query to date is this. (which provides the info i need)

Declare @date varchar(10)
set @Date = (select dateadd(MM,-12,max(hst_date_processed))
from FDMS.dbo.Fact_Financial_History)
SELECT
o.ParentID,
SUM(f.hst_sales_amt) AS Rolling_12,
--[FDMS].[dbo].[SalesBandRM](Sum(f.hst_sales_amt)) as SalesBanding
o.RM_Code,
o.Account_Status,
o.DBA_Name,
o.LBG_Status,
o.Post_Code
INTO [#Rolling12]
FROM
dbo.Fact_Financial_History AS f FULL OUTER JOIN dbo.Dim_Outlet AS o ON f.hst_merchnum = o.FDMSAccountNo_First9
--[FDMS].[dbo].[Geo_PCA_Sellers]as G full outer join dbo.Dim_Outlet AS o on o.Post_Code = g.pca
WHERE (f.hst_date_processed > @date)
and Account_Status ='16'
and ISO_Account = 'n'
and LBG_Status <> 'accepted'
and RM_Account = 'n'
and Agent_Chain_No not in ('878970059886', '878970013883')
AND fdmsaccountno NOT IN (SELECT [ta_mid] FROM fdms_partnerreporting.tmp.trade_assocations)
GROUP BY
o.ParentID,
o.RM_Code,
o.Account_Status,
o.DBA_Name,
o.Post_Code,
o.LBG_Status
having SUM (f.hst_sales_amt) > 1999999
order by SUM(f.hst_sales_amt) desc

i have then wrote this query

select
#Rolling12.ParentID,
#Rolling12.Post_Code,
case
WHEN Isnumeric(RIGHT(LEFT(post_code, 2), 1)) = '0' THEN
LEFT(post_code, 2)
ELSE LEFT(post_code, 1)
END AS 'NewPostcode'
into #Update
from #Rolling12

Which provides me with the following three columns

parentid, Postcode , Newpostcode

based on the newpostcode, i want to inner join onto following table below. There is a direct link with the newpostcode column and PCA . From here i would like to bring over the folowing column ,[RMSC]

SELECT TOP 1000 [ID]
,[PCA]
,[Area]
,[Field_Seller_Less_1M]
,[Field_Seller_More_1M]
,[ASM]
,[RM]
,[RMSC]
,[SF_ID]
FROM [FDMS].[dbo].[Geo_PCA_Sellers]

Final outcome should be

Parentid, Postcode, Newpostcode, RMSC

any ideas , on how to do that ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-11 : 11:07:21

[code]
SELECT u.parentid,
u.Postcode ,
u.Newpostcode,
g.[RMSC]
FROM [FDMS].[dbo].[Geo_PCA_Sellers] g
INNER JOIN #Update u
ON u.NewPostcode = g.PCA
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources