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

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

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

sql server - Why does Linq-to-SQL add unnecessary COUNT()? -

here tables: create table customer ( cid int primary key, name varchar(32) ) create table ord ( oid int primary key, cid int foreign key references customer, address varchar(20) ) here's linq-to-sql statement: var aaa = c in db.customer select new { c, o = c.ord.tolist() }; and here's inexplicable query generated linq-to-sql: select [t0].[cid] [cid], [t0].[name] [name], [t1].[oid] [oid], [t1].[cid] [cid2], [t1].[address] [address], (select count(*) [dbo].[ord] [t2] [t2].[cid] = [t0].[cid]) [value] [dbo].[customer] [t0] left outer join [dbo].[ord] [t1] on [t1].[cid] = [t0].[cid] order [t0].[cid], [t1].[oid] i want understand how rid of count(*) part. it's entirely uncalled for! linq needs count(*) in order determine number of entries returned o = c.ord.tolist() .
Read more