Teradata Default List

There is this one table which contains the amounts and states that I need. However, this table contains a year information but I want month. For example, in the table it shows information for Kentucky for 2011..and thats it. For California it shows about 5 different years. But I need it to repeat by month.

So if in 2011 Kentucky had 12 total, then I need a query that shows 12 for January, February, May....repeatedly

Right now I get this output with a dumb query I have:

Kentucky  12   January
California 800 January

This is done easily by grouping by State, Quantity and Month

I want to make sure that no matter what the Quantity is, each State has ALL months

开发者_如何学CKentucky  12   January
Kentucky  12   February
Kentucky  12   May
California 800 January
California 800 February
California 800 May

Any idea on how to do this with Teradata SQL?

---

The overall query would look something like this:

SELECT
  state_quantities.state,
  state_quantities.quantity,
  all_months.month_name
FROM state_quantities
CROSS JOIN (
  ...
) all_months

What goes between the brackets for all_months depends on what you mean by "all months".

If you mean all months that appear in state_quantities irrespective of state (so if you have Kentucky with January, California with February and Florida with May, you'd only get those three months) you could use something like this:

SELECT
  month_name
FROM state_quantities
GROUP BY month_name

If you want all 12 months, you would join to a table containing all 12 months. In the absence of that, you could use sys_calendar.calendar (syntax below might be off):

SELECT
  CAST(calendar_date AS DATE FORMAT 'MMM') AS month_name
FROM sys_calendar.calendar
GROUP BY month_name