Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
@Anjaneyachari , 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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |