SQL Query to display the order of work orders

Question

Sorry for the poor topic first.

EDIT: The query here duplicates OrderNumbers. I need a query not to duplicate OrderNumbers. EDIT: Shortened the question and provided a cleaner question.

I have a table in which there is a record of all completed work orders. There are two types of orders. Installs and causes problems. My query is to find all the problem calls that occurred within 30 days after installation and match this call (TC) with the corresponding installation (IN). Thus, the date of detection of the malfunction should occur after installation, but no more than 30 days. In addition, if there are two installations and two problems with calls for one account within 30 days, and they occur so that the results reflect this. The problem I am facing is that I get a setup order corresponding to two different calls (TC) and Trouble Call (TC), which correspond to two different calls (IN)

In the SQL Fiddle example, pay close attention to the installation order number 1234567810 and fault order number 1234567890, and you will see the question that I have. http://sqlfiddle.com/#!3/811df/8

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 

An example of what I'm looking for results to look. Thank you for any help you can provide by setting me on the right path.

Answer 1

You were very close. I realized that you really need the MIN() TC date, which is more than each setup date for this account number if they are 30 days or less away.

So you need to group by installation dates from your result set, except for WorkOrderNumber . Sort of:

 SELECT a.AccountNumber, MIN(a.scheduleddate) TCDate, b.scheduleddate INDate FROM ( SELECT WorkOrderNumber, ScheduledDate, JobType, AccountNumber FROM workorders WHERE JobType = 'TC' ) a INNER JOIN ( SELECT WorkOrderNumber, ScheduledDate, JobType, AccountNumber FROM workorders WHERE JobType = 'IN' ) b ON a.AccountNumber = b.AccountNumber WHERE b.ScheduledDate < a.ScheduledDate AND DATEDIFF(DAY, b.ScheduledDate, a.ScheduledDate) <= 30 GROUP BY a.AccountNumber, b.AccountNumber, b.ScheduledDate 

This applies to dates and AccountNumber s, but you still need WorkOrderNumber s, so I went into the workorders table workorders , once for each type.

NOTE. I assume that each work order has a unique date for each account number. So, if you have work order 1 ("TC") for account 1 done on "1/1/2015", and you also have work order 2 ("TC") for account 1 done on " 1/1/2015 ", then I can’t guarantee that you will have the correct WorkOrderNumber in your result set.

My last request looked like this:

 SELECT aggdata.AccountNumber, inst.workordernumber OriginalWorkOrderNumber, inst.JobType OriginalJobType, inst.ScheduledDate OriginalScheduledDate, tc.WorkOrderNumber NewWorkOrderNumber, tc.JobType NewJobType, tc.ScheduledDate NewScheduledDate FROM ( SELECT a.AccountNumber, MIN(a.scheduleddate) TCDate, b.scheduleddate INDate FROM ( SELECT WorkOrderNumber, ScheduledDate, JobType, AccountNumber FROM workorders WHERE JobType = 'TC' ) a INNER JOIN ( SELECT WorkOrderNumber, ScheduledDate, JobType, AccountNumber FROM workorders WHERE JobType = 'IN' ) b ON a.AccountNumber = b.AccountNumber WHERE b.ScheduledDate < a.ScheduledDate AND DATEDIFF(DAY, b.ScheduledDate, a.ScheduledDate) <= 30 GROUP BY a.AccountNumber, b.AccountNumber, b.ScheduledDate ) aggdata LEFT OUTER JOIN workorders tc ON aggdata.TCDate = tc.ScheduledDate AND aggdata.AccountNumber = tc.AccountNumber AND tc.JobType = 'TC' LEFT OUTER JOIN workorders inst ON aggdata.INDate = inst.ScheduledDate AND aggdata.AccountNumber = inst.AccountNumber AND inst.JobType = 'IN' 

Answer 2

 select in1.accountnumber, in1.scheduleddate as OriginalDate, in1.workordernumber as OriginalOrder, 'IN' as OriginalType, tc.scheduleddate as NewDate, tc.workordernumber as NewOrder, 'TC' as NewType from workorders in1 out apply (Select min(in2.scheduleddate) from workorders in2 Where in2.jobtype = 'IN' and in1.accountnumber=in2.accountnumber and in2.scheduleddate>in1.scheduleddate) ins join workorders tc on tc.jobtype = 'TC' and tc.accountnumber=in1.accountnumber and tc.scheduleddate>in1.scheduleddate and tc.scheduleddate<ins.scheduleddate and DATEDIFF(day,in1.scheduleddate,tc.scheduleddate) < 31 Where in1.jobtype = 'IN'