sql server - Adding column which groups data by time -

-

i've got table shows: power, time, diff-days, diff-hours, diff-minutes diff columns use datediff , lag calculate difference in times between rows. 

(powerkw) (time)               (diff-days) (diff-hours) (diff-minutes)     31011.39  2014-01-01 00:30:00  null        null         null    31838.74  2014-01-01 00:40:00  0           0            -10    32356.35  2014-01-01 00:50:00  0           0            -10    32358.82  2014-01-01 01:00:00  0           -1           -10    32414.15  2014-01-01 01:10:00  0           0            -10    32413.81  2014-01-01 01:20:00  0           0            -10    32412.35  2014-01-01 01:30:00  0           0            -10    32416.23  2014-01-01 01:40:00  0           0            -10    32014.94  2014-01-01 01:50:00  0           0            -10    31184.45  2014-01-01 03:40:00  0           -2           -110    32403.38  2014-01-01 03:50:00  0           0            -10    32415.07  2014-01-01 04:00:00  0           -1           -10    32388.04  2014-01-01 04:10:00  0           0            -10    32320.70  2014-01-01 04:20:00  0           0            -10    32297.44  2014-01-01 04:30:00  0           0            -10

what want 6th column groups these rows events happening consecutively, i.e 9 happening every 1 minutes 1 after other have 1 in 6th column there 2 hour difference , 6 rows happening after each other have 2 in 6th column, possible?
i.e. 

(powerkw) (time)               (diff-days) (diff-hours) (diff-minutes) (group)    31011.39  2014-01-01 00:30:00  null        null         null           1    31838.74  2014-01-01 00:40:00  0           0            -10            1               32356.35  2014-01-01 00:50:00  0           0            -10            1               32358.82  2014-01-01 01:00:00  0           -1           -10            1    32414.15  2014-01-01 01:10:00  0           0            -10            1   32413.81  2014-01-01 01:20:00  0           0            -10            1    32412.35  2014-01-01 01:30:00  0           0            -10            1    32416.23  2014-01-01 01:40:00  0           0            -10            1    32014.94  2014-01-01 01:50:00  0           0            -10            1    31184.45  2014-01-01 03:40:00  0           -2           -110           2    32403.38  2014-01-01 03:50:00  0           0            -10            2    32415.07  2014-01-01 04:00:00  0           -1           -10            2    32388.04  2014-01-01 04:10:00  0           0            -10            2    32320.70  2014-01-01 04:20:00  0           0            -10            2    32297.44  2014-01-01 04:30:00  0           0            -10            2

if definition of consecutive based on "diff minutes" being greater value (or less than, given these negative), can use cumulative sum:

with q (<your query here>) select q.*,        sum(case when diff_minutes < -50 1 else 0 end) on (order time) grp q;

if want start @ "1", add 1 value.


Comments

Post a Comment

Popular posts from this blog

matlab - error with cyclic autocorrelation function -

-
Image
i'm trying code cyclic autocorrelation function in matlab follows: t=0:(n-1); t=t*te; i_alpha=0; tau=-n2*te:te:n2*te; alpha=-1/2:1/n:1/2; ryy_cl=zeros(length(alpha),length(tau)); alpha=alpha/te ind_tau=0; i_alpha=i_alpha+1; k=tau ind_tau=ind_tau+1; if k>0 % ryy_cl(i_alpha,ind_tau)=1/length(sig_bin_syl_mod(1:end- k))*sum((sig_bin_syl_mod(1:end-k)).*sig_bin_syl_mod(k+1:end)).*exp(1i*2*pi*alpha*t(1+k:length(sig_bin_syl_mod(1:end)))); ryy_cl(i_alpha,ind_tau)=(1/n)*sum((sig_bin_syl_mod(1:end-k)).*sig_bin_syl_mod(k+1:end)).*exp(-1i*2*pi*alpha*t(1:end-k)); else ryy_cl(i_alpha,ind_tau)=(1/n)*sum((sig_bin_syl_mod(1-k:end)).*sig_bin_syl_mod(1:end+k)).*exp(-1i*2*pi*alpha*t(1-k:end)); end end end i'm getting errors, , doesn't show expecting. according below formulae, how can fix it? the code provided incomplete, difficult confidently reproduce error. in particular, haven't de...
Read more

django - (fields.E300) Field defines a relation with model 'AbstractEmailUser' which is either not installed, or is abstract -

-
i trying create customuser django project email username , add radio button truck , company. in registration process email-id registered per truck or company. mentioned radio button 'tag' , add manytomany field tag in emailuser model. when makemigrations, raising error : (fields.e300) field defines relation model 'abstractemailuser' either not installed, or abstract. i quite new django , not sure whether have created correct code wants. please me solving this. here code, models.py: import django django.contrib.auth.models import ( abstractbaseuser, baseusermanager, permissionsmixin) django.core.mail import send_mail django.db import models django.utils import timezone django.utils.translation import ugettext_lazy _ class emailusermanager(baseusermanager): """custom manager emailuser.""" def _create_user(self, email, password, is_staff, is_superuser, **extra_fields): """create , save emailus...
Read more

c# - What is a good .Net RefEdit control to use with ExcelDna? -

-
i using exceldna , c# build suite of tools. part of forms i'd want user select cells/ranges workbook. i've got application.inputbox route working...but doesn't cool... i haven't read many things excel's refedit control, , see many have in past written workarounds, in (shudder) vba or vb.net. however, none of these seem recent... best approach/control refedit functionality on form run via exceldna (if applicable)? the refedit sample project of github explores different ways of dealing threading when showing form excel add-in, main issue making refedit control .net add-in. check page links various other implementations: https://github.com/excel-dna/exceldna/wiki/links-about-refedit
Read more