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
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
Fussionweb 2019-09-19 15:27:062019-09-19 15:27:06
0 respostas
Perguntas semelhantes
Veja mais perguntas por marcas sql-server