sql server - Alternative way to select row value without using scalar value function in sql -


i have 2 table in sql pricetable , targettable. want price item based on few conditions joining 2 tables. 2 tables are,

pricetable pricetable image

    itemrelation    fromdate    todate  amount     12-pbst 2016-09-30  1900-01-01  85.31     12-bbst 2016-08-08  1900-01-01  95.31     12-mbst 2016-09-08  1900-01-01  15.31     12-tbst 2016-07-20  1900-01-01  65.31

targettable target table image 

   itemid   itemname    periodstart targetquantity     12-pmch meadow chandan  05-01-16    36     12-pmch meadow chandan  06-01-16    36     12-pmgu meadow gulab    04-01-16    36     12-pmlv meadow lavendor 04-01-16    36


have written scalar valued function price , it's working fine when try retrieve less 10 rows. try pull more 50 records takes long time produce results, scalar function is,

create function [dbo].[gettargetitemprice](@item nvarchar(50),@date  nvarchar(50)) returns integer  begin declare @id integer set @id= (select top(1) amount pricedisctable itemrelation=@item  , @date between fromdate , todate) return @id end

query price 

select     dbo.getdistargetitemprice(itemid,periodstart,t.custaccount) price,     t.targetquantityas qty di_targettransactive t     periodstart between '2017-08-01' , '2017-08-31' ,     t.custaccount='dska-100323' group t.custaccount


sample output :

price   qty   itemid    targetprice 85.31   1173    12-pbst 99706 95.31   352     12-bbst 33440 15.31   1172    12-mbst 17580 35.31   352     12-sbst 12320 65.31   586     12-tbst 38090

i tried outer apply query gives me wrong price results, alternative solution or sub query scalar valued function in sql


Comments

Post a Comment

Popular posts from this blog

What is happening when Matlab is starting a "parallel pool"? -

running parallel cpu processes in matlab starts command parpool() according documentation , function: [creates] special job on pool of workers, , [connects] matlab client parallel pool. this function takes bit of time execute, on order of 30 seconds. in other multi-cpu paradigms openmp, parallel execution seems totally transparent -- i've never noticed behavior analogous matlab (granted i'm not experienced parallel programming). so, happening between time parpool() called , when finishes executing? takes long? parallel computing toolbox enables run matlab code in parallel using several different paradigms (e.g. jobs , tasks, parfor , spmd , parfeval , batch processing), , run either locally (parallelised across cores in local machine) or remotely (parallelised across machines in cluster - either 1 own, or 1 in cloud). in of these cases, code run on matlab workers , copies of matlab without interactive desktop. if you're intending run on remo...
Read more

php - Cannot override Laravel Spark authentication with own implementation -

during login process of laravel spark need request token of remote api. api stores exact same users , passwords laravel spark application. therefore need username , non hashed password of user during authentication process. i thought overriding authenticated method solution problem. in routes/web.php override post /login endpoint , pointing endpoint own logincontroller: <?php namespace app\http\controllers\auth; use illuminate\support\facades\log; use illuminate\http\request; use laravel\spark\http\controllers\auth\logincontroller sparklogincontroller; class logincontroller extends sparklogincontroller { /** * create new login controller instance. * * @return void */ public function __construct() { parent::__construct(); } /** * handle successful authentication attempt. * * @param request $request * @param \illuminate\contracts\auth\authenticatable $user * @return response */ public fu...
Read more

Qt QGraphicsScene is not accessable from QGraphicsView (on Qt 5.6.1) -

i new qt. trying catch mouse press , release in qgraphicsview area , draw line. this, extended qgraphicsview , called myqgraphicsview . seems scene not attached since added " hello world " , never appears in view. here code extended class: class myqgraphicsview : public qgraphicsview { q_object public: myqgraphicsview(qgraphicsscene *scene, qwidget *parent = q_nullptr) : qgraphicsview(scene,parent){ // qgraphicsview::qgraphicsview(scene,parent); } qpoint *pointptr, *startpos,*endpos; qgraphicsview *cv;//maa qgraphicsscene *c; qgraphicsscene *sceneptr; // maa protected: void paintevent(qpaintevent *event) { show(); } void mousepressevent(qmouseevent *event) { // pos wrt widget. global wer global screen qdebug()<<" mouse pressed @ "<<qwidget::mapfromglobal(event->globalpos()); //globalx(); startpos = new qpoint; startpos->setx(qwidget::mapfromglobal(event->globalpos()).x()); startpos->sety(qw...
Read more