July 11, 2017 at 12:51 pm
Hello All,
I have a Oracle SQL question in which the code fails to count RESV_NAME_ID's. It does count them, but does not do so distinctly. Anybody have any clue why?
Thanks for the help!!
SELECT"RESERVATION_STAT_DAILY"."RESORT" AS "RESORT","RESERVATION_STAT_DAILY"."BUSINESS_DATE" AS "BUSINESS_DATE", to_char("RESERVATION_STAT_DAILY"."BUSINESS_DATE",'MON-yyyy') AS "MONTHYEAR", Extract(day from "RESERVATION_STAT_DAILY"."BUSINESS_DATE") AS "DAY", Extract(month from "RESERVATION_STAT_DAILY"."BUSINESS_DATE") AS "MONTH", Extract(year from "RESERVATION_STAT_DAILY"."BUSINESS_DATE") AS "YEAR", "RESERVATION_STAT_DAILY"."SOURCE_CODE" AS "SOURCE_CODE", "RESERVATION_STAT_DAILY"."MARKET_CODE" AS "MARKET_CODE", "RESERVATION_STAT_DAILY"."RATE_CODE" AS "RATE_CODE", "RESERVATION_STAT_DAILY"."RESV_NAME_ID" AS "RESV_NAME_ID", (CASE WHEN "RESERVATION_STAT_DAILY"."SOURCE_CODE" = 'GDS' AND "RESERVATION_STAT_DAILY"."RATE_CODE" NOT IN ('BKIT', 'EXPEDIA') AND "RESERVATION_STAT_DAILY"."MARKET_CODE" NOT IN ('GOVG', 'ENT') THEN 'GDS' ELSE 'Other'END) AS "BizUnit",COUNT(DISTINCT CASE WHEN "RESERVATION_STAT_DAILY"."SOURCE_CODE" = 'GDS' AND "RESERVATION_STAT_DAILY"."RATE_CODE" NOT IN ('BKIT', 'EXPEDIA') AND "RESERVATION_STAT_DAILY"."MARKET_CODE" NOT IN ('GOVG', 'ENT') THEN "RESERVATION_STAT_DAILY"."RESV_NAME_ID" ELSE NULL END) AS "COST",(SUM("RESERVATION_STAT_DAILY"."BUSINESS_DATE" - "RESERVATION_STAT_DAILY"."BUSINESS_DATE_CREATED")/(COUNT ("RESERVATION_STAT_DAILY"."BUSINESS_DATE_CREATED"))) AS "DIFF",SUM(NVL("RESERVATION_STAT_DAILY"."NIGHTS",0)) AS "NIGHTS",SUM(NVL("RESERVATION_STAT_DAILY"."ROOM_REVENUE",0)) AS "ROOM_REVENUE"FROM "OPERA"."RESERVATION_STAT_DAILY" "RESERVATION_STAT_DAILY"Where RESORT in ('558339','558341','4856','558340','602836','HCA','HZSD', 'TAC') andBUSINESS_DATE < SYSDATE AND EXTRACT(year FROM "RESERVATION_STAT_DAILY"."BUSINESS_DATE_CREATED") >=2016GROUP BY"RESERVATION_STAT_DAILY"."RESORT","RESERVATION_STAT_DAILY"."BUSINESS_DATE", to_char("RESERVATION_STAT_DAILY"."BUSINESS_DATE",'MON-yyyy'), Extract(day from "RESERVATION_STAT_DAILY"."BUSINESS_DATE"), Extract(month from "RESERVATION_STAT_DAILY"."BUSINESS_DATE"), Extract(year from "RESERVATION_STAT_DAILY"."BUSINESS_DATE"), "RESERVATION_STAT_DAILY"."SOURCE_CODE", "RESERVATION_STAT_DAILY"."MARKET_CODE", "RESERVATION_STAT_DAILY"."RATE_CODE", "RESERVATION_STAT_DAILY"."RESV_NAME_ID", ( CASE WHEN (("RESERVATION_STAT_DAILY"."SOURCE_CODE" = 'GDS') AND ("RESERVATION_STAT_DAILY"."RATE_CODE" != 'BKIT' OR "RESERVATION_STAT_DAILY"."RATE_CODE" != 'EXPEDIA')) THEN 'GDS'ELSE 'Other'END )
July 11, 2017 at 1:22 pm
phaenggi - Tuesday, July 11, 2017 12:51 PMHello All,
I have a Oracle SQL question in which the code fails to count RESV_NAME_ID's. It does count them, but does not do so distinctly. Anybody have any clue why?
Thanks for the help!!
SELECT"RESERVATION_STAT_DAILY"."RESORT" AS "RESORT","RESERVATION_STAT_DAILY"."BUSINESS_DATE" AS "BUSINESS_DATE", to_char("RESERVATION_STAT_DAILY"."BUSINESS_DATE",'MON-yyyy') AS "MONTHYEAR", Extract(day from "RESERVATION_STAT_DAILY"."BUSINESS_DATE") AS "DAY", Extract(month from "RESERVATION_STAT_DAILY"."BUSINESS_DATE") AS "MONTH", Extract(year from "RESERVATION_STAT_DAILY"."BUSINESS_DATE") AS "YEAR", "RESERVATION_STAT_DAILY"."SOURCE_CODE" AS "SOURCE_CODE", "RESERVATION_STAT_DAILY"."MARKET_CODE" AS "MARKET_CODE", "RESERVATION_STAT_DAILY"."RATE_CODE" AS "RATE_CODE", "RESERVATION_STAT_DAILY"."RESV_NAME_ID" AS "RESV_NAME_ID", (CASE WHEN "RESERVATION_STAT_DAILY"."SOURCE_CODE" = 'GDS' AND "RESERVATION_STAT_DAILY"."RATE_CODE" NOT IN ('BKIT', 'EXPEDIA') AND "RESERVATION_STAT_DAILY"."MARKET_CODE" NOT IN ('GOVG', 'ENT') THEN 'GDS' ELSE 'Other'END) AS "BizUnit",COUNT(DISTINCT CASE WHEN "RESERVATION_STAT_DAILY"."SOURCE_CODE" = 'GDS' AND "RESERVATION_STAT_DAILY"."RATE_CODE" NOT IN ('BKIT', 'EXPEDIA') AND "RESERVATION_STAT_DAILY"."MARKET_CODE" NOT IN ('GOVG', 'ENT') THEN "RESERVATION_STAT_DAILY"."RESV_NAME_ID" ELSE NULL END) AS "COST",(SUM("RESERVATION_STAT_DAILY"."BUSINESS_DATE" - "RESERVATION_STAT_DAILY"."BUSINESS_DATE_CREATED")/(COUNT ("RESERVATION_STAT_DAILY"."BUSINESS_DATE_CREATED"))) AS "DIFF",SUM(NVL("RESERVATION_STAT_DAILY"."NIGHTS",0)) AS "NIGHTS",SUM(NVL("RESERVATION_STAT_DAILY"."ROOM_REVENUE",0)) AS "ROOM_REVENUE"FROM "OPERA"."RESERVATION_STAT_DAILY" "RESERVATION_STAT_DAILY"Where RESORT in ('558339','558341','4856','558340','602836','HCA','HZSD', 'TAC') andBUSINESS_DATE < SYSDATE AND EXTRACT(year FROM "RESERVATION_STAT_DAILY"."BUSINESS_DATE_CREATED") >=2016GROUP BY"RESERVATION_STAT_DAILY"."RESORT","RESERVATION_STAT_DAILY"."BUSINESS_DATE", to_char("RESERVATION_STAT_DAILY"."BUSINESS_DATE",'MON-yyyy'), Extract(day from "RESERVATION_STAT_DAILY"."BUSINESS_DATE"), Extract(month from "RESERVATION_STAT_DAILY"."BUSINESS_DATE"), Extract(year from "RESERVATION_STAT_DAILY"."BUSINESS_DATE"), "RESERVATION_STAT_DAILY"."SOURCE_CODE", "RESERVATION_STAT_DAILY"."MARKET_CODE", "RESERVATION_STAT_DAILY"."RATE_CODE", "RESERVATION_STAT_DAILY"."RESV_NAME_ID", ( CASE WHEN (("RESERVATION_STAT_DAILY"."SOURCE_CODE" = 'GDS') AND ("RESERVATION_STAT_DAILY"."RATE_CODE" != 'BKIT' OR "RESERVATION_STAT_DAILY"."RATE_CODE" != 'EXPEDIA')) THEN 'GDS'ELSE 'Other'END )
I have no idea. You didn't post any sample data, or anything that we could use to understand the problem. You also posted in a SQL Server forum when this is related to Oracle. Finally, you didn't even bothered to format your code.
If someone else is interested on helping, here's a version with some format.
SELECT RSD.RESORT AS RESORT,
RSD.BUSINESS_DATE AS BUSINESS_DATE,
to_char(RSD.BUSINESS_DATE,'MON-yyyy') AS MONTHYEAR,
Extract(day from RSD.BUSINESS_DATE) AS DAY,
Extract(month from RSD.BUSINESS_DATE) AS MONTH,
Extract(year from RSD.BUSINESS_DATE) AS YEAR,
RSD.SOURCE_CODE AS SOURCE_CODE,
RSD.MARKET_CODE AS MARKET_CODE,
RSD.RATE_CODE AS RATE_CODE,
RSD.RESV_NAME_ID AS RESV_NAME_ID,
(CASE WHEN RSD.SOURCE_CODE = 'GDS'
AND RSD.RATE_CODE NOT IN ('BKIT','EXPEDIA')
AND RSD.MARKET_CODE NOT IN ('GOVG','ENT')
THEN 'GDS'
ELSE 'Other'END) AS BizUnit,
COUNT(DISTINCT CASE WHEN RSD.SOURCE_CODE = 'GDS'
AND RSD.RATE_CODE NOT IN ('BKIT', 'EXPEDIA')
AND RSD.MARKET_CODE NOT IN ('GOVG','ENT')
THEN RSD.RESV_NAME_ID END) AS COST,
SUM(RSD.BUSINESS_DATE - RSD.BUSINESS_DATE_CREATED)/COUNT(RSD.BUSINESS_DATE_CREATED) AS DIFF,
SUM(NVL(RSD.NIGHTS,0)) AS NIGHTS,
SUM(NVL(RSD.ROOM_REVENUE,0)) AS ROOM_REVENUE
FROM OPERA.RESERVATION_STAT_DAILY RSD
Where RESORT in ('558339','558341','4856','558340','602836','HCA','HZSD','TAC')
AND BUSINESS_DATE < SYSDATE
AND EXTRACT(year FROM RSD.BUSINESS_DATE_CREATED) >=2016
GROUP BYRSD.RESORT,
RSD.BUSINESS_DATE,
RSD.SOURCE_CODE,
RSD.MARKET_CODE,
RSD.RATE_CODE,
RSD.RESV_NAME_ID,
CASE WHEN RSD.SOURCE_CODE = 'GDS'
AND RSD.RATE_CODE NOT IN ('BKIT','EXPEDIA')
AND RSD.MARKET_CODE NOT IN ('GOVG','ENT')
THEN 'GDS'
ELSE 'Other'END;
July 11, 2017 at 4:28 pm
Luis Cazares - Tuesday, July 11, 2017 1:22 PMphaenggi - Tuesday, July 11, 2017 12:51 PMHello All,
I have a Oracle SQL question in which the code fails to count RESV_NAME_ID's. It does count them, but does not do so distinctly. Anybody have any clue why?
Thanks for the help!!
SELECT"RESERVATION_STAT_DAILY"."RESORT" AS "RESORT","RESERVATION_STAT_DAILY"."BUSINESS_DATE" AS "BUSINESS_DATE", to_char("RESERVATION_STAT_DAILY"."BUSINESS_DATE",'MON-yyyy') AS "MONTHYEAR", Extract(day from "RESERVATION_STAT_DAILY"."BUSINESS_DATE") AS "DAY", Extract(month from "RESERVATION_STAT_DAILY"."BUSINESS_DATE") AS "MONTH", Extract(year from "RESERVATION_STAT_DAILY"."BUSINESS_DATE") AS "YEAR", "RESERVATION_STAT_DAILY"."SOURCE_CODE" AS "SOURCE_CODE", "RESERVATION_STAT_DAILY"."MARKET_CODE" AS "MARKET_CODE", "RESERVATION_STAT_DAILY"."RATE_CODE" AS "RATE_CODE", "RESERVATION_STAT_DAILY"."RESV_NAME_ID" AS "RESV_NAME_ID", (CASE WHEN "RESERVATION_STAT_DAILY"."SOURCE_CODE" = 'GDS' AND "RESERVATION_STAT_DAILY"."RATE_CODE" NOT IN ('BKIT', 'EXPEDIA') AND "RESERVATION_STAT_DAILY"."MARKET_CODE" NOT IN ('GOVG', 'ENT') THEN 'GDS' ELSE 'Other'END) AS "BizUnit",COUNT(DISTINCT CASE WHEN "RESERVATION_STAT_DAILY"."SOURCE_CODE" = 'GDS' AND "RESERVATION_STAT_DAILY"."RATE_CODE" NOT IN ('BKIT', 'EXPEDIA') AND "RESERVATION_STAT_DAILY"."MARKET_CODE" NOT IN ('GOVG', 'ENT') THEN "RESERVATION_STAT_DAILY"."RESV_NAME_ID" ELSE NULL END) AS "COST",(SUM("RESERVATION_STAT_DAILY"."BUSINESS_DATE" - "RESERVATION_STAT_DAILY"."BUSINESS_DATE_CREATED")/(COUNT ("RESERVATION_STAT_DAILY"."BUSINESS_DATE_CREATED"))) AS "DIFF",SUM(NVL("RESERVATION_STAT_DAILY"."NIGHTS",0)) AS "NIGHTS",SUM(NVL("RESERVATION_STAT_DAILY"."ROOM_REVENUE",0)) AS "ROOM_REVENUE"FROM "OPERA"."RESERVATION_STAT_DAILY" "RESERVATION_STAT_DAILY"Where RESORT in ('558339','558341','4856','558340','602836','HCA','HZSD', 'TAC') andBUSINESS_DATE < SYSDATE AND EXTRACT(year FROM "RESERVATION_STAT_DAILY"."BUSINESS_DATE_CREATED") >=2016GROUP BY"RESERVATION_STAT_DAILY"."RESORT","RESERVATION_STAT_DAILY"."BUSINESS_DATE", to_char("RESERVATION_STAT_DAILY"."BUSINESS_DATE",'MON-yyyy'), Extract(day from "RESERVATION_STAT_DAILY"."BUSINESS_DATE"), Extract(month from "RESERVATION_STAT_DAILY"."BUSINESS_DATE"), Extract(year from "RESERVATION_STAT_DAILY"."BUSINESS_DATE"), "RESERVATION_STAT_DAILY"."SOURCE_CODE", "RESERVATION_STAT_DAILY"."MARKET_CODE", "RESERVATION_STAT_DAILY"."RATE_CODE", "RESERVATION_STAT_DAILY"."RESV_NAME_ID", ( CASE WHEN (("RESERVATION_STAT_DAILY"."SOURCE_CODE" = 'GDS') AND ("RESERVATION_STAT_DAILY"."RATE_CODE" != 'BKIT' OR "RESERVATION_STAT_DAILY"."RATE_CODE" != 'EXPEDIA')) THEN 'GDS'ELSE 'Other'END )
I have no idea. You didn't post any sample data, or anything that we could use to understand the problem. You also posted in a SQL Server forum when this is related to Oracle. Finally, you didn't even bothered to format your code.
If someone else is interested on helping, here's a version with some format.
SELECT RSD.RESORT AS RESORT,
RSD.BUSINESS_DATE AS BUSINESS_DATE,
to_char(RSD.BUSINESS_DATE,'MON-yyyy') AS MONTHYEAR,
Extract(day from RSD.BUSINESS_DATE) AS DAY,
Extract(month from RSD.BUSINESS_DATE) AS MONTH,
Extract(year from RSD.BUSINESS_DATE) AS YEAR,
RSD.SOURCE_CODE AS SOURCE_CODE,
RSD.MARKET_CODE AS MARKET_CODE,
RSD.RATE_CODE AS RATE_CODE,
RSD.RESV_NAME_ID AS RESV_NAME_ID,
(CASE WHEN RSD.SOURCE_CODE = 'GDS'
AND RSD.RATE_CODE NOT IN ('BKIT','EXPEDIA')
AND RSD.MARKET_CODE NOT IN ('GOVG','ENT')
THEN 'GDS'
ELSE 'Other'END) AS BizUnit,
COUNT(DISTINCT CASE WHEN RSD.SOURCE_CODE = 'GDS'
AND RSD.RATE_CODE NOT IN ('BKIT', 'EXPEDIA')
AND RSD.MARKET_CODE NOT IN ('GOVG','ENT')
THEN RSD.RESV_NAME_ID END) AS COST,
SUM(RSD.BUSINESS_DATE - RSD.BUSINESS_DATE_CREATED)/COUNT(RSD.BUSINESS_DATE_CREATED) AS DIFF,
SUM(NVL(RSD.NIGHTS,0)) AS NIGHTS,
SUM(NVL(RSD.ROOM_REVENUE,0)) AS ROOM_REVENUE
FROM OPERA.RESERVATION_STAT_DAILY RSD
Where RESORT in ('558339','558341','4856','558340','602836','HCA','HZSD','TAC')
AND BUSINESS_DATE < SYSDATE
AND EXTRACT(year FROM RSD.BUSINESS_DATE_CREATED) >=2016
GROUP BYRSD.RESORT,
RSD.BUSINESS_DATE,
RSD.SOURCE_CODE,
RSD.MARKET_CODE,
RSD.RATE_CODE,
RSD.RESV_NAME_ID,
CASE WHEN RSD.SOURCE_CODE = 'GDS'
AND RSD.RATE_CODE NOT IN ('BKIT','EXPEDIA')
AND RSD.MARKET_CODE NOT IN ('GOVG','ENT')
THEN 'GDS'
ELSE 'Other'END;
Well, aren't you a bowl of sunshine! Was my first post on here, so forgive me for not knowing everything Frenchy!
July 12, 2017 at 7:55 am
phaenggi - Tuesday, July 11, 2017 4:28 PMWell, aren't you a bowl of sunshine! Was my first post on here, so forgive me for not knowing everything Frenchy!
I'm sorry, I didn't have the best day as stress kept building up. The ways weren't adequate, but the comments are true. The syntax between Oracle and SQL Server changes enough to make it impossible to test functionality from one on the other. With no sample data and expected results, there's not much help that can be given. You can read the second article in my signature to get an idea of how to post a SQL question on a forum.
For Oracle answers, you might find better help at https://community.oracle.com/welcome
And finally, formatting the code and making it readable is basic developer's etiquette. You don't need to comply to certain standards when posting on the web, but you need to make it, at least, readable.
July 12, 2017 at 8:20 am
Luis Cazares - Wednesday, July 12, 2017 7:55 AMphaenggi - Tuesday, July 11, 2017 4:28 PMWell, aren't you a bowl of sunshine! Was my first post on here, so forgive me for not knowing everything Frenchy!I'm sorry, I didn't have the best day as stress kept building up. The ways weren't adequate, but the comments are true. The syntax between Oracle and SQL Server changes enough to make it impossible to test functionality from one on the other. With no sample data and expected results, there's not much help that can be given. You can read the second article in my signature to get an idea of how to post a SQL question on a forum.
For Oracle answers, you might find better help at https://community.oracle.com/welcome
And finally, formatting the code and making it readable is basic developer's etiquette. You don't need to comply to certain standards when posting on the web, but you need to make it, at least, readable.
Luis, I understand. I thought when I posted this in the text box that it formatted the code automatically for me, without having to go to an outside source. Other message boards that I frequent have that ability. I forgot to check to see how it looked. I was just hoping another eye might catch something wrong with the syntax. However, it was discovered there was a bug in Oracle that has now been fixed and the code works fine. Thanks for the extra tips!
July 12, 2017 at 1:28 pm
phaenggi - Tuesday, July 11, 2017 4:28 PMLuis Cazares - Tuesday, July 11, 2017 1:22 PMphaenggi - Tuesday, July 11, 2017 12:51 PMHello All,
I have a Oracle SQL question in which the code fails to count RESV_NAME_ID's. It does count them, but does not do so distinctly. Anybody have any clue why?
Thanks for the help!!
SELECT"RESERVATION_STAT_DAILY"."RESORT" AS "RESORT","RESERVATION_STAT_DAILY"."BUSINESS_DATE" AS "BUSINESS_DATE", to_char("RESERVATION_STAT_DAILY"."BUSINESS_DATE",'MON-yyyy') AS "MONTHYEAR", Extract(day from "RESERVATION_STAT_DAILY"."BUSINESS_DATE") AS "DAY", Extract(month from "RESERVATION_STAT_DAILY"."BUSINESS_DATE") AS "MONTH", Extract(year from "RESERVATION_STAT_DAILY"."BUSINESS_DATE") AS "YEAR", "RESERVATION_STAT_DAILY"."SOURCE_CODE" AS "SOURCE_CODE", "RESERVATION_STAT_DAILY"."MARKET_CODE" AS "MARKET_CODE", "RESERVATION_STAT_DAILY"."RATE_CODE" AS "RATE_CODE", "RESERVATION_STAT_DAILY"."RESV_NAME_ID" AS "RESV_NAME_ID", (CASE WHEN "RESERVATION_STAT_DAILY"."SOURCE_CODE" = 'GDS' AND "RESERVATION_STAT_DAILY"."RATE_CODE" NOT IN ('BKIT', 'EXPEDIA') AND "RESERVATION_STAT_DAILY"."MARKET_CODE" NOT IN ('GOVG', 'ENT') THEN 'GDS' ELSE 'Other'END) AS "BizUnit",COUNT(DISTINCT CASE WHEN "RESERVATION_STAT_DAILY"."SOURCE_CODE" = 'GDS' AND "RESERVATION_STAT_DAILY"."RATE_CODE" NOT IN ('BKIT', 'EXPEDIA') AND "RESERVATION_STAT_DAILY"."MARKET_CODE" NOT IN ('GOVG', 'ENT') THEN "RESERVATION_STAT_DAILY"."RESV_NAME_ID" ELSE NULL END) AS "COST",(SUM("RESERVATION_STAT_DAILY"."BUSINESS_DATE" - "RESERVATION_STAT_DAILY"."BUSINESS_DATE_CREATED")/(COUNT ("RESERVATION_STAT_DAILY"."BUSINESS_DATE_CREATED"))) AS "DIFF",SUM(NVL("RESERVATION_STAT_DAILY"."NIGHTS",0)) AS "NIGHTS",SUM(NVL("RESERVATION_STAT_DAILY"."ROOM_REVENUE",0)) AS "ROOM_REVENUE"FROM "OPERA"."RESERVATION_STAT_DAILY" "RESERVATION_STAT_DAILY"Where RESORT in ('558339','558341','4856','558340','602836','HCA','HZSD', 'TAC') andBUSINESS_DATE < SYSDATE AND EXTRACT(year FROM "RESERVATION_STAT_DAILY"."BUSINESS_DATE_CREATED") >=2016GROUP BY"RESERVATION_STAT_DAILY"."RESORT","RESERVATION_STAT_DAILY"."BUSINESS_DATE", to_char("RESERVATION_STAT_DAILY"."BUSINESS_DATE",'MON-yyyy'), Extract(day from "RESERVATION_STAT_DAILY"."BUSINESS_DATE"), Extract(month from "RESERVATION_STAT_DAILY"."BUSINESS_DATE"), Extract(year from "RESERVATION_STAT_DAILY"."BUSINESS_DATE"), "RESERVATION_STAT_DAILY"."SOURCE_CODE", "RESERVATION_STAT_DAILY"."MARKET_CODE", "RESERVATION_STAT_DAILY"."RATE_CODE", "RESERVATION_STAT_DAILY"."RESV_NAME_ID", ( CASE WHEN (("RESERVATION_STAT_DAILY"."SOURCE_CODE" = 'GDS') AND ("RESERVATION_STAT_DAILY"."RATE_CODE" != 'BKIT' OR "RESERVATION_STAT_DAILY"."RATE_CODE" != 'EXPEDIA')) THEN 'GDS'ELSE 'Other'END )I have no idea. You didn't post any sample data, or anything that we could use to understand the problem. You also posted in a SQL Server forum when this is related to Oracle. Finally, you didn't even bothered to format your code.
If someone else is interested on helping, here's a version with some format.
SELECT RSD.RESORT AS RESORT,
RSD.BUSINESS_DATE AS BUSINESS_DATE,
to_char(RSD.BUSINESS_DATE,'MON-yyyy') AS MONTHYEAR,
Extract(day from RSD.BUSINESS_DATE) AS DAY,
Extract(month from RSD.BUSINESS_DATE) AS MONTH,
Extract(year from RSD.BUSINESS_DATE) AS YEAR,
RSD.SOURCE_CODE AS SOURCE_CODE,
RSD.MARKET_CODE AS MARKET_CODE,
RSD.RATE_CODE AS RATE_CODE,
RSD.RESV_NAME_ID AS RESV_NAME_ID,
(CASE WHEN RSD.SOURCE_CODE = 'GDS'
AND RSD.RATE_CODE NOT IN ('BKIT','EXPEDIA')
AND RSD.MARKET_CODE NOT IN ('GOVG','ENT')
THEN 'GDS'
ELSE 'Other'END) AS BizUnit,
COUNT(DISTINCT CASE WHEN RSD.SOURCE_CODE = 'GDS'
AND RSD.RATE_CODE NOT IN ('BKIT', 'EXPEDIA')
AND RSD.MARKET_CODE NOT IN ('GOVG','ENT')
THEN RSD.RESV_NAME_ID END) AS COST,
SUM(RSD.BUSINESS_DATE - RSD.BUSINESS_DATE_CREATED)/COUNT(RSD.BUSINESS_DATE_CREATED) AS DIFF,
SUM(NVL(RSD.NIGHTS,0)) AS NIGHTS,
SUM(NVL(RSD.ROOM_REVENUE,0)) AS ROOM_REVENUE
FROM OPERA.RESERVATION_STAT_DAILY RSD
Where RESORT in ('558339','558341','4856','558340','602836','HCA','HZSD','TAC')
AND BUSINESS_DATE < SYSDATE
AND EXTRACT(year FROM RSD.BUSINESS_DATE_CREATED) >=2016
GROUP BYRSD.RESORT,
RSD.BUSINESS_DATE,
RSD.SOURCE_CODE,
RSD.MARKET_CODE,
RSD.RATE_CODE,
RSD.RESV_NAME_ID,
CASE WHEN RSD.SOURCE_CODE = 'GDS'
AND RSD.RATE_CODE NOT IN ('BKIT','EXPEDIA')
AND RSD.MARKET_CODE NOT IN ('GOVG','ENT')
THEN 'GDS'
ELSE 'Other'END;
Well, aren't you a bowl of sunshine! Was my first post on here, so forgive me for not knowing everything Frenchy!
Really? You go to a forum NOT for Oracle, and get ticked off because you were called out for not even bothering to format your code so it's easily readable? Really??? I don't care if it's your first post or your last. Luis is one of the top contributors here, so take your high-falutin' attitude somewhere else. Volunteers don't need to put up with that.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply