Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2011
    Posts
    4

    Unanswered: need help with a query

    Hey all,

    hope someone can lead me to the right direction on this. I basically have a result set coming back that lists a person's id, name, report id, and the value(s) from a checkbox on that report.

    What I'm trying to do is bring back all the unique values from the checkbox, because there are cases where over time a new value gets checked but I don't want to repeat the old values that were checked.

    Code:
    Person ID --     FirstName --LastName --ReportID -- chkboxValue
    001                 A             A         1            valueA 
    001                 A             A         1            valueB 
    001                 A             A         1            valueC   
    001                 A             A         2            valueA   
    001                 A             A         2            valueB       
    001                 A             A         2            valueC        
    001                 A             A         2            valueD
    so in a case like this I would only like to bring back values:
    Code:
    Person ID --     FirstName --LastName --ReportID -- chkboxValue 
    001                 A             A         2            valueA
    001                 A             A         2            valueB
    001                 A             A         2            valueC
    001                 A             A         2            valueD
    basically its like I need the max reportID cause that will have all the old values + whatever new one they added

    the query in simple terms would be like select personid, firstname, lastname, reportID, chkboxValue from some tables and left outer joins to bring in the chkboxValue.

    Any ideas?

  2. #2
    Join Date
    Oct 2010
    Posts
    46

    Using the UNION operator

    This can be implemented by using the UNION operator. The first query gets all the values for the max(ReportId) and the second query gets those new values they added. The UNION operator is then applied to get the desired results.

    Code:
    select * 
    from resultset
    where ReportId in (select max(ReportId) from resultset)
    union 
    select * 
    from resultset
    where chkBoxValue not in (
      select chkBoxValue 
      from resultset
      where ReportId in (select max(ReportId) from resultset)
    )

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    whoops nevermind
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Here are two ways to accomplish it:
    Code:
    WITH CTE AS (
    SELECT PersonID, 
    	max(ReportID) as MaxReportId
    FROM #DaTable
    GROUP BY PersonID
    )
    SELECT #DaTable.PersonID, 
    	#DaTable.FirstName, 
    	#DaTable.LastName, 
    	#DaTable.ReportID, 
    	#DaTable.chkboxValue
    FROM #DaTable 
    	INNER JOIN CTE ON
    		#DaTable.PersonID = CTE.PersonID AND
    		#DaTable.ReportID = CTE.MaxReportId
    Code:
    SELECT #DaTable.PersonID, 
    	#DaTable.FirstName, 
    	#DaTable.LastName, 
    	#DaTable.ReportID, 
    	#DaTable.chkboxValue
    FROM #DaTable 
    	INNER JOIN (	SELECT PersonID, max(ReportID) as MaxReportId
    			FROM #DaTable
    			GROUP BY PersonID) as T ON
    		#DaTable.PersonID = T.PersonID AND
    		#DaTable.ReportID = T.MaxReportId
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Oct 2010
    Posts
    46

    Different interpretation of results

    basically its like I need the max reportID cause that will have all the old values + whatever new one they added
    Code:
    Person ID --     FirstName --LastName --ReportID -- chkboxValue
    001                 A             A         1            valueA 
    001                 A             A         1            valueB 
    001                 A             A         1            valueC   
    001                 A             A         2            valueA   
    001                 A             A         2            valueB       
    001                 A             A         2            valueC        
    001                 A             A         2            valueD
    001                 A             A         1            valueE
    The results should have the new values that is added, all values from ReportID 2 (the max ReportId) plus whatever new one they added. In the example, a value 'E' is added to the existing 4 values.

    Code:
    Person ID --     FirstName --LastName --ReportID -- chkboxValue
    001                 A             A         2            valueA   
    001                 A             A         2            valueB       
    001                 A             A         2            valueC        
    001                 A             A         2            valueD
    001                 A             A         1            valueE
    Last edited by mission; 09-14-11 at 17:33.

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Code:
    SELECT #DaTable.PersonID, 
    	#DaTable.FirstName, 
    	#DaTable.LastName, 
    	#DaTable.ReportID, 
    	#DaTable.chkboxValue
    FROM #DaTable 
    	INNER JOIN (	SELECT PersonID, chkboxValue, max(ReportID) as MaxReportId
    			FROM #DaTable
    			GROUP BY PersonID, chkboxValue) as T ON
    		#DaTable.PersonID = T.PersonID AND
    		#DaTable.chkboxValue = T.chkboxValue AND
    		#DaTable.ReportID = T.MaxReportId
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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