BasharAbuShamaa BasharAbuShamaa - 4 months ago 8
SQL Question

Get Count of first records in SQL Server

When user pay for his product, I add a payment record to PaymentTBL, Now I want to know count of all first payments per month.
I built this query :

SELECT Count(*) as OptIn, Month(StartDate) As MonthNum, Year(StartDate) As YearNum
FROM [dietdb].[dbo].[PaymentsTBL]

group by Month(StartDate), Year(StartDate)


But it doesn't give me what I want exactly, because I need to know only those who start with app in this month and not those who reoccurring/renew their payments.

Is there any good way to achieve this?

Below is the PaymentTBL structure :

CREATE TABLE [dbo].[PaymentsTBL](
[AutoNo] [int] IDENTITY(1,1) NOT NULL,
[PersonID] [nvarchar](50) NOT NULL,
[UDID] [nvarchar](50) NULL,
[StartDate] [datetime] NULL,
[Duration] [float] NULL CONSTRAINT [DF_PaymentsTBL_Duration] DEFAULT ((0)),
[EndDate] [datetime] NULL,
[Points] [float] NULL CONSTRAINT [DF_PaymentsTBL_Points] DEFAULT ((0)),
[Cost] [float] NULL CONSTRAINT [DF_PaymentsTBL_Cost] DEFAULT ((0)),
[Currency] [int] NULL CONSTRAINT [DF_PaymentsTBL_Currency] DEFAULT ((0)),
[TypeID] [int] NULL CONSTRAINT [DF_PaymentsTBL_TypeID] DEFAULT ((2)),
[IsActive] [bit] NULL CONSTRAINT [DF_PaymentsTBL_IsActive] DEFAULT ((0)),
[InsertDate] [datetime] NULL CONSTRAINT [DF_PaymentsTBL_InsertDate] DEFAULT (getdate()),
[InsertUser] [nvarchar](50) NULL,
[UpdateDate] [datetime] NULL CONSTRAINT [DF_PaymentsTBL_UpdateDate] DEFAULT (getdate()),
[UpdateUser] [nvarchar](50) NULL,
[PayBy] [int] NULL CONSTRAINT [DF_PaymentsTBL_PayBy] DEFAULT ((1)),
CONSTRAINT [PK_PaymentsTBL] PRIMARY KEY CLUSTERED
(
[AutoNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


Sample of Data that I need :

OptIn MonthNo YearNo
47 1 2015
56 2 2015
72 3 2015
61 4 2015
74 5 2015
43 6 2015
154 7 2015
180 8 2015
190 9 2015
139 10 2015
169 11 2015
117 12 2015
147 1 2016
137 2 2016
135 3 2016
154 4 2016
141 5 2016
109 6 2016
162 7 2016
75 8 2016

Answer

Try this

SELECT Count(*) as OptIn, Month(StartDate) As MonthNum, Year(StartDate) As YearNum FROM
(
select PersonID, min(startdate) as startdate FROM [dietdb].[dbo].[PaymentsTBL]
group by PersonID
) as t
group by Month(StartDate), Year(StartDate)