-- Want to make this a fair test with Postgres
sp_configure 'max degree of parallelism', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO


CREATE TABLE header (
  header_id int identity(1, 1) primary key,
  description varchar(255) not null,
  amount numeric(20,10) not null
  );

CREATE TABLE detail_1 (
  detail_1_id int identity(1, 1) primary key,
  header_id int not null,
  quantity  numeric(20,10) not null,
  rate  numeric(20,10) not null
  );
  
CREATE TABLE detail_2 (
  detail_2_id int identity(1, 1) primary key,
  header_id int not null,
  amount  numeric(20,10) not null
  );
  
  with t as (
  select 1 as d
  union all
  select d + 1
  from t
  where d < 1000
  )
  INSERT INTO header (description, amount)
  SELECT 'header record ' + CAST(t.d AS varchar(255)), ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT))
  FROM t
  OPTION (MAXRECURSION 10000);
  
  with t as (
  select 1 as d
  union all
  select d + 1
  from t
  where d < 1000
  )
  INSERT INTO detail_1 (header_id, quantity, rate)
  SELECT header_id, ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) / 1000, ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) / 10000
  FROM header
  INNER JOIN t
  ON 1=1
  OPTION (MAXRECURSION 10000);
  
  with t as (
  select 1 as d
  union all
  select d + 1
  from t
  where d < 7
  )
  INSERT INTO detail_2 (header_id, amount)
  SELECT header_id, ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) / 1000
  FROM header
  INNER JOIN t
  ON 1=1
  OPTION (MAXRECURSION 10000);

  UPDATE detail_1 SET rate = rate / 1000, quantity = quantity / 50;
  UPDATE detail_2 SET amount = amount / 100;
  UPDATE header SET amount = amount / 100;

  
CREATE NONCLUSTERED INDEX idx_detail_1_header_id
ON [dbo].[detail_1] ([header_id]);

CREATE NONCLUSTERED INDEX idx_detail_2_header_id
ON [dbo].[detail_2] ([header_id]);

CREATE VIEW [dbo].[header_total]
AS
SELECT header.header_id
, coalesce(detail_1.amount, 0) AS detail_1_amount
, coalesce(detail_1.detail_1_count, 0) AS detail_1_count
, coalesce(detail_2.amount, 0) AS detail_2_amount
, coalesce(detail_2.detail_2_count, 0) AS detail_2_count
, coalesce(detail_1.amount, 0) + coalesce(detail_2.amount, 0) as detail_total
FROM header
LEFT JOIN (
SELECT header_id
, sum(rate * quantity) as amount 
, count(detail_1_id) as detail_1_count
FROM detail_1
GROUP BY header_id
) detail_1
ON header.header_id = detail_1.header_id
LEFT JOIN (
SELECT header_id
, sum(amount) as amount 
, count(detail_2_id) as detail_2_count
FROM detail_2
GROUP BY header_id
) detail_2
ON header.header_id = detail_2.header_id;


SELECT *
FROM header
INNER JOIN header_total
ON header.header_id = header_total.header_id
WHERE header.header_id = 26;


SELECT *
FROM header
INNER JOIN header_total
ON header.header_id = header_total.header_id
WHERE header.header_id < 27
AND header.header_id > 24;


SELECT *
FROM header
INNER JOIN header_total
ON header.header_id = header_total.header_id
WHERE header.description like '%5%';


SELECT *
FROM header_total
WHERE header_total.header_id IN (
SELECT header_id
FROM header
WHERE header.header_id < 27
AND header.header_id > 24);