MSBI dveloper wit SQL Learning stage

Transact-SQL
, , ,
1

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

2

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.

5

please post sample data and not a screen capture?

6

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

7

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

8

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'
10

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' )....