Insert Table based on a Result

• nirene

  SSC Enthusiast

  Points: 114

  More actions

  December 7, 2016 at 8:04 pm

  #319608

  Hai,

  I want to Insert into table based on a condition.

  There are 3 tables

  ContLeave

  Create Table ContLeave (Startdate smalldatetime , Enddate smalldatetime)

  Insert Into ContLeave

  Select '2016-01-03' ,'2016-01-03'

  Union All

  Select '2016-01-10' ,'2016-01-10'

  Union All

  Select '2016-01-15' ,'2016-01-17'

  Union All

  Select '2016-01-24' ,'2016-01-24'

  Union All

  Select '2016-01-26' ,'2016-01-26'

  (Result received from stephen hendricks)

  This table contains the leave days for given month

  Absentdata

  Create Table Absentdata (Ecode varchar(12),Absentdate smalldatetime,LOP float)

  Insert Into Absentdata

  Select 'E1','2016/01/14',1

  Union All

  Select 'E1','2016/01/18',1

  Union All

  Select 'E1','2016/01/29',1

  Union All

  Select 'E3','2016/01/01',1

  Union All

  Select 'E3','2016/01/25',1

  Union All

  Select 'E3','2016/01/27',1

  Union All

  Select 'E6','2016/01/03',1

  Union All

  Select 'E8','2016/01/14',1

  Union All

  Select 'E9',2016/01/25',1

  This table contains the absentees data employeewise datewise

  Table in which data to be inserted

  C15LL

  Create Table C15LL (Ecode varchar(12),Absentdate smalldatetime)

  If the employee absents himself before and after a leave day, then all holidays has to inserted in C15LL

  Input data sample

  E1,2016/01/15

  E1,2016/01/16

  E1,2016/01/17

  E3,2016/01/26

  Thanks in advance

  Regards

  Nirene

• colin.frame

  Default port

  Points: 1483

  More actions

  December 9, 2016 at 1:33 am

  #1916971

  How about this?

  -- Using Common Table Expression (CTE) to get Ecode date ranges to process

  WITH EcodeDateRanges AS

  (

  SELECT dayBefore.Ecode, cl.Startdate, cl.Enddate FROM dbo.Absentdata dayBefore

  JOIN dbo.ContLeave cl ON dayBefore.Absentdate = DATEADD(DAY,-1,cl.Startdate)

  JOIN dbo.Absentdata dayAfter ON cl.Enddate = DATEADD(DAY,-1,dayAfter.Absentdate)

  WHERE dayBefore.Ecode = dayAfter.Ecode

  ),

  -- Using a recursive CTE to get the individual dates covered by a range

  dateLists AS

  (

  SELECT edr.Ecode, edr.Startdate AS AbsentDate, edr.Enddate

  FROM EcodeDateRanges edr

  UNION ALL

  SELECT dl.Ecode, DATEADD(DAY,1,dl.AbsentDate), dl.Enddate

  FROM dateLists dl

  WHERE DATEADD(DAY,1,dl.AbsentDate) <= dl.Enddate

  )

  --INSERT dbo.C15LL ( Ecode, Absentdate )

  SELECT dl.Ecode, dl.AbsentDate FROM dateLists dl;

  Forum Etiquette: How to post data/code on a forum to get the best helpMake sure that you include code in the appropriate IFCode tags, e.g. <your code here>. You can find the IFCode tags under the INSERT options when you are writing a post.

• nirene

  SSC Enthusiast

  Points: 114

  More actions

  December 9, 2016 at 5:54 am

  #1917031

  Its perfect thanks a ton.