Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Jan 2005
    Posts
    24

    Unanswered: Trying to get - list of brokers with one case only in last 9 months

    I have a problem where I need to return a list of Brokers that have had only one case and that case was in the last nine months, the script below was returning appropriate numbers until I amended it to add the date of that case (cdateReceived) and the related join.

    There were 470 rows returned before the addition of the cdateReceived and 1780 rows after. Ordinarily you would expect the dataset to grow with the addition of a join on a one-to-many realtionship but the criteria should be limiting the resuilts to be brokers with only one case!!! It should not then increase the number of rows.

    What am I mis-understanding?

    SELECT TOP 100 PERCENT
    dbo.brokerDetails.bFirstname + ' ' + dbo.brokerDetails.bSurname AS Broker,
    dbo.brokerCompany.companyName,
    dbo.brokerCompany.cTown,
    dbo.brokerCompany.cPostCode,
    ISNULL(dbo.brokerParent.pName, 'DIRECTLY AUTHORISED') AS Network,
    dbo.cases.cDateReceived

    FROM dbo.brokerDetails INNER JOIN
    dbo.brokerCompany ON dbo.brokerDetails.bCompany = dbo.brokerCompany.companyID LEFT OUTER JOIN
    dbo.brokerParent ON dbo.brokerCompany.cNetworkID = dbo.brokerParent.parentID INNER JOIN
    dbo.cases ON dbo.brokerDetails.BrokerID = dbo.cases.BrokerID
    WHERE (dbo.brokerDetails.brokerID IN
    (SELECT TOP 100 PERCENT brokerID
    FROM dbo.cases
    WHERE (cDateReceived < DATEADD(mm, - 9, GETDATE())) AND (Spiked = 0) AND (IsDirect = 0)

    GROUP BY brokerID
    ORDER BY brokerID
    )
    )
    AND (dbo.brokerDetails.brokerID IN
    (SELECT TOP 100 PERCENT brokerID
    FROM dbo.cases
    WHERE (spiked <> 1) AND (IsDirect = 0)
    GROUP BY brokerID
    HAVING count(caseID) =1
    ORDER BY brokerID
    )
    )
    ORDER BY dbo.brokerDetails.bFirstname + ' ' + dbo.brokerDetails.bSurname

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    ...
    WHERE (dbo.brokerDetails.brokerID IN 
    (SELECT TOP 100 PERCENT brokerID
    FROM dbo.cases
    WHERE (cDateReceived < DATEADD(mm, - 9, GETDATE())) AND (Spiked = 0) AND (IsDirect = 0)
    
    GROUP BY brokerID
    ORDER BY brokerID
    )
    ...
    Firstly, there's no need to specify top 100 percent...
    That's done by default.

    Secondly, your problem is most likely that the subquery returns more than one value for each/most brokerID.

    Try using a SELECT DISTINCT brokerID

    Let me know the result
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    moving thread to SQL Server forum

    ANSI SQL does not support anything as silly as "TOP 100 PERCENT"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2005
    Posts
    24
    Hi georgev,
    yes you are correct about the "top 100 percent". I simply forgot to remove it after testing and pasting the subquery which had other columns to verify the results.

    The subquery that you have commented on does indeed potentially return more than one row per broker - however I expected the logical AND joining the two IN operations should remove this issue, as the other subselect only returns brokers that have had one case in total.

    Quote Originally Posted by georgev
    Code:
    ...
    WHERE (dbo.brokerDetails.brokerID IN 
    (SELECT TOP 100 PERCENT brokerID
    FROM dbo.cases
    WHERE (cDateReceived < DATEADD(mm, - 9, GETDATE())) AND (Spiked = 0) AND (IsDirect = 0)
    
    GROUP BY brokerID
    ORDER BY brokerID
    )
    ...
    Firstly, there's no need to specify top 100 percent...
    That's done by default.

    Secondly, your problem is most likely that the subquery returns more than one value for each/most brokerID.

    Try using a SELECT DISTINCT brokerID

    Let me know the result

  5. #5
    Join Date
    Jan 2005
    Posts
    24
    Appologies I intended to post it in sql server, also appologies for using Enterprise Manager which insists on using these silly statements!

    Quote Originally Posted by r937
    moving thread to SQL Server forum

    ANSI SQL does not support anything as silly as "TOP 100 PERCENT"

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    But the clauses still contradict eachother, surely?
    Yes, the logical AND should filter it, but
    I suggest you make the selects DISTINCT and see what happens.
    George
    Home | Blog

  7. #7
    Join Date
    Jan 2005
    Posts
    24
    This is still not cutting it - getting 1767 rows returned !!

    Quote Originally Posted by georgev
    But the clauses still contradict eachother, surely?
    Yes, the logical AND should filter it, but
    I suggest you make the selects DISTINCT and see what happens.

  8. #8
    Join Date
    Jan 2005
    Posts
    24
    Just thinking about your comment georgev but I am not really sure what you mean by this.

    There are two conditions which represent different not contradictory subsets of the database - the criteria is that a broker must be in both subsets - i.e he must be in the list of brokers that have only ever had one case, and he must be included in the list of brokers that have had a case or cases in the last nine months.

    Quote Originally Posted by georgev
    But the clauses still contradict eachother, surely?
    Yes, the logical AND should filter it, but
    I suggest you make the selects DISTINCT and see what happens.

  9. #9
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    the top 100 percent thing is almost always used when someone is writing a view definition with an order by clause, which is illegal. the "workaround" that allows you to keep an order by clause is to use top, and since they want all the rows, they use top 100 percent.

    this is a fragile thing to do however. the optimizer reserves the right to ignore the order by clause in a view definition if doing so will create a more efficient plan. so you can't rely on it. if this is in a view def, remove both the top and order by clauses. if you want the results ordered, use an order by clause in the query that targets the view, not in the view def itself.

    details here: http://blogs.msdn.com/sqltips/archiv...20/441053.aspx

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You do not explain the attribute "Spiked", or why you filter on it differently for your two subqueries, but if you omit it you can use this to get a list of qualifying broker IDs:

    Code:
    SELECT	brokerID
    FROM	dbo.cases
    WHERE	IsDirect = 0
    GROUP BY brokerID
    HAVING count(caseID) = 1
    	and max(cDateReceived) > DATEADD(mm, - 9, GETDATE())
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Apologies again, thought there were more brackets and you were using

    IN (this and this)
    When it's actually IN this AND IN this

    Will have a closer look when I have some more time :thumbs up:

    Sorry for the confusion
    George
    Home | Blog

  12. #12
    Join Date
    Jan 2005
    Posts
    24
    Sorry "Spiked" is actually not relevent to the problem I should have removed it when braking this down to the nitty-gritty, it is actually just an attribute to indicate that a given row is deleted from the business perspective whilst it remains in the data set - this is in order to maintain certain business archival information - products that no longer exist for instance are referenced from an old invoice - we are not able to physically remove the data but these rows need to disapear from certain views.

    When spiked=0 brokers are relevent to business this included in the output when spiked=1 that broker is not taken account of.

    Thanks for your help!

    Quote Originally Posted by blindman
    You do not explain the attribute "Spiked", or why you filter on it differently for your two subqueries, but if you omit it you can use this to get a list of qualifying broker IDs:

    Code:
    SELECT	brokerID
    FROM	dbo.cases
    WHERE	IsDirect = 0
    GROUP BY brokerID
    HAVING count(caseID) = 1
    	and max(cDateReceived) > DATEADD(mm, - 9, GETDATE())

  13. #13
    Join Date
    Jan 2005
    Posts
    24

    The Solution

    OK I have got there now,

    I believe however I have been attacking this from the wrong direction and the solution turns out to be simple!!

    Thanks for your help guys!

    Todays proverb.
    Confucious says: When bashing head against wall for many hours - take a break, have good night sleep - wake up and smell the green tea - the solution will come!

    Code:
    SELECT     TOP 100 PERCENT dbo.brokerDetails.bFirstname + ' ' + dbo.brokerDetails.bSurname AS Broker, 
                          dbo.brokerCompany.companyName, dbo.brokerCompany.cTown, dbo.brokerCompany.cPostCode, ISNULL(dbo.brokerParent.pName, 
                          'DIRECTLY AUTHORISED') AS Network, dbo.cases.cDateReceived
    FROM         dbo.cases INNER JOIN
                          dbo.brokerDetails ON dbo.cases.brokerID = dbo.brokerDetails.brokerID INNER JOIN
                          dbo.brokerCompany ON dbo.brokerDetails.bCompany = dbo.brokerCompany.companyID LEFT OUTER JOIN
                          dbo.brokerParent ON dbo.brokerCompany.cNetworkID = dbo.brokerParent.parentID
    WHERE     (dbo.cases.cDateReceived >= DATEADD(mm, - 9, GETDATE())) AND (dbo.cases.brokerID IN
                              (SELECT     TOP 100 PERCENT dbo.cases.brokerID
                                FROM          dbo.cases
                                WHERE      (spiked <> 1) AND (IsDirect = 0)
                                GROUP BY brokerID
                                HAVING      COUNT(caseID) = 1
                                ORDER BY dbo.cases.brokerID))
    ORDER BY dbo.brokerDetails.bFirstname + ' ' + dbo.brokerDetails.bSurname

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Again....and again and again....you do not need TOP clauses in this statement:
    Code:
    SELECT	dbo.brokerDetails.bFirstname + ' ' + dbo.brokerDetails.bSurname AS Broker, 
    	dbo.brokerCompany.companyName,
    	dbo.brokerCompany.cTown,
    	dbo.brokerCompany.cPostCode,
    	ISNULL(dbo.brokerParent.pName, 'DIRECTLY AUTHORISED') AS Network,
    	dbo.cases.cDateReceived
    FROM	dbo.cases
    	INNER JOIN dbo.brokerDetails ON dbo.cases.brokerID = dbo.brokerDetails.brokerID
    	INNER JOIN dbo.brokerCompany ON dbo.brokerDetails.bCompany = dbo.brokerCompany.companyID 		LEFT OUTER JOIN dbo.brokerParent ON dbo.brokerCompany.cNetworkID = dbo.brokerParent.parentID
    WHERE	(dbo.cases.cDateReceived >= DATEADD(mm, - 9, GETDATE()))
    	AND (dbo.cases.brokerID IN
    		(SELECT	dbo.cases.brokerID
    		FROM	dbo.cases
    		WHERE	(spiked <> 1) AND (IsDirect = 0)
    		GROUP BY brokerID
    		HAVING	COUNT(caseID) = 1))
    ORDER BY dbo.brokerDetails.bFirstname + ' ' + dbo.brokerDetails.bSurname
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Jan 2005
    Posts
    24
    Maybe I didnít explain myself effectively enough (but then I donít see it as a big issue) or maybe you were not reading into what I said, but I will reiterate: I forgot to take the TOP clause out of the script before pasting it in here.

    Why did I include it in the first place - well I didnít, Enterprise Manager puts it in - not me. It will not allow you to execute a script without automatically doing so. So I guess your issue is with Microsoft why donít you tell them again and again and again!

    You know you may want to be in this forum to let people know how knowledgeable you are (not that you were able to answer this problem) - but being rude even to those you consider to be contemptible, belittles you.

    Have a nice day!

    Quote Originally Posted by blindman
    Again....and again and again....you do not need TOP clauses in this statement:
    Code:
    SELECT	dbo.brokerDetails.bFirstname + ' ' + dbo.brokerDetails.bSurname AS Broker, 
    	dbo.brokerCompany.companyName,
    	dbo.brokerCompany.cTown,
    	dbo.brokerCompany.cPostCode,
    	ISNULL(dbo.brokerParent.pName, 'DIRECTLY AUTHORISED') AS Network,
    	dbo.cases.cDateReceived
    FROM	dbo.cases
    	INNER JOIN dbo.brokerDetails ON dbo.cases.brokerID = dbo.brokerDetails.brokerID
    	INNER JOIN dbo.brokerCompany ON dbo.brokerDetails.bCompany = dbo.brokerCompany.companyID 		LEFT OUTER JOIN dbo.brokerParent ON dbo.brokerCompany.cNetworkID = dbo.brokerParent.parentID
    WHERE	(dbo.cases.cDateReceived >= DATEADD(mm, - 9, GETDATE()))
    	AND (dbo.cases.brokerID IN
    		(SELECT	dbo.cases.brokerID
    		FROM	dbo.cases
    		WHERE	(spiked <> 1) AND (IsDirect = 0)
    		GROUP BY brokerID
    		HAVING	COUNT(caseID) = 1))
    ORDER BY dbo.brokerDetails.bFirstname + ' ' + dbo.brokerDetails.bSurname

Posting Permissions

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