Pivot Tables

-- =============================================         
-- Author:  <KHALID,,>         
-- Create date: <20 July,2020 ,>         
-- Description: <Jakaria bhai asked for District wise and DesignationWise EmployeeCount>         
-- exec SP_GetDistrictWiseEmployeeDesig_Pivot       
-- =============================================         
ALTER PROC [dbo].[SP_GetDistrictWiseEmployeeDesig_Pivot]         
AS         
BEGIN           

DECLARE --Put initial Selected data       
 @InitialData Table(       
  RowNum int IDENTITY(1,1) NOT NULL ,   
  EmployeeId bigint,     
  DistrictCode   nvarchar(50) ,
  DistrictName nvarchar(50),
  designationName nvarchar(50)
         
 )


Insert into @InitialData SELECT
d.EmployeeId ,
d.DistrictCode ,
d.DistrictName ,
d.designationName

 From
 (
SELECT -- dbo.FN_GET_EmployeeDistrict(e.DistrictId, 1) AS [Old District Name],                         
--dbo.FN_GET_EmployeeDistrict(e.DistrictId, 2) AS [Old District Code],
e.EmployeeId,
isNull(dis.District_Code, dbo.FN_GET_EmployeeDistrict(e.DistrictId, 2)) AS DistrictCode
, isNUll( dis.district_name_eng, dbo.FN_GET_EmployeeDistrict(e.DistrictId, 1)) AS DistrictName
 , ed.designationName
FROM      Employee AS e /* SELECT * FROM Office*/ LEFT JOIN             
                         EmployeeDesignation AS ed ON ed.DesignationId = e.DesignationId LEFT JOIN                         
                         OfficeDesignation od ON od.OfficeDesignationId = e.OfficeDesignationId 
Left Join                         
                         EmployeeAddress AS ea ON ea.EmployeeId = e.EmployeeId AND ea.AddressType = 'Pe' AND ea.IsActive = 1 LEFT JOIN                         
                         LgThana AS th ON th.thana_id = ea.ThanaId LEFT JOIN                         
                         LgUnion AS un ON un.union_id = ea.UnionId LEFT JOIN                         
                         District AS dis ON dis.district_id = ea.DistrictId AND dis.district_id IS NOT NULL LEFT JOIN                         
                         StateOrProvince AS sp ON sp.StateOrProvinceId = ea.StateOrProvinceId LEFT JOIN                         
                         ThanaOldByEmloyee AS toe ON e.EmployeeId = toe.EmployeeId

WHERE e.EmployeeStatus = 'A' AND ed.DesignationId <= 15 


) AS d

--SELECT * FROM @InitialData

 SELECT * FROM       
(     
    SELECT       
EmployeeId ,
DistrictCode ,
DistrictName ,
designationName
     
    FROM     
        @InitialData     
     
) t     
PIVOT(     
   COUNT(EmployeeId)     
    FOR DesignationName IN (     
        [TO],     
        [SPO],     
        [SO],     
        [PO],     
        [MD],     
[GM],     
        [DMD],     
[DGM],     
[D.P.O],     
[AGM],     
[A/C SPECIALIST],     
[NULL]     
         
  )     
) AS pivot_table;

--Order By DistrictCode Asc 

-- SELECT * FROM District
     
     
     
     
     
     
       
END       
-- END Main Loop       
--SP_GetPromotionInfo 838

Age Duration Calculation

ALTER FUNCTION [dbo].[GetDurationYear_Month_Day]         
(         
   @FromDate DATETIME, @ToDate DATETIME         
)         
RETURNS NVARCHAR(300)         
AS         
BEGIN 

DECLARE @date datetime, @tmpdate datetime, @years int, @months int, @days int
Declare @currentdatetime datetime 

--SELECT @date = '15-Jun-1986'


set @date = @FromDate
 
set @currentdatetime = @ToDate


 --SELECT
 -- @date = REPLACE(CONVERT(Date,e.DateOfBirth,103),' ','-'),
 
 -- @currentdatetime =  REPLACE(CONVERT(Date,e.FirstJoiningDate,103),' ','-')                                 

 --  FROM Employee as e WHERE e.EmployeeId = 26380 --12390
 
   
SELECT @tmpdate = @date

SELECT @years = DATEDIFF(yy, @tmpdate, @currentdatetime) - CASE WHEN (MONTH(@date) > MONTH(@currentdatetime)) OR (MONTH(@date) = MONTH(@currentdatetime) AND DAY(@date) > DAY(@currentdatetime)) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(yy, @years, @tmpdate)
SELECT @months = DATEDIFF(m, @tmpdate, @currentdatetime) - CASE WHEN DAY(@date) > DAY(@currentdatetime) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(m, @months, @tmpdate)
SELECT @days = DATEDIFF(d, @tmpdate, @currentdatetime)



 RETURN CAST(@years AS VARCHAR(4)) + '-' + CAST(@months AS VARCHAR(4)) + '-' + CAST(@days AS VARCHAR(4))


END

Get Value from Datatable

in c# if you get Datatable in single row, it is easy to get the values.
You dont need to map the class.



var param = new { @AccCode = entity.SalaryAccCode };
                var empList = employeeSPService.GetDataWithParameter(param, "SP_PR_Get_AccData");

           
                if (empList.Tables[0].Rows.Count > 0)
                {
                    var v = empList.Tables[0].Rows[0]["AccountCode"].ToString();
                    var AccountName = empList.Tables[0].Rows[0]["AccountName"].ToString();
                    entity.AccountName = AccountName;

                }

WIFI not active Keeps Disconnecting

1. Please check you have enabled the wireless feature of the laptop. There must be a key in the keyboard. That enables and disables the wireless devices.

2. Try to reinstall the driver of the device.  right click on MY computer -> Properties -> manage devicess -> select wireless device. Then Uninstall.

3. Then Scan For Hardware Changes.

4.Check Performance:
Right click on wireless device -> properties ->

5.select Power Management Tab -> Uncheck the Allow the computer performance

6. If Your Network is bridged then unbridge it.

7.Right Click on network -> select Properties -> uncheck bridge.