Duplicates After Self Join

Transact-SQL
,
1

I have a table that has a record of all of the work orders that have been performed. there are two types of orders. Installs and Trouble Calls. My query is to find all of the trouble calls that have taken place within 30 days of an install and match that trouble call (TC) to the proper Install (IN). So the Trouble Call date has to happen after the install but no more than 30 days after. Additionally if there are two installs and two trouble calls for the same account all within 30 days and they happen in order the results have to reflect that. The problem I am having is I am getting an Install order matching to two different Trouble Calls (TC) and a Trouble Call(TC) that is matching to two different Installs(IN)

In the example on SQL Fiddle pay close attention to the install order number 1234567810 and the Trouble Call order number 1234567890 and you will see the issue I am having.

SQL Fiddle of the example

For those that don't want to review what is on the SQL Fiddle here is the example

create table workorders
(

WorkOrderNumber varchar(10) not null,
AccountNumber varchar(10) not null,
JobType varchar(2) not null,
ScheduledDate date not null

)

insert into workorders values ('1234567890','666','TC','12/12/2015')
insert into workorders values ('1234567820','666','IN','12/11/2015')
insert into workorders values ('1234567810','666','IN','12/01/2015')
insert into workorders values ('1234567830','666','TC','12/05/2015')
insert into workorders values ('1234567822','777','IN','11/11/2015')
insert into workorders values ('1234567444','777','TC','10/20/2015')
insert into workorders values ('1234567333','777','TC','08/15/2015')
insert into workorders values ('1234567777','555','TC','04/21/2015')
insert into workorders values ('1234568888','555','IN','06/12/2015')
insert into workorders values ('1234566666','555','IN','06/14/2015')
insert into workorders values ('1234544444','555','IN','07/15/2015')
insert into workorders values ('1234999999','555','TC','06/13/2015')
insert into workorders values ('1234562222','555','TC','12/12/2015')

select b.accountnumber,MAX(b.scheduleddate) as OriginalDate,b.workordernumber as OriginalOrder,b.jobtype as OriginalType,MIN(a.scheduleddate) as NewDate,a.workordernumber as NewOrder,a.jobtype as NewType from (
select workordernumber,accountnumber,jobtype,scheduleddate from workorders where jobtype = 'TC'

) a join
(select workordernumber,accountnumber,jobtype,scheduleddate from workorders where jobtype = 'IN'
) b
on a.accountnumber = b.accountnumber
group by b.accountnumber,b.scheduleddate,b.workordernumber,b.jobtype,a.accountnumber,a.scheduleddate,a.workordernumber,a.jobtype
having MIN(a.scheduleddate) > MAX(b.scheduleddate) and DATEDIFF(day,MAX(b.scheduleddate),MIN(a.scheduleddate)) < 31

2

What is the output you want to get?

BTW, it is helpful if the sample WorkOrderNumbers are single digit or two digit numbers. Harder for the brain to distinguish between two 10 digit numbers that differ only in one or two digits.

3 
SELECT w_tc.*, w_in2.*
FROM dbo.workorders w_tc
CROSS APPLY (
    SELECT TOP (1) w_in.*
    FROM dbo.workorders w_in
    WHERE
        w_in.AccountNumber = w_tc.AccountNumber AND
        w_in.JobType = 'IN' AND
        w_in.ScheduledDate >= DATEADD(DAY, -31, w_tc.ScheduledDate) AND
        w_in.ScheduledDate < w_tc.ScheduledDate
    ORDER BY w_in.ScheduledDate DESC
) AS w_in2
WHERE w_tc.JobType = 'TC'
ORDER BY w_tc.AccountNumber, w_tc.ScheduledDate
4

I have a table that has a record of all of the work orders that have been performed. there are two types of orders. Installs and Trouble Calls. My query is to find all of the trouble calls that have occurs within 30 days of an install and match that trouble call (TC) to the proper Install (IN). :grinning:

Do not say “taken place” {vague spatial or temporal}, but “occurs” {temporal only} in your narrative. Do you know why you used “A” and “B” for aliases? Let me tell you: Physical tape and disk drives!

So the Trouble Call date has to happen after the install but no more than 30 days after. Additionally if there are two installs and two trouble calls for the same account all within 30 days and they happen in order the results have to reflect that. The problem I am having is I am getting an Install order matching to two different Trouble Calls (TC) and a Trouble Call(TC) that is matching to two different Installs(IN)

For those that don't want to review what is on the SQL Fiddle here is the example <<

This is good Netiquette! Do you like jumping to strange website when you are doing a free consultation job? I hate off-site links. Oh, did you know that ISO-8601 date format is the only format in SQL? So we have to repair your posting. And did you know that a table has to have key? Is this what you meant? Did I guess right? You were better than the average poster.

CREATE TABLE Workorders
(workorder_nbr CHAR(10) NOT NULL PRIMARY KEY,
acct_nbr CHAR(10) NOT NULL,
job_type CHAR(2) NOT NULL
CHECK (job_type IN ('TN', 'IN')), -- no current or prior state
schedule_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL
);

INSERT INTO Workorders
VALUES
('1234567890', '666', 'TC', '2015-12-12'),
('1234567820', '666', 'IN', '2015-12-11'),
('1234567810', '666', 'IN', '2015-12-01'),
('1234567830', '666', 'TC', '2015-12-05'),
('1234567822', '777', 'IN', '2015-11-11'),
('1234567444', '777', 'TC', '2015-10-20'),
('1234567333', '777', 'TC', '2015-08-15'),
('1234567777', '555', 'TC', '2015-04-21'),
('1234568888', '555', 'IN', '2015-06-12'),
('1234566666', '555', 'IN', '2015-06-14'),
('1234544444', '555', 'IN', '2015-07-15'),
('1234999999', '555', 'TC', '2015-06-13'),
('1234562222', '555', 'TC', '2015-12-12');

Now go to this link:

and change your DDL. It will be easy to add the temporal data elements.