Consulta SQL criar duas colunas, cada uma com valores diferentes a partir de duas tabelas

Eu Tenho que criar duas colunas diferentes de valor, com os mesmos dados, coisa importante de dados é a partir da combinação de duas tabelas com a Instrução de associação interna. Aqui está a tabela 1 e tabela 2 (é como relatório de participação) Estou anexando tabelas HTML. Tabela 1 Criar uma tabela

    \* table 1 *\
    CREATE TABLE [dbo].[tsStudAttandance](
        [IDNo] [int] IDENTITY(1,1) NOT NULL,
        [StudentName] [varchar](300) NULL,
        [TrainnerName] [varchar](100) NULL,
        [Date] [date] NULL,
        [InTime] [varchar](50) NOT NULL,
        [Weekday] [varchar](50) NULL,
        [OutTime] [varchar](50) NULL,
        [StudListId] [int] NULL,
     CONSTRAINT [PK_tsAttandance] PRIMARY KEY CLUSTERED 
    (
        [IDNo] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
/* INSERT QUERY NO: 1 */
INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
VALUES
(
'Ms.Eva', 'John', '8/12/2019', '11:00 AM', 'Monday   ', '1:00 PM', 1
);

  /* INSERT QUERY NO: 2 */

    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Mr.John Stev', 'Stev', '8/12/2019', '11:00 AM', 'Monday   ', '1:00 PM', 2
    );

    /* INSERT QUERY NO: 3 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Mr.Stev Job', 'John', '8/12/2019', '11:00 AM', 'Monday', '1:00 PM', 3
    );

    /* INSERT QUERY NO: 4 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Mr.Bill Gate', 'John', '8/12/2019', '9:00 AM', 'Monday', '11:00 AM', 4
    );

    /* INSERT QUERY NO: 5 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Mr.Krish', 'Stev', '8/12/2019', '9:00 AM', 'Monday', '11:00 AM', 5
    );

    /* INSERT QUERY NO: 6 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Mr.Chisty', 'Stev', '8/12/2019', '5:00 PM', 'Monday', '7:00 PM', 6
    );

    /* INSERT QUERY NO: 7 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Mr.Vicky', 'John', '8/12/2019', '7:00 PM', 'Monday', '9:00 PM', 7
    );

    /* INSERT QUERY NO: 8 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Mr.Micky', 'Stev', '8/12/2019', '11:00 AM', 'Monday', '1:00 PM', 8
    );

    /* INSERT QUERY NO: 9 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Ms.Victorya', 'John', '8/12/2019', '5:00 PM', 'Monday', '7:00 PM', 9
    );

    /* INSERT QUERY NO: 10 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Ms.Sheena', 'Stev', '8/12/2019', '7:00 PM', 'Tuesday', '9:00 PM', 10
    );

    /* INSERT QUERY NO: 11 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Ms.Eva', 'John', '8/13/2019', '11:00 AM', 'Tuesday', '1:00 PM', 1
    );

    /* INSERT QUERY NO: 12 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Mr.John Stev', 'Stev', '8/13/2019', '11:00 AM', 'Tuesday', '1:00 PM', 2
    );

    /* INSERT QUERY NO: 13 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Mr.Stev Job', 'John', '8/13/2019', '11:00 AM', 'Tuesday', '1:00 PM', 3
    );

    /* INSERT QUERY NO: 14 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Mr.Bill Gate', 'John', '8/13/2019', '9:00 AM', 'Tuesday', '11:00 AM', 4
    );

    /* INSERT QUERY NO: 15 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Mr.Krish', 'Stev', '8/13/2019', '9:00 AM', 'Tuesday', '11:00 AM', 5
    );

    /* INSERT QUERY NO: 16 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Mr.Chisty', 'Stev', '8/13/2019', '5:00 PM', 'Tuesday', '7:00 PM', 6
    );

    /* INSERT QUERY NO: 17 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Mr.Vicky', 'John', '8/13/2019', '7:00 PM', 'Tuesday', '9:00 PM', 7
    );

    /* INSERT QUERY NO: 18 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Mr.Micky', 'Stev', '8/13/2019', '11:00 AM', 'Tuesday', '1:00 PM', 8
    );

    /* INSERT QUERY NO: 19 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Ms.Victorya', 'John', '8/13/2019', '5:00 PM', 'Tuesday', '7:00 PM', 9
    );

    /* INSERT QUERY NO: 20 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Ms.Sheena', 'Stev', '8/13/2019', '7:00 PM', 'Tuesday', '9:00 PM', 10
    );

    /* INSERT QUERY NO: 21 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Ms.Eva', 'John', '8/14/2019', '11:00 AM', 'Wednesday', '1:00 PM', 1
    );

    /* INSERT QUERY NO: 22 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Mr.John Stev', 'Stev', '8/14/2019', '11:00 AM', 'Wednesday', '1:00 PM', 2
    );

    /* INSERT QUERY NO: 23 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Mr.Stev Job', 'John', '8/14/2019', '11:00 AM', 'Wednesday', '1:00 PM', 3
    );

    /* INSERT QUERY NO: 24 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Mr.Bill Gate', 'John', '8/14/2019', '9:00 AM', 'Wednesday', '11:00 AM', 4
    );

    /* INSERT QUERY NO: 25 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Mr.Krish', 'Stev', '8/14/2019', '9:00 AM', 'Wednesday', '11:00 AM', 5
    );

    /* INSERT QUERY NO: 26 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Mr.Chisty', 'Stev', '8/14/2019', '5:00 PM', 'Wednesday', '7:00 PM', 6
    );

    /* INSERT QUERY NO: 27 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Mr.Vicky', 'John', '8/14/2019', '7:00 PM', 'Wednesday', '9:00 PM', 7
    );

    /* INSERT QUERY NO: 28 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Mr.Micky', 'Stev', '8/14/2019', '11:00 AM', 'Wednesday', '1:00 PM', 8
    );

    /* INSERT QUERY NO: 29 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Ms.Victorya', 'John', '8/14/2019', '5:00 PM', 'Wednesday', '7:00 PM', 9
    );

    /* INSERT QUERY NO: 30 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Ms.Sheena', 'Stev', '8/14/2019', '7:00 PM', 'Wednesday', '9:00 PM', 10
    );

    /* INSERT QUERY NO: 31 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Ms.Eva', 'John', '8/15/2019', '11:00 AM', 'Thursday', '1:00 PM', 1
    );

    /* INSERT QUERY NO: 32 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Mr.John Stev', 'Stev', '8/15/2019', '11:00 AM', 'Thursday', '1:00 PM', 2
    );

    /* INSERT QUERY NO: 33 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Mr.Stev Job', 'John', '8/15/2019', '11:00 AM', 'Thursday', '1:00 PM', 3
    );

    /* INSERT QUERY NO: 34 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Mr.Bill Gate', 'John', '8/15/2019', '9:00 AM', 'Thursday', '11:00 AM', 4
    );

    /* INSERT QUERY NO: 35 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Mr.Krish', 'Stev', '8/15/2019', '9:00 AM', 'Thursday', '11:00 AM', 5
    );

    /* INSERT QUERY NO: 36 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Mr.Chisty', 'Stev', '8/15/2019', '5:00 PM', 'Thursday', '7:00 PM', 6
    );

    /* INSERT QUERY NO: 37 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Mr.Vicky', 'John', '8/15/2019', '7:00 PM', 'Thursday', '9:00 PM', 7
    );

    /* INSERT QUERY NO: 38 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Mr.Micky', 'Stev', '8/15/2019', '11:00 AM', 'Thursday', '1:00 PM', 8
    );

    /* INSERT QUERY NO: 39 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Ms.Victorya', 'John', '8/15/2019', '5:00 PM', 'Thursday', '7:00 PM', 9
    );

    /* INSERT QUERY NO: 40 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Ms.Sheena', 'Stev', '8/15/2019', '7:00 PM', 'Thursday', '9:00 PM', 10
    );

    /* INSERT QUERY NO: 41 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Ms.Eva', 'John', '8/16/2019', '11:00 AM', 'Friday', '1:00 PM', 1
    );

    /* INSERT QUERY NO: 42 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Mr.John Stev', 'Stev', '8/16/2019', '11:00 AM', 'Friday', '1:00 PM', 2
    );

    /* INSERT QUERY NO: 43 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Mr.Stev Job', 'John', '8/16/2019', '11:00 AM', 'Friday', '1:00 PM', 3
    );

    /* INSERT QUERY NO: 44 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Mr.Bill Gate', 'John', '8/16/2019', '9:00 AM', 'Friday', '11:00 AM', 4
    );

    /* INSERT QUERY NO: 45 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Mr.Krish', 'Stev', '8/16/2019', '9:00 AM', 'Friday', '11:00 AM', 5
    );

    /* INSERT QUERY NO: 46 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Mr.Chisty', 'Stev', '8/16/2019', '5:00 PM', 'Friday', '7:00 PM', 6
    );

    /* INSERT QUERY NO: 47 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Mr.Vicky', 'John', '8/16/2019', '7:00 PM', 'Friday', '9:00 PM', 7
    );

    /* INSERT QUERY NO: 48 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Mr.Micky', 'Stev', '8/16/2019', '11:00 AM', 'Friday', '1:00 PM', 8
    );

    /* INSERT QUERY NO: 49 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Ms.Victorya', 'John', '8/16/2019', '5:00 PM', 'Friday', '7:00 PM', 9
    );

    /* INSERT QUERY NO: 50 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Ms.Sheena', 'Stev', '8/16/2019', '7:00 PM', 'Friday', '9:00 PM', 10
    );

    /* INSERT QUERY NO: 51 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Ms.Eva', 'John', '8/17/2019', '11:00 AM', 'Saturday', '1:00 PM', 1
    );

    /* INSERT QUERY NO: 52 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Mr.John Stev', 'Stev', '8/17/2019', '11:00 AM', 'Saturday', '1:00 PM', 2
    );

    /* INSERT QUERY NO: 53 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Mr.Stev Job', 'John', '8/17/2019', '11:00 AM', 'Saturday', '1:00 PM', 3
    );

    /* INSERT QUERY NO: 54 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Mr.Bill Gate', 'John', '8/17/2019', '9:00 AM', 'Saturday', '11:00 AM', 4
    );

    /* INSERT QUERY NO: 55 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Mr.Krish', 'Stev', '8/17/2019', '9:00 AM', 'Saturday', '11:00 AM', 5
    );

    /* INSERT QUERY NO: 56 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Mr.Chisty', 'Stev', '8/17/2019', '5:00 PM', 'Saturday', '7:00 PM', 6
    );

    /* INSERT QUERY NO: 57 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Mr.Vicky', 'John', '8/17/2019', '7:00 PM', 'Saturday', '9:00 PM', 7
    );

    /* INSERT QUERY NO: 58 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Mr.Micky', 'Stev', '8/17/2019', '11:00 AM', 'Saturday', '1:00 PM', 8
    );

    /* INSERT QUERY NO: 59 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Ms.Victorya', 'John', '8/17/2019', '5:00 PM', 'Saturday', '7:00 PM', 9
    );

    /* INSERT QUERY NO: 60 */
    INSERT INTO tsStudAttandance(StudentName, TrainnerName, Date, InTime, weekday, OutTime, StudListId)
    VALUES
    (
    'Ms.Sheena', 'Stev', '8/17/2019', '7:00 PM', 'Saturday', '9:00 PM', 10
    );

Tabela 2

\* Table 2 *\
CREATE TABLE [dbo].[tsStudenList](
    [IdNo] [int] IDENTITY(1,1) NOT NULL,
    [StudentName] [varchar](300) NULL,
    [TrainnerName] [varchar](300) NULL,
    [StudentRegNo] [varchar](15) NULL,
    [Course] [varchar](max) NULL,
    [Duration] [smallint] NULL,
    [Schedule] [varchar](100) NULL,
    [Days] [int] NULL,
    [StartDate]  AS (CONVERT([date],getdate())),
    [Timing] [varchar](100) NULL,
 CONSTRAINT [PK_tsStudenList] PRIMARY KEY CLUSTERED 
(
    [IdNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[tsStudAttandance]  WITH CHECK ADD  CONSTRAINT [FK_tsStudAttandance_tsStudenList] FOREIGN KEY([StudListId])
REFERENCES [dbo].[tsStudenList] ([IdNo])
GO
ALTER TABLE [dbo].[tsStudAttandance] CHECK CONSTRAINT [FK_tsStudAttandance_tsStudenList]
GO
/* INSERT QUERY NO: 1 */
INSERT INTO table_name(idno, studentname, trainnername, studentregno, course, duration, schedule, startdate, timing)
VALUES
(
1, 'Ms.Eva', 'John', 'TSSR1900001', 'CCNA', 60, 'SAT', '9/18/2019', '09:00 AM - 01:00 PM'
);

/* INSERT QUERY NO: 2 */
INSERT INTO table_name(idno, studentname, trainnername, studentregno, course, duration, schedule, startdate, timing)
VALUES
(
2, 'Mr.John Stev', 'Stev', 'TSSR1900002', 'CCNA', 60, 'SAT-WED', '9/18/2019', '09:00 AM - 01:00 PM'
);

/* INSERT QUERY NO: 3 */
INSERT INTO table_name(idno, studentname, trainnername, studentregno, course, duration, schedule, startdate, timing)
VALUES
(
3, 'Mr.Stev Job', 'John', 'TSSR1900003', 'CCNP', 70, 'SUN-MON-WED', '9/19/2019', '09:00 AM - 11 :00 AM'
);

/* INSERT QUERY NO: 4 */
INSERT INTO table_name(idno, studentname, trainnername, studentregno, course, duration, schedule, startdate, timing)
VALUES
(
4, 'Mr.Bill Gate', 'John', 'TSSR1900004', 'PHOTOSHOP', 20, 'SUN-MON-WED', '9/20/2019', '09:00 AM - 11:00 AM'
);

/* INSERT QUERY NO: 5 */
INSERT INTO table_name(idno, studentname, trainnername, studentregno, course, duration, schedule, startdate, timing)
VALUES
(
5, 'Mr.Krish', 'Stev', 'TSSR1900005', 'INDESIGN', 25, 'SAT-THR', '9/21/2019', '09:00 AM - 01:00 PM'
);

/* INSERT QUERY NO: 6 */
INSERT INTO table_name(idno, studentname, trainnername, studentregno, course, duration, schedule, startdate, timing)
VALUES
(
6, 'Mr.Chisty', 'Stev', 'TSSR1900006', 'CEH', 80, 'SUN-MON-WED', '9/22/2019', '11:00 AM - 01:00 PM'
);

/* INSERT QUERY NO: 7 */
INSERT INTO table_name(idno, studentname, trainnername, studentregno, course, duration, schedule, startdate, timing)
VALUES
(
7, 'Mr.Vicky', 'John', 'TSSR1900007', 'CEH', 80, 'FRI', '9/22/2019', '05:00 PM - 07:00 PM'
);

/* INSERT QUERY NO: 8 */
INSERT INTO table_name(idno, studentname, trainnername, studentregno, course, duration, schedule, startdate, timing)
VALUES
(
8, 'Mr.Micky', 'Stev', 'TSSR1900008', 'INDESIGN', 25, 'SAT-THR', '9/23/2019', '09:00 AM - 01:00 PM'
);

/* INSERT QUERY NO: 9 */
INSERT INTO table_name(idno, studentname, trainnername, studentregno, course, duration, schedule, startdate, timing)
VALUES
(
9, 'Ms.Victorya', 'John', 'TSSR1900009', 'PHOTOSHOP', 20, 'SUN-MON-WED', '9/24/2019', '09:00 AM - 11:00 PM'
);

/* INSERT QUERY NO: 10 */
INSERT INTO table_name(idno, studentname, trainnername, studentregno, course, duration, schedule, startdate, timing)
VALUES
(
10, 'Ms.Sheena', 'Stev', 'TSSR1900010', 'CEH', 40, 'MON', '9/24/2019', '07:00 PM - 09:00 PM'
);

Relatório Final precisa, como este enter image description here

Aqui é a Observação de que deveria vir SIM ou Não comparando eles Intime e OutTime (verificando a Agenda se SENTOU-SEG-QUA significa três dias, portanto, se as horas são menos de 6 Comentário Deve 'NÃO'. Como Que Deve Verificar horário e Tempo para Observações) e a Porcentagem do status de classe Deve somar as horas da semana(Sábado a Quinta-feira) e dividir pelo período de Duração da segunda tabela. Eu tenho Que resultado de Semana , Mas eu preciso de status geral muito se eu use Cláusulas Where, Entre a "Data de Início" E "Data Final'.

Mais Importante do Estado Geral e Atual semana de Status como o acima pic

Aqui está o Código Que eu tentei

  BEGIN
SELECT tsStudAttandance.StudentName as 'Name', tsStudenList.StartDate as 'Start Date',
CASE WHEN
         sum(
                    case 
                        when CONVERT(VARCHAR(6), TRIM(' ' FROM Right(tsStudAttandance.OutTime , 2))) = 'PM' AND CONVERT(VARCHAR(6), TRIM(' ' FROM Right(tsStudAttandance.InTime , 2))) ='AM'
                        then  cast(CONVERT(VARCHAR(6), TRIM(':00 PM' FROM LEFT(tsStudAttandance.OutTime , 2)) ) as float)+12 
                    else cast(CONVERT(VARCHAR(6), TRIM(':00 AM' FROM LEFT(tsStudAttandance.OutTime , 2)) ) as float) 
                    end 
            - cast(CONVERT(VARCHAR(6), TRIM(':00 AM' FROM LEFT(tsStudAttandance.Intime , 2)) ) as float)) = CAST(tsStudenList.Duration as float)
Then (SELECT top 1 tsStudAttandance.Date FROM tsStudAttandance ORDER BY  tsStudAttandance.Date DESC)

END as 'End Date',
cast(cast(round(sum(
                    case 
                        when CONVERT(VARCHAR(6), TRIM(' ' FROM Right(tsStudAttandance.OutTime , 2))) = 'PM' AND CONVERT(VARCHAR(6), TRIM(' ' FROM Right(tsStudAttandance.InTime , 2))) ='AM'
                        then  cast(CONVERT(VARCHAR(6), TRIM(':00 PM' FROM LEFT(tsStudAttandance.OutTime , 2)) ) as float)+12 
                    else cast(CONVERT(VARCHAR(6), TRIM(':00 AM' FROM LEFT(tsStudAttandance.OutTime , 2)) ) as float) 
                    end 
- cast(CONVERT(VARCHAR(6), TRIM(':00 AM' FROM LEFT(tsStudAttandance.Intime , 2)) ) as float))
/ cast(tsStudenList.Duration as float)*100,2) AS DECIMAL(18,2)) as varchar(100)) + ' %%' as 'Class Status in %%',
(
cast(cast(round(sum(
                    case 
                        when CONVERT(VARCHAR(6), TRIM(' ' FROM Right(tsStudAttandance.OutTime , 2))) = 'PM' AND CONVERT(VARCHAR(6), TRIM(' ' FROM Right(tsStudAttandance.InTime , 2))) ='AM'
                        then  cast(CONVERT(VARCHAR(6), TRIM(':00 PM' FROM LEFT(tsStudAttandance.OutTime , 2)) ) as float)+12 
                    else cast(CONVERT(VARCHAR(6), TRIM(':00 AM' FROM LEFT(tsStudAttandance.OutTime , 2)) ) as float) 
                    end 
- cast(CONVERT(VARCHAR(6), TRIM(':00 AM' FROM LEFT(tsStudAttandance.Intime , 2)) ) as float))
/ cast(tsStudenList.Duration as float)*100,2) AS DECIMAL(18,2)) as varchar(100)) + ' %%'  ) as 'Over Class Status in %%', 
tsStudenList.Course as 'Course Name', tsStudenList.Timing, tsStudenList.Schedule

FROM tsStudAttandance INNER JOIN dbo.tsStudenList ON tsStudAttandance.StudListId = tsStudenList.IdNo 
GROUP BY tsStudAttandance.StudentName,tsStudenList.Duration, tsStudenList.StartDate, tsStudenList.Schedule, tsStudenList.Course, tsStudenList.Timing, tsStudenList.Schedule 

End;
0
2019-09-19 15:27:06
origem

Veja mais perguntas por marcas