SQL Multiple Join/Sums on Product table -

-

i have following table containing product sales data chain store group. data big , ugly, way can stores. information table holds 22million records, growing 300k day , growth rate exponentially increase 100% on monthly basis.

store_purchases( [id] [int] identity(1,1) not null, [storecode] [int] null, [dtdatum] [datetime] null, [barcode] [varchar](50) null, [desc] [varchar](100) null, [qty] [int] null, [amount] [money] null, [tillslipid] [int] null)

the query need pull top 500 products according sum of sales on date range. each of these products need show barcode, description, sum of quantity sold, sum of sale amounts, number of till slips product on, , sum total of products.

so far have managed come following. query takes 2m20s execute on server , think "basket sum" value incorrect. know if there better way of doing in single query, or in stored procedure.

select   a.barcode,    a.desc,    sum(b.amount) 'basket sum',   count(distinct b.tillslipid) 'basket count',    sales.count,    sales.amount  store_purchases b (nolock),  store_purchases (nolock),  (   select     top 500 barcode,     sum(qty) "count",      sum(amount) "amount"    store_purchases (nolock)   (storecode = 30143)   , dtdatum between '1-feb-2013 00:00:00' , '28-feb-2013 23:59:59'   group barcode   order sum(amount) desc ) sales (a.storecode = 30143) , a.dtdatum between '1-feb-2013 00:00:00' , '28-feb-2013 23:59:59' , a.barcode = sales.barcode  , a.tillslipid = b.tillslipid  , a.storecode = b.storecode , b.dtdatum between '1-feb-2013 00:00:00' , '28-feb-2013 23:59:59' group a.barcode, a.desc, sales.count, sales.amount order bysales.count desc

any appreciated. thanks.

select top 500 barcode, [desc], sum([count]) [count], sum([amount]) amount, sum(basketsum) [basket sum], count(tillslipid) [basket count] (     select barcode, [desc], tillslipid, [count], [amount], sum(amount) on (partition tillslipid) basketsum     (         select tillslipid, barcode, [desc], sum(qty) [count], sum(amount) [amount]         ez_sparbuck_products p         (storecode = 30143) , dtdatum between '1-feb-2013 00:00:00' , '28-feb-2013 23:59:59'         group barcode, [desc], tillslipid,      ) t ) t group barcode, [desc]

this makes assumption barcode doesn't have 2 [desc]s, which, given result you're trying achieve, seems case. if isn't case, same query changed use partition barcode , distinct.

for volumes of data doing on, think want clustered index on dtdatum, , maybe kind of partitioning on storecode.


Comments

Post a Comment

Popular posts from this blog

ruby - Trying to change last to "x"s to 23 -

-
i have been playing around irb , have string looks this: irb(main):072:0> puts ["[\"4354 5432 5432 xxxxx\", \"6547 6547 8543 xxxxx\", \"2344 6543 6674 xxxxx\", \"2346 6236 7543 xxxxx\", \"1273 5585 5587 xxxxx\"]"] => nil irb(main):073:0> what want gsub last 2 "x" s of each 17 digit combination, 23 . what i've tried far (i've been using rubular): a.gsub(/\d[x]/,"23") <= grabs "x" s i'm close a.gsub(/\w[x{2}]/,"23") <= grabs "x" s grabs two digits each combination. is there easier way i'm not understanding? a.gsub(/\d{4} \d{4} \d{4} xxx\kxx/, '23') \d{4} = 4 digits what \k drop matched far. can use don't replace entire number, still able validate have in desired format.
Read more

jquery - Clone last and append item to closest class -

-
this bare-bones code on page: <div class="list-container"> <div class="single-list-item"> <button>remove</button> </div> <div class="single-list-item"> <button>remove</button> </div> <div class="single-list-item"> <button>remove</button> </div> </div> <p><button class="add-list-item">add</button></p> <div class="list-container"> <div class="single-list-item"> <button>remove</button> </div> <div class="single-list-item"> <button>remove</button> </div> <div class="single-list-item"> <button>remove</button> </div> </div> <p><button class="add-list-item">add</button></p> <di
Read more

c - Unrecognised emulation mode: elf_i386 on MinGW32 -

-
i'm trying make kernel, , cannot link c output assembly. ld . i'm getting error: unrecognized emulation mode: elf_i386 i'm using windows 10 professional mingw32 , msys. code using: link.ld /* * link.ld */ output_format(elf32-i386) entry(start) sections { . = 0x100000; .text : { *(.text) } .data : { *(.data) } .bss : { *(.bss) } } kernel.c /* * kernel.c */ void kmain(void) { const char *str = "my first kernel"; char *vidptr = (char*)0xb8000; //video mem begins here. unsigned int = 0; unsigned int j = 0; /* loops clears screen * there 25 lines each of 80 columns; each element takes 2 bytes */ while(j < 80 * 25 * 2) { /* blank character */ vidptr[j] = ' '; /* attribute-byte - light grey on black screen */ vidptr[j+1] = 0x07; j = j + 2; } j = 0; /* loop writes string video memory */ while(str[j] != '\0') { /
Read more