If all the fields referenced by query part of the index it uses the data from index (covered index//query) and avoids Fetch whole document.
If not it needs to Fetch the document that will be over head.
Do EXPLAIN on query it tells
1 Like
So If I have 3 regular metrics coming directly from 3 keys from document you are CB recommends those to be part of Index including its dimensions (which is predicates ) ?
And I assume if I have 3 more derived metrics on top of regular metrics then I don’t need to create separate index on them correct ?
Hi @vsr1 : I have small challenges here : I have modified this query like below as you have suggested to other thread –
But I am missing the PatternDates for this years and last years because its returning single records . looks like this is happening because of [0] after lyear and cyear but how do I rewrite to add this group by this year dates and last year dates so i will get each document by year and corresponding metrics ? I am thinking because of the grain this vs last year I may need to write UNION . Am I going to right direction ?
SELECT cyear.patterndate AS patterndate ,
cyear.funcroomrev AS funcroomrev ,
cyear.fnbrev AS fnbrev,
cyear.avrev AS avrev,
cyear.otherrev AS otherrev,
cyear.funcrevpergrprn AS funcrevpergrprn,
cyear.funcrevperbkdsqft AS funcrevperbkdsqft,
cyear.funcrevpersqft AS funcrevpersqft,
cyear.grprnleadvolume AS grprnleadvolume,
cyear.grproomrev AS grproomrev,
cyear.grprn AS grprn,
cyear.grpadr AS grpadr,
cyear.conversion AS conversion,
lyear.lyfuncroomrev AS lyfuncroomrev ,
lyear.lyfnbrev AS lyfnbrev,
lyear.lyavrev AS lyavrev,
lyear.lyotherrev AS lyotherrev,
lyear.lyfuncrevpergrprn AS lyfuncrevpergrprn,
lyear.lyfuncrevperbkdsqft AS lyfuncrevperbkdsqft,
lyear.lyfuncrevpersqft AS lyfuncrevpersqft,
lyear.lygrprnleadvolume AS lygrprnleadvolume,
lyear.lygrproomrev AS lygrproomrev,
lyear.lygrprn AS lygrprn,
lyear.lygrpadr AS lygrpadr,
lyear.lyconversion AS lyconversion
LET cyear = (SELECT M.PatternDate patterndate,
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 '2018-11-01' AND '2018-11-04'
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
M.PatternDate
ORDER BY M.PatternDate
)[0],
lyear = (SELECT M.PatternDate patterndate,
ROUND(SUM(M.BlendedEventRevenueTotal),2) 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),2) 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('2018-11-01',-365,"day") AND DATE_ADD_STR('2018-11-04',-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
M.PatternDate
ORDER BY M.PatternDate
)[0]@vsr1 - Sorry I take it back … I guess UNION will not solve problem as metrics are in different name for CY vs LY and also dates should be in different document . NOt sure how can I reuse this queries . Also I am in CB 6.0 EE so not sure if I can use TimeSeries function .
SELECT
MIN([p.patterndate,p])[1] AS ly,
MAX([p.patterndate,p])[1] AS cy
FROM (SELECT M.PatternDate patterndate,
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 '2018-11-01' AND '2018-11-04' OR
A.PatternDate BETWEEN DATE_ADD_STR('2018-11-01',-1,"year") AND DATE_ADD_STR('2018-11-04',-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
GROUP BY M.PatternDate) AS p
GROUP BY SUBSTR(p.patterndate,5);Hi @vsr1 This works great and I see how attributes under CY and LY is nested in same document but for my scenario where I am going to use this in middleware App I need that LY and CY column names directly to be part of the select itself .
MIN([p.patterndate, {"lyfuncroomrev" :p.funcroomrev, "lyfnbrev":p.fnbrev, .....}])[1].*,
MAX([p.patterndate,p])[1].*
Or do another level of query and project the way you want
MIN throws Syntax error as is :
If below then just shows 3 columns instead of other 3 LY keys values which is missing :
SELECT
MIN([p.patterndate, {lyfuncroomrev :p.funcroomrev, lyfnbrev:p.fnbrev}])[1].*,
MAX([p.patterndate,p])[1].*
FROM (SELECT M.PatternDate patterndate,
ROUND(SUM(M.BlendedEventRevenueTotal)) funcroomrev,
ROUND(SUM(M.FBRevenueTotal),2) fnbrev
FROM ( SELECT MAX(A.SnapshotDate) AS SnapshotDate,
A.ExternalBookingId AS ExternalBookingId,
A.PatternDate
FROM group360all AS A
WHERE (A.PatternDate BETWEEN '2018-11-01' AND '2018-11-04' OR
A.PatternDate BETWEEN DATE_ADD_STR('2018-11-01',-1,"year") AND DATE_ADD_STR('2018-11-04',-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
GROUP BY M.PatternDate) AS p
GROUP BY SUBSTR(p.patterndate,5);
SELECT
MIN([p.patterndate,{"lyfuncroomrev":p.funcroomrev, "lyfnbrev":p.fnbrev, "lyfnbrevpercent":p.fnbrevpercent,
"lyavrev":p.avrev, "lyavrevpercent": p.avrevpercent, "lyotherrev":p.otherrev, "lyotherrevpercent":p.otherrevpercent,
"lyfuncrevpergrprn":p.funcrevpergrprn, "lyfuncrevperbkdsqft":p.funcrevperbkdsqft, "lyfuncrevpersqft":p.funcrevpersqft,
"lygrprnleadvolume":p.grprnleadvolume, "lyocctotalsqft": p.occtotalsqft, "lygrproomrev":p.grproomrev,
"lygrprn":p.grprn, "lygrpadr":p.grpadr, "lyconversion":p.conversion}])[1].*
MAX([p.patterndate,p])[1].*
FROM (SELECT M.PatternDate patterndate,
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 '2018-11-01' AND '2018-11-04' OR
A.PatternDate BETWEEN DATE_ADD_STR('2018-11-01',-365,"day") AND DATE_ADD_STR('2018-11-04',-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 M.PatternDate) AS p
GROUP BY SUBSTR(p.patterndate,5);
1 Like
Super ! Fantastic …
Understanding the different way of query capabilities in CB and its vast .
thanks for helping out .
For benefit of others just adding one point :
If you are planning to put this query in N1QL and want to use python F string we need wrap the flower braces { } in first select with another braces like {{“lyfuncroomrev”:p.funcroomrev }} . BUT DONT Add this in @vsr1 actual query as it will throw error . I just added to accept braces in my F string query
Also for Last Year data to be returned as well I have added lypatterndate as below in select :
SELECT
MIN([p.patterndate,{{'lypatterndate':p.patterndate,"lyfuncroomrev":p.funcroomrev, "lyfnbrev":p.fnbrev, "lyfnbrevpercent":p.fnbrevpercent,
"lyavrev":p.avrev, "lyavrevpercent": p.avrevpercent, "lyotherrev":p.otherrev, "lyotherrevpercent":p.otherrevpercent,
"lyfuncrevpergrprn":p.funcrevpergrprn, "lyfuncrevperbkdsqft":p.funcrevperbkdsqft, "lyfuncrevpersqft":p.funcrevpersqft, "lygrprnleadvolume":p.grprnleadvolume, "lyocctotalsqft": p.occtotalsqft, "lygrproomrev":p.grproomrev,
"lygrprn":p.grprn, "lygrpadr":p.grpadr, "lyconversion":p.conversion}}])[1].*,
MAX([p.patterndate,p])[1].*hi @vsr1
Sorry can’t get my head around to make this query return the data group by DATE_PART_STR : basically rather than date I want to get by month and by week so for month I have changed to below query and seems it doesn’t splitting the records by group as month … It is giving single record : anything I am doing wrong ? Ideally I should see 4 records for total 4 months …
So does it mean for date , month , week I need separate separate query ?
SELECT
MIN([p.patternmonth,{'lypatternmonth':p.patternmonth,"lyfuncroomrev":p.funcroomrev, "lyfnbrev":p.fnbrev, "lyfnbrevpercent":p.fnbrevpercent,
"lyavrev":p.avrev, "lyavrevpercent": p.avrevpercent, "lyotherrev":p.otherrev, "lyotherrevpercent":p.otherrevpercent,
"lyfuncrevpergrprn":p.funcrevpergrprn, "lyfuncrevperbkdsqft":p.funcrevperbkdsqft, "lyfuncrevpersqft":p.funcrevpersqft,
"lygrprnleadvolume":p.grprnleadvolume, "lyocctotalsqft": p.occtotalsqft, "lygrproomrev":p.grproomrev,
"lygrprn":p.grprn, "lygrpadr":p.grpadr, "lyconversion":p.conversion}])[1].*,
MAX([p.patternmonth,p])[1].*
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-09-01' AND '2019-12-04' OR
A.PatternDate BETWEEN DATE_ADD_STR('2019-09-01',-365,"day")
AND DATE_ADD_STR('2019-12-04',-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 SUBSTR(p.patternmonth,5)
ORDER BY SUBSTR(p.patternmonth,5)Never mind , I got it .
DATE_PART_STR(M.PatternDate,“month”) patternmonth
gives number. substr on number returns null.
GROUP BY DATE_PART_STR(M.PatternDate,“month”) ==> this will not allow last year and current year.
If you want by month may be explore DATE_TRUNC_STR(‘2016-05-18T03:59:00Z’, ‘month’) OR SUBSTR(date,0,7)
OR GROUP BY DATE_PART_STR(M.PatternDate,“year”), DATE_PART_STR(M.PatternDate,“month”)
https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/datefun.html#fn-date-trunc-str
I have removed last two group by and removed min, max after first select and this seems working for me
Hi @vsr1 - we have challenge to make the LY metrics in the same records rather than splitting up into different records . In your query it seems because of the group by Pattern date we can’t show the LY metrics in single records . Can we able to achieve that ?
For e.g. below returning two records (one for CY and other for LY) : I just need one records for CY and just have LY metrics there as well … I just dont need lypatterndate in same records … I tried commenting our below and doesn’t work
SELECT
([p.patterndate,{"lyfuncroomrev":p.funcroomrev, "lyfnbrev":p.fnbrev, "lyfnbrevpercent":p.fnbrevpercent,
"lyavrev":p.avrev, "lyavrevpercent": p.avrevpercent, "lyotherrev":p.otherrev, "lyotherrevpercent":p.otherrevpercent,
"lyfuncrevpergrprn":p.funcrevpergrprn, "lyfuncrevperbkdsqft":p.funcrevperbkdsqft, "lyfuncrevpersqft":p.funcrevpersqft,
"lygrprnleadvolume":p.grprnleadvolume, "lyocctotalsqft": p.occtotalsqft, "lygrproomrev":p.grproomrev,
"lygrprn":p.grprn, "lygrpadr":p.grpadr, "lyconversion":p.conversion}])[1].*
/* ([p.patterndate,p])[1].* */
FROM (SELECT DATE_TRUNC_STR(M.PatternDate,"month") patterndate,
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' OR
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
ORDER BY p.patterndateSELECT
MIN([p.patternyear, {"lypatternmonth":p.patternmonth,"lyfuncroomrev":p.funcroomrev, "lyfnbrev":p.fnbrev,
"lyfnbrevpercent":p.fnbrevpercent, "lyavrev":p.avrev, "lyavrevpercent": p.avrevpercent,
"lyotherrev":p.otherrev, "lyotherrevpercent":p.otherrevpercent,
"lyfuncrevpergrprn":p.funcrevpergrprn, "lyfuncrevperbkdsqft":p.funcrevperbkdsqft,
"lyfuncrevpersqft":p.funcrevpersqft, "lygrprnleadvolume":p.grprnleadvolume,
"lyocctotalsqft": p.occtotalsqft, "lygrproomrev":p.grproomrev,
"lygrprn":p.grprn, "lygrpadr":p.grpadr, "lyconversion":p.conversion}])[1].*,
MAX([p.patternyear, OBJECT_REMOVE(p,"patternyear")])[1].*
FROM (SELECT
DATE_TRUNC_STR(M.PatternDate,"year") patternyear,
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' OR
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,"year"), DATE_TRUNC_STR(M.PatternDate,"month")) AS p
GROUP BY p.patternmonth
ORDER BY p.patternmonth;saying : syntax error - at * … What I am missing here
Fixed syntax error. The solution MIN/MAX GROUP will work when you have perfect data. i.e. when you have both years data for every month.
In reality that might not be the case. See if my next post works.
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;that is correct assumption … In practical some month may not have data …
the latest query returns no records however we have the data for those months.
I changed from Sep 2019 records …no records either