Jump to content
Sign in to follow this  
Paxman

SQL Help

Recommended Posts

»Paxman    4776

Could be because it's Monday morning, but I'm having trouble thinking about how to do this. Essentially I have similar rows:

 

1544334_10153266012784546_78306107098388

 

Very similar data. It's a report done on a project at different dates. I've created a ROW_NUMBER partition (I'll show the code at the bottom). The most recent report will be seqnum = 1, the next will be seqnum = 2 etc etc. What I need to do is compare each of the status based columns (G is Green, A is Amber, R is Red). If it's G in seqnum = 1 and was Amber or Red in seqnum = 2, my TREND column (which I'll need to create) will be IMPROVED. If it's R in seqnum = 1 and was Amber or Green in seqnum = 2, my TREND column will be NEGATIVE. If it's the same (G and G) then the TREND is NO CHANGE.

 

Can someone help me think of a good way to compare the two using the query i've currently got? If this doesn't make sense let me know and I'll try and give a better explanation.

 

SELECT         
            PRJ.CODE, 
            PRJ.ID,
            SRM.FULL_NAME, 
            RAGRPT.CREATED_DATE, 
            RAGRPT.NAME,
            RAGRPT.COP_REPORT_UPDATE OVERALL_HEALTH_COMMENT,
            (CASE
                  WHEN RAGRPT.COP_SCHEDULE_STATUS + RAGRPT.COP_SCOPE_STATUS + RAGRPT.COP_COST_EFT_STATUS + RAGRPT.RMS_RESOURCE_STATUS BETWEEN 40 AND 50 THEN 'G'
                  WHEN RAGRPT.COP_SCHEDULE_STATUS + RAGRPT.COP_SCOPE_STATUS + RAGRPT.COP_COST_EFT_STATUS + RAGRPT.RMS_RESOURCE_STATUS BETWEEN 60 AND 70 THEN 'Y'
                  WHEN RAGRPT.COP_SCHEDULE_STATUS + RAGRPT.COP_SCOPE_STATUS + RAGRPT.COP_COST_EFT_STATUS + RAGRPT.RMS_RESOURCE_STATUS BETWEEN 80 AND 120 THEN 'R'
                  ELSE ' '
            END) OVERALL_STATUS,
            RAGRPT.COP_SCHEDULE_EXP SCHEDULE_EXPLANATION,
            (CASE 
                  WHEN RAGRPT.COP_SCHEDULE_STATUS = 10 THEN 'G'
                  WHEN RAGRPT.COP_SCHEDULE_STATUS = 20 THEN 'Y'
                  WHEN RAGRPT.COP_SCHEDULE_STATUS = 30 THEN 'R'
                  ELSE ' '
            END) SCHEDULE_STATUS,
            RAGRPT.COP_SCOPE_EXP SCOPE_EXPLANATION,
            (CASE 
                  WHEN RAGRPT.COP_SCOPE_STATUS = 10 THEN 'G'
                  WHEN RAGRPT.COP_SCOPE_STATUS = 20 THEN 'Y'
                  WHEN RAGRPT.COP_SCOPE_STATUS = 30 THEN 'R'
                  ELSE ' '
            END) SCOPE_STATUS,
            RAGRPT.COP_EFFORT_EXP COST_EXPLANATION,
            (CASE 
                  WHEN RAGRPT.COP_COST_EFT_STATUS = 10 THEN 'G'
                  WHEN RAGRPT.COP_COST_EFT_STATUS = 20 THEN 'Y'
                  WHEN RAGRPT.COP_COST_EFT_STATUS = 30 THEN 'R'
                  ELSE ' '
            END) COST_STATUS,
            RAGRPT.RMS_RESOURCE_EXP RESOURCE_EXPLANATION,
            (CASE 
                  WHEN RAGRPT.RMS_RESOURCE_STATUS = 10 THEN 'G'
                  WHEN RAGRPT.RMS_RESOURCE_STATUS = 20 THEN 'Y'
                  WHEN RAGRPT.RMS_RESOURCE_STATUS = 30 THEN 'R'
                  ELSE ' '
            END) RESOURCE_STATUS,
            RAGRPT.COP_KEY_ACCOMPLISH KEY_ACCOMPLISHMENTS,
            RAGRPT.COP_UPCOMING_ACT UPCOMING_ACTIVITIES,
            Row_Number() OVER (PARTITION BY PRJ.CODE ORDER BY RAGRPT.CREATED_DATE DESC) AS SEQNUM
                
FROM        ODF_CA_COP_PRJ_STATUSRPT RAGRPT, 
            INV_INVESTMENTS PRJ,
            SRM_RESOURCES SRM  
WHERE       PRJ.ID = RAGRPT.ODF_PARENT_ID (+)
            AND RAGRPT.CREATED_BY = SRM.ID (+)
            AND RAGRPT.CREATED_DATE BETWEEN Trunc(SYSDATE, 'DD') - 28 AND SYSDATE

Share this post


Link to post
Share on other sites
»Paxman    4776

Don't mind me.

 

CASE WHEN (SEQNUM = 1 AND [OTHER THING]) AND (SEQNUM = 2 AND [OTHER THING]) THEN [COMPARISON]

 

Definitely Monday being shitty.

Share this post


Link to post
Share on other sites
Inexorably    339

I don't into SQL (only c++) but after googling it quickly:

[spoiler]

// sql.cpp -- sample program to read a database
// Special project requirements:
//   Project->Settings->All Configurations: use MFC in a static library
//   Add a Libs folder to the project and include in it Odbc32.lib from the vc98 folder
//   Configure the database for ODBC access using Start->Settings->Control Panel->ODBC32

#include <iostream>
using std::cin;
using std::cout;
using std::endl;

#include <afxwin.h>
#include <sql.h>
#include <sqlext.h>

int main()
{
  // declarations needed for SQL
  RETCODE rc;        // ODBC return code
  HENV henv;         // Environment
  HDBC hdbc;         // Connection handle
  HSTMT hstmt;       // Statement handle
  SDWORD cbData;     // Output length of data

  // attempt to connect to the ODBC database
  char db[] = "myDatabase"; // ODBC database name
  cout << "Attempting to open database " << db << "..." << endl;
  SQLAllocEnv(&henv);
  SQLAllocConnect(henv, &hdbc);
  rc = SQLConnect(hdbc, (unsigned char*)db, SQL_NTS, 0, 0, 0, 0);

  // if not successful, quit
  if ((rc != SQL_SUCCESS) && (rc != SQL_SUCCESS_WITH_INFO))
  {
    cout << "Cannot open database -- make sure ODBC is configured properly." << endl;
    SQLFreeConnect(hdbc);
    SQLFreeEnv(henv);
    cout << "Press ENTER to continue." << endl;
    cin.get();
    return 1;
  }

  // create a SQL statement
  rc = SQLAllocStmt(hdbc, &hstmt);
  char* sql = "INSERT INTO courses (desig, term, units, grade) VALUES ('COMSC-265', 'FA2001', 4, 'A')";
  cout << "Executing " << sql << endl;
  rc = SQLExecDirect(hstmt, (unsigned char*)sql, SQL_NTS);
  if ((rc != SQL_SUCCESS) && (rc != SQL_SUCCESS_WITH_INFO))
  {
    // error executing SQL statement
    SQLCHAR sqlState[8], msgText[1024];
    SQLINTEGER NativeErrorPtr;
    SQLSMALLINT TextLengthPtr;
    SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, 1, sqlState,
      &NativeErrorPtr, msgText, sizeof(msgText), &TextLengthPtr);
    SQLFreeStmt(hstmt, SQL_DROP);
    SQLDisconnect(hdbc);
    SQLFreeConnect(hdbc);
    SQLFreeEnv(henv);
    cout << "Error  " << endl << msgText << endl;
    cout << "Press ENTER to continue." << endl;
    cin.get();
    return 2;
  }
  SQLFreeStmt(hstmt, SQL_DROP);

  // create another SQL statement
  rc = SQLAllocStmt(hdbc, &hstmt);
  sql = "SELECT desig,term,units,grade FROM courses";

  // execute the SQL statement
  cout << "Executing " << sql << endl;
  rc = SQLExecDirect(hstmt, (unsigned char*)sql, SQL_NTS);
  if ((rc != SQL_SUCCESS) && (rc != SQL_SUCCESS_WITH_INFO))
  {
    // error executing SQL statement
    SQLCHAR sqlState[8], msgText[1024];
    SQLINTEGER NativeErrorPtr;
    SQLSMALLINT TextLengthPtr;
    SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, 1, sqlState,
      &NativeErrorPtr, msgText, sizeof(msgText), &TextLengthPtr);
    SQLFreeStmt(hstmt, SQL_DROP);
    SQLDisconnect(hdbc);
    SQLFreeConnect(hdbc);
    SQLFreeEnv(henv);
    cout << "Error  " << endl << msgText << endl;
    cout << "Press ENTER to continue." << endl;
    cin.get();
    return 3;
  }

  // if SQL statement is a SELECT statement, show records (skip for INSERT, DELETE, or UPDATE)
  cout << "Reading records returned by SQL statement..." << endl;
  for (rc = SQLFetch(hstmt); rc == SQL_SUCCESS; rc = SQLFetch(hstmt))
  {
    // refs:
    // http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcc_data_types.asp
    // http://www.pdc.kth.se/doc/SP/manuals/db2-7.1/html/db2l0/frame3.htm#db2l0162
    char desig[32], term[8], grade[2];
    int units;
    SQLGetData(hstmt, 1, SQL_C_CHAR, desig, sizeof(desig), &cbData); // read 1st column as text
    SQLGetData(hstmt, 2, SQL_C_CHAR, term, sizeof(term), &cbData); // read 2nd column as text
    SQLGetData(hstmt, 3, SQL_INTEGER, &units, sizeof(int), &cbData); // read 3rd column as int
    SQLGetData(hstmt, 4, SQL_C_CHAR, grade, sizeof(grade), &cbData); // read 4th column as text
    cout << desig << ' ' << term << ' ' << units << ' ' << grade << endl;
  }

  // close database
  SQLFreeStmt(hstmt, SQL_DROP);
  SQLDisconnect(hdbc);
  SQLFreeConnect(hdbc);

  cout << "Finished. Press ENTER to continue." << endl;
  cin.get();
  return 0;
}

