需要在月初开始查询

Question

SELECT  DISTINCT 
ATB.AcountCountDesc,
TB.LastFirstName,
N.EMAIL,
TB.AccountNumber,
TB.OpenShareCount,
TB.MemberOpenDate,
TB.OpenMemberCount,
TB.OpenShareBalance,
SH.ShareType,
FORMAT(SH.ShareOpenDate, 'MM/dd/yyyy') AS "ShareOpenDate",
SH.ShareCreatedByUser,
SH.ShareCreatedByUserName,
SH.ShareBranchName,
SH.ShareBranch,
cast(month(SH.ShareOpenDate) as varchar) + '/' + cast(year(SH.ShareOpenDate) as varchar) as 'Open Period',
CONCAT(SH.ShareCreatedByUser, '-',SH.ShareCreatedByUserName) 'Opened By'
FROM
arcu.vwARCUOperationMemberTrialBalance as TB
INNER JOIN arcu.vwARCUOperationMemberAccountTrialBalance as ATB ON TB.MemberSuppID = ATB.MemberID
and TB.ProcessDate = ATB.PDate
and TB.MemberStatus = 0 -- Account count for open Members only
and TB.AccountStatus <> 1
INNER JOIN arcu.vwARCUShare AS SH ON TB.ProcessDate = SH.ProcessDate
AND TB.AccountNumber = SH.AccountNumber
INNER JOIN NAME AS N ON TB.AccountNumber = N.PARENTACCOUNT
WHERE
ATB.AcountCountDesc = 1
AND TB.OpenShareCount >= 1
AND SH.ShareType = '00'
AND SH.ShareID != '40'
AND SH.ShareOpenDate >= DATEADD(s, 1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), -2))
AND TB.MemberOpenDate = SH.ShareOpenDate
AND N.EMAIL <> ''
AND N.EMAIL is not null
order by MemberOpenDate Desc;

Issue: MemberOpen Date开始于08/02/2021。期望结果:我需要从2021年8月1日开始我尝试过的:我尝试过声明一个变量，并通过使用datediff计算月份的开始，仍然得到2021年2月8日的结果。我试着改变=>To =或<=，问题仍然存在。请帮助。

Answer 1

您可以使用EOMONTH来计算每月的第一天，就像这样

SELECT DISTINCT ATB.AcountCountDesc,TB.LastFirstName,N.EMAIL,TB.AccountNumber,TB.OpenShareCount,TB.MemberOpenDate,
TB.OpenMemberCount,TB.OpenShareBalance,SH.ShareType,FORMAT(SH.ShareOpenDate,'MM/dd/yyyy') AS "ShareOpenDate",
SH.ShareCreatedByUser,SH.ShareCreatedByUserName,SH.ShareBranchName,SH.ShareBranch,cast(month(SH.ShareOpenDate) 
as varchar) + '/' + cast(year(SH.ShareOpenDate) as varchar)as 'Open Period', CONCAT(SH.ShareCreatedByUser,'-',SH.ShareCreatedByUserName)
'Opened By'
FROM arcu.vwARCUOperationMemberTrialBalance as TB
JOIN arcu.vwARCUOperationMemberAccountTrialBalance as ATB
ON TB.MemberSuppID = ATB.MemberID
and TB.ProcessDate = ATB.PDate
and TB.MemberStatus = 0 -- Account count for open Members only
and TB.AccountStatus <> 1
JOIN arcu.vwARCUShare AS SH
ON TB.ProcessDate = SH.ProcessDate
AND TB.AccountNumber = SH.AccountNumber 
JOIN NAME AS N ON TB.AccountNumber = N.PARENTACCOUNT
WHERE
ATB.AcountCountDesc = 1 AND 
TB.OpenShareCount >= 1 AND
SH.ShareType = '00' AND
SH.ShareID  !='40' AND
SH.ShareOpenDate >= (select dateadd(day, 1, dateadd(month, -1, eomonth(getdate()))))  AND
TB.MemberOpenDate = SH.ShareOpenDate AND
N.EMAIL <> '' AND
N.EMAIL is not null
order by MemberOpenDate Desc;