Date/Months query

[dbero]

I need to identify how many folks were involved with our service each month. What I have are three elements;
Person ID - 233358
Legal Status Begin date - 1/1/2012 and
Legal Status End Date - 3/30/2014 for example.

What I need to do now is count the folks that were engaged with the State each month. If they entered on the first day, or left on the last day of the month, they would count in that month.

My outcome is a report that illustrates
Jan 2012 - 1,000 involved
Feb 2012 - 988 involved.

is there a query(s) that can create this data?

I have tried countless ways and thus far to no avail. I could do a point in time during the month, but that would miss people that entered before or after that day of the month.

Thank you very much in advance!!

 

[SkipVought]

hi,

Post the SQL that has failed for you.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[dbero]

Unfortunately I am at home today and don't have access to the db. But what I tried doing was the point in time, which isnt great but easily doable. I also tried the between function in a query as well as datediff. I was thinking the only way to do this is to use vba to write out to a table each month they are in care, e.g.,

233358 March 2012
233358 April 2012
The trouble I was having with this was indicating each month included in the range.

 

[SkipVought]

so did you want to just do ONE month, or do you need a range of months?

You need a calendar table to join with your table.

This is the kind of logic required with respect to this graphic example

[pre]
LSB == Legal Status Begin Date
LSE == Legal Status End Date
Month x Month x+1
| |
LSB LSE| |
|---| | |
| |
LSB LSE |
|--------| |
| |
LSB LSE
|---------------------------------|
| |
LSB |
|------------------------------------------
| |
| LSB LSE |
| |-------------| |
| |
| LSB LSE
| |------------------------|
| |
| LSB |
| |---------------------------------
[/pre]

So now you can see that the logic is somthing like
[tt]
if [month x+1] > LSB and ([month x] <= LSE or LSE is null) then count else 0
[/tt]
And your Calendar table is what will define month x, month x+1...

Oh, yes, when the Legal Status End Date is undefined, how is that represented in your table, or maybe each person has a Legal Status End Date defined in every instance?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[dbero]

Thank you Skip. What I need is to illustrate for each month, how many were in care. So, a person id would be counted as a person each month they are in. If they do not have an end date, I would use current date for the end date.

In the strategy above, I would have to rest month value for each month I suspect? Just seems like there should be an easier way to do this than an interitive process for each month?

Thank you very much!

 

[SkipVought]

So do you/your company have a calendar table?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

Wouldn't it be something simple like:
[tt]
Select Count(Person_ID), Month(BeginDate), Year(BeginDate)
From SomeTable
Group By Month(BeginDate), Year(BeginDate)[/tt]

and do the UNION for EndDate?
You would have the same ID counted twice if BeginDate would be 1/30/2014 and EndDate 2/2/2014

You would need to have Month and Year in case you have data like:[pre]
ID BeginDate
12 1/1/2010
15 1/1/2014[/pre]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[dbero]

Thank you Andy. If I'm not mistaken this would work to count each person upon entry. I need the ID counted for each month between the begin and end date range. So each person is counted in multiple months, depending on their duration.

 

[SkipVought]

Calendar table?????

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[dbero]

I apologize Skip. I do not have a calendar table. And must admit have not seen one. What is a calendar table?

 

[SkipVought]

it a table that defines the work days for a work unit, accounting periods, etc.

so lets do it another way.

The MONTHS can be calculated as such:

DateDiff("m",[Legal Status End Date],[Legal Status Start Date]) + 1


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]