sql - Joining two log tables to get the status of an account at the time it was subject to a logged event -

-

i trying join 2 log tables using sql server 2012, in order add status of each account @ time subject of action logged.

the first table report created logs of contact requests users (to other users) of website, based on request date (request_date) , receiver (receiver_account_id) of request. if contact request approved, approval date (approval_date) column populated well. table looks this:

    receiver_account_id    sender_account_id    request_date    approval_date     13                     19                   2012-08-10      2012-09-01     13                     21                   2013-05-15      null     17                     19                   2011-09-11      null     25                     44                   2012-11-05      2012-11-07

the second table logs status changes of accounts:

    date        account_id        status     2011-07-10  13                free     2012-08-15  13                premium     2010-12-10  17                free     2012-05-05  17                premium     2010-01-20  25                free     2011-04-10  25                premium     2012-11-06  25                free

i join these 2 tables, can this:

    receiver_account_id    sender_account_id    request_date    approval_date    receiver_status_when_received    receiver_status_when_approved     13                     19                   2012-08-10      2012-09-01       free                             premium       13                     21                   2013-05-15      null             premium                          null     17                     19                   2011-09-11      null             free                             null     25                     44                   2012-11-05      2012-11-07       premium                          free

the sender's status not important @ all.

try this

;with statusperiods (     select account_id, [status], [date] statusstart,          isnull((select min(date) status_log nxt nxt.date > sl.date , nxt.account_id = sl.account_id), '2100-1-1') statusend     status_log sl )  select request.*,      statuswhenreceived.[status]  receiver_status_when_received, statuswhenapproved.[status] receiver_status_when_approved requests  inner join  statusperiods statuswhenreceived      on statuswhenreceived.receiver_account_id = requests.account_id , requests.request_date between statuswhenreceived.statusstart , statuswhenreceived.statusend left  join  statusperiods statuswhenapproved      on statuswhenapproved.receiver_account_id = requests.account_id , requests.approval_date between statuswhenapproved.statusstart , statuswhenapproved.statusend

Comments

Post a Comment

Popular posts from this blog

plot - Remove Objects from Legend When You Have Also Used Fit, Matlab -

-
Image
i've plotted data points , fitted exponential curve between them using 'fit' in matlab. problem fit-function got fitted line wanted plot, markers on top of regular markers. solved problem plotting wanted markers on top of unwanted couldn't seen. problem. when want show legends dots in there. how can remove markers legend without removing fitted line since both of them hidden inside fit-function? can stop 'fit' plotting unwanted markers? so, want remove blue dot called 'hoff' in picture below. you can leave out legend-entries manually leaving out handles of lines, dont want in legend. try this: %if plot something, want showing in legend, save handle: h_line = plot(x,y) %you dont want show line? dont anything, plot it: plot(mymarker) %then set legend-> can add text legend-entries %a cell-array containing strings want show up: legend([h_line another_line],{'text1' 'text2'}) with example (see comments) came solution: ...
Read more

java - Why does my date parsing return a weird date? -

-
here code using: string string = "08/07/2013".replace('/', '-'); date date = new simpledateformat("yyyy-mm-dd").parse(string); why date return: "wen jan 3 00:00:00 est 14"? not @ date format told use. edit: need format because database using requires format. the format use parse date string, not match it. using yyyy 08 . use following format: new simpledateformat("dd-mm-yyyy") and why @ replacing / - ? can build pattern original string only: string string = "08/07/2013" date date = new simpledateformat("dd/mm/yyyy").parse(string); and if want date string in yyyy-mm-dd format, can format date using dateformat#format(date) method: string formatteddate = new simpledateformat("yyyy-mm-dd").format(date); see also: change date format in java string simpledateformat java doc
Read more

Need help in packaging app using TideSDK on Windows -

-
i developed application using tidesdk on windows , when click launchapp works fine. moved package , installed imagemagick , wix 3.0 required packaging on windows. clicked on package runtime , cannot guess packaged app is? found file 'installer' in workspace when click on it says installer not find application path . think application not being packaged properly. here log see in tidesdk: preparing package desktop app runtime. 1 moment... staging trial -> copying contents d:\lab\tidesdk\trial d:\lab\tidesdk\trial\packages\win32\bundle\trial -> copying installer c:\programdata\tidesdk\sdk\win32\1.3.1-beta\installer d:\lab\tidesdk\trial\packages\win32\bundle\trial -> copying runtime d:\lab\tidesdk\trial\packages\win32\bundle\trial -> date: d:\lab\tidesdk\trial\packages\win32\bundle\trial\modules\app\1.3.1-beta -> date: d:\lab\tidesdk\trial\packages\win32\bundle\trial\modules\codec\1.3.1-beta -> date: d:\lab\tidesdk\trial\packages\win32\bundle\trial\mod...
Read more