sql server - Crystal Reports or T-SQL: Comparing multiple records values based on values in another table -

-

i attempting compare , perform calculations on sets of values have multiple entries in crystal reports or t-sql (ms sql server) , calculation.

example data:

table (millions of rows)

entry id 1, vendor, product id, inventory count, inventory value  entry id 2, vendor, product id, inventory count, inventory value  ...  entry id 99, vendor, product id, inventory count, inventory value

table b: (millions of rows)

entry id 1, datetime, entrytype  entry id 2, datetime, entrytype  ...  entry id 99, datetime, entrytype

an entry created in both tables every transaction instance , both tables 5 million plus rows, entry several batch operations occur on schedules , maintenance activities.

i need compare 2 values in table , calculate difference in values of inventory count & inventory value, between recent date time or entry id update in table b (max) regardless of table b entry type , last table b value of type "10" vendor & product id in table a.

so if entry 1 of type '10' , entry 99 of type 62, subtract values of entry 99 values of entry 1 come current inventory counts , values.

this how vendor choose implement in software , looking way information outside of application.

a crystal report way highly preferred, t-sql solution work me long returns products or doesn't require hard coding product ids , default return complete inventory unless further filtered in statement or crystal reports filter.

i running crystal reports 2011 ms sql server 2008 sp3

give query try. runs in few seconds on 3 million rows of random test data (having right indexes on tables important). called table 'inventory' , table b 'entry'.

select x.vendor, x.productid, i2.inventorycount - i.inventorycount inventorycountdiff, i2.inventoryvalue - i.inventoryvalue inventoryvaluediff (     select i.vendor, i.productid, max(e.entrydate) maxentrydate, (select max(e2.entryid) entry e2 join inventory i2 on i2.entryid = e2.entryid , i2.vendor = i.vendor , i2.productid = i.productid e2.entrytype = 10) maxentryid     inventory      join entry e on e.entryid = i.entryid     group i.vendor, i.productid ) x join inventory on x.vendor = i.vendor , x.productid = i.productid join entry e on i.entryid = e.entryid join inventory i2 on x.vendor = i2.vendor , x.productid = i2.productid join entry e2 on i2.entryid = e2.entryid e.entrydate = x.maxentrydate , e2.entryid = x.maxentryid

whatever query end using, favor , put in stored procedure , use procedure source report. crystal reports painful enough use without trying compose complex query in designer.


Comments

Post a Comment

Popular posts from this blog

php - render data via PDO::FETCH_FUNC vs loop -

-
i've been using 2 methods render data. the first one: function name($id,$name){ return '<div id="'.$id.'">'.$name.'</div>'; } echo implode($pdo->query("select id,name user")->fetchall(pdo::fetch_func,'name')); the second one: $users = $pdo->query("select id,name user")->fetchall(pdo::fetch_obj); foreach($users $user){ echo name($user->id,$user->name); } i don't understand how pdo::fetch_func works. tried figure out. however, not well-documented. could please explain fetch mode? , also, 1 performs better? thank you. both methods wrong , have learn how use templates , how separate business logic presentation logic. $users = $pdo->query("select id,name user")->fetchall(pdo::fetch_obj); tpl::assign('users', $users); is code business logic part. then in template <?php foreach $users $row): ?> <div id="<?=$...
Read more

c++ - OpenCV Error: Assertion failed <scn == 3 ::scn == 4> in unknown function, -

-
i read alot other solution still confused should mine... #include <opencv2/objdetect/objdetect.hpp> #include <opencv2/highgui/highgui.hpp> #include <opencv2/imgproc/imgproc.hpp> #include <iostream> #include <stdio.h> using namespace std; using namespace cv; int main(int argc, const char** argv) { //create cascade classifier object used face detection cascadeclassifier face_cascade; //use haarcascade_frontalface_alt.xml library face_cascade.load("haarcascade_frontalface_alt.xml"); //setup video capture device , link first capture device videocapture capturedevice; capturedevice.open(0); //setup image files used in capture process mat captureframe; mat grayscaleframe; //create window present results namedwindow("outputcapture", 1); //create loop capture , find faces while (true) { //capture new image frame capturedevice >> captureframe; ...
Read more

The canvas has been tainted by cross-origin data in chrome only -

-
uncaught securityerror: failed execute 'getimagedata' on 'canvasrenderingcontext2d': canvas has been tainted cross-origin data i getting above error in chrome. code(following) works fine in mozilla. have not corss domain issue. why above error in chrome? var wheel_canvas, wheel_context, can_w, can_h, wheel_grd; var large_radius = 160; var small_radius = 120; var wheel_canvas = document.getelementbyid('wheel_canvas'); var wheel_context = wheel_canvas.getcontext('2d'); var can_w = $(wheel_canvas).width(); var can_h = $(wheel_canvas).height(); var centerx = can_w / 2, centery = can_h / 2; var wheel_grd = null; test('#ff0000'); function test(hex) { $('#clrpicker').css({'left': 210, 'top': 210 }); inverted = hexbw(hex); $('#color_val_show').val(hex); current_color_hex_val_selected = hex; $('#color_val_show').css({'color':inverted,'background':hex}); fillgradient...
Read more