WolfieeifloW WolfieeifloW -4 years ago 86
SQL Question

SQL Join Doubling Some Output

I have the following query:

SELECT r.reservation_number, r.rate_type, r.room_type_code, r.room_code, t.client_code, SUM(room_revenue + package_revenue + other_charge_revenue + spa_revenue) AS revenue_total
FROM nw_src.dbo.fdresrev r
JOIN nw_src.dbo.fdres_ta_c t ON r.reservation_number = t.reservation_number
GROUP BY r.reservation_number, r.rate_type, r.room_type_code, r.room_code, t.client_code
ORDER BY r.reservation_number


That returns the results (Minus a field I need) exactly how I want them to look:

reservation_number | rate_type | room_type_code | room_code | client_code | revenue_total
10002 allincl 1cath 1cath 90 1520
10003 allincl 1dollh 1dollh 90 2001
10008 allincl 1cath 1cath 90 1355


The issue is I need an additional field from a different table. This other table can have multiple records for any given
reservation_number
. So for example, it has two records for
10002
and
10008
, but only one record for
10003
.

I have tried the following query to get what I want from this table:

SELECT r.reservation_number, b.created_date_time, r.rate_type, r.room_type_code, r.room_code, t.client_code, SUM(room_revenue + package_revenue + other_charge_revenue + spa_revenue) AS revenue_total
FROM nw_src.dbo.fdresrev r
JOIN nw_src.dbo.fdres_ta_c t ON r.reservation_number = t.reservation_number
JOIN nw_src.dbo.fdroomresc b ON r.reservation_number = b.reservation_number
GROUP BY r.reservation_number, b.created_date_time, r.rate_type, r.room_type_code, r.room_code, t.client_code
ORDER BY r.reservation_number


This however seems to double the
revenue_total
of any record that has multiple records in
fdroomresc
(In this example,
10002
and
10008
). The above query is returning:

reservation_number | created_date_time | rate_type | room_type_code | room_code | client_code | revenue_total
10002 2017-02-03 08:32:00.000 allincl 1cath 1cath 90 3040
10003 2017-02-03 08:35:00.000 allincl 1dollh 1dollh 90 2001
10008 2017-02-03 08:43:00.000 allincl 1cath 1cath 90 2710


The desired output is:

reservation_number | created_date_time | rate_type | room_type_code | room_code | client_code | revenue_total
10002 2017-02-03 08:32:00.000 allincl 1cath 1cath 90 1520
10003 2017-02-03 08:35:00.000 allincl 1dollh 1dollh 90 2001
10008 2017-02-03 08:43:00.000 allincl 1cath 1cath 90 1355

Answer Source

Instead of:

JOIN nw_src.dbo.fdroomresc b ON r.reservation_number = b.reservation_number

you can join a table derived from the original one after grouping is applied:

JOIN (
   SELECT MAX(created_date_time) AS created_date_time, reservation_number
   FROM nw_src.dbo.fdroomresc
   GROUP BY reservation_number
) AS b ON r.reservation_number = b.reservation_number

Note: I assume here that you want to pick the latest date from table nw_src.dbo.fdroomresc out of the group of records having the same reservation_number.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download