I did some checking on a small sample of data (included with a solution below), and I discovered that the OBSValue works just fine as a datetime value in the...

Here is a new query with two CTEs: the first finds all the ancestors of the selected client; the second CTE finds all the descendants of the top ancestor in...

First, let me point out that I understand "Link_Client_ID" to mean "the client of the current person" and not the other way around. In other words the first row of...

You really only need one CTE to rank the data, and then in the main query use a combination of aggregate functions and CASE statements to pivot the results into...

I made the following tweaks to your CTE to give what I hope is a better result:

1. Fixed the main problem, which was that the anchor member of the "child"...

sdhanpaul,

The problem with the "group by" approach you propose is that it defines a duplicate row as that shares values in all the columns Student AND IDNo AND Tel3 AND...

I've continued to scratch my head over this one, and I think I have a better solution now. What was troubling me is the fact that there is no good...

Sergiy,

I apologize for overlooking that detail of your script. Your script does identify the names with a duplicate entry by isolating the "known" ID values from the "unknown" ID values;...

After reading through the thread, I see that none of the solutions offered thus far have recognized the necessity of treating the rows with a known IDNo differently than the...

I read the thread from your original post, and so far the emphasis has been on finding alternatives to LEFT and RIGHT, but your original question was just about the...

I think I finally figured out why it is acceptible to use the surrogate key for the many-to-many relationship between dimensions like my dimNames and dimAddresses dimensions. It is because...

That is not unlike how the many-to-many relationship is handled in the OLTP database from which I am pulling the data. In this approach every time a new Type 2...

I'm not sure how a factless fact table would differ from a snowflake dimension in terms of the actual table structure. But even if I pursue this approach, I am...

I have continued to research this question, and I have come to the conclusion that the term "snowflake dimension" does not really describe what my intent is for the "addresses"...

Thanks for the reply, Greg. Based on the use cases for the facts in the database I'm dealing with, I don't think any fact tables will reference more than one...