Got it but still returning incorrect data, lag value for ID2 on 2022-07-04 should be 200

--Microsoft SQL Server 2019 
--Build a temp calendar table
DROP TABLE IF EXISTS...

[/quote]Why can't you use CTEs?[/quote]

I want to compile a view into a third party system that does not support CTEs. If it's not possible to accomplish without CTEs I may...

I can't...

Hello Again,

I have added to my DML setup to more accurately reflect my situation.

Added a second calendar type but that doesn't see to be an issue.

Added an ID to my...

I am still studying the code. I anticipate I am going to have an additional requirement.

I want to report on 2022-07-10. There is no data on 2022-07-10 so I get...

Looks like I can wrap around the code as a subquery and apply the criteria to the outer query

--Microsoft SQL Server 2019 
--Build a temp calendar table
DROP...

I guess the method needs the  previous date to be returned. Whenever I add a criteria for a specific date I don't get the previous data.

SELECT C.CalendarDate
...

• This reply was modified 2 years, 3 months ago by  Chrissy321.

Okay, maybe I'm a bit daft, but couldn't you simply use this:

CREATE OR ALTER VIEW Test
AS
SELECT * 
FROM Calendar C
LEFT JOIN Events E ON C.CalendarDate BETWEEN...

I think the below does it. Any comments about performance best practices for doing this sort of thing would be welcome.

CREATE OR ALTER VIEW Test

AS

SELECT * FROM Calendar C

CROSS APPLY

(

SELECT...

Event 1 ended on 2022-02-01 so it shouldn't be included when querying the view for @StartDate = '2022-07-01' and @EndDate = '2022-07-31'

This query returns no rows

with 
gaps_cte(CalDate, [DayName] ,BusinessDay, Multiplier, Comment, gap) as (
    select *, case when BusinessDay<>lag(BusinessDay) over (order by CalDate) then...

I'm really curious about this request.  What is the intended use of the computed "Multiplier" column?

If the answer is to determine which day is (for example) "3 business days...

I guess this works but its not particularly pleasing to me.

SELECT 
*
FROM
(
SELECT
* 
FROM 
(
SELECT 
*,RANK() OVER (PARTITION BY MyIdentifier ORDER BY LoadDateTime DESC) LoadDateTimeRank
FROM #temptable 
) T1
WHERE
LoadDateTimeRank...

That works with my sample data but if there are more than 2 records prior to the date range I don't think it will work. See below with additional sample...

Very nice, thank you. Putting aside the most recent non-null record issue I started toying with the below. The advantage being that I don't have too have a second expanded...