Maybe a ForEach table loop and a LEFT OUTER JOIN?

If you split the Date and Time fields into two different columns in SQL Server, then this query is trivial.

Don't understand why you think you need windowing functions...

Does this do what you want? The inner query, inside the parentheses... query "x", gets the counts for each grouping, and then...

you can use plain old DATEADD if you're just counting days, without respect to holidays and weekends... but until you post actual consumable data, it's hard to tell.

Okay, this is getting pretty old. If you don't want to post some sample consumable data... a CREATE TABLE script and some INSERTs to populate the table(s), then I guess...

So you create a Calendar table to have as many days as you need, and when the new year rolls around, you add more dates. Sure, you have to...

It should work. The main reason for using the Calendar is that you're guaranteed no gaps in the dates. It also gives you a handy table to group on....

Read this article[/url] and after you're read it, fix your post so that it's answerable.

Self-joins with aggregates. YUCK.

How about something really simple... maybe like this?

SELECT PatientID

, SessionID

, SessionTime

, LAG([SessionTime]) OVER (PARTITION BY PatientID ORDER BY SessionTime) AS PrevSession

FROM

(SELECT 1 AS PatientID, 1 AS SessionID,...

Well, you can't turn columns into rows from that... there's nothing to pivot on.

You might want to read this article[/url].

Can you please explain the logic of how to get the result you're looking for? Maybe I'm slow, but that part baffles me. Maybe there's something you understand about the...

Are there tables with the data in it somewhere that you could share? From your sample data, it's not entirely clear which columns belong in which logical group (table).

1234...

Short answer is that it's a cross join of the first 3 columns (all possible combinations) and then outer join back to the original table to pick up the nulls...

Did you try LEAD()?

Either you have something wrong in your data or T2 is irrelevant to the question.

Here's my solution with the sample data included. Note that the second table you provide is...