You could pivot this stuff in a Matrix in SSRS and be done already. Is that not an option? Wouldn't be if you had to output this result for...

How many possible combinations are there for "formula"? You could write a CASE statement that determined which formula to use to calculate the answer... surely must be faster than a...

Took me a minute... if you use Windowing functions (requires SQL 2012 or later), then this is easy. The PARTITION basically groups the records together... then you can use...

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...