tsql - SQL Server to count how many times a value appears between multiple date ranges -


i have table 2 columns in question.

  • column - timestamp
  • column b - empid

what i'm trying compare weeks see how many times empid has repeated. example:

week1 base (starting date range e.g. between '2017-07-22' , '2017-07-29 23:59:59.993'). want compare week2 against week1. if empid repeats in week2 see count of 2 , if appears first time in week2 count of 1.

moving on week3. if empid appears in week1, week2 , week3 want see count of 3, if appears in week2 , week3 count of 2 , if appears in week3 count of 1.

and week4. if empid appears in week1, week2, week3 , week4 want see count of 4. if empid appears in week2, week3 , week4 count of 3, if appears in week3 , week4 count of 2 , if appears first time in week4 count of 1.

any appreciated.

added i've tried far not getting desired results. 

select t.emp_id, (select count(emp_id)  [vacation audit care 2017] ((upload_date between '2017-07-22' , '2017-07-29 23:59:59.993') or(upload_date between '2017-07-29' , '2017-08-05 23:59:59.993') or (upload_date between '2017-08-12' , '2017-08-19 23:59:59.993')) , emp_id=t.emp_id) counts [vacation audit care 2017] t group t.emp_id order counts desc

sample data

╔═════════════════╦═════════╗ ║ time_stamp      ║ emp_id  ║ ║ 7/20/2017 19:40 ║ 3140340 ║ ║ 7/20/2017 19:40 ║ 2000950 ║ ║ 7/20/2017 19:40 ║ 3118410 ║ ║ 7/20/2017 19:40 ║ 311840  ║ ║ 7/23/2017 21:19 ║ 3140340 ║ ║ 7/23/2017 21:19 ║ 2000950 ║ ║ 7/23/2017 21:19 ║ 3118410 ║ ║ 7/23/2017 21:19 ║ 3124160 ║ ║ 7/30/2017 7:00  ║ 3140340 ║ ║ 7/30/2017 7:00  ║ 2000950 ║ ║ 7/30/2017 7:00  ║ 3118410 ║ ║ 7/30/2017 7:00  ║ 311840  ║ ║ 8/6/2017 12:00  ║ 3140340 ║ ║ 8/6/2017 12:00  ║ 3118410 ║ ║ 8/6/2017 12:00  ║ 3124160 ║ ║ 8/6/2017 12:00  ║ 311840  ║ ║ 8/13/2017 12:00 ║ 3140340 ║ ║ 8/13/2017 12:00 ║ 3118410 ║ ║ 8/13/2017 12:00 ║ 3124160 ║ ║ 8/13/2017 12:00 ║ 311840  ║ ╚═════════════════╩═════════╝

expected output

╔═════════╦════════╦═══════╦════════╗ ║         ║ 30-jul ║ 6-aug ║ 13-aug ║ ║ emp_id  ║ wk2    ║ wk3   ║ wk4    ║ ║ 3140340 ║ 2      ║ 3     ║ 4      ║ ║ 2000950 ║ 2      ║       ║        ║ ║ 3118410 ║ 2      ║ 3     ║ 4      ║ ║ 311840  ║ 1      ║ 2     ║ 3      ║ ║ 3124160 ║        ║ 1     ║ 2      ║ ╚═════════╩════════╩═══════╩════════╝

as per expected output using 7/23 base (week1) table shows expected data results. in week2 311840 appears first time expect count of 1.

the following week (8/6) 311840 appears once in wk2 , once in wk3 expect count of 2 while 3124160 appears first time expect count of 1 etc.

this give results in format show in sample. i've included week before , after in output. remove weeks remove [29] , [33] pivot. comment out pivot line underlying data.

;with  weekcounts (     select time_stamp, emp_id, datepart(week, time_stamp) int_week sampledata )  ,counts (     select emp_id, int_week, 1 int_count     weekcounts     union     select weekcounts.emp_id, weekcounts.int_week, 1 int_count     weekcounts         inner join counts             on weekcounts.emp_id = counts.emp_id             , (weekcounts.int_week - 1) = counts.int_week )  ,countsagg (     select emp_id, int_week, sum(int_count) int_count     counts     group emp_id, int_week )  select * countsagg pivot (max(int_count) int_week in ([29],[30],[31],[32],[33])) piv

Comments

Post a Comment

Popular posts from this blog

android - InAppBilling registering BroadcastReceiver in AndroidManifest -

i'm implementing inapp billing in android app. works well, however, i'm trying decouple broadcast receiver activity manifest. suggestion in android's trivialdrive sample: // important: dynamically register broadcast messages updated purchases. // register receiver here instead of <receiver> in manifest // because call getpurchases() @ startup, therefore can ignore // broadcasts sent while app isn't running. // note: registering listener in activity bad idea, done here // because sample. regardless, receiver must registered after // iabhelper setup, before first call getpurchases(). currently there's class extends broadcastreceiver : public class iabbroadcastreceiver extends broadcastreceiver { /** * intent action receiver should filter for. */ public static final string action = "com.android.vending.billing.purchases_updated"; private final iabbroadcastlistener mlistener; public iabbroadcastreceiver(iabbroadcastlistener listener) { m...
Read more

python Tkinter Capturing keyboard events save as one single string -

is possible store keyboard events 1 string? code below stores 1 char , prints it. card reader or bar code reader, contains collection of character/ string not 1 character @ time. goal save char pressed text variable. from tkinter import * root = tk() def key(event): text= event.char text+= event.char print ("pressed", text) def callback(event): frame.focus_set() print ("clicked at", event.x, event.y) frame = frame(root, width=100, height=100) frame.bind("<key>", key) frame.bind("<button-1>", callback) frame.pack() root.mainloop() currently, creating text variable , everytime key() function gets called, , text stores last character typed. you can define text module level variable , , use module level text inside key function - text = '' def key(event): global text text+= event.char print("pressed", text)
Read more

sql server - Why does Linq-to-SQL add unnecessary COUNT()? -

here tables: create table customer ( cid int primary key, name varchar(32) ) create table ord ( oid int primary key, cid int foreign key references customer, address varchar(20) ) here's linq-to-sql statement: var aaa = c in db.customer select new { c, o = c.ord.tolist() }; and here's inexplicable query generated linq-to-sql: select [t0].[cid] [cid], [t0].[name] [name], [t1].[oid] [oid], [t1].[cid] [cid2], [t1].[address] [address], (select count(*) [dbo].[ord] [t2] [t2].[cid] = [t0].[cid]) [value] [dbo].[customer] [t0] left outer join [dbo].[ord] [t1] on [t1].[cid] = [t0].[cid] order [t0].[cid], [t1].[oid] i want understand how rid of count(*) part. it's entirely uncalled for! linq needs count(*) in order determine number of entries returned o = c.ord.tolist() .
Read more