View Poll Results: Is it more foolish to ask, or to pretend to be ALL-KNOWING?

Voters
2. You may not vote on this poll
  • Ask but not too often

    0 0%
  • Ask but don't repeat your question

    2 100.00%
  • Ask but only if you know you will not lose respect?

    0 0%
  • Don't ask and remain stupid, thinking you are actually intelligent!

    0 0%
Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2010
    Posts
    1

    Unanswered: SQL2005 - Eliminate Duplicate Descriptions for UNIQUE Key value

    I have a master file that I have inherited which has duplicate Rows for the same Account Code due to Typo's and Case's and different way of saying the same thing. This is because the Warehouse draws from 14 countries where some of them have not been brought to standard.

    e.g.: 678900 Salaries & Wages
    678900 Sal & Wag
    678900 Salries and Wages
    726353 Temp Wages
    726353 Casual Workers Wages

    I want to reference (Join) to this table and return the Description of the 1st occurring row pertaining to a specific code. i.e. All Transactions with the AccountCode = 678900 must have a column with 'Salaries & Wages'. Currently I am getting duplicate records because there are 3 or more posibilities any transaction could reference.

    USE [BSUA_MastMeta]
    GO

    /****** Object: Table [MastMeta].[DIM_AccountStructure] Script Date: 07/11/2010 17:24:27 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    SET ANSI_PADDING ON
    GO

    CREATE TABLE [MastMeta].[DIM_AccountStructure](
    [Account_SK] [int] NOT NULL,
    [Account_BK] [varchar](50) NOT NULL,
    [Account] [varchar](100) NULL,
    [DrCr_IND] [char](2) NULL,
    [Operator] [char](1) NULL,
    [SeqNo] [int] NULL,
    [Formula_BK] [varchar](1000) NULL,
    [Formula_SK] [varchar](1000) NULL,
    [Properties] [varchar](100) NULL,
    [ParentAccount_BK] [varchar](50) NULL,
    [ParentAccount_SK] [int] NULL,
    [AccountType_SK] [int] NULL,
    [AccountType_BK] [char](2) NULL,
    [AccountType] [varchar](75) NULL,
    [Statement_SK] [int] NULL,
    [Statement_BK] [char](2) NULL,
    [Statement] [varchar](75) NULL,
    [Profitability] [varchar](50) NULL,
    [AllocationRule] [varchar](50) NULL,
    [RuleDescription] [varchar](400) NULL,
    [Operation_Code_SK] [int] NULL,
    [Operation_Code_BK] [varchar](5) NULL,
    [Operation_BSUCode_SK] [varchar](2) NULL
    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF
    GO

    ----------------------

    DROP TABLE #tempGlMastDesc
    SELECT DISTINCT STRM.*
    --INTO #tempGlMastDesc

    FROM
    (SELECT
    SUBSTRING(STRU.[Account_BK],9,6) AS Account_Code
    --, DrCr_IND
    , STRU.AccountType_BK
    --, STRU.AccountType
    --, STRU.Statement_BK
    --, STRU.[Statement]
    ----, STRU.[Account_BK]
    , REPLACE(SUBSTRING(STRU.[Account],18,3),' ','')+SUBSTRING(STRU.[Account],21,29) AS [Account_Description]
    FROM BSUA_MastMeta.MastMeta.DIM_AccountStructure AS STRU
    WHERE LEN(STRU.Account_BK) = 14 AND STRU.AccountType_BK <> 0
    UNION ALL
    SELECT SUBSTRING(STRF.[Account_BK],9,6) AS Account_Code
    --, DrCr_IND
    , STRF.AccountType_BK
    --, STRF.AccountType
    --, STRF.Statement_BK
    --, STRF.[Statement]
    --, STRF.[Account_BK]
    , REPLACE(SUBSTRING(STRF.[Account],18,3),' ','')+SUBSTRING(STRF.[Account],21,29) AS [Account_Description]
    FROM BSUA_MastMeta.MastMeta.DIM_AccountStructure AS STRF
    WHERE LEN(STRF.Account_BK) = 14 AND STRF.AccountType_BK <> 0
    ) AS STRM
    ORDER BY STRM.AccountType_BK,Account_Code

    SELECT DISTINCT STRM.*, DESCR.Account_Description FROM
    (SELECT
    SUBSTRING(STRU.[Account_BK],9,6) AS Account_Code
    , DrCr_IND
    , STRU.AccountType_BK
    , STRU.AccountType
    , STRU.Statement_BK
    , STRU.[Statement]
    --, STRU.[Account_BK]
    -- , REPLACE(SUBSTRING(STRU.[Account],18,3),' ','')+SUBSTRING(STRU.[Account],21,29) AS [Account_Description]
    --INTO #tempGlMast
    FROM BSUA_MastMeta.MastMeta.DIM_AccountStructure AS STRU
    WHERE LEN(STRU.Account_BK) = 14 AND STRU.AccountType_BK <> 0
    UNION ALL
    SELECT SUBSTRING(STRF.[Account_BK],9,6) AS Account_Code
    , DrCr_IND
    , STRF.AccountType_BK
    , STRF.AccountType
    , STRF.Statement_BK
    , STRF.[Statement]
    --, STRF.[Account_BK]
    -- , REPLACE(SUBSTRING(STRF.[Account],18,3),' ','')+SUBSTRING(STRF.[Account],21,29) AS [Account_Description]
    FROM BSUA_MastMeta.MastMeta.DIM_AccountStructure AS STRF
    WHERE LEN(STRF.Account_BK) = 14 AND STRF.AccountType_BK <> 0
    ) AS STRM
    RIGHT OUTER JOIN (SELECT TOP 1 * FROM #tempGlMastDesc
    AS REF WHERE REF.Account_Code = STRM.Account_Code)
    AS DESCR
    ON DESCR.Account_Code = STRM.Account_Code
    ORDER BY STRM.AccountType_BK,STRM.Account_Code



    DROP TABLE BSUA_VRSL_ODS.FINTXN.ODS_FinGLTxn
    --GO
    SELECT
    [MAPOPER].[Code] AS [TM1_Mapping_Country_Oper]
    , SUBSTRING(GlCode,9,6) AS [TM1PARMVAL]
    ,GlYear
    ,GlPeriod
    ,Company
    ,ACTOP.Operation_AK
    ,MASTGLFC.[AccountType_BK] AS ParentAccount
    ,MASTGLFC.AccountType AS MainParentAccountDescription
    ,[GlCode]
    ,SUBSTRING(MASTGLFC.Account,19,40) AS AccountDescription
    , SUBSTRING([GlCode],1,3) AS [Site_Code]

    , SUBSTRING([GlCode],6,3) AS [Cost_Centre]
    , SUBSTRING([GlCode],9,4) AS [AC_Number]
    , SUBSTRING([GlCode],13,2) AS [Sub_Account]
    ,[Source]
    ,SRC.GLSourceDesc
    ,[Origin]
    ,ORIG.GLOriginDesc
    ,OriginZoomKey
    ,MASTGLFC.DrCr_IND AS DR_CR
    ,([EntryValue]) AS Value
    ,[Comment]
    ,[Reference]
    ,[JnlDate]
    ,[Journal]
    ,MASTGLFC.[Statement_BK] AS StatementKey
    ,MASTGLFC.[Statement] AS StatementDescription
    , PRD.CalendarPeriodMonth_Desc
    , PRD.CalendarPeriodMonth_Val
    ,[Line]
    ,[EntryGroup]
    INTO BSUA_VRSL_ODS.FINTXN.ODS_FinGLTxn
    FROM SYSPRO.EncoreCompanyA.dbo.GenTransaction AS FINTXN
    LEFT OUTER JOIN #tempGlMast AS MASTGLFC
    ON FINTXN.[GlCode] = MASTGLFC.Account_BK
    INNER JOIN [BSUA_MastMeta].ETL_Meta.ETL_Control_PeriodMonth AS PRD
    ON FINTXN.GlPeriod = PRD.FinancialPeriodMonth_Val
    LEFT OUTER JOIN BSUA_MastMeta.ETL_Meta.ETL_Control_Operation AS ACTOP
    ON ACTOP.Oper_Is_Active = 1
    INNER JOIN BSUA_MastData_Staging.dbo.Map_SYSPRO_TM1_OPERCODES AS MAPOPER
    ON ACTOP.Operation_AK = RTRIM(MAPOPER.[Alt Description])
    INNER JOIN BSUA_MastMeta.BRE_Meta.Mast_GLSource AS SRC
    ON SRC.GLSource_AK = FINTXN.Source
    INNER JOIN BSUA_MastMeta.BRE_Meta.Mast_GLOrigin AS ORIG
    ON ORIG.GLOrigin_AK = FINTXN.Origin
    --WHERE MASTGLFC.[AccountType_BK] = 60
    WHERE FINTXN.GlYear >= YEAR(GETDATE())-2
    AND (MASTGLFC.AccountType_BK IN ('60','65')
    OR (Source = 'WP' AND Origin = 'COSB'))
    GROUP BY ACTOP.Operation_AK
    ,[MAPOPER].[Code]
    ,[Company]
    ,[GlCode]


    ,MASTGLFC.[AccountType_BK]
    ,MASTGLFC.AccountType
    ,MASTGLFC.Account

    ,[Statement_BK]
    ,[Statement]
    ,MASTGLFC.DrCr_IND

    , FINTXN.GlCode

    ,[GlYear]
    ,[GlPeriod]
    , PRD.CalendarPeriodMonth_Desc
    , PRD.CalendarPeriodMonth_Val

    ,[Line]
    ,[Source]
    ,SRC.GLSourceDesc
    ,[Journal]
    ,[JnlDate]
    ,[Reference]
    ,[EntryValue]
    ,[Comment]
    ,[Origin]
    ,ORIG.GLOriginDesc
    ,OriginZoomKey

    ,[EntryGroup]

    ORDER BY FINTXN.GlYear DESC, FINTXN.GlPeriod DESC, FINTXN.GlCode,FINTXN.Source, FINTXN.Origin

    DROP TABLE #tempGlMast

    There are two STRUCTURE tables, one for FIXED COSTs and the other for the rest. I need the transactions from both into the Operational Datastore. But reference one version of the truth for description (SUBSTRING 9,6) of the GlCode! Somewhere along the line, different countries are using differencs COST CENTREs, the logic is actually built into the GLCode.

    I hope my dilemma is clear, and I would REALLY appreciate any advice.

    Thanks

    Mac : http://www.dbforums.com/db_images_v3/images/smilies/confused3.gif

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    DECLARE @your_table table (
       code        int
     , description varchar(30)
    )
    
    INSERT INTO @your_table (code, description)
              SELECT 678900, 'Salaries & Wages'
    UNION ALL SELECT 678900, 'Sal & Wag'
    UNION ALL SELECT 678900, 'Salries and Wages'
    UNION ALL SELECT 726353, 'Temp Wages'
    UNION ALL SELECT 726353, 'Casual Workers Wages'
    
    -- How do you define "first" - remember that the order of data in a database has no meaning!
    -- If these are our only fields then I am going to just grab an arbitrary row as the first...
    
    
    -- SQL 2005 onwards
    SELECT code
         , description
    FROM   (
            SELECT code
                 , description
                 , Row_Number() OVER (PARTITION BY code ORDER BY description) As row_num
            FROM   @your_table
           ) As x
    WHERE  row_num = 1
    
    
    -- SQL 2000 onwards
    SELECT code
         , Min(description) As description
    FROM   @your_table
    GROUP
        BY code
    I suggest you delete the dupes from your table and then apply a UNIQUE constraint on the code field to stop this problem from occuring again
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •