Finding out the differences between coder and auditor coding — oracle-tech

    Forum Stats

  • 3,715,602 Users
  • 2,242,808 Discussions
  • 7,845,458 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Finding out the differences between coder and auditor coding

Hi All,

Hope you are doing well!..I am trying to extract the difference in the dcode entered by a coder and an auditor on a transaction (a transaction is represented by a combination of Billid and ctextid)..Going by the created date time for a transaction the coder enters the dcode at the earliest datetime for a transaction and the auditor enters at a later created time for the same transaction.There are only two rows for a single transaction -the earliest created datetime has the JSON (dcodes) entered by the coder and the later created datetime has the JSON(dcodes) entered by the auditor..Now I need to pick up the codername as the first user (earliest created datetime )in a transaction.Now I need to compare the earliest created JSON with the second created JSON( and pull out what dcodes were entered by the coder was corrected by the auditor ,what dcodes was deleted by the auditor,what dcodes was added by the auditor (in only this case the code entered by the auditor needs to be picked up as it is a newly entered code ,in the previous two cases the dcode by the coder needs to be picked up)..The comparison can be made between the set of JSON's (coder and auditor) by using the number or the description, that is the sequences to be compared can be found by matching on the number or the description(for a pair of billid and ctextid).. ..Please find below the input and output DDL...Can you please help me..

Input table

create table ##input1

(Billid int,

Ctextid int,

info JSON,

user varchar(30),

created datetime2

)

insert into ##input1 values

"('2132','91156','[

{

""description"": ""fabula "",

""dCode"": ""8901"",

""CodeId"": ""90001"",

""messages"": [

""""

],

""Number"": 1

},

{

""description"": ""EXper "",

""dCode"": ""9034"",

""CodeId"": ""88343"",

""messages"": [

""""

],

""Number"": 2

}

]','amt1','08/03/2020 17:07'),"

"('2132','91156','[

{

""description"": ""fabula "",

""dCode"": ""8901"",

""CodeId"": ""90001"",

""messages"": [

""""

],

""Number"": 1

},

{

""description"": ""EXper "",

""dCode"": ""9034"",

""CodeId"": ""88343"",

""messages"": [

""""

],

""Number"": 2

},

{

""description"": ""siluka "",

""dCode"": ""887756"",

""CodeId"": ""883773"",

""messages"": [

""""

],

""Number"": 3

}

]','all1','08/03/2020 21:07'),"

"('5678','99344','[

{

""description"": ""TORYA "",

""dCode"": ""99002"",

""CodeId"": ""988332"",

""messages"": [

""""

],

""Number"": 1

},

{

""description"": ""triact "",

""dCode"": ""90774"",

""CodeId"": ""7800034"",

""messages"": [

""""

],

""Number"": 2

}

]','jk1','08/04/2020 18:07'),"

"('5678','99344','[

{

""description"": ""TORYA "",

""dCode"": ""99002"",

""CodeId"": ""988332"",

""messages"": [

""""

],

""Number"": 1

},

{

""description"": ""triact "",

""dCode"": ""90974"",

""CodeId"": ""78034"",

""messages"": [

""""

],

""Number"": 2

}

]','jk2','08/05/2020 18:07'),"

"('7789','60045','[

{

""description"": ""ABNORMAL FINDINGS HELA "",

""dCode"": ""Z003345"",

""CodeId"": ""288897"",

""messages"": [

""""

],

""Number"": 1

},

{

""description"": ""IMPACTED BILATERAL "",

""dCode"": ""U8923"",

""CodeId"": ""7324"",

""messages"": [

""""

],

""Number"": 2

},

{

""description"": ""IMMUNIZATION "",

""dCode"": ""H678"",

""CodeId"": ""26519"",

""messages"": [

""""

],

""Number"": 3

},

