Headcount Query

Question

Headcount Query

Alath

SSC Veteran

Points: 290
More actions
February 12, 2009 at 11:47 am

#71860

Dear All,
Can anyone please help me to build a query to display the headcounts?
I have two tables here. Details as follows.
tbl_user_list with columns user_id,name,join_dt,status
tbl_leaver_list with columns leaver_id,user_id,leave_dt,status
In user_list, leavers and joiners holds status '0' and '1' respectively.
Regards,
Vinod

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Create a table of months, join it to the two tables, where join date is less than the end of the month, and leave date is either null (hasn't left yet) or is later than the beginning of the month. That should get you everyone who was a member at any time during that month.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Alath SSC Veteran Points: 290 More actions · Answer 2

Alath

SSC Veteran

Points: 290

February 12, 2009 at 1:34 pm

#943163

Can you please post the query?

Anvie Ten Centuries Points: 1246 More actions · Answer 3

I have questions:

Headcount of what?

is user_id column is unique?

John Rowan SSC Guru Points: 56440 More actions · Answer 4

Vinod,

Why don't you make an attempt to write the query using the guidence that GSquared has given you?

If you still need help, post your work and we can help further.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 5

Please see the article at the link in my signature below. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GSquared SSC Guru Points: 260824 More actions · Answer 6

I think this will get you what you need. I can't really test it fully, not having access to your system, but it should be okay.

if object_id(N'tempdb..#Numbers') is not null

drop table #Numbers;

if object_id(N'tempdb..#Months') is not null

drop table #Months;

create table #Numbers (

Number int identity primary key,

PlaceHolder bit);

insert into #Numbers (PlaceHolder)

select top 120 null

from dbo.syscolumns;

create table #Months (

FirstDate datetime primary key,

constraint CK_FirstDate check(datepart(day, firstdate) = 1),

constraint CK_DateOnly check(firstdate = dateadd(day, datediff(day, 0, firstdate), 0)),

LastDate as dateadd(month, firstdate));

insert into #Months (FirstDate)

select dateadd(month, number, '1/1/2000')

from #Numbers;

select datepart(year, firstdate) as Year, datepart(month, firstdate) as Month,

count(*) as HeadCount

from tbl_user_list users

left outer join tbl_leaver_list leavers

on users.user_id = leavers.user_id

inner join #Months

on join_dt < lastdate

and

(leave_dt >= firstdate

or

leave_dt is null)

group by datepart(year, firstdate), datepart(month, firstdate)

order by datepart(year, firstdate), datepart(month, firstdate);

Of course, you may need to adjust the initial date, and you may need to adjust the number of months (the "top X" in the Numbers temp table).

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon