Suikwan tests, Sql Server

Suikwan tests

/*----- creat Tickets -----*/
create table #Tickets(
    "id" int primary key,
    "creation_date" date,
    "expiration_date" date
    );
    
insert into #Tickets
values(1, '20180120', '20210920'),
      (2, '20180530', '20181030'),
      (3, '20180615', '20211130');

/*----- creat Owners -----*/
create table #Owners(
    "id" int primary key,
    "ticket_id" int,
    "owner_name" varchar(50),
    "doc_num" varchar(9),
    "address" varchar(50),
    "phone" varchar(8)
    );
    
insert into #Owners
values(1, 1, 'Dhzon', '11 22 333', 'LA 5-10', '12345678'),
      (2, 2, 'Dhzek', '12 22 335', 'LA 1-2', '11222112'), 
      (3, 3, 'Mary', '55 33 211', 'LA 8-6', '12233557');

/*----- creat Orders -----*/
create table #Orders(
    "id" int primary key,
    "ticket_id" int,
    "book_id" int,
    "taken_start_date" date,
    "taken_end_date" date,
    "returned_date" date,
    "overdue" int,
    );
    
insert into #Orders
values(1, 1, 2, '20180520', '20180530', '20180528', 0),
      (2, 1, 3, '20190601', '20190611', '20190615', 1),
      (3, 2, 1, '20180720', '20180730', '20180722', 0),
      (4, 3, 2, '20180910', '20180920', '20180918', 0),
      (5, 3, 4, '20181002', '20181012', '20181014', 1),
      (6, 3, 5, '20181016', '20181026', '20181028', 1),
      (7, 1, 5, '20181028', '20181106', '20181105', 0),
      (8, 3, 6, '20181101', '20181111', '20181116', 1);
     
/*----- tests -----*/
select 'Select from table Tickets:' as 'Next result';
select * from #Tickets;
select 'Select from table Owners:' as 'Next result';
select * from #Owners;
select 'Select from table Orders:' as 'Next result';
select * from #Orders;

/*----- Declare test date -----*/
DECLARE @date date = GETDATE();
set @date = '20211020';
select @date as 'Test date';

/*---------- solution 1 -----------*/
select 'overdue tickets:' as 'Next result';
SELECT 
  ow.ticket_id as "Номер билета",
  ow.owner_name as "Имя владельца",
  ow.phone as "Телефон владельца",
  ti.expiration_date as "Дата окончания действия билета"
FROM
  #Owners as ow
     INNER JOIN #Tickets as ti on ti.id = ow.ticket_id
WHERE
  ti.expiration_date < @date;
    
 /*--------- solution 2------------*/
 select 'owners whis overdue book:' as 'Next result';
SELECT
  ow.ticket_id as "Номер билета",
  ow.owner_name as "Имя владельца",
  ow.phone as "Телефон владельца",
  COUNT(ords.overdue) as "Количество просрочек"
FROM
  #Owners as ow
     INNER JOIN #Orders as ords on ords.ticket_id = ow.ticket_id
	 INNER JOIN #Tickets as ti on ti.id = ow.ticket_id and ti.expiration_date > @date
WHERE
  ords.overdue = 1
GROUP BY
    ow.ticket_id, ow.owner_name, ow.phone
HAVING
  COUNT(ords.overdue) >= 3;


run \| edit \| history \| help	0