Change p to p1 in the first line. If still issue execute inner queries and see if those give results;
if you have edited …I am seeing yes it is returning records now .but again 2 records … I just need 2019-12-01 records having CY value and ly metrics (no need to show 2018-12-01 ) I guess this is coming because of group by
Are you sure. Please post the query and sample document
SELECT OBJECT_CONCAT(IFMISSINGORNULL(p1.arr[0], {}), IFMISSINGORNULL(p1.arr[1], {})).*
FROM (
SELECT p.patternmonth, ARRAY_AGG(p) AS arr
FROM (
(SELECT DATE_TRUNC_STR(M.PatternDate,"month") patternmonth,
ROUND(SUM(M.BlendedEventRevenueTotal)) funcroomrev,
ROUND(SUM(M.FBRevenueTotal),2) fnbrev,
ROUND(100 * SUM(M.FBRevenueTotal) / SUM(M.BlendedEventRevenueTotal),2) fnbrevpercent,
ROUND(SUM(M.AVRevTotal),2) avrev ,
ROUND(100 * SUM(M.AVRevTotal) / SUM(M.BlendedEventRevenueTotal),2) avrevpercent,
ROUND(SUM(M.OtherRevTotal),2) otherrev ,
ROUND(100 * SUM(M.OtherRevTotal) / SUM(M.BlendedEventRevenueTotal),2) otherrevpercent,
ROUND(SUM(M.BlendedEventRevenueTotal) / SUM(M.BlendedRoomnightsTotal),2) funcrevpergrprn,
ROUND(SUM(M.BlendedEventRevenueTotal) / SUM(M.BookedSqft),2) funcrevperbkdsqft,
ROUND(SUM(M.BlendedEventRevenueTotal) / 100,2) funcrevpersqft,
SUM(M.BlendedRoomnightsTotal) grprnleadvolume,
ROUND(SUM(M.BookedSqft) /100,2) occtotalsqft ,
ROUND(SUM(M.BlendedGuestroomRevenueTotal),2) grproomrev,
SUM(M.BlendedRoomnightsTotal) grprn,
ROUND(SUM(M.BlendedGuestroomRevenueTotal) / SUM(M.BlendedRoomnightsTotal),0) grpadr,
ROUND(SUM(CASE WHEN M.BookingStatus = 'Definite' THEN M.BlendedRoomnightsTotal ELSE 0 END) / SUM(M.BlendedRoomnightsTotal),3) conversion
FROM ( SELECT MAX(A.SnapshotDate) AS SnapshotDate,
A.ExternalBookingId AS ExternalBookingId,
A.PatternDate
FROM group360all AS A
WHERE A.PatternDate BETWEEN '2019-12-01' AND '2019-12-31'
AND A.BookingStatus IN ['Definite']
GROUP BY A.PatternDate, A.ExternalBookingId) T
INNER JOIN group360all M ON M.ExternalBookingId = T.ExternalBookingId AND M.SnapshotDate = T.SnapshotDate
AND T.PatternDate = M.PatternDate
GROUP BY DATE_TRUNC_STR(M.PatternDate,"month"))
UNION ALL
(SELECT DATE_TRUNC_STR(M.PatternDate,"month") patternmonth,
ROUND(SUM(M.BlendedEventRevenueTotal)) lyfuncroomrev,
ROUND(SUM(M.FBRevenueTotal),2) lyfnbrev,
ROUND(100 * SUM(M.FBRevenueTotal) / SUM(M.BlendedEventRevenueTotal),2) lyfnbrevpercent,
ROUND(SUM(M.AVRevTotal),2) lyavrev ,
ROUND(100 * SUM(M.AVRevTotal) / SUM(M.BlendedEventRevenueTotal),2) lyavrevpercent,
ROUND(SUM(M.OtherRevTotal),2) lyotherrev ,
ROUND(100 * SUM(M.OtherRevTotal) / SUM(M.BlendedEventRevenueTotal),2) lyotherrevpercent,
ROUND(SUM(M.BlendedEventRevenueTotal) / SUM(M.BlendedRoomnightsTotal),2) lyfuncrevpergrprn,
ROUND(SUM(M.BlendedEventRevenueTotal) / SUM(M.BookedSqft),2) lyfuncrevperbkdsqft,
ROUND(SUM(M.BlendedEventRevenueTotal) / 100,2) lyfuncrevpersqft,
SUM(M.BlendedRoomnightsTotal) lygrprnleadvolume,
ROUND(SUM(M.BookedSqft) /100,2) lyocctotalsqft ,
ROUND(SUM(M.BlendedGuestroomRevenueTotal),2) lygrproomrev,
SUM(M.BlendedRoomnightsTotal) lygrprn,
ROUND(SUM(M.BlendedGuestroomRevenueTotal) / SUM(M.BlendedRoomnightsTotal),0) lygrpadr,
ROUND(SUM(CASE WHEN M.BookingStatus = 'Definite' THEN M.BlendedRoomnightsTotal ELSE 0 END) / SUM(M.BlendedRoomnightsTotal),3) lyconversion
FROM ( SELECT MAX(A.SnapshotDate) AS SnapshotDate,
A.ExternalBookingId AS ExternalBookingId,
A.PatternDate
FROM group360all AS A
WHERE A.PatternDate BETWEEN DATE_ADD_STR('2019-12-01',-365,"day") AND DATE_ADD_STR('2019-12-31',-365,"day")
AND A.BookingStatus IN ['Definite']
GROUP BY A.PatternDate, A.ExternalBookingId) T
INNER JOIN group360all M ON M.ExternalBookingId = T.ExternalBookingId AND M.SnapshotDate = T.SnapshotDate
AND T.PatternDate = M.PatternDate
GROUP BY DATE_TRUNC_STR(M.PatternDate,"month"))
) AS p
GROUP BY p.patternmonth) AS p1
ORDER BY p1.patternmonth
Results:
[
{
“lyavrev”: 250,
“lyavrevpercent”: 0.56,
“lyconversion”: 1,
“lyfnbrev”: 34088.87,
“lyfnbrevpercent”: 75.69,
“lyfuncrevperbkdsqft”: 28.17,
“lyfuncrevpergrprn”: 72.18,
“lyfuncrevpersqft”: 450.39,
“lyfuncroomrev”: 45039,
“lygrpadr”: 142,
“lygrprn”: 624,
“lygrprnleadvolume”: 624,
“lygrproomrev”: 88487.88,
“lyocctotalsqft”: 15.99,
“lyotherrev”: 10700.43,
“lyotherrevpercent”: 23.76,
“patternmonth”: “2018-12-01”
},
{
“avrev”: 250,
“avrevpercent”: 1.99,
“conversion”: 1,
“fnbrev”: 10539,
“fnbrevpercent”: 84.02,
“funcrevperbkdsqft”: 47.51,
“funcrevpergrprn”: 40.46,
“funcrevpersqft”: 125.43,
“funcroomrev”: 12543,
“grpadr”: 149,
“grprn”: 310,
“grprnleadvolume”: 310,
“grproomrev”: 46047,
“occtotalsqft”: 2.64,
“otherrev”: 1753.9,
“otherrevpercent”: 13.98,
“patternmonth”: “2019-12-01”
}
]
SELECT OBJECT_CONCAT(IFMISSINGORNULL(p1.arr[0], {}), IFMISSINGORNULL(p1.arr[1], {})).*
FROM (
SELECT ARRAY_AGG(p) AS arr
FROM (
(SELECT DATE_TRUNC_STR(M.PatternDate,"month") patternmonth,
ROUND(SUM(M.BlendedEventRevenueTotal)) funcroomrev,
ROUND(SUM(M.FBRevenueTotal),2) fnbrev,
ROUND(100 * SUM(M.FBRevenueTotal) / SUM(M.BlendedEventRevenueTotal),2) fnbrevpercent,
ROUND(SUM(M.AVRevTotal),2) avrev ,
ROUND(100 * SUM(M.AVRevTotal) / SUM(M.BlendedEventRevenueTotal),2) avrevpercent,
ROUND(SUM(M.OtherRevTotal),2) otherrev ,
ROUND(100 * SUM(M.OtherRevTotal) / SUM(M.BlendedEventRevenueTotal),2) otherrevpercent,
ROUND(SUM(M.BlendedEventRevenueTotal) / SUM(M.BlendedRoomnightsTotal),2) funcrevpergrprn,
ROUND(SUM(M.BlendedEventRevenueTotal) / SUM(M.BookedSqft),2) funcrevperbkdsqft,
ROUND(SUM(M.BlendedEventRevenueTotal) / 100,2) funcrevpersqft,
SUM(M.BlendedRoomnightsTotal) grprnleadvolume,
ROUND(SUM(M.BookedSqft) /100,2) occtotalsqft ,
ROUND(SUM(M.BlendedGuestroomRevenueTotal),2) grproomrev,
SUM(M.BlendedRoomnightsTotal) grprn,
ROUND(SUM(M.BlendedGuestroomRevenueTotal) / SUM(M.BlendedRoomnightsTotal),0) grpadr,
ROUND(SUM(CASE WHEN M.BookingStatus = 'Definite' THEN M.BlendedRoomnightsTotal ELSE 0 END) / SUM(M.BlendedRoomnightsTotal),3) conversion
FROM ( SELECT MAX(A.SnapshotDate) AS SnapshotDate,
A.ExternalBookingId AS ExternalBookingId,
A.PatternDate
FROM group360all AS A
WHERE A.PatternDate BETWEEN '2019-12-01' AND '2019-12-31'
AND A.BookingStatus IN ['Definite']
GROUP BY A.PatternDate, A.ExternalBookingId) T
INNER JOIN group360all M ON M.ExternalBookingId = T.ExternalBookingId AND M.SnapshotDate = T.SnapshotDate
AND T.PatternDate = M.PatternDate
GROUP BY DATE_TRUNC_STR(M.PatternDate,"month"))
UNION ALL
(SELECT DATE_TRUNC_STR(M.PatternDate,"month") patternmonth,
ROUND(SUM(M.BlendedEventRevenueTotal)) lyfuncroomrev,
ROUND(SUM(M.FBRevenueTotal),2) lyfnbrev,
ROUND(100 * SUM(M.FBRevenueTotal) / SUM(M.BlendedEventRevenueTotal),2) lyfnbrevpercent,
ROUND(SUM(M.AVRevTotal),2) lyavrev ,
ROUND(100 * SUM(M.AVRevTotal) / SUM(M.BlendedEventRevenueTotal),2) lyavrevpercent,
ROUND(SUM(M.OtherRevTotal),2) lyotherrev ,
ROUND(100 * SUM(M.OtherRevTotal) / SUM(M.BlendedEventRevenueTotal),2) lyotherrevpercent,
ROUND(SUM(M.BlendedEventRevenueTotal) / SUM(M.BlendedRoomnightsTotal),2) lyfuncrevpergrprn,
ROUND(SUM(M.BlendedEventRevenueTotal) / SUM(M.BookedSqft),2) lyfuncrevperbkdsqft,
ROUND(SUM(M.BlendedEventRevenueTotal) / 100,2) lyfuncrevpersqft,
SUM(M.BlendedRoomnightsTotal) lygrprnleadvolume,
ROUND(SUM(M.BookedSqft) /100,2) lyocctotalsqft ,
ROUND(SUM(M.BlendedGuestroomRevenueTotal),2) lygrproomrev,
SUM(M.BlendedRoomnightsTotal) lygrprn,
ROUND(SUM(M.BlendedGuestroomRevenueTotal) / SUM(M.BlendedRoomnightsTotal),0) lygrpadr,
ROUND(SUM(CASE WHEN M.BookingStatus = 'Definite' THEN M.BlendedRoomnightsTotal ELSE 0 END) / SUM(M.BlendedRoomnightsTotal),3) lyconversion
FROM ( SELECT MAX(A.SnapshotDate) AS SnapshotDate,
A.ExternalBookingId AS ExternalBookingId,
A.PatternDate
FROM group360all AS A
WHERE A.PatternDate BETWEEN DATE_ADD_STR('2019-12-01',-365,"day") AND DATE_ADD_STR('2019-12-31',-365,"day")
AND A.BookingStatus IN ['Definite']
GROUP BY A.PatternDate, A.ExternalBookingId) T
INNER JOIN group360all M ON M.ExternalBookingId = T.ExternalBookingId AND M.SnapshotDate = T.SnapshotDate
AND T.PatternDate = M.PatternDate
GROUP BY DATE_TRUNC_STR(M.PatternDate,"month"))
) AS p
GROUP BY DATE_PART_STR(p.patternmonth,"month")) AS p1
ORDER BY p1.patternmonth;great … looks good now …only thing I am seeing patternmonth as “2018-12-01” instead of “2019-12-01”
That is little difficult .
Option 1: OBJECT_CONCAT(IFMISSINGORNULL(p1.arr[0], {}), IFMISSINGORNULL(p1.arr[1], {}), {“patternmonth”: CASE WHEN p1.arr[0].pattrenmonth > p1.arr[1].patternmonth THEN p1.arr[0].pattrenmonth ELSE p1.arr[1].pattrenmonth
END).*
This still will have issue if only one present.
Option 2: Replace DATE_TRUNC_STR() with DATE_PART_STR() this gives 1,2,3 instead of “2018-12-01” (change DATE_PART_STR(p.patternmonth,“month”) to p.patternmonth)
Which one are you saying will have issue with months ? option 1 or 2 … Idea is if 2019-12-01 data available and 2018-12-01 doesn’t then 2018-12-01 can show the number 0 . And if ‘2019-12-01’ data is not available then current year metrics will be 0 but 2018-12-01 metrics can still be available against the ‘2019-12-01’ records .
Basically I am plotting this in chart with Ly and CY metrics and all I need to have is CY pattern 1day of the month and chart will have CY series and LY series to plot those values
If you can give me simple but tedious solution that would be okay either .
patternmonth values are 1-12 represent months.
No cy, ly data for that month data no object
only one of cy, ly is present returns that
if both cy, ly present combines into single object
SELECT (CASE WHEN ARRAY_LENGTH(arr) == 1 THEN arr[0] ELSE OBJECT_CONCAT(arr[0],arr[1]) END).*
FROM (
(SELECT DATE_PART_STR(M.PatternDate,"month") patternmonth,
ROUND(SUM(M.BlendedEventRevenueTotal)) funcroomrev,
ROUND(SUM(M.FBRevenueTotal),2) fnbrev,
ROUND(100 * SUM(M.FBRevenueTotal) / SUM(M.BlendedEventRevenueTotal),2) fnbrevpercent,
ROUND(SUM(M.AVRevTotal),2) avrev ,
ROUND(100 * SUM(M.AVRevTotal) / SUM(M.BlendedEventRevenueTotal),2) avrevpercent,
ROUND(SUM(M.OtherRevTotal),2) otherrev ,
ROUND(100 * SUM(M.OtherRevTotal) / SUM(M.BlendedEventRevenueTotal),2) otherrevpercent,
ROUND(SUM(M.BlendedEventRevenueTotal) / SUM(M.BlendedRoomnightsTotal),2) funcrevpergrprn,
ROUND(SUM(M.BlendedEventRevenueTotal) / SUM(M.BookedSqft),2) funcrevperbkdsqft,
ROUND(SUM(M.BlendedEventRevenueTotal) / 100,2) funcrevpersqft,
SUM(M.BlendedRoomnightsTotal) grprnleadvolume,
ROUND(SUM(M.BookedSqft) /100,2) occtotalsqft ,
ROUND(SUM(M.BlendedGuestroomRevenueTotal),2) grproomrev,
SUM(M.BlendedRoomnightsTotal) grprn,
ROUND(SUM(M.BlendedGuestroomRevenueTotal) / SUM(M.BlendedRoomnightsTotal),0) grpadr,
ROUND(SUM(CASE WHEN M.BookingStatus = 'Definite' THEN M.BlendedRoomnightsTotal ELSE 0 END) / SUM(M.BlendedRoomnightsTotal),3) conversion
FROM ( SELECT MAX(A.SnapshotDate) AS SnapshotDate,
A.ExternalBookingId AS ExternalBookingId,
A.PatternDate
FROM group360all AS A
WHERE A.PatternDate BETWEEN '2019-12-01' AND '2019-12-31'
AND A.BookingStatus IN ['Definite']
GROUP BY A.PatternDate, A.ExternalBookingId) T
INNER JOIN group360all M ON M.ExternalBookingId = T.ExternalBookingId AND M.SnapshotDate = T.SnapshotDate
AND T.PatternDate = M.PatternDate
GROUP BY DATE_PART_STR(M.PatternDate,"month"))
UNION ALL
(SELECT DATE_PART_STR(M.PatternDate,"month") patternmonth,
ROUND(SUM(M.BlendedEventRevenueTotal)) lyfuncroomrev,
ROUND(SUM(M.FBRevenueTotal),2) lyfnbrev,
ROUND(100 * SUM(M.FBRevenueTotal) / SUM(M.BlendedEventRevenueTotal),2) lyfnbrevpercent,
ROUND(SUM(M.AVRevTotal),2) lyavrev ,
ROUND(100 * SUM(M.AVRevTotal) / SUM(M.BlendedEventRevenueTotal),2) lyavrevpercent,
ROUND(SUM(M.OtherRevTotal),2) lyotherrev ,
ROUND(100 * SUM(M.OtherRevTotal) / SUM(M.BlendedEventRevenueTotal),2) lyotherrevpercent,
ROUND(SUM(M.BlendedEventRevenueTotal) / SUM(M.BlendedRoomnightsTotal),2) lyfuncrevpergrprn,
ROUND(SUM(M.BlendedEventRevenueTotal) / SUM(M.BookedSqft),2) lyfuncrevperbkdsqft,
ROUND(SUM(M.BlendedEventRevenueTotal) / 100,2) lyfuncrevpersqft,
SUM(M.BlendedRoomnightsTotal) lygrprnleadvolume,
ROUND(SUM(M.BookedSqft) /100,2) lyocctotalsqft ,
ROUND(SUM(M.BlendedGuestroomRevenueTotal),2) lygrproomrev,
SUM(M.BlendedRoomnightsTotal) lygrprn,
ROUND(SUM(M.BlendedGuestroomRevenueTotal) / SUM(M.BlendedRoomnightsTotal),0) lygrpadr,
ROUND(SUM(CASE WHEN M.BookingStatus = 'Definite' THEN M.BlendedRoomnightsTotal ELSE 0 END) / SUM(M.BlendedRoomnightsTotal),3) lyconversion
FROM ( SELECT MAX(A.SnapshotDate) AS SnapshotDate,
A.ExternalBookingId AS ExternalBookingId,
A.PatternDate
FROM group360all AS A
WHERE A.PatternDate BETWEEN DATE_ADD_STR('2019-12-01',-365,"day") AND DATE_ADD_STR('2019-12-31',-365,"day")
AND A.BookingStatus IN ['Definite']
GROUP BY A.PatternDate, A.ExternalBookingId) T
INNER JOIN group360all M ON M.ExternalBookingId = T.ExternalBookingId AND M.SnapshotDate = T.SnapshotDate
AND T.PatternDate = M.PatternDate
GROUP BY DATE_PART_STR(M.PatternDate,"month"))
) AS p
GROUP BY p.patternmonth
LETTING arr = ARRAY_AGG(p)
ORDER BY patternmonth;
OR
patternmonth will be first day of month
No cy, ly data for that month data no object
only one of cy, ly is present returns that
if both cy, ly present combines into single object with latest date.
SELECT (CASE WHEN ARRAY_LENGTH(arr) == 1 THEN arr[0] ELSE OBJECT_CONCAT(arr[0],arr[1]) END).*,
(CASE WHEN ARRAY_LENGTH(arr) == 2 AND arr[1].patternmonth > arr[0].patternmonth
THEN arr[1].patternmonth ELSE arr[0].patternmonth END) AS patternmonth
FROM (
(SELECT DATE_TRUNC_STR(M.PatternDate,"month") patternmonth,
ROUND(SUM(M.BlendedEventRevenueTotal)) funcroomrev,
ROUND(SUM(M.FBRevenueTotal),2) fnbrev,
ROUND(100 * SUM(M.FBRevenueTotal) / SUM(M.BlendedEventRevenueTotal),2) fnbrevpercent,
ROUND(SUM(M.AVRevTotal),2) avrev ,
ROUND(100 * SUM(M.AVRevTotal) / SUM(M.BlendedEventRevenueTotal),2) avrevpercent,
ROUND(SUM(M.OtherRevTotal),2) otherrev ,
ROUND(100 * SUM(M.OtherRevTotal) / SUM(M.BlendedEventRevenueTotal),2) otherrevpercent,
ROUND(SUM(M.BlendedEventRevenueTotal) / SUM(M.BlendedRoomnightsTotal),2) funcrevpergrprn,
ROUND(SUM(M.BlendedEventRevenueTotal) / SUM(M.BookedSqft),2) funcrevperbkdsqft,
ROUND(SUM(M.BlendedEventRevenueTotal) / 100,2) funcrevpersqft,
SUM(M.BlendedRoomnightsTotal) grprnleadvolume,
ROUND(SUM(M.BookedSqft) /100,2) occtotalsqft ,
ROUND(SUM(M.BlendedGuestroomRevenueTotal),2) grproomrev,
SUM(M.BlendedRoomnightsTotal) grprn,
ROUND(SUM(M.BlendedGuestroomRevenueTotal) / SUM(M.BlendedRoomnightsTotal),0) grpadr,
ROUND(SUM(CASE WHEN M.BookingStatus = 'Definite' THEN M.BlendedRoomnightsTotal ELSE 0 END) / SUM(M.BlendedRoomnightsTotal),3) conversion
FROM ( SELECT MAX(A.SnapshotDate) AS SnapshotDate,
A.ExternalBookingId AS ExternalBookingId,
A.PatternDate
FROM group360all AS A
WHERE A.PatternDate BETWEEN '2019-12-01' AND '2019-12-31'
AND A.BookingStatus IN ['Definite']
GROUP BY A.PatternDate, A.ExternalBookingId) T
INNER JOIN group360all M ON M.ExternalBookingId = T.ExternalBookingId AND M.SnapshotDate = T.SnapshotDate
AND T.PatternDate = M.PatternDate
GROUP BY DATE_TRUNC_STR(M.PatternDate,"month"))
UNION ALL
(SELECT DATE_TRUNC_STR(M.PatternDate,"month") patternmonth,
ROUND(SUM(M.BlendedEventRevenueTotal)) lyfuncroomrev,
ROUND(SUM(M.FBRevenueTotal),2) lyfnbrev,
ROUND(100 * SUM(M.FBRevenueTotal) / SUM(M.BlendedEventRevenueTotal),2) lyfnbrevpercent,
ROUND(SUM(M.AVRevTotal),2) lyavrev ,
ROUND(100 * SUM(M.AVRevTotal) / SUM(M.BlendedEventRevenueTotal),2) lyavrevpercent,
ROUND(SUM(M.OtherRevTotal),2) lyotherrev ,
ROUND(100 * SUM(M.OtherRevTotal) / SUM(M.BlendedEventRevenueTotal),2) lyotherrevpercent,
ROUND(SUM(M.BlendedEventRevenueTotal) / SUM(M.BlendedRoomnightsTotal),2) lyfuncrevpergrprn,
ROUND(SUM(M.BlendedEventRevenueTotal) / SUM(M.BookedSqft),2) lyfuncrevperbkdsqft,
ROUND(SUM(M.BlendedEventRevenueTotal) / 100,2) lyfuncrevpersqft,
SUM(M.BlendedRoomnightsTotal) lygrprnleadvolume,
ROUND(SUM(M.BookedSqft) /100,2) lyocctotalsqft ,
ROUND(SUM(M.BlendedGuestroomRevenueTotal),2) lygrproomrev,
SUM(M.BlendedRoomnightsTotal) lygrprn,
ROUND(SUM(M.BlendedGuestroomRevenueTotal) / SUM(M.BlendedRoomnightsTotal),0) lygrpadr,
ROUND(SUM(CASE WHEN M.BookingStatus = 'Definite' THEN M.BlendedRoomnightsTotal ELSE 0 END) / SUM(M.BlendedRoomnightsTotal),3) lyconversion
FROM ( SELECT MAX(A.SnapshotDate) AS SnapshotDate,
A.ExternalBookingId AS ExternalBookingId,
A.PatternDate
FROM group360all AS A
WHERE A.PatternDate BETWEEN DATE_ADD_STR('2019-12-01',-365,"day") AND DATE_ADD_STR('2019-12-31',-365,"day")
AND A.BookingStatus IN ['Definite']
GROUP BY A.PatternDate, A.ExternalBookingId) T
INNER JOIN group360all M ON M.ExternalBookingId = T.ExternalBookingId AND M.SnapshotDate = T.SnapshotDate
AND T.PatternDate = M.PatternDate
GROUP BY DATE_TRUNC_STR(M.PatternDate,"month"))
) AS p
GROUP BY DATE_PART_STR(p.patternmonth, "month")
LETTING arr = ARRAY_AGG(p)
ORDER BY patternmonth;
1 Like
syntax error - at AS … there are too many AS now I am confused . sorry!
Updated above post with 2 solutions (1 - patternmonth :1,2,3 2-patternmonth as “2019-12-01”)
THIS IS AMAZING … I WROTE IT CAPS TO STRESS IT … I like solution #2 : Now I am going to modify it for PatternWeek .
Appreciate your help … This thread is kind of never ending ! But this is great solution
week is not easy (may not possible) because dates will vary. Unless you use DATE_PART_STR(date,“week”) gives 1-53 (i.e day/7+1). Each year starts with different weekday
You can try this
pwstart is Sunday of the week aggregates cy, ly based on pwstart name them pwstart, lypwstart
As sunday comes different date cy, ly, so adjust pw last year so that pw of cy, ly same
Now combine the results based on month+day of pw similar to month
pwstart, lypwstart gives Sundays of the week. pw give any day in that week (i.e cy is sunday, ly might be Monday or Tuesday).
SELECT (CASE WHEN ARRAY_LENGTH(arr) == 1 THEN arr[0] ELSE OBJECT_CONCAT(arr[0],arr[1]) END).*,
(CASE WHEN ARRAY_LENGTH(arr) == 2 AND arr[1].pw > arr[0].pw
THEN arr[1].pw ELSE arr[0].pw END) AS pw
FROM (
(SELECT pw AS pwstart,
pw,
ROUND(SUM(M.BlendedEventRevenueTotal)) funcroomrev,
ROUND(SUM(M.FBRevenueTotal),2) fnbrev,
ROUND(100 * SUM(M.FBRevenueTotal) / SUM(M.BlendedEventRevenueTotal),2) fnbrevpercent,
ROUND(SUM(M.AVRevTotal),2) avrev ,
ROUND(100 * SUM(M.AVRevTotal) / SUM(M.BlendedEventRevenueTotal),2) avrevpercent,
ROUND(SUM(M.OtherRevTotal),2) otherrev ,
ROUND(100 * SUM(M.OtherRevTotal) / SUM(M.BlendedEventRevenueTotal),2) otherrevpercent,
ROUND(SUM(M.BlendedEventRevenueTotal) / SUM(M.BlendedRoomnightsTotal),2) funcrevpergrprn,
ROUND(SUM(M.BlendedEventRevenueTotal) / SUM(M.BookedSqft),2) funcrevperbkdsqft,
ROUND(SUM(M.BlendedEventRevenueTotal) / 100,2) funcrevpersqft,
SUM(M.BlendedRoomnightsTotal) grprnleadvolume,
ROUND(SUM(M.BookedSqft) /100,2) occtotalsqft ,
ROUND(SUM(M.BlendedGuestroomRevenueTotal),2) grproomrev,
SUM(M.BlendedRoomnightsTotal) grprn,
ROUND(SUM(M.BlendedGuestroomRevenueTotal) / SUM(M.BlendedRoomnightsTotal),0) grpadr,
ROUND(SUM(CASE WHEN M.BookingStatus = 'Definite' THEN M.BlendedRoomnightsTotal ELSE 0 END) / SUM(M.BlendedRoomnightsTotal),3) conversion
FROM ( SELECT MAX(A.SnapshotDate) AS SnapshotDate,
A.ExternalBookingId AS ExternalBookingId,
A.PatternDate
FROM group360all AS A
WHERE A.PatternDate BETWEEN '2019-12-01' AND '2019-12-31'
AND A.BookingStatus IN ['Definite']
GROUP BY A.PatternDate, A.ExternalBookingId) T
INNER JOIN group360all M ON M.ExternalBookingId = T.ExternalBookingId AND M.SnapshotDate = T.SnapshotDate
AND T.PatternDate = M.PatternDate
LET pw = DATE_ADD_STR(M.PatternDate, {"Sunday":0,"Monday":-1,"Tuesday":-2,"Wednesday":-3,"Thursday":-4,"Friday":-5,"Saturday":-6}.[WEEKDAY_STR(M.PatternDate)],"day")
GROUP BY pw)
UNION ALL
(SELECT pw AS lypwstart,
DATE_ADD_STR(pw,-IMOD(DATE_DIFF_STR(pw, DATE_ADD_STR(pw,1,"year"), 'day'),7),"day") AS pw,
ROUND(SUM(M.BlendedEventRevenueTotal)) lyfuncroomrev,
ROUND(SUM(M.FBRevenueTotal),2) lyfnbrev,
ROUND(100 * SUM(M.FBRevenueTotal) / SUM(M.BlendedEventRevenueTotal),2) lyfnbrevpercent,
ROUND(SUM(M.AVRevTotal),2) lyavrev ,
ROUND(100 * SUM(M.AVRevTotal) / SUM(M.BlendedEventRevenueTotal),2) lyavrevpercent,
ROUND(SUM(M.OtherRevTotal),2) lyotherrev ,
ROUND(100 * SUM(M.OtherRevTotal) / SUM(M.BlendedEventRevenueTotal),2) lyotherrevpercent,
ROUND(SUM(M.BlendedEventRevenueTotal) / SUM(M.BlendedRoomnightsTotal),2) lyfuncrevpergrprn,
ROUND(SUM(M.BlendedEventRevenueTotal) / SUM(M.BookedSqft),2) lyfuncrevperbkdsqft,
ROUND(SUM(M.BlendedEventRevenueTotal) / 100,2) lyfuncrevpersqft,
SUM(M.BlendedRoomnightsTotal) lygrprnleadvolume,
ROUND(SUM(M.BookedSqft) /100,2) lyocctotalsqft ,
ROUND(SUM(M.BlendedGuestroomRevenueTotal),2) lygrproomrev,
SUM(M.BlendedRoomnightsTotal) lygrprn,
ROUND(SUM(M.BlendedGuestroomRevenueTotal) / SUM(M.BlendedRoomnightsTotal),0) lygrpadr,
ROUND(SUM(CASE WHEN M.BookingStatus = 'Definite' THEN M.BlendedRoomnightsTotal ELSE 0 END) / SUM(M.BlendedRoomnightsTotal),3) lyconversion
FROM ( SELECT MAX(A.SnapshotDate) AS SnapshotDate,
A.ExternalBookingId AS ExternalBookingId,
A.PatternDate
FROM group360all AS A
WHERE A.PatternDate BETWEEN DATE_ADD_STR('2019-12-01',-1,"year") AND DATE_ADD_STR('2019-12-31',-1,"year")
AND A.BookingStatus IN ['Definite']
GROUP BY A.PatternDate, A.ExternalBookingId) T
INNER JOIN group360all M ON M.ExternalBookingId = T.ExternalBookingId AND M.SnapshotDate = T.SnapshotDate
AND T.PatternDate = M.PatternDate
LET pw = DATE_ADD_STR(M.PatternDate, {"Sunday":0,"Monday":-1,"Tuesday":-2,"Wednesday":-3,"Thursday":-4,"Friday":-5,"Saturday":-6}.[WEEKDAY_STR(M.PatternDate)],"day")
GROUP BY pw)
) AS p
GROUP BY SUBSTR(p.pw,5)
LETTING arr = ARRAY_AGG(p)
ORDER BY pw;hi @vsr1 - I am getting “Ambiguous reference to field d.” in week query
Sorry I am taking it back … Query has NO error but it is returning weird number of rows which may not give the right solution .
I think I got you when you are saying it is becoming complex . Let me simplify the scenario as we know the starting day of week will not be similar this year vs last year . So lets think this is as Date Aligned … so if I am doing current year 2019-12-01 to 2019-12-31 I am getting 5 weeks … so 1st , 08th , 15th , 22nd and 29th of 2019 starting sunday . I just want to go back to 2018 with same dates of 2019 - 365 days so last year week will be : 2018-12-01 , 2018-12-08 , 2018-12-15 , 2018-12-22 , 2018-12-29 .
That could make the query easy and may be easy to understand … Is that feasible in your current query approach ?
thanks always .
Unless I have data i can’t verify what is wrong with the query. You can adjust the dates in this query and give me dates that produces.
SELECT RAW M.PatternDate
FROM ( SELECT MAX(A.SnapshotDate) AS SnapshotDate,
A.ExternalBookingId AS ExternalBookingId,
A.PatternDate
FROM group360all AS A
WHERE (A.PatternDate BETWEEN '2019-12-01' AND '2019-12-31'
OR (A.PatternDate BETWEEN DATE_ADD_STR('2019-12-01',-1,"year") AND DATE_ADD_STR('2019-12-31',-1,"year")))
AND A.BookingStatus IN ['Definite']
GROUP BY A.PatternDate, A.ExternalBookingId) T
INNER JOIN group360all M ON M.ExternalBookingId = T.ExternalBookingId AND M.SnapshotDate = T.SnapshotDate
AND T.PatternDate = M.PatternDate;
Also there will be issues at the beginning/end of ranges due to partial weeks.
You can follow similar to MONTH query.
One you suggested can be achieved with DATE_PART_STR(date,“week”); This gives 1 for 1-7, 2 for 8-14, 3 for 15-21 etc.
Used -1 year instead of -365 days. 365 days will not work right for LEAF year
SELECT (CASE WHEN ARRAY_LENGTH(arr) == 1 THEN arr[0] ELSE OBJECT_CONCAT(arr[0],arr[1]) END).*,
(CASE WHEN ARRAY_LENGTH(arr) == 2 AND arr[1].pwstart > arr[0].pwstart
THEN arr[1].pwstart ELSE arr[0].pwstart END) AS pwstart
FROM (
(SELECT pwstart,
ROUND(SUM(M.BlendedEventRevenueTotal)) funcroomrev,
ROUND(SUM(M.FBRevenueTotal),2) fnbrev,
ROUND(100 * SUM(M.FBRevenueTotal) / SUM(M.BlendedEventRevenueTotal),2) fnbrevpercent,
ROUND(SUM(M.AVRevTotal),2) avrev ,
ROUND(100 * SUM(M.AVRevTotal) / SUM(M.BlendedEventRevenueTotal),2) avrevpercent,
ROUND(SUM(M.OtherRevTotal),2) otherrev ,
ROUND(100 * SUM(M.OtherRevTotal) / SUM(M.BlendedEventRevenueTotal),2) otherrevpercent,
ROUND(SUM(M.BlendedEventRevenueTotal) / SUM(M.BlendedRoomnightsTotal),2) funcrevpergrprn,
ROUND(SUM(M.BlendedEventRevenueTotal) / SUM(M.BookedSqft),2) funcrevperbkdsqft,
ROUND(SUM(M.BlendedEventRevenueTotal) / 100,2) funcrevpersqft,
SUM(M.BlendedRoomnightsTotal) grprnleadvolume,
ROUND(SUM(M.BookedSqft) /100,2) occtotalsqft ,
ROUND(SUM(M.BlendedGuestroomRevenueTotal),2) grproomrev,
SUM(M.BlendedRoomnightsTotal) grprn,
ROUND(SUM(M.BlendedGuestroomRevenueTotal) / SUM(M.BlendedRoomnightsTotal),0) grpadr,
ROUND(SUM(CASE WHEN M.BookingStatus = 'Definite' THEN M.BlendedRoomnightsTotal ELSE 0 END) / SUM(M.BlendedRoomnightsTotal),3) conversion
FROM ( SELECT MAX(A.SnapshotDate) AS SnapshotDate,
A.ExternalBookingId AS ExternalBookingId,
A.PatternDate
FROM group360all AS A
WHERE A.PatternDate BETWEEN '2019-12-01' AND '2019-12-31'
AND A.BookingStatus IN ['Definite']
GROUP BY A.PatternDate, A.ExternalBookingId) T
INNER JOIN group360all M ON M.ExternalBookingId = T.ExternalBookingId AND M.SnapshotDate = T.SnapshotDate
AND T.PatternDate = M.PatternDate
LET pwstart = DATE_ADD_STR(DATE_TRUNC_STR(M.PatternDate,"year"),7*(DATE_PART_STR(M.PatternDate,"week")-1),"day")
GROUP BY pwstart)
UNION ALL
(SELECT pwstart AS pwstart,
ROUND(SUM(M.BlendedEventRevenueTotal)) lyfuncroomrev,
ROUND(SUM(M.FBRevenueTotal),2) lyfnbrev,
ROUND(100 * SUM(M.FBRevenueTotal) / SUM(M.BlendedEventRevenueTotal),2) lyfnbrevpercent,
ROUND(SUM(M.AVRevTotal),2) lyavrev ,
ROUND(100 * SUM(M.AVRevTotal) / SUM(M.BlendedEventRevenueTotal),2) lyavrevpercent,
ROUND(SUM(M.OtherRevTotal),2) lyotherrev ,
ROUND(100 * SUM(M.OtherRevTotal) / SUM(M.BlendedEventRevenueTotal),2) lyotherrevpercent,
ROUND(SUM(M.BlendedEventRevenueTotal) / SUM(M.BlendedRoomnightsTotal),2) lyfuncrevpergrprn,
ROUND(SUM(M.BlendedEventRevenueTotal) / SUM(M.BookedSqft),2) lyfuncrevperbkdsqft,
ROUND(SUM(M.BlendedEventRevenueTotal) / 100,2) lyfuncrevpersqft,
SUM(M.BlendedRoomnightsTotal) lygrprnleadvolume,
ROUND(SUM(M.BookedSqft) /100,2) lyocctotalsqft ,
ROUND(SUM(M.BlendedGuestroomRevenueTotal),2) lygrproomrev,
SUM(M.BlendedRoomnightsTotal) lygrprn,
ROUND(SUM(M.BlendedGuestroomRevenueTotal) / SUM(M.BlendedRoomnightsTotal),0) lygrpadr,
ROUND(SUM(CASE WHEN M.BookingStatus = 'Definite' THEN M.BlendedRoomnightsTotal ELSE 0 END) / SUM(M.BlendedRoomnightsTotal),3) lyconversion
FROM ( SELECT MAX(A.SnapshotDate) AS SnapshotDate,
A.ExternalBookingId AS ExternalBookingId,
A.PatternDate
FROM group360all AS A
WHERE A.PatternDate BETWEEN DATE_ADD_STR('2019-12-01',-1,"year") AND DATE_ADD_STR('2019-12-31',-1,"year")
AND A.BookingStatus IN ['Definite']
GROUP BY A.PatternDate, A.ExternalBookingId) T
INNER JOIN group360all M ON M.ExternalBookingId = T.ExternalBookingId AND M.SnapshotDate = T.SnapshotDate
AND T.PatternDate = M.PatternDate
LET pwstart = DATE_ADD_STR(DATE_TRUNC_STR(M.PatternDate,"year"),7*(DATE_PART_STR(M.PatternDate,"week")-1),"day")
GROUP BY pwstart)
) AS p
GROUP BY DATE_PART_STR(p.pwstart,"week")
LETTING arr = ARRAY_AGG(p)
ORDER BY pwstart;Your above query data output is like this :
[
"2019-12-08",
"2019-12-06",
"2018-12-23",
"2019-12-19",
"2019-12-07",
"2018-12-02",
"2018-12-16",
"2019-12-16",
"2018-12-10",
"2019-12-29",
"2018-12-02",
"2019-12-18",
"2019-12-04",
"2019-12-14",
"2019-12-07",
"2018-12-17",
"2018-12-01",
"2019-12-28",
"2018-12-10",
"2018-12-25",
"2019-12-11",
"2018-12-01",
"2019-12-24",
"2018-12-05",
"2018-12-17",
"2018-12-08",
"2019-12-05",
"2018-12-13",
"2019-12-15",
"2019-12-02",
"2018-12-19",
"2018-12-22",
"2018-12-08",
"2018-12-08",
"2018-12-02",
"2018-12-07",
"2018-12-17",
"2018-12-07",
"2018-12-18",
"2018-12-20",
"2019-12-01",
"2018-12-01",
"2018-12-18",
"2019-12-27",
"2019-12-26",
"2019-12-25",
"2019-12-13",
"2018-12-20",
"2018-12-24",
"2018-12-23",
"2019-12-08",
"2019-12-03",
"2018-12-05",
"2018-12-08",
"2018-12-22",
"2019-12-14",
"2018-12-01",
"2019-12-16",
"2018-12-05",
"2018-12-13",
"2018-12-24",
"2018-12-30",
"2019-12-11",
"2019-12-28",
"2018-12-26",
"2019-12-07",
"2019-12-19",
"2018-12-18",
"2019-12-18",
"2019-12-19",
"2018-12-23",
"2018-12-15",
"2019-12-29",
"2018-12-30",
"2018-12-06",
"2018-12-13",
"2018-12-09",
"2019-12-09",
"2019-12-20",
"2018-12-16",
"2018-12-22",
"2019-12-20",
"2019-12-31",
"2019-12-05",
"2018-12-02",
"2018-12-23",
"2018-12-14",
"2019-12-02",
"2018-12-16",
"2019-12-25",
"2018-12-11",
"2019-12-10",
"2018-12-11",
"2018-12-28",
"2018-12-12",
"2019-12-26",
"2018-12-13",
"2018-12-02",
"2018-12-05",
"2018-12-21",
"2018-12-22",
"2018-12-07",
"2018-12-01",
"2019-12-15",
"2019-12-01",
"2018-12-29",
"2018-12-06",
"2018-12-30",
"2018-12-01",
"2018-12-21",
"2018-12-01",
"2018-12-15",
"2018-12-18",
"2019-12-27",
"2019-12-26",
"2018-12-04",
"2019-12-17",
"2018-12-20",
"2018-12-31",
"2018-12-20",
"2019-12-12",
"2019-12-21",
"2018-12-08",
"2018-12-15",
"2018-12-02",
"2019-12-22",
"2018-12-15",
"2019-12-28",
"2018-12-10",
"2019-12-03",
"2018-12-19",
"2018-12-18",
"2018-12-01",
"2018-12-14",
"2018-12-17",
"2018-12-08",
"2019-12-04",
"2018-12-05",
"2018-12-20",
"2019-12-06",
"2018-12-06",
"2018-12-17",
"2019-12-30",
"2018-12-14",
"2019-12-31",
"2018-12-04",
"2018-12-01",
"2018-12-18",
"2019-12-23",
"2018-12-02",
"2018-12-08",
"2019-12-07",
"2018-12-20",
"2019-12-13",
"2018-12-14",
"2018-12-13",
"2019-12-10",
"2019-12-09",
"2018-12-31",
"2018-12-30",
"2018-12-06",
"2018-12-09",
"2018-12-29",
"2018-12-26",
"2018-12-09",
"2018-12-29",
"2018-12-31",
"2018-12-07",
"2018-12-09",
"2018-12-15",
"2018-12-09",
"2018-12-13",
"2018-12-15",
"2018-12-03",
"2018-12-15",
"2018-12-28",
"2018-12-11",
"2019-12-12",
"2018-12-26",
"2018-12-14",
"2018-12-23",
"2018-12-03",
"2019-12-05",
"2019-12-22",
"2019-12-17",
"2018-12-15",
"2019-12-30",
"2018-12-16",
"2018-12-29",
"2018-12-11",
"2019-12-27",
"2019-12-06",
"2018-12-14",
"2018-12-28",
"2019-12-09",
"2018-12-27",
"2019-12-04",
"2018-12-04",
"2018-12-07",
"2018-12-15",
"2018-12-14",
"2019-12-10",
"2018-12-26",
"2018-12-10",
"2018-12-29",
"2019-12-13",
"2018-12-11",
"2018-12-05",
"2018-12-21",
"2018-12-27",
"2018-12-12",
"2018-12-03",
"2018-12-28",
"2019-12-23",
"2018-12-22",
"2019-12-03",
"2018-12-21",
"2018-12-12",
"2018-12-27",
"2018-12-14",
"2019-12-28",
"2018-12-06",
"2018-12-11",
"2018-12-10",
"2019-12-11",
"2018-12-14",
"2018-12-02",
"2019-12-09",
"2019-12-14",
"2019-12-06",
"2019-12-08",
"2019-12-20",
"2019-12-21",
"2019-12-16",
"2018-12-25",
"2018-12-10",
"2019-12-29",
"2018-12-01",
"2018-12-25",
"2019-12-18",
"2018-12-07",
"2019-12-07",
"2018-12-04",
"2018-12-07",
"2018-12-10",
"2019-12-26",
"2018-12-19",
"2018-12-19",
"2019-12-02",
"2018-12-01",
"2018-12-08",
"2018-12-17",
"2018-12-20",
"2018-12-27",
"2018-12-20",
"2018-12-15",
"2019-12-12",
"2019-12-15",
"2018-12-04",
"2019-12-24",
"2019-12-05",
"2019-12-01",
"2019-12-14",
"2018-12-12",
"2018-12-02",
"2018-12-18",
"2018-12-09",
"2018-12-26",
"2018-12-13",
"2019-12-11",
"2019-12-16",
"2018-12-21",
"2018-12-12",
"2019-12-20",
"2019-12-14",
"2018-12-19",
"2019-12-19",
"2019-12-18",
"2018-12-26",
"2018-12-03",
"2018-12-31",
"2018-12-24",
"2018-12-05",
"2018-12-27",
"2018-12-15",
"2018-12-09",
"2019-12-06",
"2018-12-15",
"2019-12-08",
"2018-12-29",
"2019-12-07",
"2018-12-31",
"2019-12-10",
"2018-12-12",
"2018-12-21",
"2018-12-28",
"2018-12-14",
"2018-12-31",
"2018-12-16",
"2019-12-30",
"2018-12-11",
"2018-12-27",
"2018-12-14",
"2018-12-28",
"2018-12-03",
"2018-12-22",
"2019-12-01",
"2018-12-11",
"2019-12-05",
"2019-12-24",
"2018-12-08",
"2018-12-21",
"2018-12-12",
"2019-12-25",
"2018-12-07",
"2018-12-09",
"2019-12-19",
"2018-12-29",
"2018-12-27",
"2018-12-28",
"2019-12-15",
"2018-12-31",
"2018-12-07",
"2019-12-12",
"2018-12-05",
"2018-12-07",
"2019-12-02",
"2018-12-29",
"2019-12-28",
"2018-12-04",
"2018-12-25",
"2018-12-13",
"2018-12-04",
"2019-12-17",
"2019-12-14",
"2019-12-22",
"2018-12-10",
"2018-12-05",
"2018-12-07",
"2018-12-25",
"2018-12-12",
"2019-12-12",
"2019-12-21",
"2018-12-19",
"2018-12-10",
"2019-12-02",
"2019-12-27",
"2018-12-16",
"2018-12-25",
"2019-12-13",
"2018-12-20",
"2019-12-09",
"2018-12-04",
"2018-12-09",
"2018-12-08",
"2019-12-31",
"2018-12-28",
"2019-12-04",
"2018-12-17",
"2019-12-11",
"2019-12-23",
"2018-12-11",
"2018-12-19",
"2019-12-10",
"2018-12-03",
"2019-12-07",
"2018-12-20",
"2019-12-30",
"2019-12-06",
"2019-12-08",
"2019-12-03",
"2018-12-18",
"2018-12-03",
"2018-12-15",
"2018-12-19",
"2018-12-06",
"2018-12-17",
"2018-12-24",
"2018-12-30",
"2018-12-31",
"2019-12-17",
"2018-12-09",
"2018-12-06",
"2018-12-06",
"2018-12-08",
"2018-12-30",
"2019-12-27",
"2018-12-07",
"2018-12-23",
"2018-12-24",
"2018-12-05",
"2018-12-13",
"2018-12-03",
"2019-12-12",
"2019-12-21",
"2019-12-20",
"2018-12-16",
"2018-12-13",
"2018-12-23",
"2018-12-03",
"2019-12-07",
"2019-12-31",
"2019-12-09",
"2019-12-10",
"2019-12-06",
"2019-12-08",
"2018-12-16",
"2018-12-05",
"2018-12-27",
"2018-12-24",
"2019-12-04",
"2018-12-14",
"2018-12-22",
"2019-12-03",
"2018-12-21",
"2018-12-12",
"2018-12-07",
"2018-12-22",
"2018-12-11",
"2018-12-16",
"2019-12-13"
]
Below query giving error . However I don’t see anything as pw.
[
{
"code": 3000,
"msg": "Ambiguous reference to field pw.",
"query_from_user": "SELECT (CASE WHEN ARRAY_LENGTH(arr) == 1 THEN arr[0] ELSE OBJECT_CONCAT(arr[0],arr[1]) END).*,\r\n (CASE WHEN ARRAY_LENGTH(arr) == 2 AND arr[1].pwstart > arr[0].pwstart\r\n THEN arr[1].pwstart ELSE arr[0].pwstart END) AS pwstart\r\nFROM (\r\n (SELECT pwstart,\r\n ROUND(SUM(M.BlendedEventRevenueTotal)) funcroomrev,\r\n ROUND(SUM(M.FBRevenueTotal),2) fnbrev,\r\n ROUND(100 * SUM(M.FBRevenueTotal) / SUM(M.BlendedEventRevenueTotal),2) fnbrevpercent,\r\n ROUND(SUM(M.AVRevTotal),2) avrev ,\r\n ROUND(100 * SUM(M.AVRevTotal) / SUM(M.BlendedEventRevenueTotal),2) avrevpercent,\r\n ROUND(SUM(M.OtherRevTotal),2) otherrev ,\r\n ROUND(100 * SUM(M.OtherRevTotal) / SUM(M.BlendedEventRevenueTotal),2) otherrevpercent,\r\n ROUND(SUM(M.BlendedEventRevenueTotal) / SUM(M.BlendedRoomnightsTotal),2) funcrevpergrprn,\r\n ROUND(SUM(M.BlendedEventRevenueTotal) / SUM(M.BookedSqft),2) funcrevperbkdsqft,\r\n ROUND(SUM(M.BlendedEventRevenueTotal) / 100,2) funcrevpersqft,\r\n SUM(M.BlendedRoomnightsTotal) grprnleadvolume,\r\n ROUND(SUM(M.BookedSqft) /100,2) occtotalsqft ,\r\n ROUND(SUM(M.BlendedGuestroomRevenueTotal),2) grproomrev,\r\n SUM(M.BlendedRoomnightsTotal) grprn,\r\n ROUND(SUM(M.BlendedGuestroomRevenueTotal) / SUM(M.BlendedRoomnightsTotal),0) grpadr,\r\n ROUND(SUM(CASE WHEN M.BookingStatus = 'Definite' THEN M.BlendedRoomnightsTotal ELSE 0 END) / SUM(M.BlendedRoomnightsTotal),3) conversion\r\n FROM ( SELECT MAX(A.SnapshotDate) AS SnapshotDate,\r\n A.ExternalBookingId AS ExternalBookingId,\r\n A.PatternDate\r\n FROM group360all AS A\r\n WHERE A.PatternDate BETWEEN '2019-12-01' AND '2019-12-31'\r\n AND A.BookingStatus IN ['Definite']\r\n GROUP BY A.PatternDate, A.ExternalBookingId) T\r\n INNER JOIN group360all M ON M.ExternalBookingId = T.ExternalBookingId AND M.SnapshotDate = T.SnapshotDate\r\n AND T.PatternDate = M.PatternDate\r\n LET pwstart = DATE_ADD_STR(DATE_TRUNC_STR(M.PatternDate,\"year\"),7*(DATE_PART_STR(M.PatternDate,\"week\")-1),\"day\")\r\n GROUP BY pwstart)\r\n UNION ALL\r\n (SELECT pwstart AS pwstart,\r\n DATE_ADD_STR(pw,-IMOD(DATE_DIFF_STR(pw, DATE_ADD_STR(pw,1,\"year\"), 'day'),7),\"day\") AS pw,\r\n ROUND(SUM(M.BlendedEventRevenueTotal)) lyfuncroomrev,\r\n ROUND(SUM(M.FBRevenueTotal),2) lyfnbrev,\r\n ROUND(100 * SUM(M.FBRevenueTotal) / SUM(M.BlendedEventRevenueTotal),2) lyfnbrevpercent,\r\n ROUND(SUM(M.AVRevTotal),2) lyavrev ,\r\n ROUND(100 * SUM(M.AVRevTotal) / SUM(M.BlendedEventRevenueTotal),2) lyavrevpercent,\r\n ROUND(SUM(M.OtherRevTotal),2) lyotherrev ,\r\n ROUND(100 * SUM(M.OtherRevTotal) / SUM(M.BlendedEventRevenueTotal),2) lyotherrevpercent,\r\n ROUND(SUM(M.BlendedEventRevenueTotal) / SUM(M.BlendedRoomnightsTotal),2) lyfuncrevpergrprn,\r\n ROUND(SUM(M.BlendedEventRevenueTotal) / SUM(M.BookedSqft),2) lyfuncrevperbkdsqft,\r\n ROUND(SUM(M.BlendedEventRevenueTotal) / 100,2) lyfuncrevpersqft,\r\n SUM(M.BlendedRoomnightsTotal) lygrprnleadvolume,\r\n ROUND(SUM(M.BookedSqft) /100,2) lyocctotalsqft ,\r\n ROUND(SUM(M.BlendedGuestroomRevenueTotal),2) lygrproomrev,\r\n SUM(M.BlendedRoomnightsTotal) lygrprn,\r\n ROUND(SUM(M.BlendedGuestroomRevenueTotal) / SUM(M.BlendedRoomnightsTotal),0) lygrpadr,\r\n ROUND(SUM(CASE WHEN M.BookingStatus = 'Definite' THEN M.BlendedRoomnightsTotal ELSE 0 END) / SUM(M.BlendedRoomnightsTotal),3) lyconversion\r\n FROM ( SELECT MAX(A.SnapshotDate) AS SnapshotDate,\r\n A.ExternalBookingId AS ExternalBookingId,\r\n A.PatternDate\r\n FROM group360all AS A\r\n WHERE A.PatternDate BETWEEN DATE_ADD_STR('2019-12-01',-1,\"year\") AND DATE_ADD_STR('2019-12-31',-1,\"year\")\r\n AND A.BookingStatus IN ['Definite']\r\n GROUP BY A.PatternDate, A.ExternalBookingId) T\r\n INNER JOIN group360all M ON M.ExternalBookingId = T.ExternalBookingId AND M.SnapshotDate = T.SnapshotDate\r\n AND T.PatternDate = M.PatternDate\r\n LET pwstart = DATE_ADD_STR(DATE_TRUNC_STR(M.PatternDate,\"year\"),7*(DATE_PART_STR(M.PatternDate,\"week\")-1),\"day\")\r\n GROUP BY pwstart)\r\n ) AS p\r\nGROUP BY DATE_PART_STR(pwstart,\"week\")\r\nLETTING arr = ARRAY_AGG(p)\r\nORDER BY pwstart"
}
]
And you are right … with LEAP year the 365 days minus will not fly well. As long this simple DATE_PART_STR(date,“week”) works I am happy with that .
The query you gave me 4 days ago it is returning the results weird which I don’t want . Here is the JSON output :
But I guess with the 1st day of week aligned year ago going to be tricky to show in my chart when I am showing 1st day of week based on current year and Last year metric in a single date of reference . so I will keep date to be date aligned and not day of week aligned : if we get multiple weeks start for this year vs last year it is going to mess up the plot due to two different starting day of week window.
However as you have asked here is output:
[
{
"lyavrev": 0,
"lyavrevpercent": 0,
"lyconversion": 1,
"lyfnbrev": 5128.9,
"lyfnbrevpercent": 82.64,
"lyfuncrevperbkdsqft": 38.55,
"lyfuncrevpergrprn": 124.12,
"lyfuncrevpersqft": 62.06,
"lyfuncroomrev": 6206,
"lygrpadr": 160,
"lygrprn": 50,
"lygrprnleadvolume": 50,
"lygrproomrev": 7997,
"lyocctotalsqft": 1.61,
"lyotherrev": 1077.07,
"lyotherrevpercent": 17.36,
"lypwstart": "2018-11-25",
"pw": "2018-11-26"
},
{
"lyavrev": 0,
"lyavrevpercent": 0,
"lyconversion": 1,
"lyfnbrev": 11057.07,
"lyfnbrevpercent": 74.26,
"lyfuncrevperbkdsqft": 26.03,
"lyfuncrevpergrprn": 77.15,
"lyfuncrevpersqft": 148.89,
"lyfuncroomrev": 14889,
"lygrpadr": 156,
"lygrprn": 193,
"lygrprnleadvolume": 193,
"lygrproomrev": 30143.88,
"lyocctotalsqft": 5.72,
"lyotherrev": 3831.99,
"lyotherrevpercent": 25.74,
"lypwstart": "2018-12-02",
"pw": "2018-12-03"
},
{
"lyavrev": 250,
"lyavrevpercent": 1.56,
"lyconversion": 1,
"lyfnbrev": 12059.1,
"lyfnbrevpercent": 75.16,
"lyfuncrevperbkdsqft": 28.7,
"lyfuncrevpergrprn": 144.55,
"lyfuncrevpersqft": 160.45,
"lyfuncroomrev": 16045,
"lygrpadr": 144,
"lygrprn": 111,
"lygrprnleadvolume": 111,
"lygrproomrev": 15969,
"lyocctotalsqft": 5.59,
"lyotherrev": 3735.63,
"lyotherrevpercent": 23.28,
"lypwstart": "2018-12-09",
"pw": "2018-12-10"
},
{
"lyavrev": 0,
"lyavrevpercent": 0,
"lyconversion": 1,
"lyfnbrev": 4171.8,
"lyfnbrevpercent": 70.99,
"lyfuncrevperbkdsqft": 23.14,
"lyfuncrevpergrprn": 32.47,
"lyfuncrevpersqft": 58.76,
"lyfuncroomrev": 5876,
"lygrpadr": 121,
"lygrprn": 181,
"lygrprnleadvolume": 181,
"lygrproomrev": 21887,
"lyocctotalsqft": 2.54,
"lyotherrev": 1704.62,
"lyotherrevpercent": 29.01,
"lypwstart": "2018-12-16",
"pw": "2018-12-17"
},
{
"lyavrev": 0,
"lyavrevpercent": 0,
"lyconversion": 1,
"lyfnbrev": 1672,
"lyfnbrevpercent": 82.64,
"lyfuncrevperbkdsqft": 38.17,
"lyfuncrevpergrprn": 45.98,
"lyfuncrevpersqft": 20.23,
"lyfuncroomrev": 2023,
"lygrpadr": 124,
"lygrprn": 44,
"lygrprnleadvolume": 44,
"lygrproomrev": 5456,
"lyocctotalsqft": 0.53,
"lyotherrev": 351.12,
"lyotherrevpercent": 17.36,
"lypwstart": "2018-12-23",
"pw": "2018-12-24"
},
{
"lyavrev": 0,
"lyavrevpercent": null,
"lyconversion": 1,
"lyfnbrev": 0,
"lyfnbrevpercent": null,
"lyfuncrevperbkdsqft": null,
"lyfuncrevpergrprn": 0,
"lyfuncrevpersqft": 0,
"lyfuncroomrev": 0,
"lygrpadr": 156,
"lygrprn": 45,
"lygrprnleadvolume": 45,
"lygrproomrev": 7035,
"lyocctotalsqft": 0,
"lyotherrev": 0,
"lyotherrevpercent": null,
"lypwstart": "2018-12-30",
"pw": "2018-12-31"
},
{
"avrev": 0,
"avrevpercent": 0,
"conversion": 1,
"fnbrev": 6018,
"fnbrevpercent": 85.12,
"funcrevperbkdsqft": 44.75,
"funcrevpergrprn": 51.98,
"funcrevpersqft": 70.7,
"funcroomrev": 7070,
"grpadr": 164,
"grprn": 136,
"grprnleadvolume": 136,
"grproomrev": 22270,
"occtotalsqft": 1.58,
"otherrev": 1051.8,
"otherrevpercent": 14.88,
"pw": "2019-12-01",
"pwstart": "2019-12-01"
},
{
"avrev": 250,
"avrevpercent": 8.1,
"conversion": 1,
"fnbrev": 2352,
"fnbrevpercent": 76.19,
"funcrevperbkdsqft": 42.29,
"funcrevpergrprn": 88.21,
"funcrevpersqft": 30.87,
"funcroomrev": 3087,
"grpadr": 146,
"grprn": 35,
"grprnleadvolume": 35,
"grproomrev": 5107,
"occtotalsqft": 0.73,
"otherrev": 485.2,
"otherrevpercent": 15.72,
"pw": "2019-12-08",
"pwstart": "2019-12-08"
},
{
"avrev": 0,
"avrevpercent": 0,
"conversion": 1,
"fnbrev": 2169,
"fnbrevpercent": 90.91,
"funcrevperbkdsqft": 72.3,
"funcrevpergrprn": 49.71,
"funcrevpersqft": 23.86,
"funcroomrev": 2386,
"grpadr": 140,
"grprn": 48,
"grprnleadvolume": 48,
"grproomrev": 6709,
"occtotalsqft": 0.33,
"otherrev": 216.9,
"otherrevpercent": 9.09,
"pw": "2019-12-15",
"pwstart": "2019-12-15"
},
{
"avrev": 0,
"avrevpercent": null,
"conversion": 1,
"fnbrev": 0,
"fnbrevpercent": null,
"funcrevperbkdsqft": null,
"funcrevpergrprn": 0,
"funcrevpersqft": 0,
"funcroomrev": 0,
"grpadr": 121,
"grprn": 51,
"grprnleadvolume": 51,
"grproomrev": 6179,
"occtotalsqft": 0,
"otherrev": 0,
"otherrevpercent": null,
"pw": "2019-12-22",
"pwstart": "2019-12-22"
},
{
"avrev": 0,
"avrevpercent": null,
"conversion": 1,
"fnbrev": 0,
"fnbrevpercent": null,
"funcrevperbkdsqft": null,
"funcrevpergrprn": 0,
"funcrevpersqft": 0,
"funcroomrev": 0,
"grpadr": 145,
"grprn": 40,
"grprnleadvolume": 40,
"grproomrev": 5782,
"occtotalsqft": 0,
"otherrev": 0,
"otherrevpercent": null,
"pw": "2019-12-29",
"pwstart": "2019-12-29"
}
]try updated query in post 56
yep tried that . It doesn’t bring 2018 data though : All I see 2019 plus “2019-11-26” . seems not taking 1st Dec 2019 in account and also week start by Sunday
[
{
"avrev": 0,
"avrevpercent": 0,
"conversion": 1,
"fnbrev": 0,
"fnbrevpercent": 0,
"funcrevperbkdsqft": 6.62,
"funcrevpergrprn": 37.5,
"funcrevpersqft": 2.25,
"funcroomrev": 225,
"grpadr": 140,
"grprn": 6,
"grprnleadvolume": 6,
"grproomrev": 842,
"lyavrev": 0,
"lyavrevpercent": 0,
"lyconversion": 1,
"lyfnbrev": 5128.9,
"lyfnbrevpercent": 79.17,
"lyfuncrevperbkdsqft": 32.07,
"lyfuncrevpergrprn": 83.05,
"lyfuncrevpersqft": 64.78,
"lyfuncroomrev": 6478,
"lygrpadr": 160,
"lygrprn": 78,
"lygrprnleadvolume": 78,
"lygrproomrev": 12470,
"lyocctotalsqft": 2.02,
"lyotherrev": 1349.32,
"lyotherrevpercent": 20.83,
"occtotalsqft": 0.34,
"otherrev": 225,
"otherrevpercent": 100,
"pwstart": "2019-11-26"
},
{
"avrev": 0,
"avrevpercent": 0,
"conversion": 1,
"fnbrev": 6018,
"fnbrevpercent": 85.12,
"funcrevperbkdsqft": 44.75,
"funcrevpergrprn": 49.1,
"funcrevpersqft": 70.7,
"funcroomrev": 7070,
"grpadr": 163,
"grprn": 144,
"grprnleadvolume": 144,
"grproomrev": 23488,
"lyavrev": 0,
"lyavrevpercent": 0,
"lyconversion": 1,
"lyfnbrev": 15762.07,
"lyfnbrevpercent": 76.58,
"lyfuncrevperbkdsqft": 28.59,
"lyfuncrevpergrprn": 120.36,
"lyfuncrevpersqft": 205.82,
"lyfuncroomrev": 20582,
"lygrpadr": 155,
"lygrprn": 171,
"lygrprnleadvolume": 171,
"lygrproomrev": 26444.88,
"lyocctotalsqft": 7.2,
"lyotherrev": 4820.04,
"lyotherrevpercent": 23.42,
"occtotalsqft": 1.58,
"otherrev": 1051.8,
"otherrevpercent": 14.88,
"pwstart": "2019-12-03"
},
{
"avrev": 250,
"avrevpercent": 8.73,
"conversion": 1,
"fnbrev": 2352,
"fnbrevpercent": 82.17,
"funcrevperbkdsqft": 73.39,
"funcrevpergrprn": 114.49,
"funcrevpersqft": 28.62,
"funcroomrev": 2862,
"grpadr": 144,
"grprn": 25,
"grprnleadvolume": 25,
"grproomrev": 3591,
"lyavrev": 250,
"lyavrevpercent": 2.21,
"lyconversion": 1,
"lyfnbrev": 7880.9,
"lyfnbrevpercent": 69.8,
"lyfuncrevperbkdsqft": 23.92,
"lyfuncrevpergrprn": 91.79,
"lyfuncrevpersqft": 112.9,
"lyfuncroomrev": 11290,
"lygrpadr": 141,
"lygrprn": 123,
"lygrprnleadvolume": 123,
"lygrproomrev": 17337,
"lyocctotalsqft": 4.72,
"lyotherrev": 3159.5,
"lyotherrevpercent": 27.98,
"occtotalsqft": 0.39,
"otherrev": 260.2,
"otherrevpercent": 9.09,
"pwstart": "2019-12-10"
},
{
"avrev": 0,
"avrevpercent": 0,
"conversion": 1,
"fnbrev": 2169,
"fnbrevpercent": 90.91,
"funcrevperbkdsqft": 72.3,
"funcrevpergrprn": 54.22,
"funcrevpersqft": 23.86,
"funcroomrev": 2386,
"grpadr": 140,
"grprn": 44,
"grprnleadvolume": 44,
"grproomrev": 6165,
"lyavrev": 0,
"lyavrevpercent": 0,
"lyconversion": 1,
"lyfnbrev": 5197,
"lyfnbrevpercent": 79.42,
"lyfuncrevperbkdsqft": 32.55,
"lyfuncrevpergrprn": 40.14,
"lyfuncrevpersqft": 65.43,
"lyfuncroomrev": 6543,
"lygrpadr": 121,
"lygrprn": 163,
"lygrprnleadvolume": 163,
"lygrproomrev": 19745,
"lyocctotalsqft": 2.01,
"lyotherrev": 1346.37,
"lyotherrevpercent": 20.58,
"occtotalsqft": 0.33,
"otherrev": 216.9,
"otherrevpercent": 9.09,
"pwstart": "2019-12-17"
},
{
"avrev": 0,
"avrevpercent": null,
"conversion": 1,
"fnbrev": 0,
"fnbrevpercent": null,
"funcrevperbkdsqft": null,
"funcrevpergrprn": 0,
"funcrevpersqft": 0,
"funcroomrev": 0,
"grpadr": 117,
"grprn": 53,
"grprnleadvolume": 53,
"grproomrev": 6179,
"lyavrev": 0,
"lyavrevpercent": 0,
"lyconversion": 1,
"lyfnbrev": 120,
"lyfnbrevpercent": 82.64,
"lyfuncrevperbkdsqft": 36.3,
"lyfuncrevpergrprn": 3.3,
"lyfuncrevpersqft": 1.45,
"lyfuncroomrev": 145,
"lygrpadr": 124,
"lygrprn": 44,
"lygrprnleadvolume": 44,
"lygrproomrev": 5456,
"lyocctotalsqft": 0.04,
"lyotherrev": 25.2,
"lyotherrevpercent": 17.36,
"occtotalsqft": 0,
"otherrev": 0,
"otherrevpercent": null,
"pwstart": "2019-12-24"
},
{
"avrev": 0,
"avrevpercent": null,
"conversion": 1,
"fnbrev": 0,
"fnbrevpercent": null,
"funcrevperbkdsqft": null,
"funcrevpergrprn": 0,
"funcrevpersqft": 0,
"funcroomrev": 0,
"grpadr": 152,
"grprn": 38,
"grprnleadvolume": 38,
"grproomrev": 5782,
"lyavrev": 0,
"lyavrevpercent": null,
"lyconversion": 1,
"lyfnbrev": 0,
"lyfnbrevpercent": null,
"lyfuncrevperbkdsqft": null,
"lyfuncrevpergrprn": 0,
"lyfuncrevpersqft": 0,
"lyfuncroomrev": 0,
"lygrpadr": 156,
"lygrprn": 45,
"lygrprnleadvolume": 45,
"lygrproomrev": 7035,
"lyocctotalsqft": 0,
"lyotherrev": 0,
"lyotherrevpercent": null,
"occtotalsqft": 0,
"otherrev": 0,
"otherrevpercent": null,
"pwstart": "2019-12-31"
}
]