In SQL Nested select statments

Hi Maahi

 

Just post the table Structure and requirement. As per your last post I came to know you want to insert some data in table JTSDataExcel when some condition match which you checked 

 

You can do all the thing in sinle insert with Join. You can make some changes in your query also.

 

Remove Distinct in Exists()

use Select Top 1 1 from TableName where condition 

 

Secound in first  condition match you again check secound contion and insert data in else so change the condition exist with not exists and add you insert code inside that block.

 

 

Which I found most critcal is your query  for comment 'Preparer, Reviewer and QA cannot be the same'  is not sync with exists condition. This shuld be given some unconstant response. Might be you face this?

Below is the query:

 

I need to optimize the query i.e,

 

 here i need to check bulk of data on each step which ever record valid that menas match with the condition if will check to next 2nd condition otherwise unmatched data will go in else record

 

WHILE (@i <= @numrows)

BEGIN

If Exists(select distinct id, tjts.*

from Client_Service_Master csm inner join

#tempJTSData tjts on

csm.Client_Code = tjts.Client_Name

where tjts.id= (@i) and

csm.Client_Code = tjts.Client_Name and

csm.Service_Type_Code = tjts.Service_Names and

csm.Status ='Active' )

Begin

If Exists(select distinct id, tjts.*

from #tempJTSData tjts

where tjts.id= (@i) and

tjts.Preparer <> tjts.Reviewer and

tjts.Reviewer <> tjts.QA and

tjts.QA <> tjts.Preparer )

Begin

print 'hi'

End

else 

Begin

INSERT into JTSDataExcel (Client_Name,Service_Names ,File_Names ,Type_Of_Work ,Date_In ,Budgetted_Hrs ,Mode_Of_Receipt,Job_Status , Billed_Hrs ,Date_Out ,Manager ,Team_Leader ,Preparer ,Prep_Hrs ,Reviewer ,Review_Hrs , QA ,QA_Hrs,Comments )

select top 1 tjts.Client_Name,tjts.Service_Names ,tjts.File_Names ,tjts.Type_Of_Work ,tjts.Date_In ,tjts.Budgetted_Hrs ,tjts.Mode_Of_Receipt,tjts.Job_Status ,tjts.Billed_Hrs , tjts.Date_Out,tjts.Manager ,tjts.Team_Leader ,tjts.Preparer ,tjts.Prep_Hrs ,tjts.Reviewer ,tjts.Review_Hrs ,tjts.QA ,tjts.QA_Hrs,'Preparer, Reviewer and QA cannot be the same'

from #tempJTSData tjts

where tjts.id= (@i) and

tjts.Preparer = tjts.Reviewer OR

tjts.Reviewer = tjts.QA OR

tjts.Preparer = tjts.QA

End

SET @i = @i + 1

End

Else --Client & Service Name Does not Exists

Begin

Insert into JTSDataExcel (Client_Name,Service_Names ,File_Names ,Type_Of_Work ,Date_In ,Budgetted_Hrs ,Mode_Of_Receipt,Job_Status , Billed_Hrs ,Date_Out ,Manager ,Team_Leader ,Preparer ,Prep_Hrs ,Reviewer ,Review_Hrs , QA ,QA_Hrs,Comments )

select top 1 tjts.Client_Name,tjts.Service_Names ,tjts.File_Names ,tjts.Type_Of_Work ,tjts.Date_In ,tjts.Budgetted_Hrs ,tjts.Mode_Of_Receipt,tjts.Job_Status ,tjts.Billed_Hrs , tjts.Date_Out,tjts.Manager ,tjts.Team_Leader ,tjts.Preparer ,tjts.Prep_Hrs ,tjts.Reviewer ,tjts.Review_Hrs ,tjts.QA ,tjts.QA_Hrs, 'Client Name & Service Name not exists'

from #tempJTSData tjts inner join

Client_Service_Master csm on

tjts.Client_Name != csm.Client_Code

where tjts.id= (@i)

End

SET @i = @i + 1

END

select * from JTSDataExcel

Hi Maahi,

 

if you want an answer here, then you have to describe your problem clearly with enough details