Need help on a case scenario

Database
Enthusiast

Need help on a case scenario

Hi All,

I need a help on a Scenario to frame an sql. We have a price table which will have ID and Country level price or Country level price. Price must be picked from Table2 with respect to Date and keys. The Latest date with price must be considered. First preference goes to ID and Country level price and then Country level price. Please find some sample data with result looking for .

Table1

DATE           ID      COUNTRY PRICE

2014-07-11 1       US             10

2014-07-12 N\A    US            50

2014-07-21 2       US             20

2014-07-21 N\A    US             30

Table2

DATE              ID        COUNTRY   QTY

2014-07-20    1            US           20

2014-07-25    2            US           40

Result

DATE              ID       COUNTRY      QTY*PRICE

2014-07-20    1           US               20*50

2014-07-25    2           US               40*20

Tags (1)
9 REPLIES
Enthusiast

Re: Need help on a case scenario

Not sure the question.  You are joining table 1 to table 2 based on ID.

Result should be ID 1 - 20*10  and ID 2 40*20.

Enthusiast

Re: Need help on a case scenario

Yeah , joining table1 and table2 , in which  ID , Country and date is the key and also the price must be picked in such a way like latest date price  is considered from the date in Table2. If duplicate price is there for the latest date price after join,  then first preference must be for ID and country level price in Table1 and next goes to Country level price.

Enthusiast

Re: Need help on a case scenario

Hi Sujith,

I have a questioins for you. I am refererring your Result Table now. As per the output availble for QRY*PRICE, how is that value arrived 20*50 ?

As per the above statement "The Latest date with price must be considered", there are two dates availble and I assume 2014-07-21 should be the latest date. - My assumption here is

There are three open option now

1. 20*10

2. 20*50

3. 20*30

I assume 20*30 would the QTY*PRICE as per ID 1 due to the latest date. - What do you think ?

Enthusiast

Re: Need help on a case scenario

From Table2(Base Table) we have two dates , so for date "2014-07-20" the latest effective price(Table1) is from date "2014-07-12" and here we have only one price available which is at country level. (Price for date 2014-07-21" is only valid from that date , which cannot be considered for this record)Result QTY*PRICE :20*50

Now when we go for  date "2014-07-25" (Table2) , we end up with two price records from latest effective date "2014-07-21", in this case preference will go for customer and country level price. Result QTY*PRICE :40*20

Senior Apprentice

Re: Need help on a case scenario

Might be possible doing two joins to table1 or using ROW_NUMBER, but you need to add more details:

You only show two rows per id or country, I assume there might be older rows, too. If yes, is there a known maximum number? 

How big are those tables?

Is this query supposed to run very often?

Btw, your datamodel is not the best for this kind of request, if there was a (valid_from, valid_to) range it would be much easier.

N/A

Re: Need help on a case scenario

I am trying to creAate a tabke using the following code and am getting the erro message listed. I am new to Teradata. Please assist. Thanks in advance RT

CREATE TABLE MKTCIADB.w_Competitor

,FALLBACK

,NO BEFORE JOURNAL

,NO AFTER JOURNAL

(Competitor_Identifier [DECIMAL(18,0), NOT NULL] as 'Competitor Identifier'

,Competitor_Name [VARCHAR(50),NULL] as 'Competitor Name'

Constraint PK_Competitor_Identifier) ;

 

 

--ERROR CREATE TABLE FAILED. 3707: Syntax error, expected something like a 'CHECK' keyword or an 'UNIQUE' keyword between the word 'Competitor_Identifier' and the end of the request

Senior Apprentice

Re: Need help on a case scenario

Hi Robert,

please post unrelated questions as a new topic.

 [DECIMAL(18,0), NOT NULL]  is not valid in Teradata and the definition of the PK_Competitor_Identifier constraint is missing :-)

This should work:

CREATE TABLE MKTCIADB.w_Competitor
,FALLBACK
,NO BEFORE JOURNAL
,NO AFTER JOURNAL
(Competitor_Identifier DECIMAL(18,0) NOT NULL title 'Competitor Identifier'
,Competitor_Name VARCHAR(50) NULL title 'Competitor Name'
,Constraint PK_Competitor_Identifier primary key(Competitor_Identifier));
Enthusiast

Re: Need help on a case scenario

Yes the query will run on daily basis. Can't predict the table size at this point.

Yeah , we can include a  (valid_from, valid_to) range , but it will be set for key (ID,COUNTRY) of Table1. 

I have just modified the data set for table 1 with end date information also for same ID 

Table1

DATED_ST       DATED_END     ID     COUNTRY PRICE

2014-07-11   2014-07-20      1         US      10.0000

2014-07-12   2014-07-20    N/A       US        50.0000

2014-07-21   9999-12-31     1         US        20.0000

2014-07-21   9999-12-31 N/A          US        30.0000

Table2

DATE              ID        COUNTRY   QTY

2014-07-20    1            US           20

2014-07-25    1            US           40


Query used : (But In this query , I am satisfying the first preference of ID and Country level price pick and second for country level price pick..Its not going for the latest price)

sel 

A.DATED,

A.ID,

A.COUNTRY

,COALESCE(B.PRICE,C.PRICE) 

FROM 

Table2 A

LEFT OUTER JOIN

Table1  B

on A.ID=B.ID

AND A.COUNTRY=B.COUNTRY

AND A.DATED BETWEEN B.DATED_ST AND B.DATED_END

LEFT OUTER JOIN 

Table1 C

on 'N/A'=C.ID

AND A.COUNTRY=C.COUNTRY

AND A.DATED BETWEEN C.DATED_ST AND C.DATED_END


Result:

DATED         ID  COUNTRY <Price Alone>

2014-07-20 1   US             10.0000

2014-07-25 1   US              20.0000

Actual Result looking for :

DATED         ID  COUNTRY <Price Alone>

2014-07-20 1   US              50.0000

2014-07-25 1   US               20.0000


Senior Apprentice

Re: Need help on a case scenario

You're close, you just need to add a condition, this should work:

  ,COALESCE(CASE WHEN B.DATED_ST >= C.DATED_ST THEN B.PRICE END, C.PRICE)

It's much easier with the included DATED_END :-)