Need some help with sql from the experts. I am trying to simulate the issue with some test data here.
I get the data from a 3rd party software.
In the dataset, for the same Unit, I could have multiple AccNos with many In/Out.
Sometimes the RefDate in OUT could be the same as IN or any other value in IntName. I thought, if I sort the data using RefDate, I will get the data the way I need .
Ex: IN followed with other data and the last record must be OUT (if entered) for a specific Unit, AccNo
I found out that this is not the case all the time. Sometimes I get the OUT record first followed by the IN record when the dates are same.
This is why I entered the data the way I get it in the following script to show the issue.
With this sample data set, I am expecting to get the following out put.
B001 AC123 2020-01-01 00:00:00.000 IN
B001 AC123 2020-01-23 00:00:00.000 QA1
B001 AC123 2020-01-23 00:00:00.000 OUT
B001 AC222 2020-02-13 00:00:00.000 IN
B001 AC222 2020-02-13 00:00:00.000 OUT
B002 AC333 2020-01-01 00:00:00.000 IN
B002 AC333 2020-01-13 00:00:00.000 OUT
B002 AC444 2020-01-14 00:00:00.000 IN
B002 AC444 2020-01-14 00:00:00.000 OUT
B002 AC555 2020-03-27 00:00:00.000 IN
B002 AC555 2020-03-27 00:00:00.000 OUT
B002 AC666 2020-03-28 00:00:00.000 IN
B002 AC666 2020-04-27 00:00:00.000 QA1
B002 AC666 2020-05-01 00:00:00.000 QA2
B002 AC666 2020-05-01 00:00:00.000 QA2-Cor
B002 AC666 2020-05-17 00:00:00.000 QA3
B002 AC666 2020-06-21 00:00:00.000 FA
B002 AC666 2020-07-21 00:00:00.000 AD
B002 AC666 2020-07-21 00:00:00.000 OUT
All the records are nested between IN and OUT for a specific Unit, AccNo. Would like to know whether this is possible with the amount of data that I have included here. If possible to add a sort column that will help too.
CREATE table #temp
([Unit] nvarchar(20), [AccNo] nvarchar(30),[RefDate] datetime, [IntName] nvarchar(50))
insert into #temp VALUES ('B001','AC123','01/01/2020','IN');
insert into #temp VALUES ('B001','AC123','01/23/2020','OUT');
insert into #temp VALUES ('B001','AC123','01/23/2020','QA1');
insert into #temp VALUES ('B001','AC222','02/13/2020','OUT');
insert into #temp VALUES ('B001','AC222','02/13/2020','IN');
insert into #temp VALUES ('B002','AC333','01/01/2020','IN');
insert into #temp VALUES ('B002','AC333','01/13/2020','OUT');
insert into #temp VALUES ('B002','AC444','01/14/2020','IN');
insert into #temp VALUES ('B002','AC444','01/14/2020','OUT');
insert into #temp VALUES ('B002','AC555','03/27/2020','OUT');
insert into #temp VALUES ('B002','AC555','03/27/2020','IN');
insert into #temp VALUES ('B002','AC666','03/28/2020','IN');
insert into #temp VALUES ('B002','AC666','04/27/2020','QA1');
insert into #temp VALUES ('B002','AC666','05/01/2020','QA2');
insert into #temp VALUES ('B002','AC666','05/01/2020','QA2-Cor');
insert into #temp VALUES ('B002','AC666','05/17/2020','QA3');
insert into #temp VALUES ('B002','AC666','06/21/2020','FA');
insert into #temp VALUES ('B002','AC666','07/21/2020','OUT');
insert into #temp VALUES ('B002','AC666','07/21/2020','AD');
select * from #temp
drop table #temp