i have a table like sales....the field are

Prodid Jan(jam month sales)Feb March apr may jun july

1 20 76 50 74 94 93 83
2 30 94 40 94 93 93 02
3 40 90 30 49 94 92 56
4 70 20 30 93 46 35 78
5 23 40 40 39 84 46 57
6 85 30 55 93 46 46 46
7 84 20 65 83 56 57 57
8 10 93 40 93 68 57 36
9 57 30 30 83 67 57 79
10 38 83 40 83 55 68 47
11 35 39 90 94 57 78 47
12 83 89 50 93 79 69 69
i wat ti find the max sales of every product which i want to
load into target table

Answers were Sorted based on User's Feedback



i have a table like sales....the field are Prodid Jan(jam month sales)Feb March apr may jun ju..

Answer / tdguy

Vaishnavi...i think the data presented here is already in a
reporting format...because sales amount for each month for
each prod id has been aggregated column wise. As far as I
know,any aggregate function like max,min,sum,count could
be applied only to row wise data. In case of manipulation
of column wise data, we may have to go with case statement
or we may have to transpose the column wise data to row
data for the manipulation to be done. So in this case,
either case statement has to be used for comparison of
column data or column to row transposition has to be done
by utilizing union statement(sel jan from table union sel
feb from table...till dec) and then the max value function
can be applied. Or if creation of volatile/temp tables is
allowed, then the above result can be stored temporarily
and manipulations can be done. Other option would be to
create UDF, but i think that has to be looped(thru SP i
think..not sure)...This is my opinion. Suggestions are
welcome.

Is This Answer Correct ?    5 Yes 0 No

i have a table like sales....the field are Prodid Jan(jam month sales)Feb March apr may jun ju..

Answer / rkraju

hi.,the query is
>>select case when jan>feb and jan>mar and jan> apr and jan>
may and jan>jun then
jan else (case when feb>jan and feb>mar and feb>apr and
feb>may and feb>jun then
feb else (case when mar>jan and mar>feb and mar>apr and
mar>may and mar>jun then
mar else (case when apr>jan and apr>feb and apr>mar and
apr>may and apr>jun then
apr else (case when may>jan and may>feb and may>apr and
may>mar and may>jun then
may else (case when jun>jan and jun>feb and jun>mar and
jun>apr and jun>may then
jun end)end)end)end)end)end from sales;
select case when jan>feb and jan>mar and jan> apr and jan>
may and jan>jun then
jan else (case when feb>jan and feb>mar and feb>apr and
feb>may and feb>jun then
feb else (case when mar>jan and mar>feb and mar>apr and
mar>may and mar>jun then
mar else (case when apr>jan and apr>feb and apr>mar and
apr>may and apr>jun then
apr else (case when may>jan and may>feb and may>apr and
may>mar and may>jun then
may else (case when jun>jan and jun>feb and jun>mar and
jun>apr and jun>may then
jun end)end)end)end)end)end from sales;
like this u can find max sal upto dec .,i hope this query
fulfill ur req.,

Is This Answer Correct ?    1 Yes 0 No

i have a table like sales....the field are Prodid Jan(jam month sales)Feb March apr may jun ju..

Answer / rkraju

select case when jan>feb and jan>mar and jan> apr and jan>
may and jan>jun then
jan else (case when feb>jan and feb>mar and feb>apr and
feb>may and feb>jun then
feb else (case when mar>jan and mar>feb and mar>apr and
mar>may and mar>jun then
mar else (case when apr>jan and apr>feb and apr>mar and
apr>may and apr>jun then
apr else (case when may>jan and may>feb and may>apr and
may>mar and may>jun then
may else (case when jun>jan and jun>feb and jun>mar and
jun>apr and jun>may then
jun end)end)end)end)end)end from sales;
i hope this ans fulfill ur req.,

Is This Answer Correct ?    0 Yes 0 No

i have a table like sales....the field are Prodid Jan(jam month sales)Feb March apr may jun ju..

Answer / guest

thank for ur rly.
But apart from this ans can we any other way?

Is This Answer Correct ?    0 Yes 0 No

i have a table like sales....the field are Prodid Jan(jam month sales)Feb March apr may jun ju..

Answer / vaishnavi

Greatest and LEast cannot be used in Teradata. It does not
support those two clauses.
The only option is by using Case Stmnt as given above.
But then if the no.of Column increases(say 200) , its
tedious to write a complex Case stmnt and also it not a
optimised way of writing such queries.
HOping to see more better ans for this question. Also it is
a commonly repeated ques in most of the interviews.

Is This Answer Correct ?    0 Yes 0 No

i have a table like sales....the field are Prodid Jan(jam month sales)Feb March apr may jun ju..

Answer / rahul

I agree wth the solution....

but here is the easy way to find the max of sales min of
sales with the two functions that are
greatest and least

the query is

select prodid, greatest(jan,feb,march,apr,may,june,july) as
higher ,
least(jan,feb,march,apr,may,june,july)as low_sales from sales
group by prodid;

Thanks
Rahul

Is This Answer Correct ?    0 Yes 2 No

Post New Answer

More Teradata Interview Questions

what are the default access rights in the teradata?

1 Answers  


how do you manage the production space. what are the proactive methods you can take ?

0 Answers  


Backup Script was blocked then you are unable to archive the data. how do you analyze it and where do you identify ?

0 Answers  


What is the meaning of Caching in Teradata?

0 Answers  


How many types of index are present in teradata?

1 Answers  






What are the joins in teradata?

0 Answers  


There is a load to the Table every one hour and 24/7.Morning trafic is high, afternoon trafiic is less, Night trafiic is high.According to this situation which Utility you use and how do you load,which utility used?

4 Answers   IBM,


During the Display time, how is the sequence generated by Teradata?

0 Answers  


Why FLOAD doesn't supports NUSI? Where as Mload supports NUSI. Please explain in this regard. Thanks in advance.

4 Answers   Teradata,


What is a common data source for the central enterprise data warehouse?

1 Answers   Wipro,


if a error occured in FASTLOAD is the fastload job stops?

1 Answers   CTS, Deloitte,


Explain fallback in teradata?

0 Answers  


Categories