CODEWITHSUNDEEP
sqlvb6ms-access-2010
Sancho Almeda
Sancho Almeda

Reputation: 151

VB6 ADO: Returning Result Sets based on values of first columns

Task:

I have a table with 8 columns and there is about 100,000 rows in it.

Year    Cycle   PHSRCode  Blanket  LastName  FirstName  Status  ExcusedStatus
2012    5       GW1-01     null    CASTILLO  LILIBETH   Yes     FALSE
2012    5       GW1-01     null    CLAVERIA  PAMELA     Yes     FALSE
2012    5       GW1-01     1       RAMOS     LAILANI    Yes     FALSE
2012    5       GW1-01     2       SIY       ZERZENDEE  Yes     FALSE
2012    5       GW1-01     null    SANTOS    MARILIN    Yes     FALSE
2012    5       GW1-01     null    BALDERAS  JULIET     No      FALSE

For the query result, I need to return all 8 columns and 100,000 rows with an additional 4 columns whose values can be derived from the values of the 8 columns and 100,000 rows of the same table.

Basically, the output should be like this:

    Year    Cycle   PHSRCode  Blanket  LastName  FirstName  Status  ExcusedStatus  RawActualRate   RawTargetRate   RawActualReach
    2012    5       GW1-01     null    CASTILLO  LILIBETH   Yes     FALSE               1              1             1

Columns:

Question:

Can this be done in one query statement? I wish to not use any looping statements through code since getting the result this way takes a bit of time (10-20 mins).

What I have so far:

This is the SQL statement that I am using. However, I am stuck with the last part since tb1 is not returning any values yet:

Private Function sql_string1v2() As String
    Dim sql As String

    sql = "SELECT "
    sql = sql & "tb1.ID, "
    sql = sql & "tb1.PeriodYear, "
    sql = sql & "tb1.PeriodCycle, "
    sql = sql & "tb1.PeriodZone, "
    sql = sql & "tb1.PHSRCode, "
    sql = sql & "tb1.Blanket, "
    sql = sql & "tb1.LastName, "
    sql = sql & "tb1.FirstName, "
    sql = sql & "tb1.MiddleName, "
    sql = sql & "tb1.PRC, "
    sql = sql & "tb1.Specialty, "
    sql = sql & "tb1.HCPType, "
    sql = sql & "tb1.Class, "
    sql = sql & "tb1.Room, "
    sql = sql & "tb1.Institution, "
    sql = sql & "tb1.Address, "
    sql = sql & "tb1.Region, "
    sql = sql & "tb1.Province, "
    sql = sql & "tb1.City, "
    sql = sql & "tb1.Brick, "
    sql = sql & "tb1.Type, "
    sql = sql & "tb1.Affiliation, "
    sql = sql & "tb1.Frequency, "
    sql = sql & "tb1.Status, "
    sql = sql & "tb1.MissCallReason,"
    sql = sql & "tb1.FlexiCallZone, "
    sql = sql & "tb1.Process, "
    sql = sql & "tb1.DateAdded, "
    sql = sql & "tb1.Encoder, "
    sql = sql & "tb1.ExcusedStatus, "

    sql = sql & "IIF(tb1.Status = 'Yes', 1, 0) AS raw_actual_rate, "
    sql = sql & "IIF(tb1.Status <> '', 1, 0) AS raw_target_rate, "

    sql = sql & "(SELECT (1/COUNT(ID)) AS raw_actual_reach FROM tblDCM as tb2 WHERE tb2.Status = 'Yes' AND tb2.PeriodYear = tbl1.PeriodYear AND PeriodCycle = tb1.PeriodCycle AND PHSRCode = tb1.PHSRCode AND ((tb1.Blanket IS NOT NULL) AND Blanket = tb1.Blanket) OR (LastName = tb1.LastName AND FirstName = tb1.FirstName)) AS raw_actual_reach, "

    sql = sql & "FROM tblDCM as tb1 "
    sql = sql & "WHERE LEFT(tb1.PHSRCode,2) = 'N0'"

    sql_string1v2 = sql
End Function

Upvotes: 1

Views: 255

Answers (1)

Eric Walker
Eric Walker

Reputation: 1052

This should be a comment, but I don't have enough reputation yet to post comments.

That said, I can offer some advice.

First, edit your question to include the target database (access, MSSQL, Oracle etc.) as the subset of SQL commands can vary greatly by the platform you're querying against.

Second, this is purely a SQL question and you should forget about the vb6 aspect until you have the query working as expected.

I can say with certainty that raw_actual_reach is going to be a problem the way you have it currently written. For one not all of your fields include their alias prefix and when comparing fields with identical names, you have to include the alias as a prefix or you will encounter errors.

Also, you've got an extra comma at the end of the field list (that or you missed a line of code when you were copy/pasting.) I've pulled the query out of your code sample and have a corrected one below. There still may be issues with this query depending on the db platform you're querying against, but now at least the syntax is consistent. HTH.

SELECT  tb1.ID, tb1.PeriodYear, tb1.PeriodCycle, tb1.PeriodZone, tb1.PHSRCode, tb1.Blanket, tb1.LastName, tb1.FirstName,
        tb1.MiddleName, tb1.PRC, tb1.Specialty, tb1.HCPType, tb1.Class, tb1.Room, tb1.Institution, tb1.Address, tb1.Region,
        tb1.Province, tb1.City, tb1.Brick, tb1.Type, tb1.Affiliation, tb1.Frequency, tb1.Status, tb1.MissCallReason, tb1.FlexiCallZone,
        tb1.Process, tb1.DateAdded, tb1.Encoder, tb1.ExcusedStatus, IIF(tb1.Status = 'Yes', 1, 0) AS raw_actual_rate, IIF(tb1.Status <> '', 1, 0) AS raw_target_rate,
        (
            SELECT  (1/COUNT(ID)) AS raw_actual_reach
            FROM    tblDCM as tb2
            WHERE   tb2.Status = 'Yes' AND tb2.PeriodYear = tbl1.PeriodYear AND tb2.PeriodCycle = tb1.PeriodCycle AND tb2.PHSRCode = tb1.PHSRCode AND 
                    ((tb1.Blanket IS NOT NULL) AND tb2.Blanket = tb1.Blanket) OR
                    (tb2.LastName = tb1.LastName AND tb2.FirstName = tb1.FirstName)
        ) AS raw_actual_reach
FROM    tblDCM as tb1
WHERE   LEFT(tb1.PHSRCode,2) = 'N0'

Upvotes: 1

Related Questions