[/spoiler]

You could do something like:
[spoiler]

string prior;
string current;
int max = ???; //The number of queries you have (?)
prior = SQLGetData(hstmt, max, SQL_C_CHAR, desig, sizeof(desig), &cbData);

for (int i = max - 1; i != 0; i--)
{
current = SQLGetData(hstmt, i, SQL_C_CHAR, desig, sizeof(desig), &cbData);

if (current == "G" && (prior == "R" || prior == "A"))
//write to Trend column improved
else if (current == "R" && (prior == "A" || prior == "G"))
//write to Trend column neg
else if (current == prior && prior == "G")
//write no change

prior = current;
}

[/spoiler]

But I don't know if your database is being updated while you're running this or what (which would effect max being const), so yeah, just throwing this out there -- would similar code work when translated into SQL queries?

Share this post


Link to post
Share on other sites
»Paxman    4776
Not sure but I don't think so. My other option is to duplicate the table under two aliases and then compare, but I'm then interested to know if there's a way to do it when it's in a single table.

Share this post


Link to post
Share on other sites
»Paxman    4776

Ok, I'm on the right track. Unfortunately what I have now is two tables (one containing all the projects with seqnum = 1, the other with seqnum = 2) that compare... but each row of the first table is comparing with every row in the second table. So basically for each project (row) in table 1, it's comparing itself against every project (row) in table 2.

 

Is there a way I can tell it to stop once it finds a match WHEN t1.code = t2.code?

 

[spoiler]

SELECT t1.CODE,
       t1.ID,
       SubStr(To_Char((Trunc(t1.CREATED_DATE, 'DD') - 7)), 1, 10) SR_PERIOD_START, 
       SubStr(To_Char(t1.CREATED_DATE), 1, 10) SR_PERIOD_FINISH,
       t1.NAME,
       t1.OVERALL_HEALTH_COMMENT,
       t1.OVERALL_STATUS,
       (CASE
            WHEN t1.CODE = t2.CODE AND t1.OVERALL_STATUS = 'G' AND (t2.OVERALL_STATUS = 'A' OR t2.OVERALL_STATUS = 'R') THEN 'IMPROVED'
            WHEN t1.CODE = t2.CODE AND t1.OVERALL_STATUS = 'A' AND (t2.OVERALL_STATUS = 'R') THEN 'IMPROVED'
            WHEN t1.CODE = t2.CODE AND t1.OVERALL_STATUS = 'G' AND (t2.OVERALL_STATUS = 'G') THEN 'NO CHANGE'
            WHEN t1.CODE = t2.CODE AND t1.OVERALL_STATUS = 'A' AND (t2.OVERALL_STATUS = 'A') THEN 'NO CHANGE'
            WHEN t1.CODE = t2.CODE AND t1.OVERALL_STATUS = 'R' AND (t2.OVERALL_STATUS = 'R') THEN 'NO CHANGE'
            WHEN t1.CODE = t2.CODE AND t1.OVERALL_STATUS = 'R' AND (t2.OVERALL_STATUS = 'A' OR t2.OVERALL_STATUS = 'G') THEN 'NEGATIVE'
            WHEN t1.CODE = t2.CODE AND t1.OVERALL_STATUS = 'A' AND (t2.OVERALL_STATUS = 'G') THEN 'NEGATIVE'
            ELSE 'N/A'
        END) OVERALL_COMPARE,
       t1.SCHEDULE_EXPLANATION,
       t1.SCHEDULE_STATUS,
       (CASE
            WHEN t1.CODE = t2.CODE AND t1.SCHEDULE_STATUS = 'G' AND (t2.SCHEDULE_STATUS = 'A' OR t2.SCHEDULE_STATUS = 'R') THEN 'IMPROVED'
            WHEN t1.CODE = t2.CODE AND t1.SCHEDULE_STATUS = 'A' AND (t2.SCHEDULE_STATUS = 'R') THEN 'IMPROVED'
            WHEN t1.CODE = t2.CODE AND t1.SCHEDULE_STATUS = 'G' AND (t2.SCHEDULE_STATUS = 'G') THEN 'NO CHANGE'
            WHEN t1.CODE = t2.CODE AND t1.SCHEDULE_STATUS = 'A' AND (t2.SCHEDULE_STATUS = 'A') THEN 'NO CHANGE'
            WHEN t1.CODE = t2.CODE AND t1.SCHEDULE_STATUS = 'R' AND (t2.SCHEDULE_STATUS = 'R') THEN 'NO CHANGE'
            WHEN t1.CODE = t2.CODE AND t1.SCHEDULE_STATUS = 'R' AND (t2.SCHEDULE_STATUS = 'A' OR t2.SCHEDULE_STATUS = 'G') THEN 'NEGATIVE'
            WHEN t1.CODE = t2.CODE AND t1.SCHEDULE_STATUS = 'A' AND (t2.SCHEDULE_STATUS = 'G') THEN 'NEGATIVE'
            ELSE 'N/A'
        END) SCHEDULE_COMPARE,
       t1.SCOPE_EXPLANATION,
       t1.SCOPE_STATUS,
       (CASE
            WHEN t1.CODE = t2.CODE AND t1.SCOPE_STATUS = 'G' AND (t2.SCOPE_STATUS = 'A' OR t2.SCOPE_STATUS = 'R') THEN 'IMPROVED'
            WHEN t1.CODE = t2.CODE AND t1.SCOPE_STATUS = 'A' AND (t2.SCOPE_STATUS = 'R') THEN 'IMPROVED'
            WHEN t1.CODE = t2.CODE AND t1.SCOPE_STATUS = 'G' AND (t2.SCOPE_STATUS = 'G') THEN 'NO CHANGE'
            WHEN t1.CODE = t2.CODE AND t1.SCOPE_STATUS = 'A' AND (t2.SCOPE_STATUS = 'A') THEN 'NO CHANGE'
            WHEN t1.CODE = t2.CODE AND t1.SCOPE_STATUS = 'R' AND (t2.SCOPE_STATUS = 'R') THEN 'NO CHANGE'
            WHEN t1.CODE = t2.CODE AND t1.SCOPE_STATUS = 'R' AND (t2.SCOPE_STATUS = 'A' OR t2.SCOPE_STATUS = 'G') THEN 'NEGATIVE'
            WHEN t1.CODE = t2.CODE AND t1.SCOPE_STATUS = 'A' AND (t2.SCOPE_STATUS = 'G') THEN 'NEGATIVE'
            ELSE 'N/A'
        END) SCOPE_COMPARE,
       t1.COST_EXPLANATION,
       t1.COST_STATUS,
       (CASE
            WHEN t1.CODE = t2.CODE AND t1.COST_STATUS = 'G' AND (t2.COST_STATUS = 'A' OR t2.COST_STATUS = 'R') THEN 'IMPROVED'
            WHEN t1.CODE = t2.CODE AND t1.COST_STATUS = 'A' AND (t2.COST_STATUS = 'R') THEN 'IMPROVED'
            WHEN t1.CODE = t2.CODE AND t1.COST_STATUS = 'G' AND (t2.COST_STATUS = 'G') THEN 'NO CHANGE'
            WHEN t1.CODE = t2.CODE AND t1.COST_STATUS = 'A' AND (t2.COST_STATUS = 'A') THEN 'NO CHANGE'
            WHEN t1.CODE = t2.CODE AND t1.COST_STATUS = 'R' AND (t2.COST_STATUS = 'R') THEN 'NO CHANGE'
            WHEN t1.CODE = t2.CODE AND t1.COST_STATUS = 'R' AND (t2.COST_STATUS = 'A' OR t2.COST_STATUS = 'G') THEN 'NEGATIVE'
            WHEN t1.CODE = t2.CODE AND t1.COST_STATUS = 'A' AND (t2.COST_STATUS = 'G') THEN 'NEGATIVE'
            ELSE 'N/A'
        END) COST_COMPARE,
       t1.RESOURCE_EXPLANATION,
       t1.RESOURCE_STATUS,
       (CASE
            WHEN t1.CODE = t2.CODE AND t1.RESOURCE_STATUS = 'G' AND (t2.RESOURCE_STATUS = 'A' OR t2.RESOURCE_STATUS = 'R') THEN 'IMPROVED'
            WHEN t1.CODE = t2.CODE AND t1.RESOURCE_STATUS = 'A' AND (t2.RESOURCE_STATUS = 'R') THEN 'IMPROVED'
            WHEN t1.CODE = t2.CODE AND t1.RESOURCE_STATUS = 'G' AND (t2.RESOURCE_STATUS = 'G') THEN 'NO CHANGE'
            WHEN t1.CODE = t2.CODE AND t1.RESOURCE_STATUS = 'A' AND (t2.RESOURCE_STATUS = 'A') THEN 'NO CHANGE'
            WHEN t1.CODE = t2.CODE AND t1.RESOURCE_STATUS = 'R' AND (t2.RESOURCE_STATUS = 'R') THEN 'NO CHANGE'
            WHEN t1.CODE = t2.CODE AND t1.RESOURCE_STATUS = 'R' AND (t2.RESOURCE_STATUS = 'A' OR t2.RESOURCE_STATUS = 'G') THEN 'NEGATIVE'
            WHEN t1.CODE = t2.CODE AND t1.RESOURCE_STATUS = 'A' AND (t2.RESOURCE_STATUS = 'G') THEN 'NEGATIVE'
            ELSE 'N/A'
        END) RESOURCE_COMPARE,
       t1.KEY_ACCOMPLISHMENTS,
       t1.UPCOMING_ACTIVITIES
