Writing stored procedure

Question

Hi All,

I want to write a stored procedure or sql query for the following functionality:

• Select the branch name from a existing table (table name is reports)
• loop through each branch name and get the latest date
• Check if the test date exists for that branch, if not insert into table (new table created. table name: TestResultAvailibiltyMaster)

Please let me know how can I achieve this in Sql.
Kindly waiting for your response.

Thanks,
Santosh

Answer 1

Hi @Santosh ,

Thanks for your update.

It is strongly recommended for you to provide enough sample data to illustrate all angles of the problem and the expected output. Then we could avoid guessing and correcting again and again.

Please refer below updated one:

drop table if exists reports, TestResultAvailibiltyMaster  
      
create table reports  
(branchname varchar(20),  
testdate date)  
          
insert into reports values  
('Work_Branch','2021-05-30'),  
('Work_Branch','2021-06-01'),  
('Test_Branch','2021-05-31'),  
('Test_Branch','2021-06-02')  
      
create table TestResultAvailibiltyMaster  
(branchname varchar(20),  
testdate date,  
ResultAvailability time)  
      
insert into TestResultAvailibiltyMaster values  
('Work_Branch','2021-06-01','21:10:10')  

Method One using not exists:

insert into TestResultAvailibiltyMaster   
select a.*,FORMAT(GETDATE(),'hh:mm:ss') ResultAvailability  
from (select branchname,max(testdate) testdate from reports group by branchname) a  
where not exists (select 1 from TestResultAvailibiltyMaster b where b.branchname=a.branchname and b.testdate=a.testdate)  

Method Two using left join:

insert into TestResultAvailibiltyMaster   
select a.*,FORMAT(GETDATE(),'hh:mm:ss') ResultAvailability  
from (select branchname,max(testdate) testdate from reports group by branchname) a  
left join TestResultAvailibiltyMaster b on b.branchname=a.branchname and b.testdate=a.testdate  
where b.branchname is null  

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.

Answer 2

Hi @Santosh ,

Welcome to Microsoft Q&A!

We recommend that you post CREATE TABLE statements for your tables(reports and TestResultAvailibiltyMaster) together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample.

Please refer below one simple example and check whether it is helpful to you.

create table reports  
(branchname varchar(10),  
testdate date)  
  
insert into reports values  
('A','2021-01-03'),  
('A',NULL),  
('A','2021-05-12'),  
('B','2021-06-01'),  
('B','2021-01-03'),  
('C',NULL)  
  
create table TestResultAvailibiltyMaster  
(branchname varchar(10)  
)  
  
insert into TestResultAvailibiltyMaster  
select branchname    
from reports  
group by branchname  
having max(testdate) is null  
  
select * from TestResultAvailibiltyMaster  

Output:

branchname  
C  

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.

Answer 3

Thank you Melissa for the response.

However, one more requirement for me is "Check if the test date exists for that branch, if not insert into table". Basically, I need to insert the values into table only if the value (testdate and branch name) does not exist.
Kindly waiting for your response.

Thanks,
Santosh

Answer 4

Hi Melissa,

Basically I will be inserting BranchName and TestDate into TestResultAvailibiltyMaster table. I should not insert these values if for the same value for BranchName and TestDate already exists.
For example: If BranchName = "Work_Branch" and TestDate ="210601" already exists, I should not insert these values again in TestResultAvailibiltyMaster table again.

Hope you got what I am looking for.
Kindly waiting for your response.

Thanks,
Santosh

Answer 5

Hi @Santosh ,

Please refer below:

drop table if exists reports, TestResultAvailibiltyMaster  
  
create table reports  
 (branchname varchar(20),  
 testdate date)  
      
 insert into reports values  
 ('Work_Branch','2021-06-01'),  
 ('Test_Branch','2021-05-31')  
  
 create table TestResultAvailibiltyMaster  
 (branchname varchar(20),  
 testdate date)  
  
  insert into TestResultAvailibiltyMaster values  
 ('Work_Branch','2021-06-01')  

Method One using not exists:

insert into TestResultAvailibiltyMaster   
select * from reports a  
where not exists (select 1 from TestResultAvailibiltyMaster b where b.branchname=a.branchname and b.testdate=a.testdate)  

Method Two using left join:

insert into TestResultAvailibiltyMaster   
select a.* from reports a  
left join TestResultAvailibiltyMaster b on b.branchname=a.branchname and b.testdate=a.testdate  
where b.branchname is null  

If above is not working, please provide more sample data and expected output.

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.