SQLTeam.com | Weblogs | Forums

MSBI dveloper wit SQL Learning stage

I need help on sql to get head count of employees for every month and total months of year and years from 1952 to 2017
based on doj column I need employee count
i have 3 columns like EMPID nchar(20) ,DOJ Nvarchar (273),ISActive char(1)
i need one column to create headcount based on doj column
please help me someone

please provide sample data as follows

create table #rakesh(EMPID nchar(20) ,DOJ Nvarchar (273),ISActive char(1))

insert into #rakesh
select '7637763', 'whateverthisis', 'Y' union
select '7637764', 'whateverthisis', 'Y' union
select '7637765', 'whateverthisis', 'Y' 

and what the expected final result should look like.

please post sample data and not a screen capture?

You had better remove that screen shot before you end up in jail.
It looks like real data with Names and DOBs.

Thanks for everyone about reply to my Query
The Actual Scenario is I have some Columns Like Gender,Ename,EmpID,Firm,Dateofjoining,LWD,IsActive and I have dateofjoining data between like 2016 to 2017
But I need every month ending employee count and total count of the year in single query Based on DateOfJoining Column
But I have two Conditions to get HeadCount

  1. Dateof joining between >01-01-2017 to <30-01-2017 get Empcount Who are active and Lastworking day will be 1
  2. Dateof joining between >01-01-2017 to <30-01-2017 get Empcount Who are active and Lastworking day will be EndDate or 0
    I need Total Empcount who are active and inactive based Using ISActive column and LastworkingDay Column Based on Date of Joining column
    Gender,Ename ,EmpID, Firm, Dateofjoining, LWD , IsActive
    Male ramu 1001 abc 10-03-2017 Null 1
    female rajini 1002 xyz 23-03-2016 2017-02-11 0
    male raju 1332 aby 23-01-2017 2017-04-11 0
    female shilpa 1221 kfj 01-04-2016 2017-03-10 0
    male sunny 1340 kky 01-03-2017 Null 1

Note: if IsActive = 1 indicates the employee who is still working else IsActive =0 indicates who left company

select Gender,Firm,'2017-03-31', count(empid) as empcount from vw_Talent
where (doj <= '2017-03-31' and IsActive=1) or (LastworkingDay>'2017-03-31' and IsActive=0)
Group by Gender,Firm

This Query gives me output like Till who have joined end of the March 2017 Empcount

but I need individual every month ending emp count like till 31 april 2016,31may 2016,31 june 2016........up to 31 may 2017 in a single emp count of all months

Probably people are not responding because you are not providing sample data.

please provide sample data as follows

create table #rakesh(EMPID nchar(20) ,DOJ Nvarchar (273),ISActive char(1))
insert into #rakesh
select '7637763', 'whateverthisis', 'Y' union
select '7637764', 'whateverthisis', 'Y' union
select '7637765', 'whateverthisis', 'Y'

Thanks for reply worked it with union

select

Gen,Generation,Firm,EmpStatus,Carlev,Lcn,Buzness,Dept,B4,Exp,'2015-06-30' Period,COUNT(Empid)
from My_View
where (IsAct=1 and DOJ<='2015-06-30') or (IsAct=0 and DOJ<='2015-06-30' and Lwd>='2015-06-30' )

union

Gen,Generation,Firm,EmpStatus,Carlev,Lcn,Buzness,Dept,B4,Exp,'2015-06-30' Period,COUNT(Empid)
from My_View
where (IsAct=1 and DOJ<='2015-06-30') or (IsAct=0 and DOJ<='2015-06-30' and Lwd>='2015-06-30' )....