Post reply

Show results based on String Search

  • February 16, 2018 at 5:10 pm

    #343609

    Hi Everyone,
                         I have a complex requirement. There is a ASP.NET page where an Agent logs in to run a SSRS report. The Agent may belong to Agency or CompanionAgency. 

    Relationship between Agency to CompanionAgency is one to many. So CompanionAgency can be said as child of Agency.

    The table name is dbo.LkpCompanionAgency.
    This table has two columns -- AgencyNo and CompanionAgencyNo. Table looks like this. 
    AgencyNo    CompanionAgencyNo
    06984           08587
    06984           08555

    We don't know for sure if Agent (who logs into ASP.NET page) is from Agency or CompanionAgency. So the SP should show results for the following scenario.

    a) if the Agent logs from the direct Agency. In the above example 06984 (AgencyNo). Pull data from source table that has PolicyNo and AgencyNo
    b) if the Agent logs in from Companion Agency. In the above example 08587 or 08555. In real time there can be 90 Companion Agency for a Agency too.

    I am done with scenario a) but the problem is scenario b. I stuck at this place as the source table to pull data does not have CompanionAgenyNo and it has only AgencyNo. 

    Any help is highly appreciated.

    Thanks.

  • February 17, 2018 at 12:37 pm

    #1980176

    shyamhr - Friday, February 16, 2018 5:10 PM

    Hi Everyone,
                         I have a complex requirement. There is a ASP.NET page where an Agent logs in to run a SSRS report. The Agent may belong to Agency or CompanionAgency. 

    Relationship between Agency to CompanionAgency is one to many. So CompanionAgency can be said as child of Agency.

    The table name is dbo.LkpCompanionAgency.
    This table has two columns -- AgencyNo and CompanionAgencyNo. Table looks like this. 
    AgencyNo    CompanionAgencyNo
    06984           08587
    06984           08555

    b) if the Agent logs in from Companion Agency. In the above example 08587 or 08555. In real time there can be 90 Companion Agency for a Agency too.

    I am done with scenario a) but the problem is scenario b. I stuck at this place as the source table to pull data does not have CompanionAgenyNo and it has only AgencyNo. 

    Any help is highly appreciated.

    Thanks.

    So, for scenario B), you get a Companion Agency Number and your "source" table doesn't have CompanionAgencyNo.  What is it that you expect to return?  If it's information on the Companion Agency, then you're SOL because (it appears) you don't actually have Companion Agency information stored anywhere except as IDs in a parent/child list.  The best you can do is grab the adjacent Agency Number from that parent/child list and use that to do the lookup with a note saying that it was actually from company agency number xxxxx.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 17, 2018 at 2:26 pm

    #1980181

    Thanks Jeff for the idea. I actually implemented the same idea you said. I penned down all the stuff in the papers. I got the same idea so now I pick the AgencyNo list from CompanionAgencyNo look up table and match it with input parameter and pick the rows from the source table for those AgencyNo.

  • February 17, 2018 at 4:14 pm

    #1980186

    shyamhr - Saturday, February 17, 2018 2:26 PM

    Thanks Jeff for the idea. I actually implemented the same idea you said. I penned down all the stuff in the papers. I got the same idea so now I pick the AgencyNo list from CompanionAgencyNo look up table and match it with input parameter and pick the rows from the source table for those AgencyNo.

    What would be better is to brow beat the designers of these tables into submission to have them actually required the Companion Agency data to be present before it's allowed to appear in the Parent/Child list.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 18, 2018 at 1:11 am

    #1980193

    I agree with you Jeff. I wish I could do that. Lol....

  • February 18, 2018 at 8:01 am

    #1980194

    shyamhr - Sunday, February 18, 2018 1:11 AM

    I agree with you Jeff. I wish I could do that. Lol....

    Heh... understood and much appreciated.  It reminds me of an old Rolling Stones song...

    No, you can't always get what you want.
    You can't always get what you want.
    You can't always get what you want.
    But if you try sometime, you might find,
    You get what you need!
    Of course, there has to be someone listening and capable of understanding but the answer is always "no" unless you ask.  😀

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply