CODEWITHSUNDEEP
sqlsql-server
WolfieeifloW
WolfieeifloW

Reputation: 619

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

Upvotes: 2

Views: 64

Answers (2)

Orifjon
Orifjon

Reputation: 1097

It looks like you are trying to get calculated column, not aggregated column. It this is the case, try this:

SELECT r.reservation_number, b.created_date_time, r.rate_type, r.room_type_code, r.room_code, t.client_code, (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
ORDER BY r.reservation_number

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

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.

Upvotes: 3

Related Questions