coder coder - 7 months ago 10
SQL Question

Add Values to dropdownlist on the basis of SQL Query

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

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

Answer

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