Post reply

I'm Sure its Something Simple! Returning Different Companies Based on Parameter

  • April 3, 2012 at 7:18 am

    #254253

    Hi All

    Need help, I think I've just been looking at this for too long and got brain freeze and am missing something very simple or over thinking the problem and will undoubtedly feel stupid for not spotting the obvious solution!

    I'll try to explain as best I can as I'm still trying to understand the requirements myself and am not the best at explaining things!

    The data and query provided are obviously overly simplified!

    We are using SSRS to produce a set of reports where users can choose to include or exclude certain companies based on parameter selection (the reason I've posted this here and not reporting services forum is I think its the query that's the issue not SSRS, well partially anyway ;-))

    So in the example (current logic we are using for a similar report) to follow the user would like to display every row where CompanyA = 1 and CompanyB = 1 & 3 and where the SourceCompany = 1.

    Based on this we do not want any records returned where CompanyB = 2

    DECLARE

    @CompanyA INT = 1

    ,@CompanyB INT = 3

    ,@SourceCompany INT = 1

    CREATE TABLE #TEMP

    (

    ID INT, CompanyA INT, CompanyB INT, SourceCompany INT

    )

    INSERT INTO #TEMP

    SELECT 1, 1, 1 , 1 UNION ALL

    SELECT 2, 1, 1 , 1 UNION ALL

    SELECT 3, 1, 2 , 1 UNION ALL

    SELECT 4, 1, 2 , 1 UNION ALL

    SELECT 5, 1, 3 , 1 UNION ALL

    SELECT 6, 1, 3 , 1 UNION ALL

    SELECT 7, 2, 1 , 1 UNION ALL

    SELECT 8, 2, 1 , 1 UNION ALL

    SELECT 9, 2, 2 , 1 UNION ALL

    SELECT 10, 2, 2 , 1 UNION ALL

    SELECT 11, 3, 2 , 1 UNION ALL

    SELECT 12, 3, 2 , 1

    SELECT *

    FROM

    #TEMP

    WHERE

    CompanyA = @CompanyA OR CompanyB IN (@CompanyA,@CompanyB)

    AND SourceCompany IN (1)

    DROP TABLE #TEMP

    Result Set

    IDCompanyACompanyBSourceCompany

    11 1 1

    21 1 1

    31 2 1

    41 2 1

    51 3 1

    61 3 1

    72 1 1

    82 1 1

    Based on the result set returned two rows with a CompanyB of 2 are returned due to the matches on the other columns.

    I know that there are many ways to get around this i.e. build in a sub query ect I could do that to exclude these rows no problem however that's not possible.

    The WHERE clause is based on the parameters that are passed through by SSRS and therefore I can only test for the CompanyID's that exist and not the ones that are de-selected from the drop down...

    I suppose it may be possible to add another parameter to the reports and switch queries with an IF statement one for include company and one for exclude company but that would be a REALLY BIG job as there is already many IF statements contained in the code to handle other things or add an extra parameter for include company exclude company however if I did this way the users would have to select / deselect the same company from different drop downs and it would ruin the user experience and there has to be an easier way to do this!

    Any help or pointers would be appreciated.

    ==========================================================================================================================
    A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

  • April 3, 2012 at 7:27 am

    #1468201

    I may be oversimplifying what you've said but. . .

    You said. . .

    Andy Hyslop (4/3/2012)

    every row where CompanyA = 1 and CompanyB = 1 & 3 and where the SourceCompany = 1

    Your query says. . .

    Andy Hyslop (4/3/2012)

    CompanyA = @CompanyA OR CompanyB IN (@CompanyA,@CompanyB)

    AND SourceCompany IN (1)

    So to reflect what you said, it should instead be. . .

    WHERE CompanyA = @CompanyA AND CompanyB IN (@CompanyA,@CompanyB)

    AND SourceCompany IN (1)

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • April 3, 2012 at 7:35 am

    #1468207

    Cadavre (4/3/2012)

    I may be oversimplifying what you've said but. . .

    You said. . .

    Andy Hyslop (4/3/2012)

    every row where CompanyA = 1 and CompanyB = 1 & 3 and where the SourceCompany = 1

    Your query says. . .

    Andy Hyslop (4/3/2012)

    CompanyA = @CompanyA OR CompanyB IN (@CompanyA,@CompanyB)

    AND SourceCompany IN (1)

    So to reflect what you said, it should instead be. . .

    WHERE CompanyA = @CompanyA AND CompanyB IN (@CompanyA,@CompanyB)

    AND SourceCompany IN (1)

    Hi

    Nope - as I said not very good at explaining things!!

    Based on this we do not want any records returned where CompanyB = 2

    If I did that then I would drop 2 rows where the companyA = 2 we need these returned as the sourcecompany = 1

    ==========================================================================================================================
    A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

  • April 3, 2012 at 7:42 am

    #1468211

    I know whats coming next - why then the company A filter!!

    I think I need to have a think how to explain this better!:crazy:

    ==========================================================================================================================
    A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

  • April 3, 2012 at 8:37 am

    #1468253

    Your requirement is logically inconsistent. The two rows you say you want to exclude do, in fact, meet the criteria that you gave at the beginning, which is that CompanyA = 1.

    This does what you want.

    SELECT *

    FROM

    #TEMP

    WHERE SourceCompany IN (1)

    and ( CompanyA = @CompanyA and CompanyB IN (@CompanyA,@CompanyB) )

    union

    SELECT *

    FROM

    #TEMP

    WHERE SourceCompany IN (1)

    and CompanyB IN (@CompanyA, @CompanyB)

    You might be able to achieve a result with a more elegant query by using SOME, ANY or ALL, but I haven't been able to do it (yet).

    I think you really need to clear up the requirements first.

  • April 3, 2012 at 8:47 am

    #1468263

    Your requirement is logically inconsistent.

    Welcome to my world! I can't tell you how many times I've had to do some funky code to get around some requirements!!

    To be honest I'm soo confused with what is actually needed as its changed so many times today already :crazy:

    Thanks for taking the time to post but I'm going to get these requirements laid in stone and then take another look :hehe:

    ==========================================================================================================================
    A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

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

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