SQL How to Add a new column to the exists SQL Query Programming Logic and Code with Syntax

OLD Query

 Top 100+ Sql Server Interview Questions

 

select lm.name,
[Issued Doc No],[Request Doc No],[Issue Date],[Request Date],[ERP Code],[Item Code],[Item Name],Para_Description AS [Department Name],[UOM],
[Issued Quantity],[Requested Qty],Rate,Final_Amt,Location_name,ur_name from (
select 
 a.ISSUE_NO as [Issued Doc No],
 i.requi_srno as [Request Doc No],
 convert(nvarchar,a.date,103) as [Issue Date],
 convert(nvarchar,i.Requi_Date,103) as [Request Date],
 c.iT_code as [ERP Code], 
 c.iT_NAME as [Item Code], 
 c.ALT_NAME as [Item Name], 
 e.Para_Description as [UOM],
 b.ALT_QTY as [Issued Quantity],
 j.alt_qty as [Requested Qty],
 g.Rate as Rate,isnull(b.ALT_QTY,0)*isnull(g.rate,0) Final_Amt,
case when isnull(b.Location_Code,'') <> '' then (select name from parameter_location where co_code = b.co_Code and urn_no = b.Location_Code) else '' end as Location_name,
convert(nvarchar,DecryptByPassPhrase('key', h.ur_name )) as ur_name,
D.Para_Description
from Material_Issue_Mast a,
     Material_Issue_Det b,
itemmaster c,
Parameter_Department d,
Parameter_Measurement_Unit e,
sub_trans f,user_master h,
item_stock g,
Requisition_Mast i,
Requisition_Detail j
-- Linked_Item_Group k
where a.co_code = b.co_code and a.urn_no = b.urn_no 
  and b.co_code = c.co_code and b.IT_CODE = c.IT_CODE
  and b.from_urn_no=j.urn_no and b.From_Item_Sr_No=j.Requi_No
         and j.URN_No=i.URN_No
and a.co_Code = d.co_Code and a.dept_code = d.URN_No and b.co_Code =e.co_Code and b.ALT_UNIT_ID=e.urn_no
--and c.co_Code = k.co_Code and c.ig_code = k.URN_No 
and a.co_Code = f.co_Code and i.db_code = f.Subtrans_Code   and a.co_Code = h.co_Code and a.ur_code = h.ur_code 
and b.co_Code = g.co_code and b.urn_no = g.type_no and  g.type= 'Material_Issue' and b.it_code = g.item_code and b.ISSUE_SRNO = g.type_srno
and a.co_Code = @Prm_Co_Code and a.date>=@fromdate and a.date <=@todate
and a.MI_Status = 'Approved'
) as t2 
  left outer join itemmaster im on t2.[ERP Code]=im.iT_code 

  left outer join Linked_Item_Group lm on im.IG_CODE=lm.URN_No




We have to add ID Also

So first select the table name and with Dot(.) operator add the Column Name
New Query will be Like That-




select lm.name,
[Issued Doc No],[Request Doc No],[Issue Date],[Request Date],[ERP Code],[Item Code],[Item Name],Para_Description AS [Department Name],[UOM],
[Issued Quantity],[Requested Qty],Rate,Final_Amt,Location_name,ur_name from (
select 
a.URN_No as [Unique ID],
 a.ISSUE_NO as [Issued Doc No],
 i.requi_srno as [Request Doc No],
 convert(nvarchar,a.date,103) as [Issue Date],
 convert(nvarchar,i.Requi_Date,103) as [Request Date],
 c.iT_code as [ERP Code], 
 c.iT_NAME as [Item Code], 
 c.ALT_NAME as [Item Name], 
 e.Para_Description as [UOM],
 b.ALT_QTY as [Issued Quantity],
 j.alt_qty as [Requested Qty],
 g.Rate as Rate,isnull(b.ALT_QTY,0)*isnull(g.rate,0) Final_Amt,
case when isnull(b.Location_Code,'') <> '' then (select name from parameter_location where co_code = b.co_Code and urn_no = b.Location_Code) else '' end as Location_name,
convert(nvarchar,DecryptByPassPhrase('key', h.ur_name )) as ur_name,
D.Para_Description,
i.contracto_name as [Contractor Name] 
from Material_Issue_Mast a,
     Material_Issue_Det b,
itemmaster c,
Parameter_Department d,
Parameter_Measurement_Unit e,
sub_trans f,user_master h,
item_stock g,
Requisition_Mast i,
Requisition_Detail j
-- Linked_Item_Group k
where a.co_code = b.co_code and a.urn_no = b.urn_no 
  and b.co_code = c.co_code and b.IT_CODE = c.IT_CODE
  and b.from_urn_no=j.urn_no and b.From_Item_Sr_No=j.Requi_No
         and j.URN_No=i.URN_No
and a.co_Code = d.co_Code and a.dept_code = d.URN_No and b.co_Code =e.co_Code and b.ALT_UNIT_ID=e.urn_no
--and c.co_Code = k.co_Code and c.ig_code = k.URN_No 
and a.co_Code = f.co_Code and i.db_code = f.Subtrans_Code   and a.co_Code = h.co_Code and a.ur_code = h.ur_code 
and b.co_Code = g.co_code and b.urn_no = g.type_no and  g.type= 'Material_Issue' and b.it_code = g.item_code and b.ISSUE_SRNO = g.type_srno
and a.co_Code = @Prm_Co_Code and a.date>=@fromdate and a.date <=@todate
and a.MI_Status = 'Approved'
) as t2 
  left outer join itemmaster im on t2.[ERP Code]=im.iT_code 

  left outer join Linked_Item_Group lm on im.IG_CODE=lm.URN_No




I have added two columns in  the existing Query. which highlighted by Blue Colour