Search code examples
sqlsql-serversql-server-2014

Getting wrong output in pivot query

Attendance Table Sample Data - 

+--------------+-------------+-----------+------------+------------+
| EnrollmentNo | SubjectCode |   Date    | Attendance | CourseCode |
+--------------+-------------+-----------+------------+------------+
| DDU-140/12   | CSHT101     | 1/9/2018  |          1 | BSCCS      |
| DDU-140/12   | CSHT101     | 1/10/2018 |          1 | BSCCS      |
| DDU-140/12   | CSHT101     | 1/11/2018 |          2 | BSCCS      |
| DDU-286/12   | CSHT101     | 1/9/2018  |          1 | BSCCS      |
| DDU-286/12   | CSHT101     | 1/10/2018 |          1 | BSCCS      |
| DDU-286/12   | CSHT101     | 1/11/2018 |          0 | BSCCS      |
| DDU-286/12   | CSHT102     | 1/11/2018 |          1 | BSCCS      |
| DDU-286/12   | CSHT102     | 1/14/2018 |          2 | BSCCS      |
| DDU-320/12   | CSHT101     | 1/9/2018  |          2 | BSCCS      |
| DDU-320/12   | CSHT101     | 1/10/2018 |          1 | BSCCS      |
| DDU-320/12   | CSHT101     | 1/11/2018 |          0 | BSCCS      |
| DDU-320/12   | CSHT102     | 1/11/2018 |          1 | BSCCS      |
| DDU-320/12   | CSHT102     | 1/14/2018 |          0 | BSCCS      |
+--------------+-------------+-----------+------------+------------+

Student Table Sample Data - 

+--------------+--------+-------------+------------+------+
| EnrollmentNo | RollNO | CollegeCode | CourseCode | Year |
+--------------+--------+-------------+------------+------+
| DDU-140/12   |     22 | DDUC        | BSCCS      | 2012 |
| DDU-286/12   |     15 | DDUC        | BSCCS      | 2012 |
| DDU-320/12   |     38 | DDUC        | BSCCS      | 2012 |
+--------------+--------+-------------+------------+------+

StudentSubject Table Sample Data - 

+--------------+-------------+
| EnrollmentNo | SubjectCode |
+--------------+-------------+
| DDU-140/12   | CSHT101     |
| DDU-286/12   | CSHT101     |
| DDU-286/12   | CSHT102     |
| DDU-320/12   | CSHT101     |
| DDU-320/12   | CSHT102     |
+--------------+-------------+

Subject Table Sample Data - 

+-------------+---------------------------+
| SubjectCode |        SubjectName        |
+-------------+---------------------------+
| CSHP101     | Software Lab based on 101 |
| CSHT101     | Programming Fundamentals  |
| CSHT102     | Discrete Structures       |
+-------------+---------------------------+

Output -

Output

Wrong output is displaying with parameters -

@enrollno = 'DDU-320/12',
@startdate = '1/9/2018',
@enddate = '1/11/2018'

As 2,1,0 and NA,NA,1 should be displayed instead 2,1,1 and 2,1,1 is displaying.

Couldn't point out the mistake I am making. Can somebody take a look ?

CREATE PROCEDURE GET_ATTENDANCE_REPORT_FOR_STUDENT
@enrollno varchar(10),
@startdate DATE,  
@enddate DATE

AS BEGIN

DECLARE @query as varchar(MAX);

with cte (startdate) as 
(
    select @startdate startdate
    union all 
    select dateadd(DD, 1, startdate) 
    from cte
    where startdate < @enddate
)

select @query = coalesce(@query, '') + 
              N',coalesce(MAX(CASE when A.[Date] = ''' + 
              cast(cte.startdate as nvarchar(20)) + 
              N''' THEN Convert(varchar(10),A.[Attendance]) end), ''NA'') ' + 
              quotename(convert(char(6), cte.startdate,106))
from cte
where datename(weekday, cte.startdate) <> 'Sunday';

set @query = 'Select Concat(S.SubjectCode,'' '',S.SubjectName) Subject' + @query + '
              from Attendance A, Student St, StudentSubject SS, Subject S
              where A.EnrollmentNo=St.EnrollmentNo and St.EnrollmentNo=SS.EnrollmentNo and SS.SubjectCode=S.SubjectCode and St.EnrollmentNo =''' + @enrollno + '''
              Group By S.SubjectName,S.SubjectCode';


Execute (@query)
END
Solution

  • You are missing a join condition, Attendance table needs to be joined with SubjectCode as well. Due to the missing condition, it is picking the dates from both the SubjectCodes. 

    set @query = '
SELECT Concat(S.SubjectCode, '' '', S.SubjectName) Subject
       '+ @query +'
FROM   Attendance A
       INNER JOIN Student St
               ON A.EnrollmentNo = St.EnrollmentNo
       INNER JOIN StudentSubject SS
               ON St.EnrollmentNo = SS.EnrollmentNo
                  AND a.SubjectCode = ss.SubjectCode --here
       INNER JOIN Subject S
               ON SS.SubjectCode = S.SubjectCode
WHERE  St.EnrollmentNo =''' + @enrollno + '''
GROUP  BY S.SubjectName,
          S.SubjectCode'

Execute (@query)

    Also start using INNER JOIN syntax over the old style comma separated joins. Keep the join conditions in ON clause and filters in Where clause.

    Having said that all, you don't need those two tables in first place according to your expected result