How to Group By Dates

Question

Post reply

How to Group By Dates

hazeleyre_23

SSC-Addicted

Points: 449
More actions
January 19, 2016 at 10:18 am

#310962

Hi,
I have some data in SQL that has staff name, case ref and case date completed.
What I am hoping to get from my data is see how many cases were completed on each day for each member of staff:
Example:
Mr Smith 2015-01-18
Mr Smith 2015-01-18
Mr Smith 2015-01-18
Mr Smith 2015-01-18
Total 4 cases for the 2015-01-18.
Is there a way I can do this Using Group By.
Many thanks

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247180 More actions · Answer 1

Something like this?

select name, CaseDate, ct = count(1)

from tbl

group by name, CaseDate

hazeleyre_23 SSC-Addicted Points: 449 More actions · Answer 2

hazeleyre_23

SSC-Addicted

Points: 449

January 20, 2016 at 2:42 am

#1853060

Thank you so much that worked perfectly.

Phil Parkin SSC Guru Points: 247180 More actions · Answer 3

Phil Parkin

SSC Guru

Points: 247180

January 20, 2016 at 6:11 am

#1853105

No problem, thanks for posting back.

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 4

Quick thought, hard to get this wrong as one can almost count anything

😎

USE tempdb;

GO

SET NOCOUNT ON;

--http://www.sqlservercentral.com/Forums/Topic1753771-3411-1.aspx

DECLARE @SAMPLE_SIZE INT = 100;

DECLARE @BASE_DATE DATE = CONVERT(DATE,GETDATE(),0);

DECLARE @DAY_VARIANCE INT = 2; -- +/- DAYS

DECLARE @CHAR_VARIANCE INT = 5; -- Number of CASE_REF

;WITH T(N) AS (SELECT N FROM ( VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS X(N))

, NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) 0 AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)

,SAMPLE_DATA AS

(

SELECT

DATEADD(DAY,CHECKSUM(NEWID()) % @DAY_VARIANCE,@BASE_DATE) AS CASE_DATE

,CHAR(65 + (ABS(CHECKSUM(NEWID())) % @CHAR_VARIANCE)) AS CASE_REF

FROM NUMS

)

SELECT

SD.CASE_REF

,SD.CASE_DATE

,COUNT(*) AS COUNT_ASTERIX

,COUNT(1) AS COUNT_ONE

,COUNT(SD.CASE_REF) AS COUNT_CASE_REF

,COUNT(SD.CASE_DATE) AS COUNT_CASE_DATE

,COUNT('What ever') AS COUNT_WhatEver

FROM SAMPLE_DATA SD

GROUP BY SD.CASE_DATE,CASE_REF;

Results

CASE_REF CASE_DATE COUNT_ASTERIX COUNT_ONE COUNT_CASE_REF COUNT_CASE_DATE COUNT_WhatEver

-------- ---------- ------------- ----------- -------------- --------------- --------------

A 2016-01-19 2 2 2 2 2

A 2016-01-20 13 13 13 13 13

A 2016-01-21 7 7 7 7 7

B 2016-01-19 7 7 7 7 7

B 2016-01-20 7 7 7 7 7

B 2016-01-21 5 5 5 5 5

C 2016-01-19 2 2 2 2 2

C 2016-01-20 8 8 8 8 8

C 2016-01-21 9 9 9 9 9

D 2016-01-19 5 5 5 5 5

D 2016-01-20 9 9 9 9 9

D 2016-01-21 4 4 4 4 4

E 2016-01-19 6 6 6 6 6

E 2016-01-20 9 9 9 9 9

E 2016-01-21 7 7 7 7 7

Phil Parkin SSC Guru Points: 247180 More actions · Answer 5

Phil Parkin

SSC Guru

Points: 247180

January 20, 2016 at 9:52 am

#1853206

Asterix.

Asterisk.

😀

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 6

Phil Parkin (1/20/2016)
Asterix.
Asterisk.
😀

ROFL (and disabling auto correction)

😎

BTW counting René Goscinny/Albert Uderzo's Asterix will also work.