Reputation: 4735
I have a dropdownlist
whose values are binded by a query which is
select emp_card_no,
emp_name + '-'
+ cast(emp_card_no as varchar)
+ '('
+ datename(MM,a.dt_of_leave)
+ ' - '
+ cast(year(a.dt_of_leave)as varchar)
+')' emp_name
from emp_mst a
where month(a.dt_of_leave) >= month(getdate())-1
and year(a.dt_of_leave) =
case when month(getdate())=1 then year(getdate())-1
else year(getdate())
end
order by emp_name
whose results look something like this.
[![SQL output][1]][1]
Now what I want is, in Emp_name
column, I want to add text as PROCESS
OR PENDING
after (April - 2016)
on the basis of query which is below
select emp_mkey, * from emp_mon_day
where emp_mkey = 312
and month = 4
and year = 2016
If query returns any result then PROCESS
otherwise PENDING
.
NOTE
1st query column Emp_card_no
is the Emp_mkey
in emp_mon_day
table.
Also see the code for binding dropdownlist
protected void funfillEmployee()
{
DataTable DtCombo = new DataTable();
string strdate = System.DateTime.Now.ToString("dd/MM/yyyy");
DtCombo = ObjPriDal.ExecuteDataTable("select emp_card_no, emp_name + '-' + cast(emp_card_no as varchar)+ '(' + datename(MM,a.dt_of_leave) + ' - ' + cast(year(a.dt_of_leave)as varchar)+')' emp_name " +
" from emp_mst a where month(a.dt_of_leave) >= month(getdate())-1 and year(a.dt_of_leave)= case " +
" when month(getdate())=1 then year(getdate())-1 else year(getdate()) end order by emp_name ");
cmbEmp_Name.DataTextField = "emp_name";
cmbEmp_Name.DataValueField = "emp_card_no";
cmbEmp_Name.DataSource = DtCombo;
cmbEmp_Name.DataBind();
cmbEmp_Name.Items.Insert(0, new ListItem("--Select--", "0"));
DtCombo.Clear();
}
Let me know how to do that.
I am using SQL-server-2005
Upvotes: 2
Views: 42
Reputation: 10226
Try something like this :
select a.emp_card_no,
a.emp_name + '-'
+ cast(a.emp_card_no as varchar)
+ '('
+ datename(MM,a.dt_of_leave)
+ ' - '
+ cast(year(a.dt_of_leave)as varchar)
+') '
+
case when m.emp_mkey IS NULL
then 'PENDING'
else 'PROCESS'
end
emp_name
from emp_mst a
LEFT JOIN emp_mon_day m ON m.Emp_mkey = a.Emp_card_no
AND m.month = month(a.dt_of_leave)
AND m.year = year(a.dt_of_leave)
where month(a.dt_of_leave) >= month(getdate())-1
and year(a.dt_of_leave) =
case when month(getdate())=1 then year(getdate())-1
else year(getdate())
end
order by emp_name
but you might have adapt that part
AND m.month = month(a.dt_of_leave) AND m.year = year(a.dt_of_leave)
as month and year are reserved words and I doubt you named your columns like this, but you did not specified other names
Upvotes: 2