help organizing report(SQL)
  • I have this view that i use for report.The view definition is below.
    SELECT     ct.LastName, ih.InspectionID, er.Enrollment AS HcStatus, ISNULL(ih.Compliant, 'No') AS Compliant, ih.Year, ih.Season, CAST(ih.Season + CAST(ih.Year AS varchar(20))
                          AS varchar(20)) AS Combined, CONVERT(varchar(20), ih.InspectionDate, 101) AS InspectionDate, ih.OperationID AS AnimalBusinessID
    FROM         dbo.InspectionsHistory AS ih INNER JOIN
                              (SELECT     OperationID, Year, Season, MAX(InspectionDate) AS InspectionDate
                                FROM          dbo.InspectionsHistory
                                GROUP BY OperationID, Year, Season) AS f ON ih.OperationID = f.OperationID AND ih.Year = f.Year AND ih.Season = f.Season AND
                          ih.InspectionDate = f.InspectionDate INNER JOIN
                          dbo.AnimalBusinesses AS ab ON ih.OperationID = ab.AnimalBusinessID INNER JOIN
                          dbo.Enrollments AS er ON ab.AnimalBusinessID = er.AnimalBusinessID INNER JOIN
                          dbo.Contacts AS ct ON ab.ContactID = ct.ContactID
    WHERE     (ab.AnimalClassification = 'CERVID')

    I wrote this store procedure the generate a report
    DECLARE @cols AS NVARCHAR(MAX),@cols2 AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX)

    select @cols =  stuff(( select '],[' + Combined
    from vCervidInspections group by Combined
    for xml path('')), 1, 2, '') + ']'

    select @cols2 =  stuff(( select ', ISnull(Cast([' + Combined +'] as Varchar(20)),'+''''+ 'N/A'+''''+') '+Combined+', (select IsNull(max(Compliant),'+''''+'N/A'+''''+ ') Compliant from vCervidInspections where Combined=' +''''+ Combined + '''' + ' and InspectionDate='+Combined + ' and HcStatus=pvt.HcStatus and LastName=pvt.LastName) Compliant'
    from vCervidInspections group by Combined
    for xml path('')), 1, 2, '')

    set @query = 'SELECT LastName,HcStatus, '
    + @cols2 +'
    from
     (
      Select LastName,HcStatus,Compliant, Combined,
        InspectionDate,
        cast(InspectionDate as dateTIME) dateTIME,
        row_number() over(partition by LastName, Combined, cast(InspectionDate as dateTIME)
                          order by InspectionDate) rn
      from vCervidInspections
      group by LastName,HcStatus,Compliant, Combined,InspectionDate
    )p
    pivot
    (
      min(InspectionDate)
      For Combined in (' + @cols + ')
    )pvt order by LastName asc'

    execute(@query)

    I am getting this result showing multiples last name per row.
    http://imageshack.com/a/img208/2444/bdho.png

    But my goal is to have only 1 last name per row .For ALMOND i want something like this:
    http://imageshack.com/a/img834/2082/6e5f.png

Howdy, Stranger!

It looks like you're new here. If you want to get involved, or you want to Ask a new Question, Please Login or Create a new Account by Clicking below

Login with Facebook

Popular Posts of the Week

Optimum Creative