September 6, 2022 at 3:43 pm
It's not clear what you mean by "complete attr3". I've tried several possibilities and none of them work for all records. This is the closest that I got. It uses a windowed aggregate on an aggregate for the YTD amounts.
WITH totals AS
(
SELECT f.businessdate
, f.businessid
, d1.attr1_name
, d2.attr2_name
, SUM(CASE WHEN d3.attr3_id = 3001 THEN f.amount ELSE 0 END) amt_attr_3_type_3a
, SUM(CASE WHEN d3.attr3_id = 3002 THEN f.amount ELSE 0 END) amt_attr_3_type_3b
, SUM(CASE WHEN d3.attr3_id = 3003 THEN f.amount ELSE 0 END) amt_attr_3_type_3c
, SUM(CASE WHEN d3.attr3_id = 3004 THEN f.amount ELSE 0 END) amt_attr_3_type_3d
, SUM(SUM(CASE WHEN d3.attr3_id IN (3002, 3003) THEN f.amount END)) OVER(PARTITION BY f.businessid ORDER BY f.businessdate ROWS UNBOUNDED PRECEDING) AS ytd
FROM #fact AS f
INNER JOIN #dim1 AS d1
ON f.attr1_id = d1.attr1_id
INNER JOIN #dim2 AS d2
ON f.attr2_id = d2.attr2_id
INNER JOIN #dim3 AS d3
ON f.attr3_id = d3.attr3_id
GROUP BY f.businessdate, f.businessid, d1.attr1_name, d2.attr2_name
)
SELECT *
FROM totals
WHERE totals.businessdate >= '20220501' AND totals.businessdate < '20220601'
ORDER BY totals.businessdate, totals.businessid
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing post 16 (of 15 total)
You must be logged in to reply to this topic. Login to reply