Summing values from two tables and summing them separately

[Papa_Don]

Hi group! I'll be up front and say I'm very new to SQL. I'm just now getting my feet under me in my new job. So I have lots of questions. I'll start simple (I hope).

I've created my own tables (2) that have multiple columns of data that looks something like this:

Property Name | Arrival Date | Room Nites | Revenue
Hotel 1 1/1/2014 1 $120
Hotel 1 1/1/2014 1 $115
Hotel 1 1/1/2014 1 $103

The above table is for January 2014 and the second one is for January 2015. It looks identical to the above except for the arrival dates.

I need to merge the data from these two properties into one table. In other words I need to have everything from both tables/columns into 1 table/column. Therefore the new table needs to look like this:

Property Name | Arrival Date | Room Nites | Revenue This Year | Revenue Prior Year
Hotel 1 1/1/2015 1 $120
Hotel 1 1/1/2014 1 $115
Hotel 1 1/1/2015 1 $103

Note - each instance of the data represents 1 reservation for 1 room for 1 room night. Thus the reason you'll see multiple listings for the same property on the same night.

So my question is, how to I do this?

In advance, thanks for your help.

Don

[rch1231]

Hello,

If I understand correctly you have two tables. One contains data for 2014 and the other contains data from 2015. What you want is to print a report that contains data from both tables. That is not hard to do if the tables have the same field names like it sounds like yours do. If that is the case then the option you are looking for is called UNION and you basically run something like this:

SELECT * FROM table2014
UNION
SELECT * FROM table2015;

Now if you are trying to provide a single line with a summary of data from the different years it gets a lot tricker depending on what you want. If I read you question right you want to compare the two years with out put on one row. It can be done but it is going to take some work. The easiest way I can think of is to create a temporary table with the fields you are trying to get the result to look like and the update that table with records from each year posting each income record from 2014 to the last year field with the this year field empty and income from 2015 to the this year field and the last year field blank then generate the output using group by and SUM to put what dates you want together. How that makes sense and helps.

[Papa_Don]

@rch1231, that makes sense to me. I'll give that a try and report back.

Don

[Papa_Don]

@rch1231, I'm getting some data back, but not all. It seems it doesn't like what I'm trying to return and put in one table. Here's what I'm doing:

select      LOS2014.PROP_KEY            Property_Key,            
            LOS2014.ARV_TXN             ARVTXN,
            LOS2014.PROP_MASTER_ID      Property_NO,             
            LOS2015.PROP_KEY            Property_Key,            
            LOS2015.ARV_TXN             ARVTXN,
            LOS2015.PROP_MASTER_ID      Property_NO

from        LOS2014,
            LOS2015

Where       LOS2014.PROP_KEY = LOS2015.PROP_KEY

Order by    LOS2014.PROP_NAME,
            LOS2015.PROP_NAME

This is different from what you suggest, which may be why I'm only getting 2014 data back. What should my syntax be to get everything back and get it into the appropriot column?

Don

[Papa_Don]

I've used your commands as you suggested. It works and appears to bring all of the data back. However I need to do a couple of things:

I need separate some of the 2014 data from the 2015 data. Specifically I need to do this:

LOS2014.RoomNites  RNLY --(name Room Nites Last Year)
LOS2014.Revenue    RevLY --(Revenue Last Year)
LOS2015.RoomNites  RNCY  --(Room Nites This Year)
LOS2015.Revenue    RevCY --(Revenue This Year)

How do I rename these columns using your method of joining the tables? Would I use a "Case" statement?