Pre-Fixed the Date

Analytics
Enthusiast

Pre-Fixed the Date

Dear Expert,
In SAS, the date (e.g.Date_1'2007-01-01', Date_2'2007-06-30')can be prefix and the subsequence SQL do not need to retype the date.
In teradata, did such a function available? How many date can be prefix?

Thanks!
10 REPLIES
Enthusiast

Re: Pre-Fixed the Date

I wonder if you are talking about NAMED

say like ...

SELECT 7*10 (NAMED X01), X01 + 12 (NAMED X02), X01+X02 TOTAL
Enthusiast

Re: Pre-Fixed the Date

Nope.
Actually prefixed a date look like reference table.
In SAS, if user fixed the date like this,
Date1 : 2007-01-01
Date2 : 2007-01-31
Date3 : 2007-06-30
and so on. . .

For the rest of the SQL (let said user had 10 SQLs for 10 analysis)
User can use Date1,Date2 and Date3 instead of 2007-01-01, 2007-01-31
and 2007-06-30.

The advantage of the prefix function is, user do not need to change the date for the 10 SQLs they build last time, they just need to change the prefix date. It save time and avoid human error.

Imagine if the SQL having 500 variable to analysis, and every variables request different time period, some monthly, some quarterly.
By prefix the date, user need to modify the prefix date one time once a month.
Without the prefixed date, the user have to scan through 500 variables and change the date for 500 times, seem not eficient.

Any idea how Teradata do this?

Thanks!
Enthusiast

Re: Pre-Fixed the Date

I think you will have to use a table driven approach for this ....

One option I can think of is to create a lookup table like this ...

CREATE TABLE DATELOOKUP
(
DATENME VARCHAR(30) NOT NULL,
DATEVAL DATE NOT NULL
) UNIQUE PRIMARY INDEX(DATENME);

-- contents

DATENME DATEVAL

Date1 2007-01-01
Date2 2007-01-31
Date3 2007-06-30

The queries could be like
SQL1:

SELECT * FROM MYTABLE1 T1, DATELOOKUP T2
WHERE T2.DATENME = 'Date1' AND T1.BILLDATE = T2.DATEVAL

.......

SQL2

SELECT * FROM MYTABLE2 T1, DATELOOKUP T2
WHERE T2.DATENME = 'Date2' AND T1.BILLDATE = T2.DATEVAL

And so on .......

That way the dates needs to be changed only on the lookup table.

just a thought ...
Enthusiast

Re: Pre-Fixed the Date

Thanks for the idea. But I try before to create the lookup table as you did.

But with the lookup table design,
Can the table goes with the query? E.g:

Sel X1 ,X2 ,X3. . . .
From MyTable
Where Date between 'Date_1' AND 'Date_2'

I don't think the lookup table can satisfy the query above.

Anyone any idea?

Thanks.

Enthusiast

Re: Pre-Fixed the Date



Tricky, but possible.

Sel X1 ,X2 ,X3. . . .
From MyTable,
(
SELECT MAX(CASE DATENME WHEN 'Date_1' THEN DATEVAL ELSE NULL END)
, MAX(CASE DATENME WHEN 'Date_2' THEN DATEVAL ELSE NULL END)
FROM DATELOOKUP
) DATEINFO(DATE_1, DATE_2)
Where MyTable.DateCol between Date_1 AND Date_2

Enthusiast

Re: Pre-Fixed the Date

Thanks it work.

I need to do some calculation which the value derive from different tables.

Appreciate if you can let me know,
How to link it to other's tables?
Let said I had MyTable1, MyTable2, MyTable3. . .and so on.

Regards!
Enthusiast

Re: Pre-Fixed the Date

Not sure I got your question,

Is this something that you are looking for ?

SELECT T1.* FROM MYTABLE1 T1, MYTABLE2 T2, DATELOOKUP D1
WHERE D1.DATENME = 'Date1'
AND T1.BILLDATE = D1.DATEVAL
AND T1.BILLNO = T2.BILLNO

or

Sel T1.X1 ,T1.X2 , T1.X3. . . ., T2.X1, T2.X2 ......
From MyTable1 T1,
(
SELECT MAX(CASE DATENME WHEN 'Date_1' THEN DATEVAL ELSE NULL END)
, MAX(CASE DATENME WHEN 'Date_2' THEN DATEVAL ELSE NULL END)
FROM DATELOOKUP
) DATEINFO(DATE_1, DATE_2),
MyTable T2
Where MyTable.DateCol between Date_1 AND Date_2
AND T1.DateCol = T2.DateCol

Enthusiast

Re: Pre-Fixed the Date

I need to do some calculation like this,
Let said: 100*(Purchase_Amount_Last_Month/Purchase_Amount_Last_3_Month)

Thus, 2 queries had to build, the first one contain the data for Last_Month and the 2nd query contain data for Last_3_Month.

***1st Query***
Sel Sum(Purchase_Amount)
From MyTable,
(
SELECT MAX(CASE DATENME WHEN 'Date_1' THEN DATEVAL ELSE NULL END)
, MAX(CASE DATENME WHEN 'Date_2' THEN DATEVAL ELSE NULL END)
FROM DATELOOKUP
) DATEINFO(DATE_1, DATE_2)
Where MyTable.DateCol between Date_1 AND Date_2

***2nd Query***
Sel Sum(Purchase_Amount)
From MyTable,
(
SELECT MAX(CASE DATENME WHEN 'Date_1' THEN DATEVAL ELSE NULL END)
, MAX(CASE DATENME WHEN 'Date_4' THEN DATEVAL ELSE NULL END)
FROM DATELOOKUP
) DATEINFO(DATE_1, DATE_4)
Where MyTable.DateCol between Date_1 AND Date_4

How to link above 2 queries into one and process the calculation for:
Proportion_LM/L3M = 100*(Purchase_Amount_Last_Month/Purchase_Amount_Last_3_Month)

Enthusiast

Re: Pre-Fixed the Date


I haven't tested the syntax, but I think this would do the job. ( Assuming Date_2 is between Date_1 and Date_4 )

SELECT 100 *
SUM(CASE WHEN T1.DATECOL BETWEEN DATE_1 AND DATE_2 THEN T1.PURCHASE_AMOUNT ELSE 0 END) /
SUM(PURCHASE_AMOUNT)
FROM MYTABLE T1,
(
SELECT MAX(CASE DATENME WHEN 'Date_1' THEN DATEVAL ELSE NULL END)
, MAX(CASE DATENME WHEN 'Date_2' THEN DATEVAL ELSE NULL END)
, MAX(CASE DATENME WHEN 'Date_4' THEN DATEVAL ELSE NULL END)
FROM DATELOOKUP
) DATEINFO(DATE_1, DATE_2, DATE_4)
WHERE T1.DATECOL BETWEEN DATE_1 AND DATE_4;