Query Year in TeraData

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
LVL 8
Leo TorresSQL DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
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
0
Leo TorresSQL DeveloperAuthor 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
0
Leo TorresSQL DeveloperAuthor 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.
0
CompTIA Cloud+

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

PortletPaulEE Topic AdvisorCommented:
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.
0
PortletPaulEE Topic AdvisorCommented:
good luck, sounds like a decent challenge.
0
Leo TorresSQL DeveloperAuthor 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
0
PortletPaulEE Topic AdvisorCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Leo TorresSQL DeveloperAuthor Commented:
The 2014 is not static it changes.

The concatenation is done in SSIS will I make these changes. Makes sense.
0
Leo TorresSQL DeveloperAuthor 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!
0
PortletPaulEE Topic AdvisorCommented:
A pleasure. Cheers.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.