Average Time Query

[marcoman44]

I have a list of customers that are on a program. In that program they recieve items that are dated. How can I figure out the average time a customer is on the program by this date. I am looking for a month time frame.


Help is appreciated.

Thanks,

 

[SkipVought]

marco,

for a single customer, what data would typical row(s) contain from which this would calculate

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[marcoman44]

Each row is consider one unit. I am looking to figure out the average monthly time that a user has a instance in a month over a year.

 

[SkipVought]

Select Count(Distince MyMonth) As "Nbr Of Months', Customer
From MyTable
Group By Customer

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[marcoman44]

SkipVought,

I need to be able to figure out how long (Average number of months) that a customer is using this service. If they have an instance in the month then it counts for the month. I am not sure if the code solves this.


Thanks,

 

[lespaul]

How about:

What are the fields in your table? What does the data "look" like? What do you want the results of your query to "look" like? Something like this:

TableName
Field1 Field2 Field3 Field4
a b c d
e f e a
g b i d


query results
Field2 Count Field4
b 2
f 1

This will help us to help you.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[SkipVought]

You know, you're making me GUESS at what your data looks like. I asked for a typical example. Why are you making me guess what's in your head???

Select DateDif("m", Min(MyDate), Max(MyDate)) / Count(MyDate), Customer

Group By Customer

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[marcoman44]

Ok here in the Data: I have a customer ID and a corresponing Date of Service. The Customer could have multiple Dates withing the same month. I am only looking to see of the total customer base what is the average monthly enrollment per year.

Cusomter ID DOS(Date of Service)
1 04/05/2004
2 04/05/2004
3 05/06/2004
4 ...
5 ...
6
7
8
9
1
3
4
6

 

[SkipVought]

Did you try the approch I suggested?

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[marcoman44]

Number CustomerID
0.102803738317757 AM101048425


Skip,

This is the result (Top). I want the Number just to be an addition of how many months they had service.

Example

Number Customer
3 Customer 1
9 Customer 2

Therefore the average montlhy time a customer is usining the service is 6.

 

[SkipVought]

Well, from the info I have given you, have you tried anything youself?

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[lespaul]

So from that data you expect what results?

Why are you making me guess what's in your head???

Why Skip don't you know that it's much easier on the person posting the question to only sprinkle pieces of information through out a thread a little at a time so that we get frustrated and irritated and stop helping??

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[marcoman44]

In my last post I thought I gave an accurate description of what I am looking for. I am not a programmer or DB guru nor have I ever claimed. I am just looking for some help and this is the first time on this site where I was denied.

 

[SkipVought]

What have YOU tried so far that has produced results, unwanted or otherwise???

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[marcoman44]

What I think might work is to create a month field and update a month name based on Date.

Then create a Distinct Customer ID/Month

Then Count Month field and group by Customer ID.


I will then export to Excel and Total and divide by number of unique customers.

This is probabaly a major work around and that is why I am looking for advise/solution.

 

[SkipVought]

What have YOU TRIED relative to the suggestions that have already been posted?

Are you experimenting, trying stuff, trial & error, or do you want someone else to give you the answer?

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[marcoman44]

I have tried every suggestion given. I do appreciate the help thanks.

 

[SkipVought]

If you were to post back with the specifics regarding the things that you have tried and how they did or didn't work, you might get a better outcome.

But you have been stingy with your responses and have not demonstrated that you have tried anything specific.

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[lespaul]

so you have this data:

Cusomter ID DOS(Date of Service)
1 04/05/2004
2 04/05/2004
3 05/06/2004
4 ...
5 ...
6
7
8
9
1
3
4
6

and you want these results:


Number Customer
3 Customer 1
9 Customer 2

How did you get these results from the data you provided? Is "Number" the number of entries Customer1 has? Is it the number of months between the first Customer1 record and the second Customer1 record? How did you come up wiht 3 and 9 for those customers?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[marcoman44]

I used the DATEPART command to give me a month number. Then selected Customer ID distinctly with month number. Then counted Customer ID to give total months.


Thanks