首页 > 代码库 > We need the sql script to query the table Ditronics.Kiosk.Journal to find journal with mismatch denom information versus amount.

We need the sql script to query the table Ditronics.Kiosk.Journal to find journal with mismatch denom information versus amount.

        CREATE TABLE #MoneyTable
            (
              Id INT IDENTITY(1, 1)
                     PRIMARY KEY ,
              MoneyName VARCHAR(50) ,
              Cents INT
            )
        INSERT  INTO #MoneyTable
                ( MoneyName, Cents )
        VALUES  ( UnSupported, 0 )
        INSERT  INTO #MoneyTable
                ( MoneyName, Cents )
        VALUES  ( DollarOne, 100 )
        INSERT  INTO #MoneyTable
                ( MoneyName, Cents )
        VALUES  ( DollarTwo, 200 )
        INSERT  INTO #MoneyTable
                ( MoneyName, Cents )
        VALUES  ( DollarFive, 500 )
        INSERT  INTO #MoneyTable
                ( MoneyName, Cents )
        VALUES  ( DollarTen, 1000 )
        INSERT  INTO #MoneyTable
                ( MoneyName, Cents )
        VALUES  ( DollarTwenty, 2000 )
        INSERT  INTO #MoneyTable
                ( MoneyName, Cents )
        VALUES  ( DollarFifty, 5000 )
        INSERT  INTO #MoneyTable
                ( MoneyName, Cents )
        VALUES  ( DollarHundred, 10000 )
        INSERT  INTO #MoneyTable
                ( MoneyName, Cents )
        VALUES  ( CentOne, 1 )
        INSERT  INTO #MoneyTable
                ( MoneyName, Cents )
        VALUES  ( CentFive, 5 )
        INSERT  INTO #MoneyTable
                ( MoneyName, Cents )
        VALUES  ( CentTen, 10 )
        INSERT  INTO #MoneyTable
                ( MoneyName, Cents )
        VALUES  ( CentTwentyFive, 25 )
        INSERT  INTO #MoneyTable
                ( MoneyName, Cents )
        VALUES  ( CentFifty, 50 )      
            
         
        SELECT  id ,
                Payload.value((TransactionJournal/TransactionDetail/BillBreakTransactionDetail/BillBreakDetail/MoneyDenom/denom)[1],
                              varchar(50)) AS MoneyName ,
                Payload.value((TransactionJournal/TransactionDetail/Amount)[1],
                              int) AS Cents ,
                Payload.value((TransactionJournal/TransactionDualDetail/BillBreakTransactionDetail/BillBreakDetail/MoneyDenom/denom)[1],
                              varchar(50)) AS DualMoneyName ,
                Payload.value((TransactionJournal/TransactionDualDetail/Amount)[1],  --2.Search the specified node in the specified field
                              int) AS DualCents
        INTO    #tempJournal   --1.Don‘t to state create #tempJournal
        FROM    Kiosk.Journal
        WHERE   ModuleTypeId = 3
        
       
       

        SELECT  *
        FROM    Kiosk.Journal
        WHERE   id NOT IN ( SELECT  t1.id
                            FROM    #tempJournal t1 ,
                                    #MoneyTable t2
                            WHERE   ( t1.MoneyName = t2.MoneyName
                                      AND t1.Cents = t2.Cents
                                      AND t1.DualMoneyName IS NULL
                                      AND t1.DualCents IS NULL
                                    )
                                    OR ( t1.DualMoneyName = t1.MoneyName
                                         AND t1.DualCents = t2.Cents
                                         AND t1.MoneyName IS NULL
                                         AND t1.Cents IS NULL
                                       ) )
                AND ModuleTypeId = 3

        DROP TABLE #MoneyTable
        DROP TABLE #tempJournal