动态生成列之和

Question

我正在创造学生的每月出勤率。

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

这样可以很好地生成出勤日期记录，但是可以有多个相同日期的记录。

在这种情况下，应显示该特定日期的出席总人数。

那么，我应该在@query中更改什么才能达到预期的结果呢？

样本数据：

CREATE TABLE #Attendance (EnrollmentNo varchar(50),
                          SubjectCode varchar(10),
                          [Date] datetime,
                          Attendance numeric(1,0),
                          CourseCode varchar(10),
                          FacultyId varchar(50));

INSERT INTO #Attendance
VALUES ('DDU-320/12','CSHT101','20180201',0,'BSCCS','Fac101'),
       ('DDU-320/12','CSHT101','20180201',1,'BSCCS','Fac101'),
       ('DDU-320/12','CSHT101','20180201',1,'BSCCS','Fac101'),
       ('DDU-320/12','CSHT101','20180202',1,'BSCCS','Fac101'),
       ('DDU-320/12','CSHT101','20180202',2,'BSCCS','Fac101'),
       ('DDU-320/12','CSHT101','20180202',0,'BSCCS','Fac101');

CREATE TABLE #Student (EnrollmentNo varchar(50),
                       RollNo numeric(3,0),
                       CollegeCode varchar(10),
                       CourseCode varchar(10),
                       [year] int);
INSERT INTO #Student
VALUES ('DDU-320/12',38,'DDUC','BSCCS',2012);

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

期望输出：

+---------+--------------+----+----+-------+
| Roll No |     Name     | 01 | 02 | Total |
+---------+--------------+----+----+-------+
|      38 | Suyash Gupta |  2 |  3 |     5 |
+---------+--------------+----+----+-------+

Answer 1

以下是编译代码时最终结果的样子：

我将您的测试数据从UserDetails更改为UserDetail。然后我写的不是s.EnrollmentNo = u.UserId的地方，而是s.EnrollmentNo = u.EnrollmentID 此外，我使用了联接而不是where子句。 我也没有对你的总数做任何事情，因为很明显，你只需要去掉你的计数和斜杠/

SELECT S.RollNo AS [Roll No]
    ,CONCAT (
        FirstName
        ,' '
        ,LastName
        ) NAME
    ,coalesce(sum(CASE 
                WHEN A.[Date] = '2018-02-01'
                    THEN A.[Attendance]
                END), 0) [01]
    ,coalesce(SUM(CASE 
                WHEN A.[Date] = '2018-02-02'
                    THEN  A.[Attendance]
                END), 0) [02]

    ,CONCAT (
        sum(Attendance)
        ,'/'
        ,count(Attendance)
        ) Total
FROM Attendance A
    inner join Student S on A.EnrollmentNo = S.EnrollmentNo
    inner join UserDetail U on S.EnrollmentNo = U.EnrollmentNo
WHERE
     S.CollegeCode = 'DDUC'
    AND A.CourseCode = 'BSCCS'
    AND A.SubjectCode = 'CSHT101'
    AND A.DATE BETWEEN '2018-02-01'
        AND '2018-02-02'
GROUP BY S.RollNo
    ,U.FirstName
    ,U.LastName

，所以你会喜欢这个

 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(sum(CASE when A.[Date] = ''' + 
              cast(cte.startdate as nvarchar(20)) + 
              N''' THEN A.[Attendance] end), 0) ' + 
              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 inner join Student S on A.EnrollmentNo = S.EnrollmentNo
        inner join UserDetail U on S.EnrollmentNo = U.EnrollmentNo
              where 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';

PRINT @query Execute (@query)

结果为8天，只是为了显示当它们不相等时所发生的事情，

​

​

Answer 2

使用示例数据(在更正之前)，这将给出所需的答案，但仍然需要适应动态工作：

WITH x AS (
SELECT
    s.RollNo,
    u.LastName + ' ' + u.Firstname AS [Name],
    DATEPART(DAY, a.[Date]) AS [day],
    SUM(a.Attendance) AS Attendance
FROM
    #Student s
    INNER JOIN #UserDetail u ON u.EnrollmentNo = s.EnrollmentNo
    INNER JOIN #Attendance a ON a.EnrollmentNo = s.EnrollmentNo
GROUP BY
    s.RollNo,
    u.LastName + ' ' + u.Firstname,
    DATEPART(DAY, a.[Date]))
SELECT 
    *,
    (SELECT SUM(Attendance) FROM x WHERE x.RollNo = p.RollNo) AS total
FROM 
    x 
    PIVOT (SUM(Attendance) FOR [Day] IN ([1], [2])) p;

结果：

RollNo  Name            1   2   total
38      Gupta Suyash    2   3   5