FROM  (SELECT         
            PRJ.CODE, 
            PRJ.ID,
            (SRM.LAST_NAME + ', ' + SRM.FIRST_NAME) FULL_NAME, 
            RAGRPT.CREATED_DATE, 
            RAGRPT.NAME,
            RAGRPT.COP_REPORT_UPDATE OVERALL_HEALTH_COMMENT,
            (CASE
                  WHEN RAGRPT.COP_SCHEDULE_STATUS + RAGRPT.COP_SCOPE_STATUS + RAGRPT.COP_COST_EFT_STATUS + RAGRPT.RMS_RESOURCE_STATUS BETWEEN 40 AND 50 THEN 'G'
                  WHEN RAGRPT.COP_SCHEDULE_STATUS + RAGRPT.COP_SCOPE_STATUS + RAGRPT.COP_COST_EFT_STATUS + RAGRPT.RMS_RESOURCE_STATUS BETWEEN 60 AND 70 THEN 'A'
                  WHEN RAGRPT.COP_SCHEDULE_STATUS + RAGRPT.COP_SCOPE_STATUS + RAGRPT.COP_COST_EFT_STATUS + RAGRPT.RMS_RESOURCE_STATUS BETWEEN 80 AND 120 THEN 'R'
                  ELSE ' '
            END) OVERALL_STATUS,
            RAGRPT.COP_SCHEDULE_EXP SCHEDULE_EXPLANATION,
            (CASE 
                  WHEN RAGRPT.COP_SCHEDULE_STATUS = 10 THEN 'G'
                  WHEN RAGRPT.COP_SCHEDULE_STATUS = 20 THEN 'A'
                  WHEN RAGRPT.COP_SCHEDULE_STATUS = 30 THEN 'R'
                  ELSE ' '
            END) SCHEDULE_STATUS,
            RAGRPT.COP_SCOPE_EXP SCOPE_EXPLANATION,
            (CASE 
                  WHEN RAGRPT.COP_SCOPE_STATUS = 10 THEN 'G'
                  WHEN RAGRPT.COP_SCOPE_STATUS = 20 THEN 'A'
                  WHEN RAGRPT.COP_SCOPE_STATUS = 30 THEN 'R'
                  ELSE ' '
            END) SCOPE_STATUS,
            RAGRPT.COP_EFFORT_EXP COST_EXPLANATION,
            (CASE 
                  WHEN RAGRPT.COP_COST_EFT_STATUS = 10 THEN 'G'
                  WHEN RAGRPT.COP_COST_EFT_STATUS = 20 THEN 'A'
                  WHEN RAGRPT.COP_COST_EFT_STATUS = 30 THEN 'R'
                  ELSE ' '
            END) COST_STATUS,
            RAGRPT.RMS_RESOURCE_EXP RESOURCE_EXPLANATION,
            (CASE 
                  WHEN RAGRPT.RMS_RESOURCE_STATUS = 10 THEN 'G'
                  WHEN RAGRPT.RMS_RESOURCE_STATUS = 20 THEN 'A'
                  WHEN RAGRPT.RMS_RESOURCE_STATUS = 30 THEN 'R'
                  ELSE ' '
            END) RESOURCE_STATUS,
            RAGRPT.COP_KEY_ACCOMPLISH KEY_ACCOMPLISHMENTS,
            RAGRPT.COP_UPCOMING_ACT UPCOMING_ACTIVITIES,
            Row_Number() OVER (PARTITION BY PRJ.CODE ORDER BY RAGRPT.CREATED_DATE DESC) AS SEQNUM
                
      FROM  ODF_CA_COP_PRJ_STATUSRPT RAGRPT, 
            INV_INVESTMENTS PRJ,
            SRM_RESOURCES SRM  
      WHERE PRJ.ID = RAGRPT.ODF_PARENT_ID (+)
            AND RAGRPT.CREATED_BY = SRM.ID (+)
            AND RAGRPT.CREATED_DATE BETWEEN Trunc(SYSDATE, 'DD') - 28 AND SYSDATE
      )t1,
      (SELECT         
            (CASE
                  WHEN RAGRPT.COP_SCHEDULE_STATUS + RAGRPT.COP_SCOPE_STATUS + RAGRPT.COP_COST_EFT_STATUS + RAGRPT.RMS_RESOURCE_STATUS BETWEEN 40 AND 50 THEN 'G'
                  WHEN RAGRPT.COP_SCHEDULE_STATUS + RAGRPT.COP_SCOPE_STATUS + RAGRPT.COP_COST_EFT_STATUS + RAGRPT.RMS_RESOURCE_STATUS BETWEEN 60 AND 70 THEN 'A'
                  WHEN RAGRPT.COP_SCHEDULE_STATUS + RAGRPT.COP_SCOPE_STATUS + RAGRPT.COP_COST_EFT_STATUS + RAGRPT.RMS_RESOURCE_STATUS BETWEEN 80 AND 120 THEN 'R'
                  ELSE ' '
            END) OVERALL_STATUS,
            (CASE 
                  WHEN RAGRPT.COP_SCHEDULE_STATUS = 10 THEN 'G'
                  WHEN RAGRPT.COP_SCHEDULE_STATUS = 20 THEN 'A'
                  WHEN RAGRPT.COP_SCHEDULE_STATUS = 30 THEN 'R'
                  ELSE ' '
            END) SCHEDULE_STATUS,
            (CASE 
                  WHEN RAGRPT.COP_SCOPE_STATUS = 10 THEN 'G'
                  WHEN RAGRPT.COP_SCOPE_STATUS = 20 THEN 'A'
                  WHEN RAGRPT.COP_SCOPE_STATUS = 30 THEN 'R'
                  ELSE ' '
            END) SCOPE_STATUS,
            (CASE 
                  WHEN RAGRPT.COP_COST_EFT_STATUS = 10 THEN 'G'
                  WHEN RAGRPT.COP_COST_EFT_STATUS = 20 THEN 'A'
                  WHEN RAGRPT.COP_COST_EFT_STATUS = 30 THEN 'R'
                  ELSE ' '
            END) COST_STATUS,
            (CASE 
                  WHEN RAGRPT.RMS_RESOURCE_STATUS = 10 THEN 'G'
                  WHEN RAGRPT.RMS_RESOURCE_STATUS = 20 THEN 'A'
                  WHEN RAGRPT.RMS_RESOURCE_STATUS = 30 THEN 'R'
                  ELSE ' '
            END) RESOURCE_STATUS,
            PRJ.CODE,
            Row_Number() OVER (PARTITION BY PRJ.CODE ORDER BY RAGRPT.CREATED_DATE DESC) AS SEQNUM
                
      FROM  ODF_CA_COP_PRJ_STATUSRPT RAGRPT, 
            INV_INVESTMENTS PRJ,
            SRM_RESOURCES SRM  
      WHERE PRJ.ID = RAGRPT.ODF_PARENT_ID (+)
            AND RAGRPT.CREATED_BY = SRM.ID (+)
            AND RAGRPT.CREATED_DATE BETWEEN Trunc(SYSDATE, 'DD') - 28 AND SYSDATE
      )t2
