CODEWITHSUNDEEP
sqldatehiveql
Daniela
Daniela

Reputation: 587

how to create variables+table (ID & DATE) - SQL

I am new to SQL/HiveQL.

How can I create a table with 2 variables: ORG_ID (string) and TIME_PERIOD (date) like this:

ORG_ID | TIME_PERIOD
C2222  | 2018-01-31
C2222  | 2018-02-28
C2222  | 2018-03-31
...
C2222  | 2021-05-31
C2222  | 2021-06-30
D3333  | 2018-01-31
D3333  | 2018-02-28
D3333  | 2018-03-31
...
D3333  | 2021-05-31
D3333  | 2021-06-30
W2345  | 2018-01-31
W2345  | 2018-02-28
W2345  | 2018-03-31
...
W2345  | 2021-05-31
W2345  | 2021-06-30

My 6 IDs: C2222, D3333, W2345, E2111, T7232, U8967

TIME_PERIOD contains the last day of each month between 2018-01-31 and 2021-06-30 (inclusive).

I don't have these variables created in the database, how to create them?

Thanks in advance

Upvotes: 0

Views: 146

Answers (1)

James Sutcliffe
James Sutcliffe

Reputation: 5

CREATE TABLE [table_name] (     
    ORG_ID varchar(24) NOT NULL,     
    TIME_PERIOD Date NOT NULL    
)

It is also good practice to have a column in your table with a primary key for indexing, etc.

CREATE TABLE [table_name] (
    [RowID] Int IDENTITY(1,1) NOT NULL Primary Key,
    ORG_ID varchar(24) NOT NULL,     
    TIME_PERIOD Date NOT NULL    
)

RowID will auto incurment (count) and is the primary index on the table.

Upvotes: 1

Related Questions