SQL Server Filter items for a user -

-

i have order table , orderitem table. orders user placed less or equal 2 mins between them , have same products(number of products must match well) associated them.

based on question i'm able sucessfully filter out order placed <=2 mins apart user. i'm unable figure out how able orders user have same products associated them well.

i expecting output like

ordernumber  orderdatetime             userid 1111         april, 28 2012 09:00:00     1 3333         april, 28 2012 09:03:00     1 4444         april, 28 2012 09:40:00     2 5555         april, 28 2012 09:42:00     2

here have tried far: sql fiddle

using sql fiddle, came this...

with ordered (   select     ordernumber,     orderdatetime,     userid,     lag(orderdatetime,1) on (       partition userid       order orderdatetime     ) prev_time,     lead(orderdatetime,1) on (       partition userid       order orderdatetime     ) next_time,     lag(ordernumber,1) on (       partition userid       order orderdatetime     ) prev_ordernumber,     lead(ordernumber,1) on (       partition userid       order orderdatetime     ) next_ordernumber   [order] ), possibleduplicateorders (   select  ordernumber,           orderdatetime,           userid,           case              when datediff(minute,orderdatetime,next_time) <= 2 next_ordernumber             when datediff(minute,prev_time,orderdatetime) <= 2 prev_ordernumber             else null           end duplicateordernumber   ordered     datediff(minute,orderdatetime,next_time) <= 2  --this says if next value less or equal 2 minutes away return           or datediff(minute,prev_time,orderdatetime) <= 2 --this says if prev value less or equal 2 minutes away return ) select *     possibleduplicateorders pdo   not exists (       select *         order_item oi_left       left join order_item oi_right               on oi_right.ordernumber = pdo.duplicateordernumber              , oi_right.productid = oi_left.productid        oi_left.ordernumber = pdo.ordernumber       ,    oi_right.productid null     )   , not exists (       select *         order_item oi_left       left join order_item oi_right               on oi_right.ordernumber = pdo.ordernumber              , oi_right.productid = oi_left.productid        oi_left.ordernumber = pdo.duplicateordernumber       ,    oi_right.productid null     )

from can see, orders 4444 , 5555 should considered duplicates criteria. (within 2 minutes of each other , have matching items.) sql fiddle did not include quantities, did not include them in query, can add them in if need them.


Comments

Post a Comment

Popular posts from this blog

SQL php on different pages to Insert (mysqli) -

-
i have piece of php code, works when placed on index page ( index.php ), nothing if put in page, i.e. add.php for example: having code add.php page, tried go to: www.myhost.com/add.php , nothing happen. if copy code index.php , go page www.myhost.com/index.php add row in sql table. why that? <?php $server="mysql.server.com"; $user="randomusr"; $pass="randompwd"; $db="randomdb"; $link=mysqli_connect($server, $user, $pass, $db); $result=mysqli_query($link,"select * `battery` `id`"); $query = "insert battery (id,type,owner,charge,time) values ('1','lily002','maisnow','15','2015-04-22 17:20:28')"; mysqli_query($link, $query); header("location: index.php"); //this 1 comment out when copied index.php ?> l # name type collation attributes null 1 id int(16) no ...
Read more

php - How can an email be returned from Stripe Checkout? -

-
using stripe, want store customer email address email supplied in checkout. unfortunately, posting stripeemail in charge.php file returns null . how can return email checkout can use send receipt? here's form code: <script src="https://checkout.stripe.com/v2/checkout.js"></script> <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.js"></script> <form action="charge.php" method="post"> <input type="hidden" id="amount" name="chargeamount"/> <button data-charge-amount="300000" data-charge-name="name" data-charge-description="description">select pledge level</button> <button data-charge-amount="123123" data-charge-name="name2" data-charge-description="description2">donate</button> </form> <script> $('button').click(function(){ var token...
Read more

sql - Partition elimination in Greenplum -

-
i have scenario this: select * package package_type in ('box','card') the table partitioned package_type field. assume there twenty possible values package_type field. there twenty partitions including box , card , default partitions. when above query run, partition elimination happens correctly , box , card partitions scanned. result quick. however, when same query written this: select * package package_type in (select package_type package_list_table) , column package_type in package_list_table contains 2 values box , card . when above query run, 20 partitions being scanned. degrades performance. it seems compiler failing identify second query correctly , result partitions getting accessed. any workarounds overcome this? thanks in advance. the postgres manual page on partitioning includes caveat constraint exclusion works when query's clause contains constants (or externally supplied parameters). example, comparison agains...
Read more