Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi All,
My SQL query contains two dynamic values columns as well as a while loop condition. The two columns will be updated based on the While loop condition.
I need to create a table in Power Bi based on the query below.
---------------------------------------------------------------------------------------------------------------------
DECLARE @evaldate datetime = DATEADD(DD, -1, DATEADD(QQ, DATEDIFF(QQ, 0, GETDATE()), 0))
DECLARE @SQLString NVARCHAR(Max)
SET @SQLString = '
Select CONVERT(varchar(10), @accStart, 101) + ''-'' + CONVERT(varchar(10), @accEnd, 101) as [Accident_Period],
((YEAR(@evaldate) - YEAR(@accEnd) + 1)*12 + (MONTH(@evaldate) - MONTH(@accEnd))) as [Age]
FROM Dev.FCClaimDailySnapshot
'
DECLARE @accStart datetime, @accEnd datetime
WHILE YEAR(@evaldate) >= YEAR(GETDATE())-20
BEGIN
SET @accEnd = @evaldate
SET @accStart = DATEADD(DD, +1, EOMONTH(@accEnd, -12))
WHILE YEAR(@accEnd) >= YEAR(GETDATE()) -20
------------------
BEGIN
--SELECT @evaldate as [Eval], @accStart as [Acc_Start], @accEnd as [Acc_End] --for Testing dateloop
EXEC sp_executesql @SQLString,
N'@evaldate datetime, @accStart datetime, @accEnd datetime',
@evaldate = @evaldate, @accStart = @accStart, @accEnd = @accEnd
SET @accEnd = EOMONTH(@accEnd, -12)
SET @accStart = DATEADD(DD, +1, EOMONTH(@accEnd, -12))
END
-------------------------------------------
SET @evaldate = EOMONTH(@evaldate, -12)
END
@amitchandakThank you for responding. thank you very much..........!😊
You can see an example of the query's output for five years' of data here. I need to display 20 years' of data starting from this date WHILE YEAR(@accEnd) >= YEAR(GETDATE()) -20.
@Anonymous , You can use expression functions like sumx, minx, etc
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
108 | |
108 | |
107 | |
89 | |
61 |
User | Count |
---|---|
171 | |
139 | |
133 | |
103 | |
86 |