Public Pastes
SHARE
TWEET

Untitled

a guest Apr 30th, 2019 82 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.  
  2. -- TABLES --
  3. CREATE TABLE [dbo].[Root](
  4.     [ID] [NCHAR](10) NOT NULL,
  5.     [Name] [nvarchar](50) NULL,
  6.  CONSTRAINT [PK_Root] PRIMARY KEY CLUSTERED
  7. (
  8.     [ID] ASC
  9. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  10. ) ON [PRIMARY]
  11. GO
  12.  
  13. CREATE TABLE [dbo].[Job](
  14.     [ID] [NCHAR](10) NOT NULL,
  15.     [IDRoot] [NCHAR](10) NOT NULL,
  16.     [TYPE] [nvarchar](50) NULL,
  17.  CONSTRAINT [PK_Job] PRIMARY KEY CLUSTERED
  18. (
  19.     [ID] ASC
  20. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  21. ) ON [PRIMARY]
  22. GO
  23.  
  24. CREATE TABLE [dbo].[Task](
  25.     [ID] [NCHAR](10) NOT NULL,
  26.     [IDParent] [NCHAR](10) NOT NULL,
  27.     [IDRoot] [NCHAR](10) NOT NULL,
  28.     [Name] [nvarchar](50) NULL,
  29.     [StartDate] [datetime] NULL,
  30.  CONSTRAINT [PK_Task] PRIMARY KEY CLUSTERED
  31. (
  32.     [ID] ASC
  33. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  34. ) ON [PRIMARY]
  35. GO
  36.  
  37. -- SAMPLE DATA --
  38. INSERT INTO dbo.Root (ID, [Name]) VALUES ('1', 'Root 1')
  39. INSERT INTO dbo.Root (ID, [Name]) VALUES ('2', 'Root 2')
  40.  
  41. INSERT INTO dbo.Job (ID, IDRoot, [TYPE]) VALUES ('1', '1', 'Type A')
  42. INSERT INTO dbo.Job (ID, IDRoot, [TYPE]) VALUES ('2', '1', 'Type B')
  43. INSERT INTO dbo.Job (ID, IDRoot, [TYPE]) VALUES ('3', '2', 'Type A')
  44. INSERT INTO dbo.Job (ID, IDRoot, [TYPE]) VALUES ('4', '2', 'Type B')
  45.  
  46. INSERT INTO dbo.Task (ID, IDParent, IDRoot, [Name], StartDate) VALUES ('1', '1', '1', N'1st Task', '2000-01-01')
  47. INSERT INTO dbo.Task (ID, IDParent, IDRoot, [Name], StartDate) VALUES ('2', '1', '1', N'2nd Task', '2000-01-05')
  48. INSERT INTO dbo.Task (ID, IDParent, IDRoot, [Name], StartDate) VALUES ('3', '1', '1', N'3rd Task', '2000-01-10')
  49.  
  50. INSERT INTO dbo.Task (ID, IDParent, IDRoot, [Name], StartDate) VALUES ('4', '2', '1', N'1st Task', '2002-06-01')
  51. INSERT INTO dbo.Task (ID, IDParent, IDRoot, [Name], StartDate) VALUES ('5', '2', '1', N'2nd Task', '2002-06-03')
  52. INSERT INTO dbo.Task (ID, IDParent, IDRoot, [Name], StartDate) VALUES ('6', '2', '1', N'3rd Task', '2002-06-12')
  53.  
  54. INSERT INTO dbo.Task (ID, IDParent, IDRoot, [Name], StartDate) VALUES ('7', '3', '2', N'First Task', '2001-03-01')
  55. INSERT INTO dbo.Task (ID, IDParent, IDRoot, [Name], StartDate) VALUES ('8', '3', '2', N'Second Task', '2001-04-01')
  56. INSERT INTO dbo.Task (ID, IDParent, IDRoot, [Name], StartDate) VALUES ('9', '3', '2', N'Third Task', '2001-05-01')
  57.                                                            
  58. INSERT INTO dbo.Task (ID, IDParent, IDRoot, [Name], StartDate) VALUES ('10', '4', '2', N'1st Task', '2010-10-31')
  59. INSERT INTO dbo.Task (ID, IDParent, IDRoot, [Name], StartDate) VALUES ('11', '4', '2', N'2nd Task', '2010-12-25')
  60. INSERT INTO dbo.Task (ID, IDParent, IDRoot, [Name], StartDate) VALUES ('12', '4', '2', N'3rd Task', '2010-12-31')
  61.  
  62. -- QUERY A --
  63. SELECT COUNT(r.id) AS RowIndex, t.id, t.idRoot, t.Name
  64. FROM Task t
  65.   INNER JOIN Job j ON j.id = t.IdParent
  66.   INNER JOIN (
  67.     SELECT b.id, b.idParent, b.idRoot, b.StartDate
  68.     FROM Task b
  69.       INNER JOIN Job c ON b.idParent = c.id
  70.   ) r ON t.StartDate >= r.StartDate AND r.idParent = j.id AND t.idRoot = r.idRoot
  71. WHERE t.idRoot IN ('1', '2')
  72.   AND j.[TYPE] LIKE '%Type A%'
  73. GROUP BY t.idRoot, t.id, t.Name
  74. HAVING COUNT(r.id) = 1
  75.  
  76. -- Query B --
  77. SELECT COUNT(r.id) AS RowIndex, t.id, t.idRoot, t.Name
  78. FROM Task t
  79.   INNER JOIN Job j ON j.id = t.IdParent
  80.   INNER JOIN (
  81.     SELECT b.id, b.idParent, b.idRoot, b.StartDate
  82.     FROM Task b
  83.       INNER JOIN Job c ON b.idParent = c.id
  84.   ) r ON t.StartDate >= r.StartDate AND r.idParent = j.id AND t.idRoot = r.idRoot
  85. WHERE t.idRoot IN ('1', '2')
  86.   AND j.[TYPE] LIKE '%Type B%'
  87. GROUP BY t.idRoot, t.id, t.Name
  88. HAVING COUNT(r.id) = 1
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top