SQLServer

How to import the Excel file into SQLServer Database 

1)Right click on the your database and select tasks and select import data

Click next 

2)Select data source as  MicrosoftExcel from drop down  and Browse excel file  then click next button

choose windows authentication or authentication as you like  and give the username and password 

and click next button  and click next button  and give destination table name as you like .

go to your database open the table  then it will shows excel file data in your table  


Simple procedure to get the items which are taken and which are not taken 
user_Table   have the user id  and names
items_Table  have the  item id  itemtypes  itemscode(a,b)  noofitem
itemprocess-Table  have the itemprocess id  , Fk_userid , FK_itemid , stutes (T-taken , NT-not taken )

here we have question  like this 

Username  taken  nottaken  totaltaken  taken  nottaken

Create  PROCEDURE   p_items_GetAllitems
AS
BEGIN
select  I.Itemsname as EmployeeName,
sum(case when Status ='T' then P.NumberOfDays  else 0 end ) as Taken,
sum(case when Status ='NT' then P.NumberOfDays  else 0 end ) as Nottaken,
 ( select sum(NumberOfitems) from items_Table where itemID in(select LeaveTypeID from items_Table) ) as Totalitems ,
cast(Sum(case when P.itemcode in('a')  then 1  else 0 end ) as varchar(10)) + ' ('+cast(( select NumberOfitems from items_Table where itemcode in('a') ) as varchar(10)) + ')' as  taken
cast(Sum(case when P.FK_itemcode in('b')  then 1  else 0 end ) as varchar(10)) + ' ('+cast(( select NumberOfitems from 
items_table where itemcode in('b') ) as varchar(10)) + ')' as  nottaken

 from user_Table U 
  left join itemprocess-Table P on P.FK_UserID=U.UserID
left join items_Table T on P.FK_itemid=T.itemID
    group by  I.Itemsname


END


--exec p_items_GetAllitems

 itemname        taken   nottaken  totalitem    taken     nottaken
banana             1               2                    3              1(3)        2(3)