locked
Cursor for getting employee data RRS feed

  • Question

  • User-1506965535 posted

    I want to create a CURSOR for the below query for the Year 2016

    select 0.00 as LOPDays, 0.00 as 'PlRemaing' ,0.00,
         isnull((select NextLeve_Year from p_leave_allocation a where a.Emp_card_no = lev.Emp_card_no 
         and a.Year = lev.Year -1),0000) as 'NextLeve_Year',
         Emp.emp_Card_no,Emp.Emp_name, convert(varchar(10),Date_Of_Joining,103)Emp_DOJ,
         case Emp.Emp_Type when 'E' then 'Employee' when 'C' then 'Consultan' when 'T' 
         then 'Tem Employee' when 'S' then 'Site Employee' else 'Employee' end Emp_Type,  
         (select Type_Desc from type_mst_a  where Master_MKey = Emp.New_Design_Mkey)'Emp_Desg',Role_Desc'Emp_Role',  
         lev.Month1'pl1',lev.Month2'pl2',lev.Month3'pl3',lev.Month4'pl4',lev.Month5'pl5',lev.Month6'pl6',lev.Month7'pl7',lev.Month8'pl8',lev.Month9'pl9',lev.Month10'pl10',lev.Month11'pl11',lev.Month12'pl12',(lev.Month1+lev.Month2+lev.Month3+lev.Month4+lev.Month5+lev.Month6+lev.Month7+lev.Month8+lev.Month9+lev.Month10+lev.Month11+lev.Month12) 'PlTotal',isnull(lev.PL_Days,0)'PLDays',       
         CO_Days_Opening'openingCo',case when emp.Mkey  not in (select Emp_mkey from p_leave_allocation
         where Year ='2016' and PL_Days is not null ) then 'Y' else 'N' end  EditStatus,
         '2016' 'LYear',emp.Emp_Type from  Emp_mst Emp  left join p_leave_allocation lev on Emp.Emp_card_no = lev.Emp_card_no and 
         lev.Year ='2016' where  emp.status in ('A','S')  and  Year(emp.Date_Of_Joining) <='2016'   
        order by emp.emp_name

    and insert into the table p_leave_allocation_11122015.

    How to achieve that ?

    Friday, December 11, 2015 6:51 AM

Answers

All replies

  • User1867929564 posted

    It is not clear,what you want to achive ?


    What make you think that you should use cursor ? where is the problem ?

    insert into  p_leave_allocation_11122015
    
    select 0.00 as LOPDays, 0.00 as 'PlRemaing' ,0.00,
         isnull((select NextLeve_Year from p_leave_allocation a where a.Emp_card_no = lev.Emp_card_no 
         and a.Year = lev.Year -1),0000) as 'NextLeve_Year',
         Emp.emp_Card_no,Emp.Emp_name, convert(varchar(10),Date_Of_Joining,103)Emp_DOJ,
         case Emp.Emp_Type when 'E' then 'Employee' when 'C' then 'Consultan' when 'T' 
         then 'Tem Employee' when 'S' then 'Site Employee' else 'Employee' end Emp_Type,  
         (select Type_Desc from type_mst_a  where Master_MKey = Emp.New_Design_Mkey)'Emp_Desg',Role_Desc'Emp_Role',  
         lev.Month1'pl1',lev.Month2'pl2',lev.Month3'pl3',lev.Month4'pl4',lev.Month5'pl5',lev.Month6'pl6',lev.Month7'pl7',lev.Month8'pl8',lev.Month9'pl9',lev.Month10'pl10',lev.Month11'pl11',lev.Month12'pl12',(lev.Month1+lev.Month2+lev.Month3+lev.Month4+lev.Month5+lev.Month6+lev.Month7+lev.Month8+lev.Month9+lev.Month10+lev.Month11+lev.Month12) 'PlTotal',isnull(lev.PL_Days,0)'PLDays',       
         CO_Days_Opening'openingCo',case when emp.Mkey  not in (select Emp_mkey from p_leave_allocation
         where Year ='2016' and PL_Days is not null ) then 'Y' else 'N' end  EditStatus,
         '2016' 'LYear',emp.Emp_Type from  Emp_mst Emp  left join p_leave_allocation lev on Emp.Emp_card_no = lev.Emp_card_no and 
         lev.Year ='2016' where  emp.status in ('A','S')  and  Year(emp.Date_Of_Joining) <='2016'   
        order by emp.emp_name

    Friday, December 11, 2015 10:11 AM
  • User-1506965535 posted

    I want to insert the data of the query into the p_leave_allocation_11122015. table

    Friday, December 11, 2015 10:38 AM
  • User1867929564 posted

    i have also done same.Only that i don't know if given query give you correct output or not.

    i just shown you  a example.

    you can correct same query use in same manner.

    Friday, December 11, 2015 10:46 AM
  • User-1506965535 posted

    Hi Kumar,

    My joins is not working properly here

    INSERT INTO p_leave_allocation_11122015(Mkey, Entry_Sr_no, Comp_mkey,FDepartment_Id,FModule_Id,
                                           Year, Emp_mkey, Emp_card_no, U_Datetime, Delete_Flag,Month12,PL_Days,)
    
    SELECT ROW_NUMBER() OVER(ORDER BY p_leave_allocation.MKey) + 1643
        ,ROW_NUMBER() OVER(ORDER BY Entry_Sr_no) + 898, Comp_mkey,FDepartment_Id,FModule_Id,'2016',Emp_mkey,Emp_card_no,U_Datetime,Delete_Flag,Month12,
        0
    FROM p_leave_allocation lev where  Emp_mst Emp left join p_leave_allocation
     and  Emp.status in ('A','S') and Year(Emp.Date_Of_Joining) = '2015' 

    I get error as

     An expression of non-boolean type specified in a context where a condition is expected, near 'Emp'.

    what is wrong here?

    Friday, December 11, 2015 11:42 AM
  • User1867929564 posted

    try this,

    SELECT ROW_NUMBER() OVER(ORDER BY p_leave_allocation.MKey) + 1643
        ,ROW_NUMBER() OVER(ORDER BY Entry_Sr_no) + 898, Comp_mkey,FDepartment_Id,FModule_Id,'2016',Emp_mkey,Emp_card_no,U_Datetime,Delete_Flag,Month12,
        0
    FROM p_leave_allocation lev on  Emp_mst Emp left join p_leave_allocation
     where  Emp.status in ('A','S') and Year(Emp.Date_Of_Joining) = '2015' 

    Friday, December 11, 2015 12:30 PM
  • User-1506965535 posted

    getting error as

    The multi-part identifier "p_leave_allocation.MKey" could not be bound.

    Friday, December 11, 2015 12:42 PM
  • User77042963 posted

    You can go through this tutorial to speed up your learning process.

    http://www.w3schools.com/sql/

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 11, 2015 2:44 PM