sql server 2008 - SQL Database Date Ranges -

-

i have flat table holds status updates.

these updates stored in following format:

agreementid | statusid | statusdate

source data:

agreementid statusid    statusdate 109         1           14/01/2013 15:00:33 109         2           14/01/2013 15:01:28 109         2           14/01/2013 15:01:28 109         2           14/01/2013 15:02:42 109         2           26/02/2013 16:27:38 109         2           26/02/2013 16:27:45 109         8           19/02/2013 13:57:33 109         8           04/03/2013 16:46:29 109         8           18/03/2013 14:08:12 109         8           18/03/2013 14:47:00 109         8           18/03/2013 14:48:46 109         9           26/03/2013 15:41:51

what needing map agreement status in date ranges, agreement can have multiple status updates of same statusid, once agreement goes onto next statusid cannot step backwards previous status id.

for last status date range should statusdate date.

i have got following piece of code, results not giving me want...

select          agreementid,      convert(datetime, convert(varchar(10), statusdate, 103), 103) statusdate,      convert(datetime, convert(varchar(10), statusdate, 103), 103) datefrom,      case when dateto null convert(datetime, convert(varchar(10), getdate(), 103), 103) else convert(datetime, convert(varchar(10), dateto, 103), 103) end dateto,      statusid              (      select              agreementid,          statusid,          statusdate,          (select top (1) statusdate tblstatusupdates su su.agreementid = u.agreementid , su.statusdate > u.statusdate order statusid, statusdate asc) dateto,          rn = row_number() on (partition agreementid order statusdate)                       (          select                  agreementid,              statusid,              min(statusdate) statusdate                                tblstatusupdates          group              agreementid, statusid         ) u     )

here example result query:

agreementid statusdate  datefrom    dateto      statusid 109         14/01/2013  14/01/2013  14/01/2013  1 109         14/01/2013  14/01/2013  14/01/2013  2 109         19/02/2013  19/02/2013  26/02/2013  8 109         26/03/2013  26/03/2013  25/04/2013  9

as can see, date value not right, should run day before next status id.

so in example, status 2 should run 19/02/2013 18/02/13

any suggestions great. thank you.

quick update on one, trying far on complicated answer needed.

here code have got now:

select      agreementid,       statusid,      datefrom,      case when dateto not null dateadd(s, -1, dateto) else getdate() end dateto     (      select          agreementid,          statusid,          min(convert(datetime,statusdate,103)) datefrom,          (select top(1) statusdate tblstatusupdates b b.statusid > a.statusid order statusid asc, statusdate asc) dateto               tblstatusupdates      group          agreementid,          statusid     ) c

this gives me following results:

agreementid statusid    datefrom                  dateto 109         1           2013-01-14 15:00:33.360   2013-01-14 15:01:27.393 109         2           2013-01-14 15:01:28.393   2013-02-19 13:57:32.437 109         8           2013-02-19 13:57:33.437   2013-03-26 15:41:49.843 109         9           2013-03-26 15:41:50.843   2013-04-26 10:34:13.860

thank suggestions.


Popular posts from this blog

How to calculate SNR of signals in MATLAB? -

-
i've encountered simple, yet fundamental problem on calculating snr: there several signals : s1 = original , pure clean signal without noise. n1 = white gaussian noise going added s1. s2 = s1 + n1 (the noisy signal before performing noise-reduction algorithm) s3 = noise-reduced signal (after performing noise reduction algorithm) n2 = s3 - s1 (the amount of noise after performing noise-reduction algorithm) now want compare snr before , after performing noise-reduction algorithm. which signals should consider snr_before!? s1/n1 or s2/n1 ? which signals should consider snr_after!? s3/n1 or s3/n2 ? which commands or functions should use in matlab in order compute snr_before , snr_after? thanks billion putting time on helping me. what know calculating snr before : snrbeforenoise = mean( signal .^ 2 ) / mean( noise .^ 2 ); and snr after : residual_noise = signal - noise_reduced_signal; snr_after = mean( signal .^ 2 ) / mean( residual_noise .^ 2
Read more

c# - Attempting to upload to FTP: System.Net.WebException: System error -

-
i have api takes in xml , uploads files based on information in xml. uploads on schedule (also xml), , have tested surrounding , know works. i getting error 40% of time on first file attempt upload in each time cycle (time cycle = 45 minutes files, 30 minutes others). here code upload: try { loggerftp.log("uploading file: " + filename, false); // create request. ftpwebrequest request = (ftpwebrequest)webrequest.create(appsettingsftp.ftpurl + @"/" + filename); request.method = webrequestmethods.ftp.uploadfile; request.timeout = 6000000; //set 100 minutes //request.timeout = -1; //set infinite // add login credentials. request.credentials = new networkcredential(appsettingsftp.ftplogin, appsettingsftp.ftppassword); // grab file contents. streamreader sourcestream = new streamreader(appsettingsftp.uploadfiledirectory + filename); byte[] filecontents = encoding.utf8.getbytes(sourcestream.readtoend()); sourcest
Read more

ios - UISlider customization: how to properly add shadow to custom knob image -

-
Image
i need place custom knob image shadow uislider. far have image of 38x38, of there region defines shadow of knob. now while using image, when slide knob extreme ends actual knob not able cover end points, hence end points visible under transparent region of knob image this code using customize uislider uiimage *slidebtimg = [uiimage imagenamed:sliderknobimg]; [self.ratingslider setthumbimage:slidebtimg forstate:uicontrolstatenormal]; [self.ratingslider setthumbimage:slidebtimg forstate:uicontrolstatehighlighted]; how can implement custom knob shadow beneath it, covering end points?? look inside uislider.h , there methods let customize slider knob , track needed. (ios 2.0 , above) // lets subclass lay out track , thumb needed - (cgrect)minimumvalueimagerectforbounds:(cgrect)bounds; - (cgrect)maximumvalueimagerectforbounds:(cgrect)bounds; - (cgrect)trackrectforbounds:(cgrect)bounds; - (cgrect)thumbrectforbounds:(cgrect)bounds trackrect:(cgrect)rect value:
Read more