WHERE t1.SEQNUM = 1 
      AND t2.SEQNUM = 2
 
 

[/spoiler]

Share this post


Link to post
Share on other sites
»Paxman    4776

For now I've got it working, but I still wonder if there's a shorter way of doing it. Definitely a Monday thing (because it was just a join), but I also believed there had to be a shorter way of doing it.

 

[spoiler]

 

SELECT CODE,

       ID,
       SR_PERIOD_START,
       SR_PERIOD_FINISH,
       NAME,
       OVERALL_HEALTH_COMMENT,
       NEW_OVERALL_STATUS AS HEALTH_STATUS,
       (CASE
                    WHEN NEW_OVERALL_STATUS = 'G' AND (OLD_OVERALL_STATUS = 'A' OR OLD_OVERALL_STATUS = 'R') THEN 'IMPROVED'
                    WHEN NEW_OVERALL_STATUS = 'A' AND (OLD_OVERALL_STATUS = 'R') THEN 'IMPROVED'
                    WHEN NEW_OVERALL_STATUS = 'G' AND (OLD_OVERALL_STATUS = 'G') THEN 'NO CHANGE'
                    WHEN NEW_OVERALL_STATUS = 'A' AND (OLD_OVERALL_STATUS = 'A') THEN 'NO CHANGE'
                    WHEN NEW_OVERALL_STATUS = 'R' AND (OLD_OVERALL_STATUS = 'R') THEN 'NO CHANGE'
                    WHEN NEW_OVERALL_STATUS = 'R' AND (OLD_OVERALL_STATUS = 'A' OR OLD_OVERALL_STATUS = 'G') THEN 'NEGATIVE'
                    WHEN NEW_OVERALL_STATUS = 'A' AND (OLD_OVERALL_STATUS = 'G') THEN 'NEGATIVE'
                    ELSE 'N/A'
        END) OVERALL_TREND,
        SCHEDULE_EXPLANATION,
        NEW_SCHED_STATUS AS SCHEDULE_STATUS,
        (CASE
                    WHEN NEW_SCHED_STATUS = 'G' AND (OLD_SCHED_STATUS = 'A' OR OLD_SCHED_STATUS = 'R') THEN 'IMPROVED'
                    WHEN NEW_SCHED_STATUS = 'A' AND (OLD_SCHED_STATUS = 'R') THEN 'IMPROVED'
                    WHEN NEW_SCHED_STATUS = 'G' AND (OLD_SCHED_STATUS = 'G') THEN 'NO CHANGE'
                    WHEN NEW_SCHED_STATUS = 'A' AND (OLD_SCHED_STATUS = 'A') THEN 'NO CHANGE'
                    WHEN NEW_SCHED_STATUS = 'R' AND (OLD_SCHED_STATUS = 'R') THEN 'NO CHANGE'
                    WHEN NEW_SCHED_STATUS = 'R' AND (OLD_SCHED_STATUS = 'A' OR OLD_SCHED_STATUS = 'G') THEN 'NEGATIVE'
                    WHEN NEW_SCHED_STATUS = 'A' AND (OLD_SCHED_STATUS = 'G') THEN 'NEGATIVE'
                    ELSE 'N/A'
        END) SCHEDULE_TREND,
        SCOPE_EXPLANATION,
        NEW_SCOPE_STATUS AS SCOPE_STATUS,
        (CASE
                    WHEN NEW_SCOPE_STATUS = 'G' AND (OLD_SCOPE_STATUS = 'A' OR OLD_SCOPE_STATUS = 'R') THEN 'IMPROVED'
                    WHEN NEW_SCOPE_STATUS = 'A' AND (OLD_SCOPE_STATUS = 'R') THEN 'IMPROVED'
                    WHEN NEW_SCOPE_STATUS = 'G' AND (OLD_SCOPE_STATUS = 'G') THEN 'NO CHANGE'
                    WHEN NEW_SCOPE_STATUS = 'A' AND (OLD_SCOPE_STATUS = 'A') THEN 'NO CHANGE'
                    WHEN NEW_SCOPE_STATUS = 'R' AND (OLD_SCOPE_STATUS = 'R') THEN 'NO CHANGE'
                    WHEN NEW_SCOPE_STATUS = 'R' AND (OLD_SCOPE_STATUS = 'A' OR OLD_SCOPE_STATUS = 'G') THEN 'NEGATIVE'
                    WHEN NEW_SCOPE_STATUS = 'A' AND (OLD_SCOPE_STATUS = 'G') THEN 'NEGATIVE'
                    ELSE 'N/A'
        END) SCOPE_TREND,
        COST_EXPLANATION,
        NEW_COST_STATUS AS COST_STATUS,
        (CASE
                    WHEN NEW_COST_STATUS = 'G' AND (OLD_COST_STATUS = 'A' OR OLD_COST_STATUS = 'R') THEN 'IMPROVED'
                    WHEN NEW_COST_STATUS = 'A' AND (OLD_COST_STATUS = 'R') THEN 'IMPROVED'
                    WHEN NEW_COST_STATUS = 'G' AND (OLD_COST_STATUS = 'G') THEN 'NO CHANGE'
                    WHEN NEW_COST_STATUS = 'A' AND (OLD_COST_STATUS = 'A') THEN 'NO CHANGE'
                    WHEN NEW_COST_STATUS = 'R' AND (OLD_COST_STATUS = 'R') THEN 'NO CHANGE'
                    WHEN NEW_COST_STATUS = 'R' AND (OLD_COST_STATUS = 'A' OR OLD_COST_STATUS = 'G') THEN 'NEGATIVE'
                    WHEN NEW_COST_STATUS = 'A' AND (OLD_COST_STATUS = 'G') THEN 'NEGATIVE'
                    ELSE 'N/A'
        END) COST_TREND,
        RESOURCE_EXPLANATION,
        NEW_RESOURCE_STATUS AS RESOURCE_STATUS,
        (CASE
                    WHEN NEW_RESOURCE_STATUS = 'G' AND (OLD_RESOURCE_STATUS = 'A' OR OLD_RESOURCE_STATUS = 'R') THEN 'IMPROVED'
                    WHEN NEW_RESOURCE_STATUS = 'A' AND (OLD_RESOURCE_STATUS = 'R') THEN 'IMPROVED'
                    WHEN NEW_RESOURCE_STATUS = 'G' AND (OLD_RESOURCE_STATUS = 'G') THEN 'NO CHANGE'
                    WHEN NEW_RESOURCE_STATUS = 'A' AND (OLD_RESOURCE_STATUS = 'A') THEN 'NO CHANGE'
                    WHEN NEW_RESOURCE_STATUS = 'R' AND (OLD_RESOURCE_STATUS = 'R') THEN 'NO CHANGE'
                    WHEN NEW_RESOURCE_STATUS = 'R' AND (OLD_RESOURCE_STATUS = 'A' OR OLD_RESOURCE_STATUS = 'G') THEN 'NEGATIVE'
                    WHEN NEW_RESOURCE_STATUS = 'A' AND (OLD_RESOURCE_STATUS = 'G') THEN 'NEGATIVE'
                    ELSE 'N/A'
        END) RESOURCE_TREND,
        KEY_ACCOMPLISHMENTS,
        UPCOMING_ACTIVITIES
 
