Grabbing the first occurrence for a Month

MinuteImprovement1085 

As olafhelper said, there's a mismatch between your question - which relates to the individual - and the final table you show, which is a straight up aggregation of store visits (i.e. not about the individual at all.)

I've gone with the assumption that what you're after is the final table and not anything about an individual store visitor, for which I've produced the following example - the real point of which is to speak to your statement about the partition not working the way you wanted.

This is just an educated guess though, so if it's off-the-mark, you will need to provide us with more information - as per olafhelper's suggestion.

-- Create artefacts and stage some bogus data based on the described scenario.
IF OBJECT_ID('dbo.data') IS NULL
	CREATE TABLE [dbo].[data]
	(
		[accountId] [varchar](32) NOT NULL
		, [userServer] [varchar](32) NOT NULL
		, [visitStartTime] [datetime] NOT NULL
		, [dateKey] AS CAST([visitStartTime] AS [date])
	)
GO

TRUNCATE TABLE [dbo].[data]
GO

INSERT INTO [dbo].[data] VALUES
( '1', 'app.mystore.com', '2022-12-1 8:00:00' )
, ( '1', 'app.mystore.com', '2022-12-1 9:00:00' )
, ( '1', 'app.mystore.com', '2022-12-1 14:00:00' )
, ( '1', 'app.mystore.com', '2022-12-2 8:00:00' )
, ( '1', 'app.mystore.com', '2022-12-2 9:00:00' )
, ( '1', 'app.mystore.com', '2022-12-2 14:00:00' )
, ( '1', 'app.mystore.com', '2023-1-1 10:30:00' )
, ( '1', 'app.mystore.com', '2023-1-1 13:30:00' )
, ( '1', 'app.mystore.com', '2023-1-1 14:30:00' )
, ( '2', 'app.mystore.com', '2022-12-1 8:00:00' )
, ( '2', 'app.mystore.com', '2022-12-1 9:00:00' )
, ( '2', 'app.mystore.com', '2022-12-1 14:00:00' )
, ( '2', 'app.mystore.com', '2023-1-1 10:30:00' )
, ( '2', 'app.mystore.com', '2023-1-1 13:30:00' )
, ( '2', 'app.mystore.com', '2023-1-1 14:30:00' )
, ( '3', 'app.mystore.com', '2022-12-1 8:00:00' )
, ( '3', 'app.mystore.com', '2022-12-2 8:00:00' )
, ( '4', 'app.mystore.com', '2022-12-2 8:00:00' )
, ( '4', 'app.mystore.com', '2023-1-2 8:00:00' )
GO


-- Example statements relating to the described scenario.
-- First, partition the data.
WITH MyStore AS
(
	SELECT
		YEAR(d.[visitStartTime]) AS [year]
		, MONTH(d.[visitStartTime]) AS [month]
		, DAY(d.[visitStartTime]) AS [day]
		, ROW_NUMBER() OVER
		(
			PARTITION BY
				d.[accountId]
				, YEAR(d.[visitStartTime])
				, MONTH(d.[visitStartTime])
			ORDER BY
				d.[visitStartTime]
		) AS [sequence]
	FROM
		[dbo].[data] AS d
	WHERE
		d.[accountId] <> ' '
		AND d.[userServer] = 'app.mystore.com'
)

-- Next, generate the aggregates.
SELECT
	ms.[year]
	, ms.[month]
	, ms.[day]
	, COUNT(*) AS [visitors]
FROM
	[MyStore] AS ms
WHERE
	ms.[sequence] = 1
GROUP BY
	ms.[year]
	, ms.[month]
	, ms.[day]

The output from this looks like:

I didn't see the need for the [dateKey] column but I included it in the table schema since you appear to have it in there.

Cheers,

Lain