locked
problem displaying and joining correct data on a lookup RRS feed

  • Question

  • User1423824373 posted

    The below statement will not show correct data in SSRS.  It will give data from the first select only, but in SSMS, bot result sets are returned.  So I tried to do a UNION so I can have one resultset and got an error is SSMS, so I removed the Order by statements in both, and one result set was returned, except, I am not sure which are my MailedCases and which are my Cases count.

    In SSRS I also created two datasets, but I can not figure out a way to display both counts but joined on the month and lname. 

    *note, I created a lookup (=Lookup(Fields!Month_Name.Value,Fields!Month_Name.Value,Fields!MailedCases.Value,"MailedCases") (field names are correct in ssrs, just different in ssms) but I am unable to match the data on month and lname here as well.

    SELECT     COUNT(column1) AS Cases, DATENAME(month, column1) + ' - ' + DATENAME(year, column1) as [Mailed Month], MONTH(column1)  AS Mailed, ISNULL(Staff.LName, 'Unknown') AS LNameMailed

    FROM         Patient as patient_1 INNER JOIN

                          Staff AS Staff_1 ON Patient_1.Staff_ID = Staff_1.Staff_ID

                          WHERE     (column1 >= DATEADD(yy, - 1, GETDATE()))

                          group by DATENAME(month, column1 DATENAME(year, column1), MONTH(column1), staff_1.LName

                           ORDER BY DATENAME(year, column1), Mailed

                    SELECT      COUNT(column2) AS Cases, DATENAME(month, column2) + ' - ' + DATENAME(year, column2) AS [Month Name], MONTH(column2) AS MonthOrderBy, ISNULL(Staff_1.LName, 'Unknown') AS LName                     

    FROM         Patient as patient_1 INNER JOIN

                          Staff as staff_1 ON Patient_1.Staff_ID = Staff_1.Staff_ID

    WHERE     (column2 >= DATEADD(yy, - 1, GETDATE()))

    Group By DATENAME(month, column2), DATENAME(year, column2), MONTH(column2), staff_1.LName

    ORDER BY DATENAME(year, column2), MonthOrderBy                 

    Wednesday, August 8, 2012 1:53 PM

Answers

  • User1423824373 posted

    I ended up with two datasets.  It appears that because the dates were different (or not an equal number of date returned from both date columns) I was unable to use one table.  I ended up creating two tables to resolve this.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 14, 2012 10:48 AM