because of my stupidity. I had PARTITION BY, when there was no need for one. This returms 1 for...

I would expect Rank or Dense_Rank to work, but they don't :unsure:. This is not very ellegant, but does the work:

; WITH [a] AS

I understand this more as reporting request than table design problem. Let's guess 😉 your table remains as is - two columns (Name,Role). I will  now do the work you...

If you add CREATE TABLE statements for both tables and some test data, chances of getting help would go up significantly 🙂

Please provide CREATE TABLE, INSERT INTO statements with sample data, so we have something to work with. It is very tedious to convert sample data from your post into actual...

Did not have time to check the function. Generally speaking, it should work. I trued some functions in CHECK constraints, and they do work. Never tested on huge size tables...

Very nice touch with UNIQUE (SubscriberID, UnsubScribedDate). That does solve part of the problem, but not everything. To recap, this is what happened so far:

-- Fresh start, with all correct...

I agree with The Dixie Flatline. Flat design does make answering the question "What is the subscription status of any customer at any given time". Queries are definitely simpler than...

Proposed flat design does not solve any of probable issues with existing design. I mentioned the assumptions:

We must assume that the system will somehow enforce 'S' being first FLAG for...

@Pietlinden: your query returns this:

ID EffectiveDate TermDate

----------- ------------- ----------

1 NULL ...

Well, we would need to see the statements, at least...

It is hard to understand what is it your table represents. For each 'Site', first two rows look like daily count of events 'Drop link' and 'Offline'. Bottom two rows...

It seems that a Calendar table would help big time. To learn more, you may want to check this post:

[/url].

🙂

; WITH MyData AS

(

SELECT

SiteCode,

RowSeq = row_number() --- enumerate StatusTime

OVER (PARTITION BY SiteCode

ORDER BY StatusTime)

, StatusTime, [Status]

FROM [Temp2]

)

SELECT---- Show two consecutive states next to each other ...

BTW, solving 1X = 2X has X = 0 as a solution. There is no contradiction as it's also a solution of the original degenerate equation which is 0=0 that...