I have the following problem presented to me here at work:

I have a column of type date, and I am wanting to count the number of
unique and individual dates in a month that a given primary key has in the
database.

For example: provider key , date.

My goal is to count for each month the number of days (unique days) a
specific provider key has a record. The end result is going into crystal
analysis where the user can drill-through and produce month by month
reports.

So.. is it possible to take a date field and count the number of unique
total dates in it per month as a reference to a given primary key?

Jarrod

Hi Jarrod,

I would like to confirm with your question. For example, we have the
following data
providerkey date
1 2004/7/7
1 2004/7/6
1 2004/7/6
1 2004/6/13
2 2003/7/8
3 2003/12/1

so for providerkey 1, for 2004/7, the number of unique dates is 3. If I
misunderstood you, please describe the problem in details.

If so, I think you can create another column in fact table to indicate
month dimension. We can create providerkey dimension, month dimension, and
a measure DISTINCT COUNT (date). Then we can group by providerkey and
month, and retrieve distinct date number.

If let me know if that answers you questions. Thanks.


Bill Cheng
Microsoft Online Partner Support
Get Secure! – www.microsoft.com/security
This posting is provided “as is” with no warranties and confers no rights.
--------------------

billchng (AT) online (DOT) microsoft.com ("Bill Cheng") wrote in
newsr6aTJJZEHA.228 (AT) cpmsftngxa06 (DOT) phx.gbl:

Thank you.. that does indeed answer my question. That ends up being
what I thought the answer was and we proceeded down that path. I
appreciate the help!

Jarrod

billchng (AT) online (DOT) microsoft.com ("Bill Cheng") wrote in
newsr6aTJJZEHA.228 (AT) cpmsftngxa06 (DOT) phx.gbl:

<cut>

That does indeed sound like the problem and the solution we looked at and
ended up implementing. Thank you very much for the help!

Jarrod