with GetDates As
(
select DATEADD(day,1,CAST('31-oct-2020' AS DATE)) as TheDate
UNION ALL
select DATEADD(day,1, TheDate) from GetDates
where TheDate < CAST('30-nov-2020' AS DATE)
)
,
data as (
select * from (
select LBrCode,PrdAcctId,Balance4,rank() over (partition by LBrCode,PrdAcctId order by CblDate desc) rnk
from balances where CblDate<=GetDates.TheDate and PrdAcctId like '%CD%'
)h where rnk=1
)
select * from data
anybody please help
when executed the above query i get error such as
Msg 208, Level 16, State 1, Line 34
Invalid object name 'GetDates'.
The error is about the line where you are referring to the GetDates in the second CTE:
WHERE CblDate <= GetDates.TheDate
To refer to a column in GetDates, you have to explicitly specify GetDates in the join condition with balances table, for example,
balances AS b
INNER JOIN GetDates AS g ON
b.CblDate <= g.TheDate
I am not suggesting that that is the correct join. I couldn't tell you what it should be because it is not clear to me what your goal is. I understand that the first CTE, GetDates, is getting you a set of consecutive dates. So it is a virtual table with one column and, in your example, it has 30 rows. But what are you attempting to do in the second CTE named data?
As an aside, to generate a set of sequential dates, the recommended approach is to use a calendar table or a Numbers table. Using a recursive CTE which is what you are doing is very inefficient. Also, it would fail if it exceeds the default number or recursions (100) unless you set the MAXRECURSION.
hi
thanks a lot for your response sir
my second cte contains a balances and i need to fetch balances for the Get Dates cte
I though of this approach , my humble request to you is please suggest me any other approach to get data as i am very new to sql
thanks again