Somme de la colonne générée dynamiquement

Question

Je génère les présences mensuelles des étudiants.

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

Cela fonctionne bien pour générer les enregistrements de présence par date, mais il peut y avoir plusieurs enregistrements pour la même date.

Dans ce cas, la somme des présences doit être affichée pour cette date particulière.

Alors, que dois-je changer dans @query pour obtenir le résultat souhaité ?

Données de l'échantillon :

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

Sortie souhaitée :

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

Answer 1

En utilisant votre échantillon de données (avant qu'il ne soit corrigé), vous obtiendrez la réponse dont vous avez besoin, mais il faudra encore l'adapter pour qu'il fonctionne de manière dynamique :

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;

Résultats :

RollNo  Name            1   2   total
38      Gupta Suyash    2   3   5

Answer 2

Voici à quoi devrait ressembler votre résultat final lorsque le code est compilé :

J'ai changé vos données de test de UserDetails en UserDetail. Et au lieu de where s.EnrollmentNo = u.UserId, j'ai écrit s.EnrollmentNo = u.EnrollmentID.

De plus, j'ai utilisé des jointures au lieu de la clause where

Je n'ai rien fait non plus avec votre total, car il est évident que vous n'avez qu'à enlever votre compte et votre barre oblique /

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

Alors vous, le PS, vous aimeriez bien

 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)

Résultat avec 8 jours, juste pour montrer ce qui se passe quand ils ne sont pas égaux.

Answer 3

En plus de Richard Hansell Réponse de l'entreprise, J'utiliserais un pivot dynamique pour inclure tous les jours de la période de date fournie à la procédure stockée et j'utiliserais des ensembles de regroupement pour additionner le total.

Voici la requête :

        /*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