Help with writing sql script

Don75 81 Reputation points
2021-03-22T21:29:21.57+00:00

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
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,536 questions
0 comments No comments
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-03-23T01:47:52.707+00:00

    Hi @Don75

    Welcome to Microsoft Q&A!

    Please also refer below:

    select *   
    from #temp  
    order by Unit, AccNo, case when IntName='IN' then 1 when IntName='OUT' then 3 else 2 end   
    

    Best regards
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Viorel 110.7K Reputation points
    2021-03-22T22:24:15.347+00:00

    For your particular case, try this query:

    select * 
    from #temp
    order by Unit, AccNo, charindex(left(IntName, 2), 'IN QA FA AD OU'), IntName
    
    0 comments No comments

  2. Don75 81 Reputation points
    2021-03-23T14:13:13.02+00:00

    Thanks for both replies. Much appreciated. Both works for me. If you were to rank the 2 solution, which one would get rank 1?
    Pros and Cons?