Query Year in TeraData

Leo Torres
Leo Torres used Ask the Experts™
on
Hello this is a really easy question be but please understand this is the first time I write SQL TeraData code. I need to select data between years, and I do not want to use '1993-01-01' format I want an integer in this case 1993
For example my query will be something like this

Select * 
from dbo.SomeTable
Where Year(Date_Field) between 1993 and 1998

Open in new window



Date_Field is of type "DATE"
I just learned the hard way the YEAR() function does not exists in TeraData. This is the way I would do it in MS SQL. Any help as to how I can run this same query in Teradata
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
Hi. You should NOT do it that way in MS SQL or Teradata. The reason why is quite simple:

a. for EVERY ROW you are running a function (this consumes cpu), and, MORE IMPORTANTLY
b. using a function on the column date DISABLES any INDEX in query optimization on the column
i.e. it makes your query SLOWER than it should be

there is a simple saying: "don't use functions on data in the where clause"

This is what you asked for:

Select *
from dbo.SomeTable
Where   EXTRACT (YEAR FROM Date_Field)   between 1993 and 1998

But in truth the faster way is this:

Select *
from dbo.SomeTable
Where  Date_Field >= '1993-01-01' and Date_Field  < '1999-01-01'

Also note, between is just not good for date ranges, always use >= with < instead
see Beware of Between

the best practice with date and time ranges is to avoid BETWEEN and to always use the form:

WHERE col >= '20120101' AND col < '20120201'
This form works with all types and all precisions, regardless of whether the time part is applicable.
Itzik Ben-Gan

Author

Commented:
Well put!!! I realized that not to long ago had to remove the between and do >= and <.

As for the years. I understand that but this query is already very complex add the month date i thinks makes it more complex.

So here is what I am doing I will try explain best I can.

literally 10 Minutes ago I realized this has to be a 3 layer loop query

First Level easy 1-12 possible medical Diag codes on a professional claim
Second Level MemberID between 53,003,282,427 - 53,248,364,872(245 Million difference) going thru this in chucks of 5 Million Appox 48 iterations
Third Level Years 30 years doing in chucks on 3 so 10 iterations(This is the part relevant to the original question)

that's 480 iterations by 12 so => 5760 Iterations

Why I am doing it this way its because data in 2 sources and I have to join 9 billion records to 20,000 records
9 billion are in TeraData and 20000 in MS SQL

So the query changes dynamically the MemberIDs and years and processes.

If you can think of a better way to do this I am all ears

Author

Commented:
Also the limitation on the box does not allow me to process more the 75 Million records at a time due to resources on machine. That why I have broke this out in such a way.
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
With those row numbers EVEN MORE REASON to AVOID functions on the data!!

The length of SQL is not a solid measure of its efficiency; frequently "sargable" predicates are way longer than common alternatives,  but sargable predicates perform better.

I don't think I can offer much on your overall need, that would require much more knowledge of the requirements and the data. If you include your existing query I might be able to make some suggestions.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
good luck, sounds like a decent challenge.

Author

Commented:
My query. For security purposes I removed the columns being returned by query. This is a dynamic query.

The bold values are what changes in the iterations

SELECT Count(1)
FROM PHYSICIAN_CLAIM
Where HEADER_DIAGNOSIS_1_CODE not in  ('25000','25001','25002','25003','25010','25011','25012','25013','25020','25021','25022','25023','25030','25031','25032','25033','25040','25040','25041','25042','25043','25050','25051','25052','25052','25053','25060','25061','25062','25063','25070','25071','25072','25073','25080','25080','25081','25082','25083','25090','25091','25092','25093')
and HEADER_DIAGNOSIS_1_CODE is NOT NULL
and MEMBER_SYSTEM_ID >= 53003282427
and MEMBER_SYSTEM_ID < 53008282427
and EXTRACT(YEAR FROM SERVICE_FROM_DATE) >= 2014
and EXTRACT(YEAR FROM SERVICE_FROM_DATE) < 2017
EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
this is done on every row >>> EXTRACT(YEAR FROM SERVICE_FROM_DATE)
this is done on every row >>>  EXTRACT(YEAR FROM SERVICE_FROM_DATE)

2014 is a constant
you change millions of values from dates into integers, so that you can test if it is >= a constant

instead:

and SERVICE_FROM_DATE >= '2014-01-01'
and SERVICE_FROM_DATE < '2017-01-01

can't you use the integers you alreay have and concatenate with '-01-01'?

e.g. something like this:

and SERVICE_FROM_DATE >= concat(2014,'-01-01')  --<< this is done once only
and SERVICE_FROM_DATE < concat(2017,'-01-01') --<< this is done once only

or, to ensure they are dates:

and SERVICE_FROM_DATE >= cast(concat(2014,'-01-01') as date format 'yyyy-mm-dd') --<< this is done once only
and SERVICE_FROM_DATE < cast(concat(2017,'-01-01') as date format 'yyyy-mm-dd') --<< this is done once only

Yes it looks longer, but the length of the sql text is NOT a measure of efficiency

Author

Commented:
The 2014 is not static it changes.

The concatenation is done in SSIS will I make these changes. Makes sense.

Author

Commented:
Thank you! This query may run for a few days I have no idea how many hours you just saved me looking at 9 billion rows 5760 times.

oh that sounds wrong and painful

Thank you!
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
A pleasure. Cheers.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial