TSQL query to combine two records and take the startdate from first record and enddate from second record

Transact-SQL
,
1

Hello all,

I have one requirement from my manager, In my sql database i have the data similar to this sample data

create table #tempvac(

empid int,
startdate datetime,
enddate datetime,
typeid int
)

insert into #tempvac(empid,startdate,enddate,typeid) values
(1,'3/17/2015','3/23/2015',2),
(2,'4/11/2015','4/20/2015',2),
(3,'6/12/2015','6/18/2015',2),
(1,'8/24/2015','8/31/2015',2),
(1,'9/1/2015','9/10/2015',2),
(1,'10/14/2015','10/23/2015',2)

select * from #tempvac

From The above table when i pass empid=1 then i need to get the data in this way

empid startdate enddate typeid
1 2015-03-17 00:00:00.000 2015-03-23 00:00:00.000 2
1 2015-08-24 00:00:00.000 2015-09-10 00:00:00.000 2
1 2015-10-14 00:00:00.000 2015-10-23 00:00:00.000 2

when there is any two records which has enddate is month end date and then strat date of the immediate next record is next month as we have above

Start date EndDate

2015-08-24 00:00:00.000 2015-08-31 00:00:00.000

2015-09-01 00:00:00.000 2015-09-10 00:00:00.000

i need this two records in one as below

Start date EndDate

2015-08-24 00:00:00.000 2015-09-10 00:00:00.000

This i can acheive using my c# programming language but my manager want this to be done in SQL query itself. I tried a lot but since my TSQL is weak i cannot able to get the desired result.

Can anyone help me to acheive this.

Thanks

2 
; with cte as
(
    select    *, rn = ROW_NUMBER() over (partition by empid order by startdate)
    from    #tempvac
),
rcte as
(
    select    *, grp = 1
    from    cte c
    where    c.rn    = 1

    union all

    select    c.empid, c.startdate, c.enddate, c.typeid, c.rn, 
        grp    = case    when c.startdate = dateadd(day, 1, r.enddate) 
                then r.grp
                else r.grp + 1
                end
    from    cte c
        inner join rcte r    on    c.empid    = r.empid
                    and    c.rn    = r.rn + 1
)
select    empid, startdate = min(startdate), enddate = max(enddate), typeid
from    rcte
group by empid, typeid, grp
1 Like
3

Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI-ISO standards in your data.

You should follow ISO-11179 rules for naming data elements. You should follow ISO-8601 rules for displaying temporal data (https:--xkcd.com-1179-). Being in IT and not knowing this is like trying to be an engineer that never heard of the Metric system.

There no such crap as a “type_id” in RDBMS! It has to be “_id” (emp_id) or a “_type” (blood_type)and this absurd hybrid. Also, since we do no math on nominal scales, why make it numeric?

We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI/ISO Standard SQL. Here is my attempt at a basic repair to your posting:

CREATE TABLE Job_Vacancies
(emp_id CHAR(4) NOT NULL,
employment_start_date DATE NOT NULL,
employment_end_date DATE NOT NULL,
termination_type CHAR(..) NOT NULL
CHECK (termination_type IN (…)),
PRIMARY KEY (emp_id, employment_start_date)
);

INSERT INTO Job_Vacancies
VALUES
('0001', '2015-03-17', '2015-03-23', '002'),
('0002', '2015-04-11', '2015-04-20', '002'),
('0003', '2015-06-12', '2015-06-18', '002'),
('0001', '2015-08-24', '2015-08-31', '002'),
('0001', '2015-09-01', '2015-09-10', '002'),
('0001', '2015-10-14', '2015-10-23', '002');

Your next fundamental error is confusing rows with records. They are nothing alike. Your mindset is still in C#, not RDBMS!

From The above table when I pass emp_id = '0001' then I need to get the data in this way <<

Pass it to what? Where the query? What have you tried?

emp_id employment_start_date employment_end_date termination_type
1 2015-03-17 2015-03-23 2
1 2015-08-24 2015-09-10 2
1 2015-10-14 2015-10-23 2

when there is any two records [sic] which has employment_end_date is month end date and then start date of the immediate next record [sic] is next month as we have above <<

In a well-designed schema this would not happen. The procedure that added the later data would catch this overlap and update the employment_end_date in the existing row for you. Is this possible in your shop?

Start date employment_end_date
2015-08-24 2015-08-31
2015-09-01 2015-09-10

I need these two records [sic] in one as below Start date employment_end_date

2015-08-24 2015-09-10 <<

What if the employment periods overlap, instead of abut? Error or consolidation? What is the business rule? We can write a proc to build a “consolidated interval”, insert & commit it, then go back and delete the contained intervals. This stinks.

You prevent gaps in a date sequence with DDL. The best way is Alexander Kuznetsov's contiguous dates constraint idiom.

CREATE TABLE Events
(event_id CHAR(10) NOT NULL,
previous_event_end_date DATE NOT NULL
CONSTRAINT Chained_Dates
REFERENCES Events (event_end_date),
event_start_date DATE NOT NULL,
event_end_date DATE UNIQUE, -- null means event in progress
PRIMARY KEY (event_id, event_start_date),
CONSTRAINT Event_Order_Valid
CHECK (event_start_date <= event_end_date),
CONSTRAINT Chained_Dates
CHECK (DATEADD(DAY, 1, previous_event_end_date) = event_start_date)
-- CHECK (previous_event_end_date + INTERVAL '01' DAYS) = event_start_date)
);

-- disable the Chained_Dates constraint
ALTER TABLE Events NOCHECK CONSTRAINT Chained_Dates;

-- insert a starter row
INSERT INTO Events(event_id, previous_event_end_date, event_start_date, event_end_date)
VALUES ('Foo Fest', '2010-01-01', '2010-01-02', '2010-01-05');

-- enable the constraint in the table
ALTER TABLE Events CHECK CONSTRAINT Chained_Dates;

-- this works
INSERT INTO Events(event_id, previous_event_end_date, event_start_date, event_end_date)
VALUES ('Glob Week', '2010-01-05', '2010-01-06', '2010-01-10');

-- this fails
INSERT INTO Events(event_id, previous_event_end_date, event_start_date, event_end_date)
VALUES ('Snoob', '2010-01-09', '2010-01-11', '2010-01-15');

This I can achieve using my C# programming language but my manager want this to be done in SQL query itself. I tried a lot but since my TSQL is weak I cannot able to get the desired result. <<

No shame; my C# is non-existent. In fact, I still say “C- octothorpe” because I set type in my youth :anguished:
Get a copy of Rick Snodgrass' book on temporal SQL as a PDF download from the University of Arizona for the techniques.

1 Like