CODEWITHSUNDEEP
sqlsql-servert-sql
10e5x
10e5x

Reputation: 909

How to write the SQL statement to derive Consecutive worked days?

I am working this on MS SQL. Anyone knows how to derive the consecutive_d column? Consecutive days should be solely based on ENTER_DT regardless of times. And if an employee enters many times in the same day it is still counted as one consecutive days. 

PASS_M|  ENTER_DT................|CONSECUTIVE_D
Boo K K    5/1/2012 11:55:00 PM       1  
Boo K K    5/2/2012 11:30:00 PM       2  
Boo K K    5/4/2012 10:30:00 AM       1  
LIAW S     4/30/2012 11:48:52 PM      1  
LIAW S     5/1/2012 00:11:07 AM       2  
LIAW S     5/1/2012 12:32:07 AM       2  
LIAW S     5/1/2012 4:42:02 AM        2    
LIAW S     5/2/2012 1:10:09 AM        3  
LIAW S     5/2/2012 1:43:06 AM        3   
LIAW S     5/4/2012 2:17:47 AM        1

Update: this is what i have tried: 

SELECT PASS_M, ENTRY_DT, DATEDIFF(D, MIN(ENTRY_DT) OVER (PARTITION BY PASS_M), ENTRY_DT) + 1 AS CONSECTUTIVE_DAYS
INTO         TEMP_TARGET
FROM         TEMP_5
ORDER BY PASS_M, ENTRY_DT;

Upvotes: 0

Views: 449

Answers (2)

marctrem
marctrem

Reputation: 850

Use

SELECT *, CONVERT(DATE,GETDATE()) AS DateOnly, SUM(CONSECUTIVE_D) as CONSECUTIVE_SUM GROUP BY DateOnly;

This allows you to group elements by Date by extracting it from the datetime and then the sum of CONSECUTIVE_D will be CONSECUTIVE_SUM


I hope that's what you were looking for :)

You can build your own custom query from this one!
Requested Update

SELECT
PASS_M, 
ENTRY_DT, 
(DATEDIFF(D, MIN(ENTRY_DT) OVER (PARTITION BY PASS_M), ENTRY_DT) + 1) AS CONSECTUTIVE_DAYS,
CONVERT(DATE,ENTRY_DT) AS DateOnly,
SUM(CONSECUTIVE_DAYS) as CONSECUTIVE_SUM

INTO         TEMP_TARGET
FROM         TEMP_5
GROUP BY DateOnly
ORDER BY PASS_M;


In my head, that should do the trick! Don't ORDER BY ENTRY_DT, you now want to GROUP BY DateOnly

Here's a link about how to Turn datetime into date :)

Upvotes: 1

Charles Burns
Charles Burns

Reputation: 10602

I would truncate the date, trunc_date(enter_dt), then count repeated values with a GROUP BY or PARTITION BY.

Edit: For SQL Server, as Aaron mentioned, use cast(getDate() As Date) note trunc_date. The guy has a 33% acceptance as of this writing, so I didn't want to think too hard. :)

Upvotes: 1

Related Questions