替换0为1的所有列之和

Question

我正在创造学生的每月出勤率。在上一栏中，我将以以下格式显示总计类-

(上课总数)/(上课总数)

因此，sum()将给我所参加的课程总数，但是我如何获得总课程，因为出勤率可以是0到4之间的任何整数？

考勤表样本数据:-

+--------------+-------------+----------+------------+------------+-----------+
| EnrollmentNo | SubjectCode |   Date   | Attendance | CourseCode | FacultyId |
+--------------+-------------+----------+------------+------------+-----------+
| DDU-123/45   | CSHT101     | 2/1/2018 |          0 | BSCCS      | Fac101    |
| DDU-123/45   | CSHT102     | 2/1/2018 |          1 | BSCCS      | Fac101    |
| DDU-123/45   | CSHT203     | 2/1/2018 |          1 | BSCCS      | Fac101    |
| DDU-123/45   | CSHT101     | 2/2/2018 |          1 | BSCCS      | Fac101    |
| DDU-123/45   | CSHT102     | 2/2/2018 |          1 | BSCCS      | Fac101    |
| DDU-123/45   | CSHT203     | 2/2/2018 |          1 | BSCCS      | Fac101    |
| DDU-123/45   | CSHT101     | 2/3/2018 |          2 | BSCCS      | Fac101    |
| DDU-123/45   | CSHT102     | 2/3/2018 |          1 | BSCCS      | Fac101    |
| DDU-123/45   | CSHT203     | 2/3/2018 |          1 | BSCCS      | Fac101    |
| DDU-123/45   | CSHT101     | 2/5/2018 |          1 | BSCCS      | Fac101    |
| DDU-123/45   | CSHT102     | 2/5/2018 |          0 | BSCCS      | Fac101    |
| DDU-123/45   | CSHT203     | 2/5/2018 |          1 | BSCCS      | Fac101    |
+--------------+-------------+----------+------------+------------+-----------+

学生表的样本数据:-

+--------------+--------+-------------+------------+------+
| EnrollmentNo | RollNo | CollegeCode | CourseCode | Year |
+--------------+--------+-------------+------------+------+
| DDU-123/45   |     38 | DDUC        | BSCCS      | 2012 |
+--------------+--------+-------------+------------+------+

样本数据的UserDetails表:-

+--------------+-----------+----------+--------+---------+---------+
| EnrollmentNo | FirstName | LastName | Gender | Address |  Phone  |
+--------------+-----------+----------+--------+---------+---------+
| DDU-123/45   | Suyash    | Gupta    | Male   | Lucknow | 9817271 |
+--------------+-----------+----------+--------+---------+---------+

输出:-

​

​

在输出中，列应该包含4/5而不是4/4。

下面是我每月出勤的代码。

CREATE PROCEDURE GET_ATTENDANCE_REPORT_FOR_FACULTY
@startdate DATE,  
@enddate DATE,
@collegecode nvarchar(10),
@coursecode nvarchar(10),
@subjectcode nvarchar(10)
  
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), ''-'') ' + 
              quotename(convert(char(2), cte.startdate,104))
from cte
where datename(weekday, cte.startdate) <> 'Sunday';

set @query = 'Select S.RollNo AS [Roll No],Concat(FirstName,'' '',LastName) Name' + @query + ',Concat(sum(Attendance),''/'',count(Attendance)) Total
              from Attendance A, Student S, UserDetails U
              where A.EnrollmentNo=S.EnrollmentNo and S.EnrollmentNo=U.userID and S.CollegeCode=''' + @collegecode + ''' and A.CourseCode=''' + @coursecode + ''' and A.SubjectCode =''' + @subjectcode +''' 
              and A.Date between ''' + Convert(nvarchar,@startdate) + ''' and ''' + Convert(nvarchar,@enddate) + '''
              Group By S.RollNo,U.FirstName,U.LastName';
            

Execute (@query)
END

DDL和消耗品样本数据(Larnu的礼貌)

CREATE TABLE #Attendance (EnrollmentNo varchar(10),
                          SubjectCode varchar(7),
                          [Date] date,
                          Attendence tinyint,
                          CourseCode varchar(5),
                          FacultyId varchar(6));

INSERT INTO #Attendance
VALUES ('DDU-123/45','CSHT101','20180201',0,'BSCCS','Fac101'),
       ('DDU-123/45','CSHT102','20180201',1,'BSCCS','Fac101'),
       ('DDU-123/45','CSHT203','20180201',1,'BSCCS','Fac101'),
       ('DDU-123/45','CSHT101','20180202',1,'BSCCS','Fac101'),
       ('DDU-123/45','CSHT102','20180202',1,'BSCCS','Fac101'),
       ('DDU-123/45','CSHT203','20180202',1,'BSCCS','Fac101'),
       ('DDU-123/45','CSHT203','20180203',1,'BSCCS','Fac101'),
       ('DDU-123/45','CSHT101','20180205',1,'BSCCS','Fac101'),
       ('DDU-123/45','CSHT102','20180205',0,'BSCCS','Fac101'),
       ('DDU-123/45','CSHT203','20180205',1,'BSCCS','Fac101');

CREATE TABLE #Student (EnrollmentNo varchar(10),
                       RollNo smallint,
                       CollegeCode varchar(4),
                       CourseCode varchar(5),
                       [year] int);
INSERT INTO #Student
VALUES ('DDU-123/45',38,'DDUC','BSCCS',2012);

CREATE TABLE #UserDetail (EnrollmentNo varchar(10),
                          Firstname varchar(50),
                          LastName varchar(50),
                          Gender varchar (6),
                          [Address] varchar(500),
                          Phone varchar(15));
INSERT INTO #UserDetail
VALUES ('DDU-123/45','Suyash','Gupta','Male','Lucknow',9817271);
GO

Answer 1

这是一个如此简单的解决办法(礼貌拉努)。

SUM(CASE Attendance WHEN 0 THEN 1 ELSE Attendance END)