Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2008
    Posts
    4

    Unanswered: Oracle Report Help (have my work inside r/o)

    Hello,

    I have a module for a cath lab in a cardiological dept. Data entry for the procedure etc. Part of the entry also includes entry for the equipment used in each procedure. This way we can keep track of all items USED in a month.

    -I have already created the forms/report for the data entry.

    -I have also created a report that calculates all items used. (Table: PatientsCathItemsUsed)

    That works just fine.

    *Now I have created a form called Cath Lab Supplies Record. So everytime a delivery comes for equipment it is entered into the system. We get stuff from different suppliers. ***Now each item code can have different sizes. For example the ItemName : Biomatrix stent can have 5 different sizes but it all falls under one ItemCode.

    I'm not familiar with formula functions. So what I want to do is simply Sum up the value of all Products we have by ItemCode and ItemName and Size. Then I want to subtract that value from all the items used during the month. So we can get a value of how much we have in stock by a certain date of procedure. Would I Sum both numbers up and subtract them and if anyone can guide me if they are any specific formula functions in oracle sql.

    I am pasting my following tables:

    Table: CathLabItems

    SQL> desc CathLabItems
    Name Null? Type
    ------------------------------- -------- ----
    ITEMCODE NOT NULL VARCHAR2(8)
    ITEMNAME NOT NULL VARCHAR2(100)
    USERNAME VARCHAR2(30)
    ENTRYDATE DATE
    ITEMTYPECODE VARCHAR2(2)

    SQL> desc CathLabSuppliesRecord
    Name Null? Type
    ------------------------------- -------- ----
    ITEMRECORDNO NOT NULL NUMBER(12)
    SUPPLIERCODE NOT NULL VARCHAR2(6)
    DOSUPPLY NOT NULL DATE
    USERNAME VARCHAR2(30)
    ENTRYDATE DATE
    DELIVERYNOTENO NOT NULL VARCHAR2(12)

    SQL> desc CathLabSuppliesDetails
    Name Null? Type
    ------------------------------- -------- ----
    ITEMRECORDNO NOT NULL NUMBER(12)-system generated
    ITEMCODE NOT NULL VARCHAR2(8)
    ITEMSIZE VARCHAR2(20)
    BATCHNO VARCHAR2(20)
    DOEXPIRY DATE
    QUANTITY NUMBER(12)
    USERNAME VARCHAR2(30)
    ENTRYDATE DATE

    SQL> desc PatientsCathItemsUsed
    Name Null? Type
    ------------------------------- -------- ----
    CATHID NOT NULL VARCHAR2(12)
    ITEMCODE NOT NULL VARCHAR2(8)
    QUANTITY NOT NULL NUMBER(3)
    ITEMSIZE VARCHAR2(20)
    USERNAME VARCHAR2(30)
    ENTRYDATE DATE
    SUPPLIERCODE VARCHAR2(6)
    BATCHNO VARCHAR2(6)
    ITEMCATEGORY VARCHAR2(20)
    REFNO VARCHAR2(20)

    SQL> desc PatientsCathProcedures
    Name Null? Type
    ------------------------------- -------- ----
    CATHID NOT NULL VARCHAR2(12)
    PID NOT NULL VARCHAR2(9)
    DOPROCEDURE NOT NULL DATE
    AGE_YEARS NUMBER(6,2)
    PROCEDURECODE NOT NULL VARCHAR2(4)
    DOCTORID NOT NULL VARCHAR2(5)
    PERFORMINGDOCTORID VARCHAR2(5)
    ASSISTINGDOCTORID VARCHAR2(5)
    REPORTTITLE VARCHAR2(100)
    REMARKS VARCHAR2(1000
    USERNAME VARCHAR2(30)
    ENTRYDATE DATE
    PROCEDURETYPE NOT NULL CHAR(1)

    -This is my query for CathItemsused and the report comes out fine.

    SELECT distinct c.ItemName, count(a.ItemCode)
    FROM PatientsCathItemsUsed a, PatientsCathProcedures b, CathLabItems c

    WHERE a.CathID = b.CathID

    And a.ItemCode = c.ItemCode

    And To_Date(To_Char(b.DoProcedure,'DD/MM/YYYY'),'DD/MM/YYYY') >= ate1

    And To_Date(To_Char(b.DoProcedure,'DD/MM/YYYY'),'DD/MM/YYYY') <= ate2

    Group By c.ItemName

    -Sorry this is long , I'd appreciate any guidance

  2. #2
    Join Date
    Jun 2008
    Posts
    4
    Ok i Have created 2 queries in Oracle Reports now. 1) Sums up all the supplies
    2) Sums up all equip used to date. Now I need to subtract the two.

    Formula column? Not familiar...

  3. #3
    Join Date
    Jun 2008
    Posts
    4
    btw the 2 queries are

    1)

    Select distinct a.ItemName ||' '|| b.ItemSize,sum(b.Quantity)
    from CathLabItems a,CathLabSuppliesDetails b
    where a.itemcode=b.itemcode
    group by a.ItemName ||' '|| b.ItemSize;

    2)

    SELECT distinct c.ItemName ||' '|| a.ItemSize, sum(a.Quantity)

    FROM

    PatientsCathItemsUsed a, PatientsCathProcedures b,CathLabItems c

    WHERE a.CathID = b.CathID

    And a.ItemCode = c.ItemCode

    And To_Date(To_Char(b.DoProcedure,'DD/MM/YYYY'),'DD/MM/YYYY') <= ate1

    group by c.ItemName ||' '|| a.ItemSize;

Posting Permissions

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