none
Problem getting 2 duped records.

    Question

  • SELECT User.LastName,
           User.FirstName,
           User.Middle,
           User.BirthDate,
           User.ID,
           Research.ResearchDateTime,
           Report.ResearchTag,
           Research.UserTag,
           Research.ResearchID,
           ReportSnapshot.ScientistTag,
           ReportSnapshot.ReportTypeStyleSheetTag,
           Scientist.ConfirmationSignature,
           ReportSnapshot.ReportDateTime AS SignedDateTime
      FROM (((Eagle.dbo.Research Research
              INNER JOIN Eagle.dbo.User User
                 ON (Research.UserTag = User.tag))
             INNER JOIN Eagle.dbo.Report Report
                ON (Report.ResearchTag = Research.tag))
            INNER JOIN Eagle.dbo.ReportSnapshot ReportSnapshot
               ON (ReportSnapshot.ReportTag = Report.tag))
           INNER JOIN Eagle.dbo.Scientist Scientist
              ON (ReportSnapshot.ScientistTag = Scientist.tag)
     WHERE (ReportSnapshot.ReportDateTime BETWEEN '20130701 00:00:00.000'
                                              AND  '20130711 23:59:59.997')

    Sorry but I'm a noob with report writing but I'm having this issue. I wrote the report to show the users seen and the reports completed and when I run it I get the right answers (in a way) but they are all dupped. For some reason the column for ReportTypeStyleSheetTag has a 1 and 2 so I get 2 records for the same person.  All the other colums have EXACTLY the same information EXCEPT this darn ReportTypeStyleSheetTag. Is ther some way I can sum/merge??? I tried just telling it to take just the 1 with

     WHERE (ReportSnapshot.ReportTypeStyleSheetTag IN
                  (1))

    but 4 months later the report stop working because the ReportTypeStyleSheetTag is now using 3 and 4. So I need a perm fix so I don't have to keep coming back and editing this report.

    Please help a noob sql report writer out?


    • Edited by Thailog001 Thursday, July 11, 2013 10:30 PM
    Thursday, July 11, 2013 10:21 PM

Answers

  • ;WITH cteUnique AS 
    (
    SELECT User.LastName,
           User.FirstName,
           User.Middle,
           User.BirthDate,
           User.ID,
           Research.ResearchDateTime,
           Report.ResearchTag,
           Research.UserTag,
           Research.ResearchID,
           ReportSnapshot.ScientistTag,
           --ReportSnapshot.ReportTypeStyleSheetTag,
    	   ,ROW_NUMBER() 
    		OVER (PARTITION BY [User].[ID] 
    		 ORDER BY 
    			ReportSnapshot.ReportTypeStyleSheetTag) AS Rn
           Scientist.ConfirmationSignature,
           ReportSnapshot.ReportDateTime AS SignedDateTime
      FROM (((Eagle.dbo.Research Research
              INNER JOIN Eagle.dbo.User User
                 ON (Research.UserTag = User.tag))
             INNER JOIN Eagle.dbo.Report Report
                ON (Report.ResearchTag = Research.tag))
            INNER JOIN Eagle.dbo.ReportSnapshot ReportSnapshot
               ON (ReportSnapshot.ReportTag = Report.tag))
           INNER JOIN Eagle.dbo.Scientist Scientist
              ON (ReportSnapshot.ScientistTag = Scientist.tag)
     WHERE (ReportSnapshot.ReportDateTime 
    					BETWEEN '20130701 00:00:00.000'
                     AND  '20130711 23:59:59.997')
    )
    SELECT *
    FROM cteUnique
    WHERE Rn=1

    Try this if you are on SQL Server 2005 or higher version.

    Narsimha

    • Marked as answer by Thailog001 Friday, July 12, 2013 4:26 PM
    Thursday, July 11, 2013 10:36 PM
  • SELECT User.LastName,
           User.FirstName,
           User.Middle,
           User.BirthDate,
           User.ID,
           Research.ResearchDateTime,
           Report.ResearchTag,
           Research.UserTag,
           Research.ResearchID,
           ReportSnapshot.ScientistTag,
           ReportSnapshot.ReportTypeStyleSheetTag,
           Scientist.ConfirmationSignature,
           ReportSnapshot.ReportDateTime AS SignedDateTime
      FROM (((Eagle.dbo.Research Research
              INNER JOIN Eagle.dbo.User User
                 ON (Research.UserTag = User.tag))
             INNER JOIN Eagle.dbo.Report Report
                ON (Report.ResearchTag = Research.tag))
            INNER JOIN Eagle.dbo.ReportSnapshot ReportSnapshot
               ON (ReportSnapshot.ReportTag = Report.tag))
           INNER JOIN Eagle.dbo.Scientist Scientist
              ON (ReportSnapshot.ScientistTag = Scientist.tag)
     WHERE (ReportSnapshot.ReportDateTime BETWEEN '20130701 00:00:00.000'
                                              AND  '20130711 23:59:59.997')
    	AND (ReportSnapshot.ReportTypeStyleSheetTag = 
    		(SELECT TOP 1 ReportTypeStyleSheetTag
    		FROM Eagle.dbo.ReportSnapshot A
    		WHERE (ReportSnapshot.ReportTag = A.ReportTag)
    				AND (ReportSnapshot.ScientistTag = A.ScientistTag )
    			(A.ReportDateTime BETWEEN '20130701 00:00:00.000'
                                    AND  '20130711 23:59:59.997')
    		)
    	)

    Check this.

    This is a solution for your query. But I think you should inform them that there can be more than 2 style sheets for the reports/snapshots. Should you display all of them.


    Best Luck, Shenoy

    • Marked as answer by Thailog001 Friday, July 12, 2013 5:35 PM
    Friday, July 12, 2013 3:39 PM

