sql server - How to fill previous record and on conditional based? -

-

i have following data in table i.e. no, date , step

+---------+----------------------------+----------------------------+ |   no    |                       date |            step            | +---------+----------------------------+----------------------------+ | 643995g | 03/12/2012 3:22:48 pm      | transferinstart            | | 643995g | 03/12/2012 3:22:50 pm      |                            | | 643995g | 15/02/2013 10:53:57     | hold                       | | 643995g | 15/02/2013 10:54:00     | hold copy processing start | | 643995g | 20/02/2013 4:38:26 pm      |                            | | 643995g | 21/02/2013 3:27:01 pm      | exceptionstart             | | 643995g | 22/02/2013 9:38:32      | exceptionend               | | 643995g | 22/02/2013 9:39:32      |                            | | 643995g | 22/02/2013 10:04:53     |                            | | 643995g | 22/02/2013 10:04:56     |                            | | 643995g | 25/02/2013 10:48:18     | transferin                 | | 643995g | 25/02/2013 10:48:19     | catalougingstart           | | 643995g | 27/02/2013 1:59:44 pm      |                            | | 643995g | 27/02/2013 1:59:45 pm      |                            | | 643995g | 27/02/2013 2:05:01 pm      | catalouging                | | 643995g | 27/02/2013 2:05:02 pm      | processingstart            | | 643995g | 27/02/2013 6:09:51 pm      |                            | | 643995g | 27/02/2013 6:09:52 pm      |                            | | 643995g | 04/03/2013 2:03:16 pm      |                            | | 643995g | 04/03/2013 2:03:17 pm      | hold copy processing start | | 643995g | 11/03/2013 2:27:14 pm      |                            | | 643995g | 12/03/2013 10:09:55     | exceptionstart             | | 643995g | 12/03/2013 10:10:27     | exceptionend               | | 643995g | 12/03/2013 10:10:52     |                            | | 643995g | 12/03/2013 10:11:27     |                            | | 643995g | 12/03/2013 10:28:37     |                            | | 643995g | 12/03/2013 10:46:21     | processing                 | +---------+----------------------------+----------------------------+

  1. would fill in empty records previous step.

  2. after filling above value ever found exception start , exception end fill above value.

please refer blow step2 , step3 examples output. 

+---------+------------------------+----------------------------+----------------------------+----------------------------+ |   no    |          date          |        processstep         |           step2            |           step3            | +---------+------------------------+----------------------------+----------------------------+----------------------------+ | 643995g | 03/12/2012 3:22:48 pm  | transferinstart            | transferinstart            | transferinstart            | | 643995g | 03/12/2012 3:22:50 pm  |                            | transferinstart            | transferinstart            | | 643995g | 15/02/2013 10:53:57 | hold                       | hold                       | hold                       | | 643995g | 15/02/2013 10:54:00 | hold copy processing start | hold copy processing start | hold copy processing start | | 643995g | 20/02/2013 4:38:26 pm  |                            | hold copy processing start | hold copy processing start | | 643995g | 21/02/2013 3:27:01 pm  | exceptionstart             | exceptionstart             | hold copy processing start | | 643995g | 22/02/2013 9:38:32  | exceptionend               | exceptionend               | hold copy processing start | | 643995g | 22/02/2013 9:39:32  |                            | exceptionend               | hold copy processing start | | 643995g | 22/02/2013 10:04:53 |                            | exceptionend               | hold copy processing start | | 643995g | 22/02/2013 10:04:56 |                            | exceptionend               | hold copy processing start | | 643995g | 25/02/2013 10:48:18 | transferin                 | transferin                 | transferin                 | | 643995g | 25/02/2013 10:48:19 | catalougingstart           | catalougingstart           | catalougingstart           | | 643995g | 27/02/2013 1:59:44 pm  |                            | catalougingstart           | catalougingstart           | | 643995g | 27/02/2013 1:59:45 pm  |                            | catalougingstart           | catalougingstart           | | 643995g | 27/02/2013 2:05:01 pm  | catalouging                | catalouging                | catalouging                | | 643995g | 27/02/2013 2:05:02 pm  | processingstart            | processingstart            | processingstart            | | 643995g | 27/02/2013 6:09:51 pm  |                            | processingstart            | processingstart            | | 643995g | 27/02/2013 6:09:52 pm  |                            | processingstart            | processingstart            | | 643995g | 04/03/2013 2:03:16 pm  |                            | processingstart            | processingstart            | | 643995g | 04/03/2013 2:03:17 pm  | hold copy processing start | hold copy processing start | hold copy processing start | | 643995g | 11/03/2013 2:27:14 pm  |                            | hold copy processing start | hold copy processing start | | 643995g | 12/03/2013 10:09:55 | exceptionstart             | exceptionstart             | hold copy processing start | | 643995g | 12/03/2013 10:10:27 | exceptionend               | exceptionend               | hold copy processing start | | 643995g | 12/03/2013 10:10:52 |                            | exceptionend               | hold copy processing start | | 643995g | 12/03/2013 10:11:27 |                            | exceptionend               | hold copy processing start | | 643995g | 12/03/2013 10:28:37 |                            | exceptionend               | hold copy processing start | | 643995g | 12/03/2013 10:46:21 | processing                 | processing                 | processing                 | +---------+------------------------+----------------------------+----------------------------+----------------------------+