{

""description"": "" RUPT EAR DRUM "",

""dCode"": ""I0892"",

""CodeId"": ""567123"",

""messages"": [

""""

],

""Number"": 4

},

{

""description"": ""CHILDHOOD FEVER "",

""dCode"": ""Y98620"",

""CodeId"": ""55467"",

""messages"": [

""""

],

""Number"": 5

}

]','ec1','08/07/2020 6:07'),"

"('7789','60045','[

{

""description"": ""ABNORMAL FINDINGS HELA "",

""dCode"": ""Z8897645"",

""CodeId"": ""288897"",

""messages"": [

""""

],

""Number"": 1

}, {

""description"": ""IMMUNIZATION "",

""dCode"": ""H67891"",

""CodeId"": ""26519"",

""messages"": [

""""

],

""Number"": 2

},

{

""description"": "" RUPT EAR DRUM "",

""dCode"": ""I0892"",

""CodeId"": ""567123"",

""messages"": [

""""

],

""Number"": 3

},

{

""description"": ""Kirolo substaniss FEVER "",

""dCode"": ""J18907"",

""CodeId"": ""66712"",

""messages"": [

""""

],

""Number"": 4

}','ec2','08/07/2020 17:07')


Output table

create table ##output1

(billid int,

ctextid int,

codername varchar(30),

correctedcode varchar(100),

deletedcode varchar(100),

addedcode varchar(100)

)

insert into ##output1 values

('2132','91156','amt1','','','887756'),

('5678','99344','jk1','90774','',''),

('7789','60045','ec1','Z003345,H678','U8923,Y98620','J18907')

