Question About Revenue

SQL Apprentice Question
I have a reports table in my database where i am storing revenue
collected for each particular month.

Table structure
ID Int(4)
Hotel Number char(6)
Month char(1)
Year char(4)


each row can be something like 1001, 1894, 1,2006


I need to write a query which should return text 'N/A' for months that dont
contain data. For example if march 2006 has no revenue then it should return
n/a.


Can someone please assist me?


Celko Answers
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.


>> I have a reports table in my database where i am storing revenue collected for each particular month. <<


Your narrative did not have a revenue column :) We will ignore the
fact that computing values and storing them in a table is a bad
programming practice for OLTP apps and should only be done with a data
warehouse app.

CREATE TABLE ComputableSummary
(hotel_nbr CHAR(6) NOT NULL
CHECK (??),
year_month CHAR(7) NOT NULL
CHECK (year_month LIKE '[0-9][0-9][0-9][0-9]-[0-9][0-9]'),
hotel_revenue DECIMAL(12,2) NOT NULL);


This next one can be part of a general Calendar table


CREATE TABLE ReportPeriods
( cal_date DATETIME NOT NULL PRIMARY KEY,
year_month CHAR(7) NOT NULL
CHECK (year_month LIKE '[0-9][0-9][0-9][0-9]-[0-9][0-9]'),
etc.);



>> I need to write a query which should return text 'N/A' for months that don't contain data. <<


SELECT S.hotel_nbr, R.year_month, S.hotel_revenue
FROM ReportPeriods AS R
LEFT OUTER JOIN
ComputableSummary AS S
ON R.year_month = S.year_month
WHERE .. ;

And then following the basic rules of a tiered architecture, you would
do the display in the front end for NULL revenues. However, if you
want to be a bad programmer, kludge it with:


COALESCE (CAST(S.hotel_revenue AS CHAR(12)), 'N/A') AS revenus