All replies

  • Hi, I have no understanding of your business, But, here's one way of addressing it.

    WHERE (ReportSnapshot.ReportTypeStyleSheetTag IN (SELECT MAX(ReportTypeStyleSheetTag) FROM Eagle.dbo.ReportSnapshot))

    Hope this helps....

    Ione

    Thursday, July 11, 2013 10:36 PM
  • ;WITH cteUnique AS 
    (
    SELECT User.LastName,
           User.FirstName,
           User.Middle,
           User.BirthDate,
           User.ID,
           Research.ResearchDateTime,
           Report.ResearchTag,
           Research.UserTag,
           Research.ResearchID,
           ReportSnapshot.ScientistTag,
           --ReportSnapshot.ReportTypeStyleSheetTag,
    	   ,ROW_NUMBER() 
    		OVER (PARTITION BY [User].[ID] 
    		 ORDER BY 
    			ReportSnapshot.ReportTypeStyleSheetTag) AS Rn
           Scientist.ConfirmationSignature,
           ReportSnapshot.ReportDateTime AS SignedDateTime
      FROM (((Eagle.dbo.Research Research
              INNER JOIN Eagle.dbo.User User
                 ON (Research.UserTag = User.tag))
             INNER JOIN Eagle.dbo.Report Report
                ON (Report.ResearchTag = Research.tag))
            INNER JOIN Eagle.dbo.ReportSnapshot ReportSnapshot
               ON (ReportSnapshot.ReportTag = Report.tag))
           INNER JOIN Eagle.dbo.Scientist Scientist
              ON (ReportSnapshot.ScientistTag = Scientist.tag)
     WHERE (ReportSnapshot.ReportDateTime 
    					BETWEEN '20130701 00:00:00.000'
                     AND  '20130711 23:59:59.997')
    )
    SELECT *
    FROM cteUnique
    WHERE Rn=1

    Try this if you are on SQL Server 2005 or higher version.

    Narsimha

    • Marked as answer by Thailog001 Friday, July 12, 2013 4:26 PM
    Thursday, July 11, 2013 10:36 PM
  • Check the JOIN columns , there might a miss to add a column which is supposed to be part of the JOIN query.
    Friday, July 12, 2013 2:17 AM
  • SELECT User.LastName,
           User.FirstName,
           User.Middle,
           User.BirthDate,
           User.ID,
           Research.ResearchDateTime,
           Report.ResearchTag,
           Research.UserTag,
           Research.ResearchID,
           ReportSnapshot.ScientistTag,
           ReportSnapshot.ReportTypeStyleSheetTag,
           Scientist.ConfirmationSignature,
           ReportSnapshot.ReportDateTime AS SignedDateTime
      FROM (((Eagle.dbo.Research Research
              INNER JOIN Eagle.dbo.User User
                 ON (Research.UserTag = User.tag))
             INNER JOIN Eagle.dbo.Report Report
                ON (Report.ResearchTag = Research.tag))
            INNER JOIN Eagle.dbo.ReportSnapshot ReportSnapshot
               ON (ReportSnapshot.ReportTag = Report.tag))
           INNER JOIN Eagle.dbo.Scientist Scientist
              ON (ReportSnapshot.ScientistTag = Scientist.tag)
     WHERE (ReportSnapshot.ReportTypeStyleSheetTag IN (SELECT MAX(ReportTypeStyleSheetTag) FROM Eagle.dbo.ReportSnapshot))
           AND (ReportSnapshot.ReportDateTime BETWEEN '20130701 00:00:00.000'                                      AND  '20130701 23:59:59.997')

    Hi Ione,

    I plugged your code into the query but it returned no results  :(

    Friday, July 12, 2013 1:30 PM
  • SELECT User.LastName,
           User.FirstName,
           User.Middle,
           User.BirthDate,
           User.ID,
           Research.ResearchDateTime,
           Report.ResearchTag,
           Research.UserTag,
           Research.ResearchID,
           ReportSnapshot.ScientistTag,
           ReportSnapshot.ReportTypeStyleSheetTag,
           Scientist.ConfirmationSignature,
           ReportSnapshot.ReportDateTime AS SignedDateTime
      FROM (((Eagle.dbo.Research Research
              INNER JOIN Eagle.dbo.User User
                 ON (Research.UserTag = User.tag))
             INNER JOIN Eagle.dbo.Report Report
                ON (Report.ResearchTag = Research.tag))
            INNER JOIN Eagle.dbo.ReportSnapshot ReportSnapshot
               ON (ReportSnapshot.ReportTag = Report.tag))
           INNER JOIN Eagle.dbo.Scientist Scientist
              ON (ReportSnapshot.ScientistTag = Scientist.tag)
     WHERE (ReportSnapshot.ReportDateTime BETWEEN '20130701 00:00:00.000'
                                              AND  '20130711 23:59:59.997')
    	AND (ReportSnapshot.ReportTypeStyleSheetTag = 
    		(SELECT TOP 1 ReportTypeStyleSheetTag
    		FROM Eagle.dbo.ReportSnapshot A
    		WHERE (ReportSnapshot.ReportTag = A.ReportTag)
    				AND (ReportSnapshot.ScientistTag = A.ScientistTag )
    			(A.ReportDateTime BETWEEN '20130701 00:00:00.000'
                                    AND  '20130711 23:59:59.997')
    		)
    	)

    Check this.

    This is a solution for your query. But I think you should inform them that there can be more than 2 style sheets for the reports/snapshots. Should you display all of them.


    Best Luck, Shenoy

    • Marked as answer by Thailog001 Friday, July 12, 2013 5:35 PM
    Friday, July 12, 2013 3:39 PM
  • Hi Narsimha,

    Thanks for the help! I think that did it!  I had to tweet it alittle because it was merging different ResearchID's but I think I fixed that. Now I need to build it into the SSRS but its throwing errors.

    DECLARE @ResScientistAll int = 0
    ;WITH cteUnique AS 
    (SELECT
      User.LastName
      ,User.FirstName
      ,User.Middle
      ,User.LastName + ', ' + User.FirstName AS 'User Name'
      ,User.ID
      ,User.BirthDate
      ,Research.ResearchDateTime
      ,Report.ResearchTag
      ,Research.UserTag
      ,Research.ResearchID
      ,ReportSnapshot.ScientistTag
      ,ROW_NUMBER() 
    	OVER (PARTITION BY [User].[ID], [Research].[ResearchID]  
    	 ORDER BY ReportSnapshot.ReportTypeStyleSheetTag) AS Rn
      ,Scientist.ConfirmationSignature
      ,ReportSnapshot.ReportDateTime
      ,User.AccountNo
      ,Research.ResearchDescription
    FROM
      ReportSnapshot
      INNER JOIN Report
        ON Report.tag = ReportSnapshot.ReportTag
      INNER JOIN Scientist
        ON Scientist.tag = ReportSnapshot.ScientistTag
      INNER JOIN Research
        ON Research.tag = Report.ResearchTag
      INNER JOIN User
        ON User.tag = Research.UserTag
    WHERE ReportSnapshot.ReportDateTime >= @fromDate
       AND ReportSnapshot.ReportDateTime <= @toDate
       AND (@ResScientist = @ResScientistAll OR @ResScientist = Scientist.tag)
    ORDER BY Scientist.ConfirmationSignature, 'User Name'
    )
    SELECT *
    FROM cteUnique
    WHERE Rn=1

    Friday, July 12, 2013 4:24 PM
  • Hi Shenoy,

    You code works too thanks!  It was also easy to plug into SSRS.  Does this code look ok?

    DECLARE @ResScientistAll int = 0
    
    SELECT
      User.LastName
      ,User.FirstName
      ,User.Middle
      ,User.LastName + ', ' + User.FirstName AS 'User Name'
      ,User.ID
      ,User.BirthDate
      ,Research.ResearchDateTime
      ,Report.ResearchTag
      ,Research.UserTag
      ,Research.ResearchID
      ,ReportSnapshot.ScientistTag
      ,Scientist.ConfirmationSignature
      ,ReportSnapshot.ReportDateTime
      ,User.AccountNo
      ,Research.ResearchDescription
    FROM
      ReportSnapshot
      INNER JOIN Report
        ON Report.tag = ReportSnapshot.ReportTag
      INNER JOIN Scientist
        ON Scientist.tag = ReportSnapshot.ScientistTag
      INNER JOIN Research
        ON Research.tag = Report.ResearchTag
      INNER JOIN User
        ON User.tag = Research.UserTag
    WHERE ReportSnapshot.ReportDateTime >= @fromDate
       AND ReportSnapshot.ReportDateTime <= @toDate
       AND (ReportSnapshot.ReportTypeStyleSheetTag = 
    		(SELECT TOP 1 ReportTypeStyleSheetTag
    		 FROM Eagle.dbo.ReportSnapshot A
    		 WHERE (ReportSnapshot.ReportTag = A.ReportTag)
    		    AND (ReportSnapshot.ScientistTag = A.ScientistTag)
    		    AND A.ReportDateTime >= @fromDate 
                        AND A.ReportDateTime <= @toDate))
       AND (@ResScientist = @ResScientistAll OR @ResScientist = Scientist.tag)
    ORDER BY Scientist.ConfirmationSignature, 'User Name')

    Friday, July 12, 2013 5:34 PM
  • Hi, Please find modified code below.

    INNER JOIN Eagle.dbo.ReportSnapshot ReportSnapshot
               ON ReportSnapshot.ReportTag = Report.tag
    AND ReportSnapshot.ReportTypeStyleSheetTag IN (SELECT MAX(ReportTypeStyleSheetTag) FROM Eagle.dbo.ReportSnapshot)
    

    Ione

    Friday, July 12, 2013 5:46 PM
  • Hi Shenoy,

    You code works too thanks!  It was also easy to plug into SSRS.  Does this code look ok?

    Can you tell me what exactly you want to say here? Can you please mention your query here.

    You should always try to explain your query at least in brief. So that we will be able to understand your problem and suggest a good solution.


    Best Luck, Shenoy

    Saturday, July 13, 2013 8:48 AM