subquery - SQL Join three tables one vs. others two -


i did example code below example of im facing.

lets have tables: stock : have current stock picture day day. producttype : have information if product in stock fruit. fruiteprices : have historical price each fruits prices. othersprices : have historical prices other kind of products.

i need giving stock date load historical prices whether fruit or other.

so first need join between stock , producttype return column isfruit (bit column) producttype. , know do join giving stock day.

then need build historical price tables products above join return. if isfruit = 1 price fruiteprice tablem, if = 0 othersprice table.

the problem im facing lets i'm looking products on stock 10apr15 , doing fist join between stock , producttype return:

stockdate   product isfrute 10apr15     banana   1 10apr15     milk     0

then second join return banana , milk prices eachs respective tables since 10jan15 till 10apr15.

i wish see historical price of banana , milk way:

stockdate pricedate product price 10apr15   10jan15 banana     null 10apr15   10jan15 milk       4 10apr15   11jan15 banana     7 10apr15   11jan15 milk       11 10apr15   ...     banana     8 10apr15   ...     milk       3 10apr15   10apr15 banana     5 10apr15   10apr15 milk       2

i may not have missing dates on historical prices product, case should return null. above example banana start have price since 11jan15 should returned null 10jan15

finally, problem im having i'm having in above table 1 row each combination on dates n x n x n.

example doing:

select gp.pricedate, gp.prodname, case(gp.isfrute = 1, fp.price, else op.price) (     select stk.pricedate, stk.prodname, pt.isfrute     stock stk     join producttype pt on pt.prodname = stk.prodname ) gp join fruitprice fp on fp.name = gp.prodname join othersprice ot on op.name = gp.name gp.pricedate = '10apr15' , fp.date >= '10jan15' , fp.date <= '10apr15' , ot.date >= '10jan15' , ot.date <= '10apr15'

my real data, breaking down dates each table visible happening: 

refdate     date        date    price 2015-05-13  2015-05-04  null    2650.000000000000 2015-05-13  2015-05-05  null    2650.000000000000 2015-05-13  2015-05-06  null    2650.000000000000 2015-05-13  2015-05-07  null    2460.000000000000 2015-05-13  2015-05-08  null    2490.000000000000 2015-05-13  2015-05-11  null    2660.000000000000 2015-05-13  2015-05-12  null    2660.000000000000 2015-05-13  2015-05-13  null    2770.000000000000 2015-05-13  2015-05-14  null    2610.000000000000 2015-05-13  2014-12-31  2015-05-06  1490.000000000000 2015-05-13  2014-12-31  2015-05-07  1490.000000000000 2015-05-13  2014-12-31  2015-05-08  1490.000000000000 2015-05-13  2014-12-31  2015-05-11  1490.000000000000 2015-05-13  2014-12-31  2015-05-12  1490.000000000000 2015-05-13  2014-12-31  2015-05-13  1490.000000000000 2015-05-13  2014-12-31  2015-05-14  1490.000000000000 2015-05-13  2014-12-31  2015-05-05  1490.000000000000 2015-05-13  2015-01-02  2015-05-06  1490.000000000000 2015-05-13  2015-01-02  2015-05-07  1490.000000000000 2015-05-13  2015-01-02  2015-05-08  1490.000000000000 2015-05-13  2015-01-02  2015-05-11  1490.000000000000 2015-05-13  2015-01-02  2015-05-12  1490.000000000000

update 2: real query

with cte_riskfactors(riskfactor) (select riskfactor, prodid, exposure refdate = '20150513') select expo.refdate, expo.riskfactor, expo.prodid, px.price cte_riskfactors cte join exposure expo on cte.riskfactor = expo.riskfactor join prices px on px.id_product = cte.prodid px.[date] >= '20150501' , px.[date] <= '20150513' union select expo.refdate, expo.riskfactor, iv.value cte_riskfactors cte join exposure expo on cte.riskfactor = expo.riskfactor join indexesvalue iv on iv.id_riskfactor = cte.idrf iv.[date] >= '20150501' , iv.[date] <= '20150513'

error:

msg 207, level 16, state 1, line 62 invalid column name 'idrf'. msg 207, level 16, state 1, line 59 invalid column name 'price'.

would work (using cte products of 10apr15) cte creates smaller subset of products join table on. since have table of fruitprices , table of otherprices need flag isfruit? here tsql code:

with cte_products( prodname) (select prodname stock pricedate = '10apr15') select stk.pricedate, stk.prodname, coalesce(fp.price,op.price) cte_products cte    join stock stk on cte.prodname = stk.prodname left outer join fruitprice fp on cte.prodname = fp.name left outer join othersprice op on cte.prodname = op.name stk.pricedate >= '10jan15' , stk.pricedate <= '10apr15'

Comments

Post a Comment

Popular posts from this blog

Email notification in google apps script -

https://support.google.com/docs/answer/91588?hl=en i have sheet the cells filled in data pulled via apps script, website. pull in exact same data, not. on old sheets version use able emails when data "changes" different value. new sheets, email when new data pasted cells, though exact same data......and, can't set @ ranges anymore, entire document. there easy way watch cells when data changes, , email myself? this looks lot you're trying do. for checking whether value has changed after edit, this gives necessary information it. hence, know whether new value same 1 or not. implementing "workaround" talked in second link, able compare , send email myself if value changed. initiate onedit trigger goes off onedit function uses these lines after checks in place have passed. var email = "xyz@whatever.com"; mailapp.sendemail(email, "triggermail", "a change has occurred in sheet"); note: sends "notification...
Read more

c++ - Difference between pre and post decrement in recursive function argument -

i have following sample code used pre-decrement void function(int c) { if(c == 0) { return; } else { cout << "dp" << c << endl; function(--c); cout << c << endl; return; } } this function give output input 4 : dp3 dp2 dp1 dp0 0 1 2 3 but when use post decrement void function(int c) { if(c == 0) { return; } else { cout << "dp" << c << endl; function(c--); cout << c << endl; return; } } output goes in infinite loop dp4 can explain me in detail why happens ? this happens because function(c--) called same value of c , when finishes c decremented. recursively called, hence called same value no chance of return , hit stack overflow error. assume this, int x = 1, y = 0; y = x++ result y == 1 , x == 2. if y = ++x , bot x , y 2.
Read more

javascript - IE11 incompatibility with jQuery's 'readonly'? -

the code i'm running has no issues in firefox, or chrome. user typing 1 textbox, tabs next , can continue typing that. when run webpage on ie11, user tabs next textbox , can't type in. textbox has double-clicked before user can insert anything. i've googled , seems ie give problems when using older jquery (i.e. prefers 'prop' 'attr' ). in code below jquery's readonly keyword being used in way ie11 no longer recognise/accept? i assume issue readonly , removeclass('ignore') working expected. //when focus on textbox 1 $("#field1").focus(function() { //remove readonly property textbox 1 $('#field1').prop('readonly', false).removeclass('ignore'); //add readonly property other textboxes $('#field2, #field3, #field4') .prop('readonly', 'readonly').addclass('ignore').val(''); }); i'm using jquery 2.1.3 ...
Read more