首页 > 代码库 > 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
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。