sql - increase Row number based on the existing column -


i using following sql rownumber increased 1 one. have attached image show need. when rownumber null should take max number table , add 1 ,max can 12. 

    [![drop table #test123       create table #test123      ( monthname varchar(15),  monthlycount int ,  totalcount int,  rownumber int  )      insert #test123   values ('jan', 1,1,1) ,('feb' ,3,4,2) ,('mar' ,null ,null,null)      ,('apr', null,null,null) ,('may', null,null,null) ,('jun' ,null ,null,null),('jul', null,null,null) ,('aug', 6,10,3) ,('sep' ,null ,null,null)      ,('oct', null,null,null) ,('nov', 2,12,4) ,('dec' ,null ,null,null)       --select * #test123    ---i tried following sql    select monthname,monthlycount,totalcount,case when rownumber null  (select max(rownumber)+1 #test123) else rownumber end rownumber      #test123][1]][1]

enter image description here

here... order looking for:

select     [monthname]    ,[monthlycount]    ,[totalcount]    ,[rownumber]    ,row_number() over(order [rownum], [mnum]) [desired_output]       (select *           ,datepart(mm, [monthname] + ' 01 2017') [mnum]           ,case when [rownumber] null                                           13                 else                    [rownumber]            end [rownum]                  #test123) m order    [mnum];

it figures out month number [mnum] using datepart function dummy day , year ' 01 2017'.

then figures out row number [rownum] based on existing [rownumber]. if null, slugs 13 in sorting purposes.

then in outer query, finds real row number [desired_output] grabbing row number of subquery ordered [rownum] , [mnum]. existing row numbers float top , 13's float bottom. having secondary sort of [mnum], 13's sorted month order.

then final order put whole list in month order.

monthname   monthlycount    totalcount  rownumber   desired_output jan         11              1           1           1 feb         3               4           2           2 mar         null            null        null        5 apr         null            null        null        6 may         null            null        null        7 jun         null            null        null        8 jul         null            null        null        9 aug         6               10          3           3 sep         null            null        null        10 oct         null            null        null        11 nov         2               12          4           4 dec         null            null        null        12

not complicated, works under parameters gave.

hope helps. :)


Comments

Post a Comment

Popular posts from this blog

python Tkinter Capturing keyboard events save as one single string -

is possible store keyboard events 1 string? code below stores 1 char , prints it. card reader or bar code reader, contains collection of character/ string not 1 character @ time. goal save char pressed text variable. from tkinter import * root = tk() def key(event): text= event.char text+= event.char print ("pressed", text) def callback(event): frame.focus_set() print ("clicked at", event.x, event.y) frame = frame(root, width=100, height=100) frame.bind("<key>", key) frame.bind("<button-1>", callback) frame.pack() root.mainloop() currently, creating text variable , everytime key() function gets called, , text stores last character typed. you can define text module level variable , , use module level text inside key function - text = '' def key(event): global text text+= event.char print("pressed", text)
Read more

android - InAppBilling registering BroadcastReceiver in AndroidManifest -

i'm implementing inapp billing in android app. works well, however, i'm trying decouple broadcast receiver activity manifest. suggestion in android's trivialdrive sample: // important: dynamically register broadcast messages updated purchases. // register receiver here instead of <receiver> in manifest // because call getpurchases() @ startup, therefore can ignore // broadcasts sent while app isn't running. // note: registering listener in activity bad idea, done here // because sample. regardless, receiver must registered after // iabhelper setup, before first call getpurchases(). currently there's class extends broadcastreceiver : public class iabbroadcastreceiver extends broadcastreceiver { /** * intent action receiver should filter for. */ public static final string action = "com.android.vending.billing.purchases_updated"; private final iabbroadcastlistener mlistener; public iabbroadcastreceiver(iabbroadcastlistener listener) { m
Read more

javascript - Z-index in d3.js -

i have map , when hover effect on region of map below yellow line , circle, last ones disappear. how can solve problem? http://jsfiddle.net/ot21n9qx/ var arc = g.append("path") .style("fill", "none") .style("stroke", "yellow") .style("stroke-width", 2) .attr("d", "m" + pathorigin[0] + "," + pathorigin[1] + " q" + svgpoint[0] + "," + pathorigin[1] + " " + svgpoint[0] + "," + svgpoint[1]); var circlesize = d3.scale.linear() .domain([0, 0.5, 1]) .range([4, 10, 4]); svg not have z order draws objects in order created. need create path , circle after create highlight regions. here forked fiddle: http://jsfiddle.net/o00fzgaf/1/
Read more