question

Santosh-4171 avatar image
0 Votes"
Santosh-4171 asked MelissaMa-msft answered

Writing stored procedure

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



sql-server-generalsql-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 @Santosh-4171,

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.

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 MelissaMa-msft edited

Hi @Santosh-4171,

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.

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.

Santosh-4171 avatar image
0 Votes"
Santosh-4171 answered MelissaMa-msft edited

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

· 1
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 @Santosh-4171,

Could you please provide some sample data and expected output based on the sample data?

How to judge whether the value (testdate and branch name) does not exist?

Is there another column in reports table?

Actually I could not understand your requirement very well.

Best regards,
Melissa

0 Votes 0 ·
Santosh-4171 avatar image
0 Votes"
Santosh-4171 answered MelissaMa-msft commented

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

· 1
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 @Santosh-4171,

Could you please validate below and provide any update?

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

Please 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 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft edited

Hi @Santosh-4171,

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.

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.

Santosh-4171 avatar image
0 Votes"
Santosh-4171 answered

Thanks Melissa.

With both queries you have provided, all the data matching with branch name and testdate will be inserted into TestResultAvailibiltyMaster table.
However, I want to insert only one value into TestResultAvailibiltyMaster table with the maximum value of testdate.

The output should look like this:

Branchname Testdate ResultAvailability

Work_Branch 210601 21:10:10

ResultAvailability will have current time stamp.
Even though there are many entries, I want to insert with only one value for this value.

Kindly waiting for your response.

Thanks,
Santosh

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.