-- TABLES --
CREATE TABLE [dbo].[Root](
    [ID] [NCHAR](10) NOT NULL,
    [Name] [nvarchar](50) NULL,
 CONSTRAINT [PK_Root] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Job](
    [ID] [NCHAR](10) NOT NULL,
    [IDRoot] [NCHAR](10) NOT NULL,
    [TYPE] [nvarchar](50) NULL,
 CONSTRAINT [PK_Job] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Task](
    [ID] [NCHAR](10) NOT NULL,
    [IDParent] [NCHAR](10) NOT NULL,
    [IDRoot] [NCHAR](10) NOT NULL,
    [Name] [nvarchar](50) NULL,
    [StartDate] [datetime] NULL,
 CONSTRAINT [PK_Task] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

-- SAMPLE DATA --
INSERT INTO dbo.Root (ID, [Name]) VALUES ('1', 'Root 1')
INSERT INTO dbo.Root (ID, [Name]) VALUES ('2', 'Root 2')

INSERT INTO dbo.Job (ID, IDRoot, [TYPE]) VALUES ('1', '1', 'Type A')
INSERT INTO dbo.Job (ID, IDRoot, [TYPE]) VALUES ('2', '1', 'Type B')
INSERT INTO dbo.Job (ID, IDRoot, [TYPE]) VALUES ('3', '2', 'Type A')
INSERT INTO dbo.Job (ID, IDRoot, [TYPE]) VALUES ('4', '2', 'Type B')

INSERT INTO dbo.Task (ID, IDParent, IDRoot, [Name], StartDate) VALUES ('1', '1', '1', N'1st Task', '2000-01-01')
INSERT INTO dbo.Task (ID, IDParent, IDRoot, [Name], StartDate) VALUES ('2', '1', '1', N'2nd Task', '2000-01-05')
INSERT INTO dbo.Task (ID, IDParent, IDRoot, [Name], StartDate) VALUES ('3', '1', '1', N'3rd Task', '2000-01-10')

INSERT INTO dbo.Task (ID, IDParent, IDRoot, [Name], StartDate) VALUES ('4', '2', '1', N'1st Task', '2002-06-01')
INSERT INTO dbo.Task (ID, IDParent, IDRoot, [Name], StartDate) VALUES ('5', '2', '1', N'2nd Task', '2002-06-03')
INSERT INTO dbo.Task (ID, IDParent, IDRoot, [Name], StartDate) VALUES ('6', '2', '1', N'3rd Task', '2002-06-12')

INSERT INTO dbo.Task (ID, IDParent, IDRoot, [Name], StartDate) VALUES ('7', '3', '2', N'First Task', '2001-03-01')
INSERT INTO dbo.Task (ID, IDParent, IDRoot, [Name], StartDate) VALUES ('8', '3', '2', N'Second Task', '2001-04-01')
INSERT INTO dbo.Task (ID, IDParent, IDRoot, [Name], StartDate) VALUES ('9', '3', '2', N'Third Task', '2001-05-01')

INSERT INTO dbo.Task (ID, IDParent, IDRoot, [Name], StartDate) VALUES ('10', '4', '2', N'1st Task', '2010-10-31')
INSERT INTO dbo.Task (ID, IDParent, IDRoot, [Name], StartDate) VALUES ('11', '4', '2', N'2nd Task', '2010-12-25')
INSERT INTO dbo.Task (ID, IDParent, IDRoot, [Name], StartDate) VALUES ('12', '4', '2', N'3rd Task', '2010-12-31')

-- QUERY A --
SELECT COUNT(r.id) AS RowIndex, t.id, t.idRoot, t.Name
FROM Task t
  INNER JOIN Job j ON j.id = t.IdParent
  INNER JOIN (
    SELECT b.id, b.idParent, b.idRoot, b.StartDate
    FROM Task b
      INNER JOIN Job c ON b.idParent = c.id
  ) r ON t.StartDate >= r.StartDate AND r.idParent = j.id AND t.idRoot = r.idRoot
WHERE t.idRoot IN ('1', '2')
  AND j.[TYPE] LIKE '%Type A%'
GROUP BY t.idRoot, t.id, t.Name
HAVING COUNT(r.id) = 1

-- Query B --
SELECT COUNT(r.id) AS RowIndex, t.id, t.idRoot, t.Name
FROM Task t
  INNER JOIN Job j ON j.id = t.IdParent
  INNER JOIN (
    SELECT b.id, b.idParent, b.idRoot, b.StartDate
    FROM Task b
      INNER JOIN Job c ON b.idParent = c.id
  ) r ON t.StartDate >= r.StartDate AND r.idParent = j.id AND t.idRoot = r.idRoot
WHERE t.idRoot IN ('1', '2')
  AND j.[TYPE] LIKE '%Type B%'
GROUP BY t.idRoot, t.id, t.Name
HAVING COUNT(r.id) = 1