Hey everyone,
I'm new to the forum, so hopefully I'm posting in the correct area. I have a sql question for you in which the code fails to count distinct ID's. It does count them, but not distinctly. I have provided a small snippet of code below and have bolded where I am trying to count distinct. I have no idea why this is not working and would love to here some insight/solutions for this, so that I may count these ID's distinctly. I'm really really stumped here!
Thank you very much!
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" != 'BKIT' OR "RESERVATION_STAT_DAILY"."RATE_CODE" != 'EXPEDIA'
)) THEN 'GDS'
ELSE 'Other'
END ) AS "BizUnit",
(CASE
WHEN (("RESERVATION_STAT_DAILY"."SOURCE_CODE" = 'GDS') AND ("RESERVATION_STAT_DAILY"."RATE_CODE" != 'BKIT' OR "RESERVATION_STAT_DAILY"."RATE_CODE" != 'EXPEDIA'
)) THEN COUNT(DISTINCT "RESERVATION_STAT_DAILY"."RESV_NAME_ID")
ELSE COUNT(DISTINCT "RESERVATION_STAT_DAILY"."RESV_NAME_ID")
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') and
BUSINESS_DATE < SYSDATE AND EXTRACT(year FROM "RESERVATION_STAT_DAILY"."BUSINESS_DATE_CREATED") >=2016
GROUP 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 )