Skip to content

Instantly share code, notes, and snippets.

@johnmackintosh
Last active May 31, 2018 23:58
Show Gist options
  • Save johnmackintosh/e34debb27c21b368ce2a0de665decad6 to your computer and use it in GitHub Desktop.
Save johnmackintosh/e34debb27c21b368ce2a0de665decad6 to your computer and use it in GitHub Desktop.
windowing query for row of dots to enable cumulative count
SELECT x.[MovementDateTime],
x.[FirstName],
x.[LastName],
x.[Ward_Dept],
x.[Staging_Post],
x.[Movement_Type],
x.[IN_OUT],
x.[Movement15],
x.[counter],
ROW_NUMBER() OVER (PARTITION BY IN_OUT, Movement_Type,Staging_Post,Movement15 ORDER BY (MovementDateTime))AS R_Number
FROM
(SELECT [MovementDateTime],
[FirstName],
[LastName],
[Ward_Dept],
[Staging_Post],
[Movement_Type],
[IN_OUT],
cast(round(floor(cast([MovementDateTime] AS float(53))*24*4)/(24*4),5) AS smalldatetime) AS Movement15,
(CASE WHEN IN_OUT = 'IN' THEN 1 ELSE -1 END) AS [counter]
FROM [DB].[dbo].[TABLENAME])x
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment