sql server - Calculate Sum From Moving 4 Rows in SQL -

-

i've have following data.

   wm_week  pos_store_count pos_qty pos_sales   pos_cost    ------   --------------- ------  --------    --------    201541   3965            77722   153904.67   102593.04    201542   3952            77866   154219.66   102783.12    201543   3951            70690   139967.06   94724.60    201544   3958            70773   140131.41   95543.55    201545   3958            76623   151739.31   103441.05    201546   3956            73236   145016.54   98868.60    201547   3939            64317   127368.62   86827.95    201548   3927            60762   120309.32   82028.70

i need write sql query last 4 weeks of data, , last 4 weeks summed each of following columns: pos_store_count,pos_qty,pos_sales, , pos_cost.

for example, if wanted 201548's data contain 201548, 201547, 201546, , 201545's.

the sum of 201547 contain 201547, 201546, 201545, , 201544.

the query should return 4 rows when ran successfully.

how formulate recursive query this? there easier recursive this?

edit: version azure sql dw version number 12.0.2000. edit2: 4 rows should returned have sum of columns , it's 3 earlier weeks.

for example, if wanted figures 201548 return following:

wm_week pos_store_count pos_qty  pos_sales   pos_cost ------  --------------- -------  --------    -------- 201548  15780           274938   544433.79   371166.3

which sum of 4 (non-identity) columns 201548, 201547, 201546, , 201545.

pretty sure want.. im using cross apply after ordering data apply sums

create  table #weeklydata (wm_week int, pos_store_count int, pos_qty int, pos_sales money, pos_cost money)  insert #weeklydata values (201541,3965,77722,153904.67,102593.04), (201542,3952,77866,154219.66,102783.12), (201543,3951,70690,139967.06,94724.6), (201544,3958,70773,140131.41,95543.55), (201545,3958,76623,151739.31,103441.05), (201546,3956,73236,145016.54,98868.6), (201547,3939,64317,127368.62,86827.95), (201548,3927,60762,120309.32,82028.7)  declare @startweek int = 201548; cte (     select *,              row_number() on (order [wm_week] desc) rn     #weeklydata      wm_week between @startweek - 9 , @startweek   ) select * cte c1 cross apply (select sum(pos_store_count) pos_store_count_sum,                      sum(pos_qty) pos_qty_sum,                      sum(pos_sales) pos_sales_sum,                     sum(pos_cost) pos_cost_sum                cte c2                c2.rn between c1.rn , (c1.rn + 3) ) ca      c1.rn <= 4

Comments

Post a Comment

Popular posts from this blog

how to insert data php javascript mysql with multiple array session 2 -

-
i have method method discussed , successful data entered 1 when more 1 can not database this programs my php form : <form class="form-horizontal" id="form-produk"> <div class="control-group" > <label class="control-label" for="kelas">kategori</label> <div class="controls"> <select class='form-control' name='id_kategori'> <?php $tampil=mysql_query("select * kategori order nama_kategori"); if ($id_kat==0){ echo "<option value=0 selected>- pilih kategori -</option>"; } while($w=mysql_fetch_array($tampil)){ if ($id_kat==$w[id_kategori]){ echo "<option value=$w[id_kategori] selected>$w[nama_kategori]</option>"; } else{ echo "<option value=$w[id_kategori]>$...
Read more

multithreading - Exception in Application constructor -

-
i wanna write snake game javafx , there exception don't know , want know how fix it. ( know it's not complete yet ) and want know , class extends application ( start override ) main in other programs? see , here not static void main bc didn't need, if want add main shoud do? it's exeption... exception in application constructor exception in thread "main" java.lang.nosuchmethodexception: main_snake.main([ljava.lang.string;) @ java.lang.class.getmethod(class.java:1819) @ com.intellij.rt.execution.application.appmain.main(appmain.java:125) and code : import javafx.animation.animationtimer; import javafx.application.application; import javafx.event.eventhandler; import javafx.scene.scene; import javafx.scene.canvas.canvas; import javafx.scene.canvas.graphicscontext; import javafx.scene.input.keyevent; import javafx.scene.layout.borderpane; import javafx.scene.paint.color; import javafx.stage.stage; import java.util.arraylist; /** * creat...
Read more

windows - CertCreateCertificateContext returns CRYPT_E_ASN1_BADTAG / 8009310b -

-
i realize similar post others (e.g. this one ), there details missing posts might significant case. to start with, here's simplified program: #include "stdafx.h" #include <windows.h> #include <wincrypt.h> int _tmain(int argc, _tchar* argv[]) { // usage: certextract certpath char keyfile[] = "c:\\certificates\\public.crt"; byte lp[65536]; security_attributes sa; handle hkeyfile; dword bytes; pccert_context certcontext; sa.nlength = sizeof(sa); sa.lpsecuritydescriptor = null; sa.binherithandle = false; hkeyfile = createfile(keyfile, generic_read, file_share_read, &sa, open_existing, file_attribute_normal, null); if (hkeyfile) { if (readfile(hkeyfile, lp, getfilesize(hkeyfile, null), &bytes, null) && bytes > 0) { certcontext = certcreatecertificatecontext(x509_asn_encoding, lp, bytes); if (certcontext) { printf("yay...
Read more