Answers

  • mathguy
    mathguy Member Posts: 9,473 Gold Crown
    edited November 2020

    User_<whatever> wrote:


    create table ##input1

    (Billid int,

    Ctextid int,

    info JSON,

    user varchar(30),

    created datetime2

    )


    Posting CREATE TABLE and INSERT statements is great.

    But it is not so great when they have obvious errors, which you could catch easily if you would test your statements yourself, in an Oracle database, before posting them here. (Please don't say you did - that would be a lie.)

    USER is a reserved keyword in Oracle; the db will not let you use it as a column name, unless you enclose it in double-quotes. (However, that would be a very poor practice: just don't use keywords as identifiers and you won't get into all sorts of problems later.)

    Worse, there is no DATETIME2 data type in Oracle. There is no way you tested your CREATE TABLE on an Oracle database and it didn't throw an error at least for that reason. I don't suppose you created a user-defined data type by that name, did you?

    At this point in trying to help you, I stopped. If you can't even test your code before you post it (which would take a few seconds at most), how can I trust that the rest is OK? Even if I fix the data type myself, and I am able to create the table, and I understand your question, how do I know that the way you asked the question is correct? I fear that I may work on your problem, and later you will come back and say "sorry, I misspoke; in fact my problem is...." Do you see how that could be a problem?

  • User_6TGRX
    User_6TGRX Member Posts: 3

    @mathguy : Please accept my apologies!..I recently moved from using SQL Server to Oracle SQL ..Also I am using a BenchSQL interface ..But that is not a reason..I will figure out the way to test the oracle DDL soon...Meanwhile please find below the changes to my DDL..

    Input table


    create table ##input1


    (Billid int,


    Ctextid int,


    info IS JSON


    cuser varchar(30),


    created timestamp


    )


    insert into ##input1 values


    "('2132','91156','[


    {


    ""description"": ""fabula "",


    ""dCode"": ""8901"",


    ""CodeId"": ""90001"",


    ""messages"": [


    """"


    ],


    ""Number"": 1


    },


    {


    ""description"": ""EXper "",


    ""dCode"": ""9034"",


    ""CodeId"": ""88343"",


    ""messages"": [


    """"


    ],


    ""Number"": 2


    }


    ]','amt1','08/03/2020 17:07'),"


    "('2132','91156','[


    {


    ""description"": ""fabula "",


    ""dCode"": ""8901"",


    ""CodeId"": ""90001"",


    ""messages"": [


    """"


    ],


    ""Number"": 1


    },


    {


    ""description"": ""EXper "",


    ""dCode"": ""9034"",


    ""CodeId"": ""88343"",


    ""messages"": [


    """"


    ],


    ""Number"": 2


    },


    {


    ""description"": ""siluka "",


    ""dCode"": ""887756"",


    ""CodeId"": ""883773"",


    ""messages"": [


    """"


    ],


    ""Number"": 3


    }


    ]','all1','08/03/2020 21:07'),"


    "('5678','99344','[


    {


    ""description"": ""TORYA "",


    ""dCode"": ""99002"",


    ""CodeId"": ""988332"",


    ""messages"": [


    """"


    ],


    ""Number"": 1


    },


    {


    ""description"": ""triact "",


    ""dCode"": ""90774"",


    ""CodeId"": ""7800034"",


    ""messages"": [


    """"


    ],


    ""Number"": 2


    }


    ]','jk1','08/04/2020 18:07'),"


    "('5678','99344','[


    {


    ""description"": ""TORYA "",


    ""dCode"": ""99002"",


    ""CodeId"": ""988332"",


    ""messages"": [


    """"


    ],


    ""Number"": 1


    },


    {


    ""description"": ""triact "",


    ""dCode"": ""90974"",


    ""CodeId"": ""78034"",


    ""messages"": [


    """"


    ],


    ""Number"": 2


    }


    ]','jk2','08/05/2020 18:07'),"


    "('7789','60045','[


    {


    ""description"": ""ABNORMAL FINDINGS HELA "",


    ""dCode"": ""Z003345"",


    ""CodeId"": ""288897"",


    ""messages"": [


    """"


    ],


    ""Number"": 1


    },


    {


    ""description"": ""IMPACTED BILATERAL "",


    ""dCode"": ""U8923"",


    ""CodeId"": ""7324"",


    ""messages"": [


    """"


    ],


    ""Number"": 2


    },


    {


    ""description"": ""IMMUNIZATION "",


    ""dCode"": ""H678"",


    ""CodeId"": ""26519"",


    ""messages"": [


    """"


    ],


    ""Number"": 3


    },


    {


    ""description"": "" RUPT EAR DRUM "",


    ""dCode"": ""I0892"",


    ""CodeId"": ""567123"",


    ""messages"": [


    """"


    ],


    ""Number"": 4


    },


    {


    ""description"": ""CHILDHOOD FEVER "",


    ""dCode"": ""Y98620"",


    ""CodeId"": ""55467"",


    ""messages"": [


    """"


    ],


    ""Number"": 5


    }


    ]','ec1','08/07/2020 6:07'),"


    "('7789','60045','[


    {


    ""description"": ""ABNORMAL FINDINGS HELA "",


    ""dCode"": ""Z8897645"",


    ""CodeId"": ""288897"",


    ""messages"": [


    """"


    ],


    ""Number"": 1


    }, {


    ""description"": ""IMMUNIZATION "",


    ""dCode"": ""H67891"",


    ""CodeId"": ""26519"",


    ""messages"": [


    """"


    ],


    ""Number"": 2


    },


    {


    ""description"": "" RUPT EAR DRUM "",


    ""dCode"": ""I0892"",


    ""CodeId"": ""567123"",


    ""messages"": [


    """"


    ],


    ""Number"": 3


    },


    {


    ""description"": ""Kirolo substaniss FEVER "",


    ""dCode"": ""J18907"",


    ""CodeId"": ""66712"",


    ""messages"": [


    """"


    ],


    ""Number"": 4


    }','ec2','08/07/2020 17:07')




    Output table


    create table ##output1


    (billid int,


    ctextid int,


    codername varchar(30),


    correctedcode varchar(100),


    deletedcode varchar(100),


    addedcode varchar(100)


    )


    insert into ##output1 values


    ('2132','91156','amt1','','','887756'),


    ('5678','99344','jk1','90774','',''),


    ('7789','60045','ec1','Z003345,H678','U8923,Y98620','J18907')

Sign In or Register to comment.