How to get number of unique months from table

Question

Post reply

How to get number of unique months from table

pelusch

SSC Enthusiast

Points: 194
More actions
January 31, 2023 at 3:00 pm

#4143737

Hi guys,
I want some help with a code to count the number of unique months that appears in list (use created code below).
For example: ZipCode 14171 got 5 hits, but the unique months are 3 and that's the expected output.
Could someone please help with that?
Thanks! 🙂

CREATE TABLE #mytable
(
[UCLogTimeStamp] DATE,
[ZipCode] INT,
)
;
SET DATEFORMAT DMY
;
INSERT INTO #mytable
([UCLogTimeStamp], [ZipCode])
VALUES
('2018-12-13',14171)
,('2018-11-13',14171)
,('2018-11-13',14171)
,('2018-10-13',14171)
,('2018-10-13',14171)
,('2018-12-13',45634)
,('2018-11-13',45634)
,('2018-11-13',45634)
,('2018-10-13',45634)
,('2018-09-13',45634)
SELECT [UCLogTimeStamp]
, [ZipCode] FROM #mytable
February 1, 2023 at 8:38 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

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

ScottPletcher SSC Guru Points: 100948 More actions · Answer 1

SELECT ZipCode, COUNT(DISTINCT DATEADD(MONTH, DATEDIFF(MONTH, 0, UCLogTimeStamp), 0)) AS 'No. months'

FROM #mytable

GROUP BY ZipCode

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

pelusch SSC Enthusiast Points: 194 More actions · Answer 2

pelusch

SSC Enthusiast

Points: 194

January 31, 2023 at 3:58 pm

#4143773

Works perfect, thanks Scott!!

kaj SSCommitted Points: 1580 More actions · Answer 3

As alternatives to the DATEADD DATEDIFF formula, you can also use the new DATETRUNC function

COUNT(DISTINCT DATETRUNC(Month, UCLogTimeStamp))

or the old conversion to a fixed-lenght char type (ISO style yyyy-mm)

COUNT(DISTINCT CAST(UCLogTimeStamp AS CHAR(7)))

kaj SSCommitted Points: 1580 More actions · Answer 4

BTW, there isn't really a need for the outer DATEADD in Scott's query, since you only need to count the distinct number of months since 1900-01-01, which is what the DATEDIFF function returns (please correct me if I'm wrong).

SELECT ZipCode, COUNT(DISTINCT DATEDIFF(MONTH, 0, UCLogTimeStamp)) AS 'No. months'
FROM #mytable
GROUP BY ZipCode

This reply was modified 1 year, 2 months ago by kaj. Reason: "provides" became "returns"