sql server - Find the Record - Then Average the "X" Number of Previous Occurrences -

-

what hope accomplish here to create t-sql find particular record , display appropriate projections particular record. , display average of 3 previous times record occurred.

in query below, event occur on 10/7/2015 , had 2 projections of 23 , 23.

select convert(date, dm.fromdatetime) 'drivedate', acct.internalname, dm.locationid, dpact.procedureprojection, dpact.productprojection dbo.drivemaster dm inner join dbo.accounts acct on dm.accountid = acct.accountid inner join dbo.driveprojectionandcollectedtotals dpact on dm.driveid = dpact.driveid inner join dbo.drivestatusdef stat on dm.statusid = stat.statusid acct.accountid = 17708 , dm.fromdatetime = '2015-10-07'

here results - expected , appear correct:

current drive

in second query, displaying 3 previous times same event occurred @ location , marked in complete status.

select top 3 convert(date, dm.fromdatetime) 'drivedate', acct.internalname, dm.locationid, dpact.procedureprojection, dpact.productprojection hemasphere.dbo.drivemaster dm inner join dbo.accounts acct on dm.accountid = acct.accountid inner join dbo.driveprojectionandcollectedtotals dpact on dm.driveid = dpact.driveid inner join dbo.drivestatusdef stat on dm.statusid = stat.statusid acct.accountid = 17708 , dm.fromdatetime < '2015-10-07' , dm.statusid = 2 order dm.fromdatetime desc;

past 3 drives

this not looking for. instead of 3 separate drives, i'd have displayed on in line average of past 3 drives.

if possible, happen have occur within 1 statement displays "upcoming" drive occur on 10/7/2015 , in calculated columns - average 3 previous projections event location.

hopefully makes sense.

if i'm understanding correctly, how making second query subquery in first, , averaging results there. this:

-- original query select convert(date, dm.fromdatetime) 'drivedate', acct.internalname, dm.locationid, dpact.procedureprojection, dpact.productprojection,  --this sub-query prior projection average [priorprocedureprojectionaverage] = (select avg(procedureprojection) (        select top 3 dpact2.procedureprojection     hemasphere.dbo.drivemaster dm2   inner join dbo.accounts acct2 on dm2.accountid = acct2.accountid   inner join dbo.driveprojectionandcollectedtotals dpact2 on dm2.driveid = dpact2.driveid   inner join dbo.drivestatusdef stat22 on dm2.statusid = stat2.statusid     acct2.accountid = 17708   , dm2.fromdatetime < '2015-10-07'   , dm2.statusid = 2   order   dm2.fromdatetime desc; ) sub)  dbo.drivemaster dm inner join dbo.accounts acct on dm.accountid = acct.accountid inner join dbo.driveprojectionandcollectedtotals dpact on dm.driveid = dpact.driveid inner join dbo.drivestatusdef stat on dm.statusid = stat.statusid acct.accountid = 17708 , dm.fromdatetime = '2015-10-07'

that's little messy, , you'd have add second subquery productprojection if want too. gives idea.


Comments

Post a Comment

Popular posts from this blog

Delphi XE2 Indy10 udp client-server interchange using SendBuffer-ReceiveBuffer -

-
i use delphi xe2 , indy10 udp protocol. can't receive server echo on client side if use receivebuffer method. got "socket error # 10040" although send small echo message server client. console application illustrate problem below. in advance. program project1; {$apptype console} {$r *.res} uses system.sysutils, idglobal, idbasecomponent, idcomponent, idsockethandle, idudpclient, idudpserver, idudpbase, idstack; type tudp_serv = class(tidudpserver) procedure udpsvudpread(athread: tidudplistenerthread; adata: tidbytes; abinding: tidsockethandle); end; var udpserver: tudp_serv; udpcl: tidudpclient; bsnd, brcv: tbytes; s: string; k: integer; //============================================================================== procedure tudp_serv.udpsvudpread(athread: tidudplistenerthread; adata: tidbytes; abinding: tidsockethandle); begin writeln(' server read: ' + tohex(adata, length(adata))); abinding sendto(peerip, peerport, adat...
Read more

Qt ActiveX WMI QAxBase::dynamicCallHelper: ItemIndex(int): No such property in -

-
i new windows programming. here goes code objiwbemlocator = new qaxobject("wbemscripting.swbemlocator"); objwmiservice = objiwbemlocator->querysubobject("connectserver(qstring&,qstring&)",qstring("."),qstring("root\\cimv2")); qaxobject* returnlist = objwmiservice->querysubobject("execquery(qstring&)", qstring("select * %1").arg(domain)); qaxobject* result = returnlist->querysubobject("itemindex(int)", 0); i getting error on runtime qt activex wmi qaxbase::dynamiccallhelper: itemindex(int): no such property in but itemindex method exists msdn says the itemindex method not work collections not contain swbemobjects, such swbemmethodset, swbemnamedvalueset, swbemprivilegeset, swbempropertyset, , swbemqualifierset. what selecting ? have tried iterating on collection ? plus need minimum windows vista itemindex method. hope helps
Read more

Enable autocomplete or intellisense in Atom editor for PHP -

-
i'm first time user of atom editor php code. atom can autocomplete built-in functions of php. possible make editor autocomplete custom function written in separate file? function.php <?php function printsomething() { echo "hello world"; } index.php <?php require_once "function.php"; printsomething(); // autocompleted atom editor while typing thanks in advance. there text file in %userprofile%.atom called snippets.cson. in there can add snippet. more this: http://flight-manual.atom.io/using-atom/sections/snippets/ but need think can use later on other files well. otherwise need copy , paste.
Read more