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