SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    #titanic {float:none} silver trophy
    molona's Avatar
    Join Date
    Feb 2005
    Location
    from Madrid to Heaven
    Posts
    8,243
    Mentioned
    239 Post(s)
    Tagged
    1 Thread(s)

    Multiple queries: Union, Cross Reference... sure there is a better way!

    Maybe I'm over complicating it or maybe I should create a macro that inserts a temp table to do is (although then I couldn't have a historic data access to this particular report which would be annoying but still acceptable)

    Probably you will give me some ideas that I didn't think of.

    I have an Access database with some links to tables inside a Oracle database. These tables keep information about all the calls received and sent by a call center which provides support to certain applications and softwares (some as well knows as Excel, some are specific of this company) for a particular company.

    One table has information about the application itself

    CTG

    PERSID
    SYM (which is the application accronym)
    ASSIGNEE (person responsible for providing specific support)

    And more

    Another table has information about the call itself and the query/problem related to that call

    CALL_REQ

    CATEGORY
    REF_NUM
    TYPE
    CAUSE
    CLASS
    OPEN_DATE
    FINAL_DATE

    and much more

    The relationship between this two tables is PERSID ->CATEGORY

    My report needs to count how many calls (queries/problems reported) in a certain period of time and compare that data to the data of previous years but only for the 25 applications with the highest number of call/queries during that period on each year.

    Let me put an example and explain how it goes. Let's say that I need to get the report for the month of November which just finished.

    Step 1 - From the Oracle database, I would extract all the information from Nov 2013 and then from Nov 2012 and Nov 2011 (two previous years). This should remove any duplicated entry among others. That is, it should exclude any record with a CLASS="MASS", TYPE="PRB" or CAUSE="DUPL"

    Step 2 -Then, I would get the names of the 25 applications which got the highest number of calls in Nov 2013, the 25 applications with the highest number of calls in Nov 2012 and same for 2011 (three different queries)

    For steps 1 and 2, I use 3 queries in Access. The three of them are equal except that each one gets the data from one year

    SELECT TOP 25 CALL_REQ.CATEGORY, IIf([SYM]="W2k","WINDOWS",[SYM]) AS Aplic, Count(CALL_REQ.REF_NUM) AS Total, [Aņo] AS [Year]
    FROM PROB_CTG RIGHT JOIN CALL_REQ ON PROB_CTG.PERSID = CALL_REQ.CATEGORY
    WHERE (((IIf([OPEN_DATE] Is Null,"",CvrtFromUnixTime([OPEN_DATE]))) Like "*/" & [Month] & "/" & [Aņo] & "*") AND ((CALL_REQ.CAUSA)<>"DUPL" Or (CALL_REQ.CAUSA) Is Null) AND ((CALL_REQ.tipo)<>"PRB") AND ((CALL_REQ.clase)<>"MASIVA"))
    GROUP BY CALL_REQ.CATEGORY, IIf([SYM]="W2k","WINDOWS",[SYM]), [Year]
    ORDER BY Count(CALL_REQ.REF_NUM) DESC;



    Now, I have 3 lists with 25 names of applications on them. Some of these applications will be the same in all three years. Some applications will be in just one list.

    Step 3 - Then I have to create one list with the name of all the applications from all the three lists, and the data for the month of November for each of those.

    The result of these should look something like

    2011 2012 2013
    App1 150 200 125
    App2 25 125 152
    App3 275 351 452
    .
    .
    .
    App35 125 355 3

    This table should be ordered DESC from year 2013

    This is my problem. To do this I have used UNION query which, indeed, gets all the application listed and the data if already exists for the three years.

    The problem arises when App5 is on the top 25 for year 2012 (as an example) but it wasn't on the top 25 in 2011 or 2012. The UNION will not provide that.

    So if I do the UNION to get the list of applications, then I have to do another query to get the data for all those applications and for the three years.

    I tried to do this query using an InSelect as a condition for the field SYM (that is, only include the applications listed in the UNION query) but the computer simply dies.

    Each of the first queries need at least 1 min to run.

    Do you think that there is another way, a better way to get what I want?

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,501
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    So the problem is the first three? Probably, because CALL_REQ contains a huge amount of rows? In combination with some data manipulation functions in the SELECT, WHERE and GROUP BY?

    Maybe you could try to do a first extraction of the rows from the three year-months needed without doing any joining and data manipulation?

    Code:
    SELECT *
    FROM CALL_REQ 
    WHERE OPEN_DATE > '2011-11-01' 
    AND OPEN_DATE < '2011-11-30' 
    AND CAUSA <> "DUPL" 
    AND tipo <>"PRB"
    AND clase <> "MASIVA"
    Of course, you'll have to add all those parenthesis Bill likes so much, and maybe '2011-11-01' and '2011-11-30' must be transformed in unixtime (or whatever).
    Once you have this table, then you can join it to the applications table without the huge mass of rows you're not interested in, and without having to operate on the date.

  3. #3
    #titanic {float:none} silver trophy
    molona's Avatar
    Join Date
    Feb 2005
    Location
    from Madrid to Heaven
    Posts
    8,243
    Mentioned
    239 Post(s)
    Tagged
    1 Thread(s)
    Well, it is true that there are some ordering and sorting and that there's a IF there (basically, because each call is related to one application and where it says W2K needs to be substituted by WINDOWS)

    And, yes, there are lots of info in that database as this call center provides technical support to some 7,000 users

    And, definately, dates need to be converted from UNIX time so there's no way to get rid off the convertion funtion either.

    I did wanted to create a view that would be updated each month so I could have the data I needed and work only with that but I only have reading permissions in the Oracle database.

    Creating a temp table in Access importing this data could be a solution but it is a "problem" when you want to compare November report with October's as the temp table would have to be re-filled with the appropiate data each time you request for a report.

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,501
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by molona View Post
    Well, it is true that there are some ordering and sorting and that there's a IF there (basically, because each call is related to one application and where it says W2K needs to be substituted by WINDOWS)

    And, yes, there are lots of info in that database as this call center provides technical support to some 7,000 users

    And, definately, dates need to be converted from UNIX time so there's no way to get rid off the convertion funtion either.

    I did wanted to create a view that would be updated each month so I could have the data I needed and work only with that but I only have reading permissions in the Oracle database.

    Creating a temp table in Access importing this data could be a solution but it is a "problem" when you want to compare November report with October's as the temp table would have to be re-filled with the appropiate data each time you request for a report.
    If you don't want to try out my solution, at least try to put the date conversion on the 'fixed' values, not on the date column.


Bookmarks

Posting Permissions

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