question

Don75-4114 avatar image
0 Votes"
Don75-4114 asked Viorel-1 commented

Help with writing sql script

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
sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @Don75-4114

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 edited

For your particular case, try this query:

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Don75-4114 avatar image
0 Votes"
Don75-4114 answered Viorel-1 commented

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?

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Don75-4114

Thanks for your update.

It is recommended for you to add more sample data into #temp table, execute both queries and find out the better one according to your requirement.

Viorel's answer listes five values of IntName and sort them one by one.

My answer could always guarantee that 'IN' is in the first record and 'OUT' is in the last record even if more values will added in the furture.

Please also remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards
Melissa


0 Votes 0 ·

Is it important to always show FA before AD?

0 Votes 0 ·