Need T-SQL query for Below condition

All Replies

• Friday, January 18, 2013 1:34 AM

  Moderator

   

   
  

  0

  Try

  DECLARE @Projects TABLE (
  	[Project ID] INT PRIMARY KEY
  	,[EMP ID] INT
  	,[EMP Name] VARCHAR(20)
  	,[Project Start Date] DATE
  	,[Project End Date] DATE
  	,[EMP Releasing Date] DATE
  	)
  
  INSERT INTO @Projects (
  	[Project ID]
  	,[EMP ID]
  	,[EMP Name]
  	,[Project Start Date]
  	,[Project End Date]
  	,[EMP Releasing Date]
  	)
  VALUES (
  	1
  	,123
  	,'ABC'
  	,'1/1/2012'
  	,'3/23/2012'
  	,'3/22/2012'
  	)
  	,(
  	2
  	,123
  	,'ABC'
  	,'1/16/2012'
  	,'2/20/2012'
  	,'2/18/2012'
  	)
  	,(
  	3
  	,123
  	,'ABC'
  	,'3/24/2012'
  	,'7/31/2012'
  	,'7/31/2012'
  	);
  
  WITH cteOverlapped
  AS (
  	SELECT P1.*
  		,P.[Project ID] AS [OtherId]
  		,CASE 
  			WHEN P.[Emp Releasing Date] > P1.[Emp Releasing Date]
  				THEN P.[EMP Releasing Date]
  			ELSE p1.[EMP Releasing Date]
  			END AS [Max Emp Releasing Date]
  	FROM @Projects P
  	INNER JOIN @Projects P1 ON P.[EMP ID] = P1.[EMP ID]
  		AND P.[Project ID] <> P1.[Project ID]
  		AND P.[Project Start Date] >= P1.[Project Start Date]
  		AND P.[Project End Date] <= P1.[Project End Date]
  	)
  SELECT O.[Project Id]
  	,O.[EMP ID]
  	,O.[EMP Name]
  	,[Project Start Date]
  	,[Project End Date]
  	,O.[Max Emp Releasing Date]
  FROM cteOverlapped O
  
  UNION ALL
  
  SELECT P.[Project Id]
  	,P.[EMP ID]
  	,P.[EMP Name]
  	,P.[Project Start Date]
  	,P.[Project End Date]
  	,P.[Emp Releasing Date]
  FROM @Projects P
  WHERE NOT EXISTS (
  		SELECT 1
  		FROM cteOverlapped O
  		WHERE O.[EMP ID] = P.[EMP ID]
  			AND P.[Project ID] IN (
  				O.[Project Id]
  				,O.[OtherId]
  				)
  		)
  ORDER BY [Project Id]

  
  

  ---

  For every expert, there is an equal and opposite expert. - Becker's Law
  
  
  My blog

  • Reply
  • Quote

   
• Friday, January 18, 2013 6:03 AM

   

   
  

  0

  Chk this,

  DECLARE @Projects TABLE (
  [Project ID] INT PRIMARY KEY
  ,[EMP ID] INT
  ,[EMP Name] VARCHAR(20)
  ,[Project Start Date] DATE
  ,[Project End Date] DATE
  ,[EMP Releasing Date] DATE
  )
  
  INSERT INTO @Projects (
  [Project ID]
  ,[EMP ID]
  ,[EMP Name]
  ,[Project Start Date]
  ,[Project End Date]
  ,[EMP Releasing Date]
  )
  VALUES (
  1
  ,123
  ,'ABC'
  ,'1/1/2012'
  ,'3/23/2012'
  ,'3/22/2012'
  )
  ,(
  2
  ,123
  ,'ABC'
  ,'1/16/2012'
  ,'2/20/2012'
  ,'2/18/2012'
  )
  ,(
  3
  ,123
  ,'ABC'
  ,'3/24/2012'
  ,'7/31/2012'
  ,'7/31/2012'
  );
  
  -----Query
  
  ;With Cte As
  (
  Select * From (
  Select Top 1 Row_Number()Over(Order By [Project ID]) RwId,* From @Projects
  ) A
  Union All
  Select T1.RwId,
  Case When T1.[Project Start Date] between T2.[Project Start Date] and T2.[Project End Date] Then T2.[Project ID] Else T1.[Project ID] End,
  Case When T1.[Project Start Date] between T2.[Project Start Date] and T2.[Project End Date] Then T2.[EMP ID] Else T1.[EMP ID] End,
  Case When T1.[Project Start Date] between T2.[Project Start Date] and T2.[Project End Date] Then T2.[EMP Name] Else T1.[EMP Name] End,
  Case When T1.[Project Start Date] between T2.[Project Start Date] and T2.[Project End Date] Then T2.[Project Start Date] Else T1.[Project Start Date] End,
  Case When T1.[Project Start Date] between T2.[Project Start Date] and T2.[Project End Date] Then T2.[Project End Date] Else T1.[Project End Date] End,
  Case When T1.[Project Start Date] between T2.[Project Start Date] and T2.[Project End Date] Then Case When T2.[EMP Releasing Date] > T1.[EMP Releasing Date] Then T2.[EMP Releasing Date] Else T1.[EMP Releasing Date] End Else T1.[EMP Releasing Date] End
   From
  (Select Row_Number()Over(Order By [Project ID]) RwId,* From @Projects)T1
  Join Cte T2 On T1.RwId = T2.RwId+1
  
  )
  Select Distinct [Project ID] 
  ,[EMP ID]
  ,[EMP Name]
  ,Convert(Varchar(10),[Project Start Date],101)
  ,Convert(Varchar(10),[Project End Date],101) [Project End Date]
  ,Convert(Varchar(10),[EMP Releasing Date],101)  [EMP Releasing Date]
  From Cte T1

  ---

  Please have look on the comment

  • Marked As Answer by Iric WenModerator Monday, January 28, 2013 5:52 AM
  •  
  • Reply
  • Quote

   
• Friday, January 18, 2013 7:36 AM

   

   
  

  0

  The task is a bit  unclear .  Suppose there're 3 projects for a given Emp with release dates  (R)

  1 S-------R-------------------------E

  2                     S--R--E

  3                                  S--R--E

  Project 1 time interval contains both project 2 time interval and project 3 time interval,

  project 2  and 3 time intervals don't intersecrt.

  Which projects go to the result? Only project 3 or both 2 and 3?

  ---

  Serg

  
  

  • Edited by SergNL Friday, January 18, 2013 7:38 AM
  •  
  • Reply
  • Quote