attaching image of input table clear

attaching image of output values clear

with apply can these "lookups". can't validate if syntax correct should point possible solution.

with mytable (     select         [no],         convert(datetime,[date],131)[date],         [step]     (     values     ('643995g','03/12/2012 3:22:48 pm','transferinstart'),     ('643995g','03/12/2012 3:22:50 pm',null),     ('643995g','15/02/2013 10:53:57 am','hold'),     ('643995g','15/02/2013 10:54:00 am','hold copy processing start'),     ('643995g','20/02/2013 4:38:26 pm',null),     ('643995g','21/02/2013 3:27:01 pm','exceptionstart'),     ('643995g','22/02/2013 9:38:32 am','exceptionend'),     ('643995g','22/02/2013 9:39:32 am',null),     ('643995g','22/02/2013 10:04:53 am',null),     ('643995g','22/02/2013 10:04:56 am',null),     ('643995g','25/02/2013 10:48:18 am','transferin'),     ('643995g','25/02/2013 10:48:19 am','catalougingstart'),     ('643995g','27/02/2013 1:59:44 pm',null),     ('643995g','27/02/2013 1:59:45 pm',null),     ('643995g','27/02/2013 2:05:01 pm','catalouging'),     ('643995g','27/02/2013 2:05:02 pm','processingstart'),     ('643995g','27/02/2013 6:09:51 pm',null),     ('643995g','27/02/2013 6:09:52 pm',null),     ('643995g','04/03/2013 2:03:16 pm',null),     ('643995g','04/03/2013 2:03:17 pm','hold copy processing start'),     ('643995g','11/03/2013 2:27:14 pm',null),     ('643995g','12/03/2013 10:09:55 am','exceptionstart'),     ('643995g','12/03/2013 10:10:27 am','exceptionend'),     ('643995g','12/03/2013 10:10:52 am',null),     ('643995g','12/03/2013 10:11:27 am',null),     ('643995g','12/03/2013 10:28:37 am',null),     ('643995g','12/03/2013 10:46:21 am','processing')     ) t ([no],[date],[step]) ) select     [no],     [date],     mt.[step] [processstep],     case when mt.[step] null e.[step] else mt.[step] end [step2],     case when mt.[step] in ('exceptionstart','exceptionend') or e.step in ('exceptionstart','exceptionend') ihatestartandend.step else coalesce(mt.[step],e.[step]) end [step3] mytable mt outer apply (     select top 1         evalue.[step]     mytable evalue             mt.[no] = evalue.[no] ,         mt.[date] > evalue.[date] ,         isnull(evalue.[step],'') <> ''     order         evalue.[date] desc ) e outer apply (     select top 1         evalue2.[step]     mytable evalue2             mt.[no] = evalue2.[no] ,         mt.[date] >= evalue2.[date] ,         evalue2.[step] not in ('exceptionstart','exceptionend') ,         evalue2.[step] not null     order         evalue2.[date] desc ) ihatestartandend

Comments

Post a Comment

Popular posts from this blog

html - Styling progress bar with inline style -

-
i trying set progress bar color red using inline style. need in way because have access <body> part of document. style need apply: progress { color: red } progress::-moz-progress-bar { background: red;} progress::-webkit-progress-value { background: red; } progress[aria-valuenow]:before { background: red; } i tried <progress style="background: red;"> incorrect effect, progress bar color turns blue red background. guide right direction desired style? thank in advance. here live example: https://jsfiddle.net/z36d3f6l/
Read more

java - Oracle Sql developer error: could not install some modules -

-
i have downloaded fresh copy of sqldeveloper ( sqldeveloper-4.1.3.20.78-no-jre ) oracle website. when unzip , lauch gui, keep getting following error message: warning - not install modules: dynamic module config - no module providing capability org.netbeans.netbinox found. my system: windows 7 32 bit hp probook. jdk 1.6 , 1.7, 1.8 available in java/jdk subdirectory. netbeans 8.0.2 installed finally got way out after dirty hours. extracted sqldeveloper zip in nested directory. i.e: e://a/b/c/sqldeveloper. starting sqldeveloper gui above directory threw erros. solution: go in c:\users**username**\appdata\roaming : delete sql developer , sqldeveloper folders extract sql developer zip in new non nested directory. i.e: e:// start , should work now reference: https://community.oracle.com/thread/3870680?start=0&tstart=0
Read more

How to use autoclose brackets in Jupyter notebook? -

-
this may sound silly question, how make use of autoclose brackets in jupyter notebook? example, when type print( jupyter notebook auto-closes brakets print() and places cursor inside. type argument, say print(1 + 1) now cursor between second 1 , right bracket ) . key navigate right of ) ? of course can press end or → or ) achieve this, not seem save time, purpose of brackets autoclose suppose? thanks in advance.
Read more