sql server - How do I retrieve a count of timestamps over the first hour of each shift? -

-

okay 1 might tricky explain..

what trying count number of loads within first hour of each shift. mean if loading starts @ 0630, want count number of times finished loading 0630 0700. if loading started @ 0705, count 0705 0800 etc.

here's have work far:

select est.shift_date     ,case est.shift_ident         when '1' 'd'         when '2'then 'n'         end shift_ident     ,min(est.start_timestamp) start equip_status_trans est join equip_status_code esc on esc.status_code est.status_code est.shift_date >= (getdate() - 180)     , esc.status_desc 'lu loading'     , est.equip_ident in (         's803'         ,'s804'         ) group est.shift_date     ,est.shift_ident     ,est.equip_ident

ignoring filters, gets me date, shift identity, , start timestamp of loading each shift.

i have 

select est.shift_date     ,case est.shift_ident         when '1' 'd'         when '2' 'n'         end shift_ident     ,count(est.end_timestamp) count     ,min(est.start_timestamp) start equip_status_trans est join equip_status_code esc on esc.status_code = est.status_code est.shift_date >= (getdate() - 180)     , esc.status_desc = 'lu loading'     , est.equip_ident in (         's803'         ,'s804'         ) group est.shift_date     ,est.shift_ident     ,est.equip_ident     ,datepart(hh, est.end_timestamp)

which gives me date, shift identity, start timestamp of loading, , load count each hour. start timestamps minimum loading timestamps each hour instead of minimum timestamp each shift.

the problem i'm having every time try , join two, returns load count every hour during shift instead of first hour.

as new sql server, easiest way go this?

much appreciated

here's join statement looks like

 select est.shift_date,      case est.shift_ident          when '1' 'd'          when '2' 'n'          end shift_ident,      min(est.start_timestamp) start     , est.equip_ident, d1.count  equip_status_trans est  join equip_status_code esc on esc.status_code = est.status_code  left outer join     (select est2.shift_date,          case est2.shift_ident              when '1' 'd' when '2' 'n'              end shift_ident         , count(est2.end_timestamp) count         , min(est2.start_timestamp) start         , est2.equip_ident     equip_status_trans est2      join equip_status_code esc2 on esc2.status_code = est2.status_code     est2.shift_date >= (getdate() - 180)          , esc2.status_desc = 'lu loading'          , est2.equip_ident in('s803', 's804')     group est2.shift_date         , est2.shift_ident         , est2.equip_ident         , datepart(hh, est2.end_timestamp)     )d1 on est.start_timestamp = d1.start est.shift_date >= (getdate() - 180)  , esc.status_desc = 'lu loading'  , est.equip_ident in('s803', 's804') group est.shift_date, est.shift_ident, est.equip_ident, d1.count

this more of pseudo-sql abstracts-away of select data not pertinent join , filters, believe you'll want this:

with start_time_per_shift (/*select statement gives minimum start per shift */)  select est.shift_date,        count(est.end_timestamp) count,        st_time.start start   equip_status_trans est   join start_time_per_shift st_time     on est.shift_date = st_time.shift_date    , datepart(hh, est.end_timestamp) =        datepart(hh, est.shift_date) --get next hour shift start; join records within first hour  group est.shift_date st_time.start

perhaps this...

with start_time_per_shift  (select est.shift_date,          case est.shift_ident            when '1'             'd'            when '2'             'n'          end shift_ident,          /*est.equip_ident, (did need grouping well?)*/          min(est.start_timestamp) start     equip_status_trans est     join equip_status_code esc       on esc.status_code est.status_code    est.shift_date >= (getdate() - 180)      , esc.status_desc 'lu loading'      , est.equip_ident in ('s803', 's804')    group est.shift_date, est.shift_ident   /*,est.equip_ident*/   )  select est.shift_date,        case est.shift_ident          when '1'           'd'          when '2'           'n'        end shift_ident,        /*est.equip_ident, (did need grouping well?) */        count(est.end_timestamp) count,        st_time.start start   equip_status_trans est   join start_time_per_shift st_time     on est.shift_date = st_time.shift_date    , case est.shift_ident when '1' 'd' when '2' 'n' end = st_time.shift_ident  /*and est.equip_ident = st_time.equip_ident*/   join equip_status_code esc     on esc.status_code = est.status_code  est.shift_date >= (getdate() - 180)    , datepart(hh, est.shift_date) = datepart(hh, est.end_timestamp)    , esc.status_desc = 'lu loading'    , est.equip_ident in ('s803', 's804')  group est.shift_date, est.shift_ident, /*est.equip_ident,*/ st_time.start

Comments

Post a Comment

Popular posts from this blog

c++ - No viable overloaded operator for references a map -

-
i trying use map can make tag names reference number. when try , use it, in code error (6 in total every time reference map): src/main.cpp:25:45: error: no viable overloaded operator[] type 'std::map<std::string, std::string>' const char* idcs = node.child_value(tagmap[3]); this code: #include "pugi/pugixml.hpp" #include <iostream> #include <string> #include <map> int main() { pugi::xml_document doca, docb; std::map<std::string, pugi::xml_node> mapa, mapb; std::map<std::string, std::string> tagmap {{"1", "data"}, {"2", "entry"}, {"3", "id"}, {"4", "content"}}; if (!doca.load_file("a.xml") || !docb.load_file("b.xml")) { std::cout << "can't find input files"; return 1; } (auto& node: doca.child(tagmap[1]).children(tagmap[2])) { co
Read more

java - Custom OutputStreamAppender not run: LOGBACK: No context given for <MYAPPENDER> -

-
i wish write custom appender on basis of outputstreamappender . wrote following class package tests; import java.io.ioexception; import java.io.outputstream; import ch.qos.logback.core.outputstreamappender; public class myappender<e> extends outputstreamappender<e> { public myappender() { system.out.println("myappender created"); setoutputstream(new outputstream() { @override public void write(int b) throws ioexception { throw new unsupportedoperationexception(); } }); } } then wrote following runner: package tests; import org.slf4j.loggerfactory; import ch.qos.logback.classic.logger; public class runner { private static final logger log = (logger) loggerfactory.getlogger(runner.class); public static void main(string[] args) { //log.addappender(new myappender<iloggingevent>()); log.info("hello world"); } } finall
Read more

java - Cannot secure connection using TLS -

-
i'm trying establish secure socket connection between java client applet (built jdk 1.7.0_75-b13) , vc++ server application. as test vehicle, used vc++ client/server sample found in msdn forums, modified use schannel , able establish socket using cipher suite tls_rsa_with_aes_128_cbc_sha. works of tls 1.0/1.1/1.2. when try opening socket java applet same server application, connection rejected server reporting following: tls 1.0 acceptsecuritycontext failed: 0x80090327 tls 1.1 acceptsecuritycontext failed: 0x80090331 tls 1.2 acceptsecuritycontext failed: 0x80090331 this java code used create socket: debugprint("setting secure connection"); sslsocketfactory sslsocketfactory = (sslsocketfactory) sslsocketfactory.getdefault(); sslsocket sslsocket = (sslsocket) sslsocketfactory.createsocket("127.0.0.1", socketnumber); debugprint("starting handshake"); sslsocket.settcpnodelay(true); sslsocket.setsolinger(false, 0); sslsocket.s
Read more