Forum Discussion
Help rebuilding table previously built on another platform
Hey. Thanks in advance. I've recently taken over some projects from another department of ours. As most of this was left in disarray I'm trying to salvage and integrate as much of the underlying data and reports that I can. I am building a Projections Table for reservations team based on our current rates. I'm sharing the query as it was written in Clicdata. Assuming all is the same, can someone tell me the language if this was written in Power BI? Or at least give me the functions similar that i can reference.
Thanks,
SELECT
u.name AS 'Unit',
s.season_name AS 'Season',
g.name AS 'Calendar',
d.rate AS 'Weekly Rate',
t.name AS 'Rate Cal',
r.rate_type_id AS 'RT ID',
r.season_id AS 'S ID',
r.year AS 'Year',
u.is_Active AS 'Active',
u.created_at AS 'Live Date',
CASE
WHEN
u.is_active = 1
AND u.created_at > '2016-12-31'
AND r.rate_type_id = 2
AND r.season_id = 1
THEN
2 * d.rate
WHEN
u.is_active = 1
AND u.created_at > '2016-12-31'
AND r.rate_type_id = 2
AND r.season_id = 2
THEN
3 * d.rate
WHEN
u.is_active = 1
AND u.created_at > '2016-12-31'
AND r.rate_type_id = 2
AND r.season_id = 3
THEN
3 * d.rate
WHEN
u.is_active = 1
AND u.created_at > '2016-12-31'
AND r.rate_type_id = 2
AND r.season_id = 4
THEN
1 * d.rate
WHEN
u.is_active = 1
AND u.created_at > '2016-12-31'
AND r.rate_type_id = 2
AND r.season_id = 5
THEN
8 * d.rate
WHEN
u.is_active = 1
AND u.created_at > '2016-12-31'
AND r.rate_type_id = 2
AND r.season_id = 6
THEN
3 * d.rate
WHEN
u.is_active = 1
AND u.created_at > '2016-12-31'
AND r.rate_type_id = 2
AND r.season_id = 7
THEN
2 * d.rate
WHEN
u.is_active = 1
AND u.created_at > '2016-12-31'
AND r.rate_type_id = 2
AND r.season_id = 8
THEN
1 * d.rate
WHEN
u.is_active = 1
AND u.created_at > '2016-12-31'
AND r.rate_type_id = 2
AND r.season_id = 9
THEN
2 * d.rate
WHEN
u.is_active = 1
AND u.created_at > '2016-12-31'
AND r.rate_type_id = 2
AND r.season_id = 10
THEN
2 * d.rate
WHEN
u.is_active = 1
AND u.created_at > '2016-12-31'
AND r.rate_type_id = 2
AND r.season_id = 11
THEN
3 * d.rate
WHEN
u.is_active = 1
AND u.created_at > '2016-12-31'
AND r.rate_type_id = 2
AND r.season_id = 12
THEN
8 * d.rate
WHEN
u.is_active = 1
AND u.created_at > '2016-12-31'
AND r.rate_type_id = 2
AND r.season_id = 13
THEN
1 * d.rate
WHEN
u.is_active = 1
AND u.created_at > '2016-12-31'
AND r.rate_type_id = 2
AND r.season_id = 14
THEN
3 * d.rate
WHEN
u.is_active = 1
AND u.created_at > '2016-12-31'
AND r.rate_type_id = 2
AND r.season_id = 15
THEN
3 * d.rate
WHEN
u.is_active = 1
AND u.created_at > '2016-12-31'
AND r.rate_type_id = 2
AND r.season_id = 16
THEN
2 * d.rate
WHEN
u.is_active = 1
AND u.created_at > '2016-12-31'
AND r.rate_type_id = 2
AND r.season_id = 17
THEN
2 * d.rate
WHEN
u.is_active = 1
AND u.created_at > '2016-12-31'
AND r.rate_type_id = 2
AND r.season_id = 18
THEN
2 * d.rate
WHEN
u.is_active = 1
AND u.created_at > '2016-12-31'
AND r.rate_type_id = 2
AND r.season_id = 19
THEN
1 * d.rate
ELSE ('NA')
END AS '2017 Forecast',
CASE
WHEN
u.is_active = 1
AND u.created_at < '2017-01-01'
AND r.rate_type_id = 2
AND r.season_id = 1
THEN
2 * d.rate
WHEN
u.is_active = 1
AND u.created_at < '2017-01-01'
AND r.rate_type_id = 2
AND r.season_id = 2
THEN
3 * d.rate
WHEN
u.is_active = 1
AND u.created_at < '2017-01-01'
AND r.rate_type_id = 2
AND r.season_id = 3
THEN
3 * d.rate
WHEN
u.is_active = 1
AND u.created_at < '2017-01-01'
AND r.rate_type_id = 2
AND r.season_id = 4
THEN
1 * d.rate
WHEN
u.is_active = 1
AND u.created_at < '2017-01-01'
AND r.rate_type_id = 2
AND r.season_id = 5
THEN
8 * d.rate
WHEN
u.is_active = 1
AND u.created_at < '2017-01-01'
AND r.rate_type_id = 2
AND r.season_id = 6
THEN
3 * d.rate
WHEN
u.is_active = 1
AND u.created_at < '2017-01-01'
AND r.rate_type_id = 2
AND r.season_id = 7
THEN
2 * d.rate
WHEN
u.is_active = 1
AND u.created_at < '2017-01-01'
AND r.rate_type_id = 2
AND r.season_id = 8
THEN
1 * d.rate
WHEN
u.is_active = 1
AND u.created_at < '2017-01-01'
AND r.rate_type_id = 2
AND r.season_id = 9
THEN
2 * d.rate
WHEN
u.is_active = 1
AND u.created_at < '2017-01-01'
AND r.rate_type_id = 2
AND r.season_id = 10
THEN
2 * d.rate
WHEN
u.is_active = 1
AND u.created_at < '2017-01-01'
AND r.rate_type_id = 2
AND r.season_id = 11
THEN
3 * d.rate
WHEN
u.is_active = 1
AND u.created_at < '2017-01-01'
AND r.rate_type_id = 2
AND r.season_id = 12
THEN
8 * d.rate
WHEN
u.is_active = 1
AND u.created_at < '2017-01-01'
AND r.rate_type_id = 2
AND r.season_id = 13
THEN
1 * d.rate
WHEN
u.is_active = 1
AND u.created_at < '2017-01-01'
AND r.rate_type_id = 2
AND r.season_id = 14
THEN
3 * d.rate
WHEN
u.is_active = 1
AND u.created_at < '2017-01-01'
AND r.rate_type_id = 2
AND r.season_id = 15
THEN
3 * d.rate
WHEN
u.is_active = 1
AND u.created_at < '2017-01-01'
AND r.rate_type_id = 2
AND r.season_id = 16
THEN
2 * d.rate
WHEN
u.is_active = 1
AND u.created_at < '2017-01-01'
AND r.rate_type_id = 2
AND r.season_id = 17
THEN
2 * d.rate
WHEN
u.is_active = 1
AND u.created_at < '2017-01-01'
AND r.rate_type_id = 2
AND r.season_id = 18
THEN
2 * d.rate
WHEN
u.is_active = 1
AND u.created_at < '2017-01-01'
AND r.rate_type_id = 2
AND r.season_id = 19
THEN
1 * d.rate
WHEN
u.is_active = 1 AND u.created_at IS NULL
AND r.rate_type_id = 2
AND r.season_id = 1
THEN
2 * d.rate
WHEN
u.is_active = 1 AND u.created_at IS NULL
AND r.rate_type_id = 2
AND r.season_id = 2
THEN
3 * d.rate
WHEN
u.is_active = 1 AND u.created_at IS NULL
AND r.rate_type_id = 2
AND r.season_id = 3
THEN
3 * d.rate
WHEN
u.is_active = 1 AND u.created_at IS NULL
AND r.rate_type_id = 2
AND r.season_id = 4
THEN
1 * d.rate
WHEN
u.is_active = 1 AND u.created_at IS NULL
AND r.rate_type_id = 2
AND r.season_id = 5
THEN
8 * d.rate
WHEN
u.is_active = 1 AND u.created_at IS NULL
AND r.rate_type_id = 2
AND r.season_id = 6
THEN
3 * d.rate
WHEN
u.is_active = 1 AND u.created_at IS NULL
AND r.rate_type_id = 2
AND r.season_id = 7
THEN
2 * d.rate
WHEN
u.is_active = 1 AND u.created_at IS NULL
AND r.rate_type_id = 2
AND r.season_id = 8
THEN
1 * d.rate
WHEN
u.is_active = 1 AND u.created_at IS NULL
AND r.rate_type_id = 2
AND r.season_id = 9
THEN
2 * d.rate
WHEN
u.is_active = 1 AND u.created_at IS NULL
AND r.rate_type_id = 2
AND r.season_id = 10
THEN
2 * d.rate
WHEN
u.is_active = 1 AND u.created_at IS NULL
AND r.rate_type_id = 2
AND r.season_id = 11
THEN
3 * d.rate
WHEN
u.is_active = 1 AND u.created_at IS NULL
AND r.rate_type_id = 2
AND r.season_id = 12
THEN
8 * d.rate
WHEN
u.is_active = 1 AND u.created_at IS NULL
AND r.rate_type_id = 2
AND r.season_id = 13
THEN
1 * d.rate
WHEN
u.is_active = 1 AND u.created_at IS NULL
AND r.rate_type_id = 2
AND r.season_id = 14
THEN
3 * d.rate
WHEN
u.is_active = 1 AND u.created_at IS NULL
AND r.rate_type_id = 2
AND r.season_id = 15
THEN
3 * d.rate
WHEN
u.is_active = 1 AND u.created_at IS NULL
AND r.rate_type_id = 2
AND r.season_id = 16
THEN
2 * d.rate
WHEN
u.is_active = 1 AND u.created_at IS NULL
AND r.rate_type_id = 2
AND r.season_id = 17
THEN
2 * d.rate
WHEN
u.is_active = 1 AND u.created_at IS NULL
AND r.rate_type_id = 2
AND r.season_id = 18
THEN
2 * d.rate
WHEN
u.is_active = 1 AND u.created_at IS NULL
AND r.rate_type_id = 2
AND r.season_id = 19
THEN
1 * d.rate
WHEN
u.is_active = 1 AND u.created_at IS NULL
AND r.rate_type_id = 2
AND r.season_id = 19
THEN
1 * d.rate
ELSE ('NA')
END AS '2017 Projected'
FROM
units u
INNER JOIN
unit_nodes n ON u.node_id = n.id
INNER JOIN
unit_nodes_closure i ON i.ancestor = n.parent_id
INNER JOIN
channel_units p ON u.id = p.unit_id
INNER JOIN
rates r ON u.id = r.unit_id
INNER JOIN
rate_dow d ON r.id = d.rate_id
INNER JOIN
rate_types t ON r.rate_type_id = t.id
INNER JOIN
calendar_groups g ON g.id = r.calendar_group_id
INNER JOIN
season s ON r.season_id = s.id
WHERE
r.rate_type_id = 2
GROUP BY u.name , s.season_name
Anonymous,
The above is a SQL Statement.
2 Replies
v-chuncz-msftCommunity Support
Anonymous,
The above is a SQL Statement.