Answer 3

除了Richard Hansell的答复外，我还会使用动态枢轴来包括提供给存储过程的日期期间的所有天数，并使用分组集来总结总数。

以下是查询：

        /*Creating the sample data*/
                    IF OBJECT_ID ('tempdb..#Attendance') IS NOT NULL DROP TABLE #Attendance
        IF OBJECT_ID ('tempdb..#Student') IS NOT NULL DROP TABLE #Student
        IF OBJECT_ID ('tempdb..#UserDetail') IS NOT NULL DROP TABLE #UserDetail

        CREATE TABLE #Attendance (EnrollmentNo varchar(50),
                                  SubjectCode varchar(10),
                                  [Date] datetime,
                                  Attendance numeric(1,0),
                                  CourseCode varchar(10),
                                  FacultyId varchar(50));

        INSERT INTO #Attendance
        VALUES ('DDU-320/12','CSHT101','20180201',0,'BSCCS','Fac101'),
               ('DDU-320/12','CSHT101','20180201',1,'BSCCS','Fac101'),
               ('DDU-320/12','CSHT101','20180201',1,'BSCCS','Fac101'),
               ('DDU-320/12','CSHT101','20180202',1,'BSCCS','Fac101'),
               ('DDU-320/12','CSHT101','20180202',2,'BSCCS','Fac101'),
               ('DDU-322/12','CSHT100','20180202',2,'BSCCO','Fac101'),
               ('DDU-320/12','CSHT101','20180202',0,'BSCCS','Fac101');

        CREATE TABLE #Student (EnrollmentNo varchar(50),
                               RollNo numeric(3,0),
                               CollegeCode varchar(10),
                               CourseCode varchar(10),
                               [year] int);
        INSERT INTO #Student
        VALUES ('DDU-320/12',38,'DDUC','BSCCS',2012),
               ('DDU-322/12',39,'DDUC','BSCCO',2012);

        CREATE TABLE #UserDetail (userID varchar(50),
                                  Firstname varchar(50),
                                  LastName varchar(50),
                                  Gender varchar (6),
                                  [Address] varchar(500),
                                  Phone varchar(10));
        INSERT INTO #UserDetail
        VALUES ('DDU-320/12','Suyash','Gupta','Male','Lucknow',9817271),
               ('DDU-322/12','Gupta','Suyash','Male','Lucknow',9817279);
        GO

        SET NOCOUNT ON 
        IF OBJECT_ID ('tempdb..#T') IS NOT NULL DROP TABLE #T
        /*These are the Parameters for you stored procedure*/
        DECLARE @startdate DATE ='2018-02-01 00:00:00.000'  
        DECLARE @enddate DATE= '2018-02-02 00:00:00.000'
        DECLARE @collegecode nvarchar(10)= 'DDUC'
        DECLARE @coursecode nvarchar(10) = 'BSCCS'
        DECLARE @subjectcode nvarchar(10) ='CSHT101'

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

        SELECT startdate ,  CAST(RIGHT('0'+CONVERT(VARCHAR(3),DATEPART(DAY,[startdate])),2) AS VARCHAR(20)) startdate_Day
        INTO #T
        FROM cte

        DECLARE @Cols NVARCHAR(MAX) = ''

        SELECT @Cols += ',' + QUOTENAME(startdate_Day)
        FROM #T
        GROUP BY startdate_Day

        SET @Cols= STUFF(@Cols, 1,1,'') + ',[Total]'

        DECLARE @Pvt NVARCHAR(MAX)=
        '
        ;WITH Aggr as 
        (
        SELECT  s.RollNo , d.Firstname + '' '' + d.LastName [Name] , a.[Date],  T.startdate_Day,
        SUM(Attendance) Attendance
        FROM #T T
        LEFT JOIN #Attendance A 
        ON T.startdate = A.[Date]
        JOIN #Student S 
        ON A.EnrollmentNo = S.EnrollmentNo
        JOIN #UserDetail D
        ON S.EnrollmentNo = D.userID
        GROUP BY GROUPING SETS  (
                        (s.RollNo ,d.Firstname + '' '' + d.LastName , a.[Date] ,T.startdate_Day ) , (s.RollNo , d.Firstname + '' '' + d.LastName ))

        )
        SELECT RollNo , [Name] , '+@Cols+'
        FROM 
        (
        SELECT RollNo, [Name]  ,  ISNULL(startdate_Day,''Total'') startdate_Day , Attendance
         FROM Aggr
         ) Main 
         PIVOT
            (
            MAX(Attendance) FOR startdate_Day IN ('+@Cols+')
            ) P
        '
        EXEC sp_executesql @Pvt