Jumat, 07 Mei 2010

Split Character di SQL Server

Contoh pemisahan karakter (seperti LEFT, RIght,Mid di Visual Studio):


/****** Object:  StoredProcedure [Reports].[Usp_RetrieveRPTDetailARAPB2B]    Script Date: 05/07/2010 15:39:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================


ALTER PROCEDURE [Reports].[Usp_RetrieveRPTDetailARAPB2B]
    -- Add the parameters for the stored procedure here   
--declare
@Company     varchar(6) ,
@InterDistrib varchar(6),
@CdJournalType  varchar(3),
@Channel varchar(30),
@Directorate varchar(6),
@Intermediary varchar(11)

--set
--@company     ='z'
--set
--@InterDistrib ='ALL'
--set
--@CdJournalType  ='ALL'
--set
--@Channel ='AGEN'
--set
--@Directorate ='ALL'
--set
--@Intermediary ='84938800'

as
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
     
 DECLARE @TmpTABLE TABLE    
(   
 company varchar(6) ,
 InterDistrib varchar(6), 
 Channel varchar(40)
)        

DECLARE 
@company1 varchar(6),
@InterDistrib1 varchar(6),
@Channel1 varchar(40)
         
INSERT INTO @TmpTABLE (company,InterDistrib,Channel)
SELECT  @company1, @InterDistrib1 ,@Channel1
       
SELECT @company1 = company ,@InterDistrib1 =InterDistrib , @Channel1 = Channel FROM @TmpTABLE
 
SELECT @company1 = CASE WHEN @company = 'ALL' THEN  '' ELSE @company END;
SELECT @InterDistrib1 = CASE WHEN @InterDistrib = 'ALL' THEN  '' ELSE @InterDistrib END;
SELECT @Channel1 = CASE WHEN @Channel = 'ALL' THEN  '' ELSE @Channel END;

 --IF @IsReady = 1
    -- Insert statements for procedure here
    SELECT
        ROW_NUMBER() OVER (ORDER BY [Company]) AS NO
        , Reports.Company.CompanyDescription AS Company
        , Reports.RPTDetailNoteData.CdJournalType AS CdJournalType
        , Reports.RPTDetailNoteData.CdSubJournalType AS CdSubJournalType
        , Reports.RPTDetailNoteData.NoteNumber AS NoteNumber
        , Reports.RPTDetailNoteData.PolicyNo AS PolicyNo
        , RPTDetailNoteData.OurRefNo AS [OurRefNo]
        , RPTDetailNoteData.[ClaimNo] AS [ClaimNo]
        , Reports.RPTDetailNoteData.ENDNo AS ENDNo
        , Reports.RPTDetailNoteData.Currency AS Currency
        , Reports.RPTDetailNoteData.InternalDistribution AS InterDistrib
        , Reports.Channel.ChannelDescription AS Channel
        , Reports.RPTDetailNoteData.CdProduct AS CdProduct
        , Reports.RPTDetailNoteData.MOUID AS MOUID
        , Reports.RPTDetailNoteData.Cd2W4W AS Cd2W4W
        , Reports.RPTDetailNoteData.CdSalesman AS CdSalesman
        , Reports.RPTDetailNoteData.ChassisNo AS ChassisNo
        , Reports.RPTDetailNoteData.EngineNo AS EngineNo
        , Reports.RPTDetailNoteData.CdPayer AS CdPayer
        , PayerName.CustomerDescription  AS PayerName
        , Reports.RPTDetailNoteData.CdCustomer AS CdCustomer
        , Reports.Customer.CustomerDescription AS CustomerName
        --, Reports.CustomerGroup.CustomerGroupCode + '-' + Reports.CustomerGroup.CustomerGroupDescription   AS CustomerGroup   
        --, Reports.IntermediaryGroup.IntermediaryGroupCode + '-' +Reports.IntermediaryGroup.IntermediaryGroupDescription AS IntermediaryGroup  
        --, Reports.RPTDetailNoteData.CustStatus + '-' +  CustomerStatus.CustomerStatusDescription AS CustomerStatus   
        , Reports.RPTDetailNoteData.DtStart AS DtStart
        , Reports.RPTDetailNoteData.DtEND AS DtEND
        , Reports.RPTDetailNoteData.DtDue AS DtDue
        , Reports.RPTDetailNoteData.CdCOB AS CdCOB
        , Reports.RPTARAPData.NYDAmount AS NYDAmount
        , Reports.RPTDetailNoteData.NoteAmount AS NoteAmount
        , CASE Reports.RPTARAPData.CdAging 
            WHEN 0 THEN RPTARAPData.ARAmount
            ELSE 0
        END AS 'Amount_1_30'
        , CASE RPTARAPData.CdAging
            WHEN 1 THEN RPTARAPData.ARAmount
            ELSE 0
        END AS 'Amount_31_60'

        ,CASE RPTARAPData.CdAging
            WHEN 2 THEN RPTARAPData.ARAmount
            ELSE 0
        END AS 'Amount_61_90'

        ,CASE RPTARAPData.CdAging
            WHEN 3 THEN RPTARAPData.ARAmount
            ELSE 0
        END AS 'Amount_91_120'

        ,CASE RPTARAPData.CdAging
            WHEN 4 THEN RPTARAPData.ARAmount
            ELSE 0
        END AS 'Amount_121_150'

        ,CASE RPTARAPData.CdAging
            WHEN 5 THEN RPTARAPData.ARAmount
            ELSE 0
        END AS 'Amount_151_180'

        ,CASE RPTARAPData.CdAging
            WHEN 6 THEN RPTARAPData.ARAmount
            ELSE 0
        END AS 'Amount_181_210'

        ,CASE RPTARAPData.CdAging
            WHEN 7 THEN RPTARAPData.ARAmount
            ELSE 0
        END AS 'Amount_211_240'

        ,CASE RPTARAPData.CdAging
            WHEN 8 THEN RPTARAPData.ARAmount
            ELSE 0
        END AS 'Amount_241_270'

        ,CASE RPTARAPData.CdAging
            WHEN 9 THEN RPTARAPData.ARAmount
            ELSE 0
        END AS 'Amount_271_300'

        ,CASE RPTARAPData.CdAging
            WHEN 10 THEN RPTARAPData.ARAmount
            ELSE 0
        END AS 'Amount_301_330'

        ,CASE RPTARAPData.CdAging
            WHEN 11 THEN RPTARAPData.ARAmount
            ELSE 0
        END AS 'Amount_331_360'

        ,CASE RPTARAPData.CdAging
            WHEN 12 THEN RPTARAPData.ARAmount
            ELSE 0
        END AS 'Amount_360'
        , Reports.RPTARAPData.ARAmount AS ARAmount
        , Reports.RPTARAPData.AmountGT30 AS AmountGT30
        , Reports.RPTARAPData.AmountGT60 AS AmountGT60
        , Reports.RPTARAPData.AmountGT120 AS AmountGT120
        , Reports.RPTARAPData.AmountGT180 AS AmountGT180
        , Reports.RPTDetailNoteData.GroupingNo AS GroupingNo
        , Reports.RPTDetailNoteData.ContractNo AS ContractNo
        , Reports.RPTDetailNoteData.ClientNo AS ClientNo
        , Reports.RPTDetailNoteData.PHolder AS PHolder
        , Reports.RPTDetailNoteData.DtProduction AS DtProduction
        , Reports.RPTDetailNoteData.UserEntry AS UserEntry
        , Reports.RPTDetailNoteData.ENDRmk AS ENDRmk
        , RPTLastSOANumberData.SOANo AS SOANo
        , RPTlastResultCategoryData.Modul AS Modul
        , Reports.OrderType.OrderTypeDescription AS OrderTypeName
        , Reports.RPTDetailNoteData.CdDealer AS CdDealer
        , ResultCategory.ResultCategoryDescription AS ResultCat
        , Reports.RPTDetailNoteData.DtDOL AS DtDOL
        , Reports.RPTDetailNoteData.CdIntermediary  AS CdIntermediary
        , Reports.RPTDetailNoteData.CdDirectorate AS CdDirectorate
        , Reports.RPTDetailNoteData.CdUnit + '-' + Unit.UnitDescription    AS CdUnit
        , Reports.RPTDetailNoteData.Partner AS Partners 
        , Reports.RPTARAPData.DtAsAt AS DtAsAt 
       

    FROM        
        Reports.RPTDetailNoteData
        INNER JOIN
             Reports.Company 
        ON Reports.RPTDetailNoteData.Company = Reports.Company.CompanyCode
        INNER JOIN
             Reports.RPTARAPData
        ON Reports.RPTDetailNoteData.FinanceNoteID = Reports.RPTARAPData.FinanceNoteID
        INNER JOIN
             Reports.Unit
        ON Reports.RPTDetailNoteData.CdUnit= Reports.Unit.UnitCode
        INNER JOIN
             Reports.Channel
        ON (SUBSTRING(Reports.RPTDetailNoteData.Channel,1,4)) = Reports.Channel.ChannelCode
        INNER JOIN
             Reports.Customer
        ON Reports.RPTDetailNoteData.CdCustomer = Reports.Customer.CustomerCode
        INNER JOIN
             Reports.Customer AS PayerName
        ON Reports.RPTDetailNoteData.CdPayer = PayerName.CustomerCode
        INNER JOIN
             Reports.OrderType
        ON Reports.RPTDetailNoteData.OrderType = Reports.OrderType.OrderTypeCode
        INNER JOIN
             Reports.Customer AS Intermediary
        ON Reports.RPTDetailNoteData.CdIntermediary = Intermediary.CustomerCode
        INNER JOIN
             Reports.RPTlastResultCategoryData AS RPTlastResultCategoryData
        ON RPTlastResultCategoryData.FinanceNoteID = RPTDetailNoteData.FinanceNoteID
        INNER JOIN
            Reports.RPTLastSOANumberData AS RPTLastSOANumberData
        ON RPTLastSOANumberData.FinanceNoteID = RPTDetailNoteData.FinanceNoteID
        INNER JOIN
            Reports.ResultCategory
        ON  ResultCategory.ResultCategoryCode  = RPTlastResultCategoryData.ResultCategory
        INNER JOIN
            Reports.RPTSettlementData RPTSettlementData
         ON RPTDetailNoteData.FinanceNoteID = RPTSettlementData.FinanceNoteID   
                 INNER JOIN
            Reports.CustomerStatus
         ON RPTDetailNoteData.CustStatus = CustomerStatus.CustomerStatusCode 

    WHERE

        --Reports.RPTDetailNoteData.Company LIKE '%' + @Company + '%' AND   
    (CASE WHEN ISNULL(@Company1, '')= '' THEN '' ELSE Company END)=ISNULL(@Company1, '') AND   
        --Reports.RPTDetailNoteData.InternalDistribution LIKE '%' + @InterDistrib + '%' AND Not(
    (CASE WHEN ISNULL(@InterDistrib1, '')= '' THEN '' ELSE RPTDetailNoteData.InternalDistribution END)=ISNULL(@InterDistrib1, '') AND NOT(
            RPTDetailNoteData.Channel='LEAS' AND
            RPTDetailNoteData.Partner IN ('ACC','TAF','FIF','SAF')) AND
        --Reports.RPTDetailNoteData.CdJournalType LIKE '%' + @CdJournalType + '%' AND  
    (CASE WHEN ISNULL(@CdJournalType, '')= '' THEN '' ELSE RPTDetailNoteData.CdJournalType END)=ISNULL(@CdJournalType, '') AND   
        --Reports.RPTDetailNoteData.Channel LIKE '%' + @Channel + '%'  AND
    (CASE WHEN ISNULL(@Channel1, '')= '' THEN '' ELSE Channel END)=ISNULL(@Channel1, '') AND       
        --Reports.RPTDetailNoteData.CdIntermediary  LIKE '%' + @CdInetermediary + '%' AND
    (CASE WHEN ISNULL(@Intermediary, '')= '' THEN '' ELSE CdIntermediary END)=ISNULL(@Intermediary, '') AND       
        --Reports.RPTDetailNoteData.CdDirectorate LIKE '%' + @CdDirectorate + '%'
    (CASE WHEN ISNULL(@Directorate, '')= '' THEN '' ELSE CdDirectorate END)=ISNULL(@Directorate, '')
    AND RPTDetailNoteData.TpNote = 'B2B' 
   
END





Diposting oleh Sebastian Yuke di 15.48

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)