FROM (SELECT  t1.CODE,
              t1.ID,
              SubStr(To_Char((Trunc(t1.CREATED_DATE, 'DD') - 7)), 1, 10) SR_PERIOD_START, 
              SubStr(To_Char(t1.CREATED_DATE), 1, 10) SR_PERIOD_FINISH,
              t1.NAME,
              t1.OVERALL_HEALTH_COMMENT,
              t1.OVERALL_STATUS AS NEW_OVERALL_STATUS,
              t2.OVERALL_STATUS AS OLD_OVERALL_STATUS,
              t1.SCHEDULE_EXPLANATION,
              t1.SCHEDULE_STATUS AS NEW_SCHED_STATUS,
              t2.SCHEDULE_STATUS AS OLD_SCHED_STATUS,
              t1.SCOPE_EXPLANATION,
              t1.SCOPE_STATUS AS NEW_SCOPE_STATUS,
              t2.SCOPE_STATUS AS OLD_SCOPE_STATUS,
              t1.COST_EXPLANATION,
              t1.COST_STATUS AS NEW_COST_STATUS,
              t2.COST_STATUS AS OLD_COST_STATUS,
              t1.RESOURCE_EXPLANATION,
              t1.RESOURCE_STATUS AS NEW_RESOURCE_STATUS,
              t2.RESOURCE_STATUS AS OLD_RESOURCE_STATUS,
              t1.KEY_ACCOMPLISHMENTS,
              t1.UPCOMING_ACTIVITIES
      FROM  (SELECT         
                    PRJ.CODE, 
                    PRJ.ID,
                    (SRM.LAST_NAME + ', ' + SRM.FIRST_NAME) FULL_NAME, 
                    RAGRPT.CREATED_DATE, 
                    RAGRPT.NAME,
                    RAGRPT.COP_REPORT_UPDATE OVERALL_HEALTH_COMMENT,
                    (CASE
                          WHEN RAGRPT.COP_SCHEDULE_STATUS + RAGRPT.COP_SCOPE_STATUS + RAGRPT.COP_COST_EFT_STATUS + RAGRPT.RMS_RESOURCE_STATUS BETWEEN 40 AND 50 THEN 'G'
                          WHEN RAGRPT.COP_SCHEDULE_STATUS + RAGRPT.COP_SCOPE_STATUS + RAGRPT.COP_COST_EFT_STATUS + RAGRPT.RMS_RESOURCE_STATUS BETWEEN 60 AND 70 THEN 'A'
                          WHEN RAGRPT.COP_SCHEDULE_STATUS + RAGRPT.COP_SCOPE_STATUS + RAGRPT.COP_COST_EFT_STATUS + RAGRPT.RMS_RESOURCE_STATUS BETWEEN 80 AND 120 THEN 'R'
                          ELSE ' '
                    END) OVERALL_STATUS,
                    RAGRPT.COP_SCHEDULE_EXP SCHEDULE_EXPLANATION,
                    (CASE 
                          WHEN RAGRPT.COP_SCHEDULE_STATUS = 10 THEN 'G'
                          WHEN RAGRPT.COP_SCHEDULE_STATUS = 20 THEN 'A'
                          WHEN RAGRPT.COP_SCHEDULE_STATUS = 30 THEN 'R'
                          ELSE ' '
                    END) SCHEDULE_STATUS,
                    RAGRPT.COP_SCOPE_EXP SCOPE_EXPLANATION,
                    (CASE 
                          WHEN RAGRPT.COP_SCOPE_STATUS = 10 THEN 'G'
                          WHEN RAGRPT.COP_SCOPE_STATUS = 20 THEN 'A'
                          WHEN RAGRPT.COP_SCOPE_STATUS = 30 THEN 'R'
                          ELSE ' '
                    END) SCOPE_STATUS,
                    RAGRPT.COP_EFFORT_EXP COST_EXPLANATION,
                    (CASE 
                          WHEN RAGRPT.COP_COST_EFT_STATUS = 10 THEN 'G'
                          WHEN RAGRPT.COP_COST_EFT_STATUS = 20 THEN 'A'
                          WHEN RAGRPT.COP_COST_EFT_STATUS = 30 THEN 'R'
                          ELSE ' '
                    END) COST_STATUS,
                    RAGRPT.RMS_RESOURCE_EXP RESOURCE_EXPLANATION,
                    (CASE 
                          WHEN RAGRPT.RMS_RESOURCE_STATUS = 10 THEN 'G'
                          WHEN RAGRPT.RMS_RESOURCE_STATUS = 20 THEN 'A'
                          WHEN RAGRPT.RMS_RESOURCE_STATUS = 30 THEN 'R'
                          ELSE ' '
                    END) RESOURCE_STATUS,
                    RAGRPT.COP_KEY_ACCOMPLISH KEY_ACCOMPLISHMENTS,
                    RAGRPT.COP_UPCOMING_ACT UPCOMING_ACTIVITIES,
                    Row_Number() OVER (PARTITION BY PRJ.CODE ORDER BY RAGRPT.CREATED_DATE DESC) AS SEQNUM
                
              FROM  ODF_CA_COP_PRJ_STATUSRPT RAGRPT, 
                    INV_INVESTMENTS PRJ,
                    SRM_RESOURCES SRM  
              WHERE PRJ.ID = RAGRPT.ODF_PARENT_ID (+)
                    AND RAGRPT.CREATED_BY = SRM.ID (+)
                    AND RAGRPT.CREATED_DATE BETWEEN Trunc(SYSDATE, 'DD') - 28 AND SYSDATE
              )t1
              LEFT JOIN 
              (SELECT         
                    (CASE
                          WHEN RAGRPT.COP_SCHEDULE_STATUS + RAGRPT.COP_SCOPE_STATUS + RAGRPT.COP_COST_EFT_STATUS + RAGRPT.RMS_RESOURCE_STATUS BETWEEN 40 AND 50 THEN 'G'
                          WHEN RAGRPT.COP_SCHEDULE_STATUS + RAGRPT.COP_SCOPE_STATUS + RAGRPT.COP_COST_EFT_STATUS + RAGRPT.RMS_RESOURCE_STATUS BETWEEN 60 AND 70 THEN 'A'
                          WHEN RAGRPT.COP_SCHEDULE_STATUS + RAGRPT.COP_SCOPE_STATUS + RAGRPT.COP_COST_EFT_STATUS + RAGRPT.RMS_RESOURCE_STATUS BETWEEN 80 AND 120 THEN 'R'
                          ELSE ' '
                    END) OVERALL_STATUS,
                    (CASE 
                          WHEN RAGRPT.COP_SCHEDULE_STATUS = 10 THEN 'G'
                          WHEN RAGRPT.COP_SCHEDULE_STATUS = 20 THEN 'A'
                          WHEN RAGRPT.COP_SCHEDULE_STATUS = 30 THEN 'R'
                          ELSE ' '
                    END) SCHEDULE_STATUS,
                    (CASE 
                          WHEN RAGRPT.COP_SCOPE_STATUS = 10 THEN 'G'
                          WHEN RAGRPT.COP_SCOPE_STATUS = 20 THEN 'A'
                          WHEN RAGRPT.COP_SCOPE_STATUS = 30 THEN 'R'
                          ELSE ' '
                    END) SCOPE_STATUS,
                    (CASE 
                          WHEN RAGRPT.COP_COST_EFT_STATUS = 10 THEN 'G'
                          WHEN RAGRPT.COP_COST_EFT_STATUS = 20 THEN 'A'
                          WHEN RAGRPT.COP_COST_EFT_STATUS = 30 THEN 'R'
                          ELSE ' '
                    END) COST_STATUS,
                    (CASE 
                          WHEN RAGRPT.RMS_RESOURCE_STATUS = 10 THEN 'G'
                          WHEN RAGRPT.RMS_RESOURCE_STATUS = 20 THEN 'A'
                          WHEN RAGRPT.RMS_RESOURCE_STATUS = 30 THEN 'R'
                          ELSE ' '
                    END) RESOURCE_STATUS,
                    PRJ.CODE,
                    Row_Number() OVER (PARTITION BY PRJ.CODE ORDER BY RAGRPT.CREATED_DATE DESC) AS SEQNUM
                
              FROM  ODF_CA_COP_PRJ_STATUSRPT RAGRPT, 
                    INV_INVESTMENTS PRJ,
                    SRM_RESOURCES SRM  
              WHERE PRJ.ID = RAGRPT.ODF_PARENT_ID (+)
                    AND RAGRPT.CREATED_BY = SRM.ID (+)
                    AND RAGRPT.CREATED_DATE BETWEEN Trunc(SYSDATE, 'DD') - 28 AND SYSDATE
              )t2 ON t1.code = t2.code
        WHERE t1.SEQNUM = 1 
              AND t2.SEQNUM = 2
     ) 
 
 

