sql server - Increment Row Number on Group for regular withdrawal amounts -

-

i need create sql server query groups "common" sequential data , assigns unique id group. in example below have withdrawals made policy on regular basis. 

create table #temp2 (policy_id int, wdl_date int, amount decimal(9,2));  insert #temp2 values(1001, 19881028, 190.00); insert #temp2 values(1001, 19881129, 190.00); insert #temp2 values(1001, 19881229, 494.89); insert #temp2 values(1001, 19890130, 494.89); insert #temp2 values(1001, 19890227, 494.89); insert #temp2 values(1001, 19890330, 494.89); insert #temp2 values(1001, 19890530, 525.00); insert #temp2 values(1001, 19890629, 525.00); insert #temp2 values(1001, 19890728, 525.00); insert #temp2 values(1001, 19890830, 525.00); insert #temp2 values(1001, 19930723, 51.00); insert #temp2 values(1001, 19931213, 190.00); insert #temp2 values(1001, 19940311, 190.00); insert #temp2 values(1001, 19940613, 190.00);  insert #temp2 values(1002, 19881028, 50.00); insert #temp2 values(1002, 19881129, 50.00); insert #temp2 values(1002, 19881229, 60.00); insert #temp2 values(1002, 19890130, 100.00); insert #temp2 values(1002, 19890227, 100.00);

i create output such below column id populated query. note amounts sorted in date order should determine values in field id.

policy_id   wdl_date    amount  id ---------   --------    ------  -- 1001        19881028    190.00  1    1001        19881129    190.00  1 1001        19881229    494.89  2 1001        19890130    494.89  2 1001        19890227    494.89  2 1001        19890330    494.89  2 1001        19890530    525.00  3 1001        19890629    525.00  3 1001        19890728    525.00  3    1001        19890830    525.00  3 1001        19930723    51.00   4 1001        19931213    190.00  5 1001        19940311    190.00  5 1001        19940613    190.00  5  1002        19881028    50.00   1 1002        19881129    50.00   1 1002        19881229    60.00   2 1002        19890130    100.00  3 1002        19890227    100.00  3

i've tried using dense_rank() populate id problem encounter 2 separate grouped amounts 190.00 (top 2 , bottom 3 records policy_id = 1001) need kept separate , dense_rank() statement considers both groups of 190.00 1 group. need grouping restart @ each new policy. code doesn't produce required output. 

select *,        dense_rank() on (order policy_id, amount) id     #temp2 order policy_id, wdl_date;

can provide guidance on this?

to 190.00s need this...

select *,        dense_rank() on (partition policy_id order amount) id     #temp2 order policy_id, id;

your desired output has discrepancies when order amount in ranking function value 51.00 should first value not 190.00


Comments

Post a Comment

Popular posts from this blog

How to access named pipes using JavaScript in Firefox add-on? -

-
i'm trying access named pipe in firefox add-on. code, based on solution 2 this codeproject question, is: var file = components.classes["@mozilla.org/file/local;1"].createinstance(components.interfaces.nsilocalfile); file.initwithpath("\\\\.\\pipe\\test"); var text = "some text written"; var writer = components.classes["@mozilla.org/network/file-output-stream;1"].createinstance(components.interfaces.nsifileoutputstream); // open file read/write access, , create if not exist. writer.init (file, 0x04 | 0x08, -1, 0); writer.write (text, text.length); writer.flush (); writer.close (); when run firefox scratchpad, get: /* exception: component returned failure code: 0x80520012 (ns_error_file_not_found) [nsifileoutputstream.init] @6 */ line 6 line call writer.init. i've played passing different flags writer.init, no luck. i'm able write normal file path code, not named pipe path. i've been searching more information of da...
Read more

multithreading - OPAL (Open Phone Abstraction Library) Transport not terminated when reattaching thread? -

-
i in process of writing application using opal makes h323 calls. have looked online number of working examples , have managed put resembles should like, @ present able call external ip via application when accept call craps out , dies. leaving me with: 0:12.949 setupcall:8752 sert.cxx(259) assertion fail: transport not terminated when reattaching thread , file d:\voip\software\opal\src\opal\transports.cxx, line 1021 passertfunc(0xde3f88, 0xffffffffd228226f, 0, 0) + 0x82 passertfunc(0x10d6e5f8, 0x3fd, 0, 0x10d6eb98) + 0x15b opaltransport::attachthread(0xde5870, 0xffffffffd22fc76b, 0, 0) + 0x96 h323connection::setupconnection(0xffffffffd22fc713, 0, 0, 0xde6ea8) + 0x196 asynchcallsetup(0x10d3572c, 0, 0, 0xdd0108) + 0x7c pthread1arg<psafeptr<opalconnection,psafeptrbase> >::main(0xffffffffd2282faf from have deduced, perhaps incorrectly if there thread lock issue caused (possibly arising fact application sending expected h323 call, throwing in c...
Read more

node.js - req param returns an empty array -

-
i using node.js , mongodb geolocation app , req param returns empty array exports.findpressure = function(req, res) { var queryobject = req.param('q'); console.log(queryobject); db.collection('pressure', function(err, collection) { collection.find( { loc: { $near :[ req.param('q') ] , $maxdistance : 5 }},{"value" : 1, _id : 0}) .sort({_id : -1}).limit(1).toarray(function(err, items) { res.send(items); }); }); }; the longitude , latitude values displayed in console listening on port 3000... connected 'weather' database 8.9068256,52.019347499999995 /pressure?q=8.9068256,52.019347499999995 200 27ms - 2b the url follows http://localhost:3000/pressure?q=8.9068256,52.019347499999995 if use values 8.9068256,52.019347499999995 getting value database if use req.param('q') returning empty array you seem passing values part of query string. req.query.q should contain comma-s...
Read more