[/spoiler]

Share this post


Link to post
Share on other sites
wduartes    75

Can you create a view or you must resolve with just one query without external elements ( SP, VW, etc ) ?

Share this post


Link to post
Share on other sites
»Paxman    4776
The whole thing (and there's more to it - since yesterday it's grown to over 400 lines and it's only partially complete) builds a Crystal Report, so as far as I know it'll need to resolve in one query.

Share this post


Link to post
Share on other sites
wduartes    75

you should start with that, lol.

 

Can you give me more details about how do you build the report? what version of crystal are you using?(this one isnt too relevant)
I mean, do you generate it via an api ( visual studio, etc ) or its an static rpt that you call with params ( 1 and 2, i guess in this case )

In a single query will be a pain in the ass when you do mantenimiento or try to add new features.

 

It ll be easiest if you create a view ( static in the DB or in demand during the execution of your application ) and then operate over that in the crystal.

Im in my home now and dont have crystal installed, but i can give you a general idea to what to do if you want based on what you need.


EDIT: also, can you post a edited pic of what you want to see as final result in the report?

Share this post


Link to post
Share on other sites
»Paxman    4776
I just don't know Crystal yet. I'm still newish to the position and the old admin built most reports already this is just a new one. My knowledge of how things work is I only have read access to the DB. I'm a Clarity PPM admin (Project Management tool built by CA).

You upload the rpt file to their server and they build the page which feeds the parameters. In this case there's only going to be one parameter, which is the code from the screenshot at the top.

Share this post


Link to post
Share on other sites
wduartes    75

fuck my phone...

 

just to be clear, in each row of the report, you only compare 2 seq_nums, right?

Share this post


Link to post
Share on other sites
»Paxman    4776

In that subquery correct. This just grabs two 'status reports', the most recent and the one preceding it so I can compare fields and create a 'trend'. 

Share this post


Link to post
Share on other sites
wduartes    75

ok, you are limited by too much things.

 

The better way is to make a query that colects both row, and then use crystal functions to compare both ( given a row and a column, crystal has a function to get the value of the next/prev row of that column )

 

Crystal is very very simple to use, so you ll not have a problem to do this.

 

Crystal is a tool where you can collect data from existing db objects ( tables, views, sp, etc ) or for user defined query ( Commands in crystal ). Then, you can manipulate that data in the way you want. You can create variables, you can do loops, you can do whatever you want. You can create variables that evaluate values when you are "printing" the records.

 

Tell me if you want a deep explication to how to work with crystal, or how to do this in crystal.

Share this post


Link to post
Share on other sites
»Paxman    4776

I'll come back to this thread if I'm having trouble. It's nice that it can do loops though!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.

×