How to get a count of yesterdays Records

Hello,

I think this may be simple but have spent most of my day trying to figure out what I am doing wrong. I am trying to get two values.  Number of new partners yesterday and number of new partners yesterday in previous year.  

It runs but I don't get any values just empty columns.  I know that there is data there because when I hardcode yesterday's date it produces values


 SELECT Count (*) AS 'New Partners Added Yesterday', AcctMas.AccountType
 FROM [SE_PROTO].[dbo].[A01dAccountDates] AcctDate WITH (NOLOCK) INNER JOIN A01_AccountMaster AcctMAS WITH(NOLOCK) ON AcctDate.AccountNumber = AcctMAS.AccountNumber
 WHERE AcctDate.DateType = 'PTRDATE'
 AND
 AcctDate.DateValue = DATEADD(DAY,-1, GETDATE())
 GROUP BY AcctMas.AccountType
 GO

  SELECT Count (*) AS 'New Partners Added Yesterday In Previous Year', AcctMas.AccountType
 FROM [SE_PROTO].[dbo].[A01dAccountDates] AcctDate WITH (NOLOCK) INNER JOIN A01_AccountMaster AcctMAS WITH(NOLOCK) ON AcctDate.AccountNumber = AcctMAS.AccountNumber
 WHERE AcctDate.DateType = 'DDCINCEPT'
  AND
 AcctDate.DateValue = DATEADD(YEAR,-1, GETDATE()-1)
  GROUP BY AcctMas.AccountType

 

 Go


 --SAMPLE

 SELECT Count (*) AS 'New Partners Added Yesterday', AcctMas.AccountType
 FROM [SE_PROTO].[dbo].[A01dAccountDates] AcctDate WITH (NOLOCK) INNER JOIN A01_AccountMaster AcctMAS WITH(NOLOCK) ON AcctDate.AccountNumber = AcctMAS.AccountNumber
 WHERE AcctDate.DateType = 'PTRDATE'
  AND
 AcctDate.DateValue = '2015-05-19'
mwjones75Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
There are a number of different ways to accomplish this.  Here is one of them...

WITH cteDailyCount AS
(
	SELECT AcctMas.AccountType, AcctDate.DateValue, COUNT(1) AS NewPartnerCount	
	FROM [SE_PROTO].[dbo].[A01dAccountDates] AS AcctDate WITH (NOLOCK)
	INNER JOIN A01_AccountMaster AS AcctMAS WITH(NOLOCK)
		ON AcctDate.AccountNumber = AcctMAS.AccountNumber
	WHERE AcctDate.DateType = 'PTRDATE'
	GROUP BY AcctMAS.AccountType, AcctDate.DateValue
)
SELECT DYesterday.NewPartnerCount, DLastYear.NewPartnerCount
FROM cteDailyCount AS DYesterday
LEFT OUTER JOIN cteDailyCount AS DLastYear
	ON DYesterday.AccountType = DLastYear.AccountType
WHERE DYesterday.DateValue = CAST(GETDATE() - 1 AS DATE)
	AND DLastYear.DateValue = DATEADD(YEAR, -1, CAST(GETDATE() - 1 AS DATE)) 

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mwjones75Author Commented:
Thanks worked like a champ!

I will have to study this out a bit to use the whole solution of WITH cteDailyCount AS   but I really liked it.    When I just inserted the = CAST(GETDATE() - 1 AS DATE)  ......  I got the expected results though.  So I am pretty new at this and feel like I am "drinking from a firehouse"  but this was very helpful and worth the price of admission by itself!
Brian CroweDatabase AdministratorCommented:
cteDailyCount is a Common Table Expression.  It acts and compiles identically to a subquery but is easier to read and has some additional capabilities.

In this case the CTE is used to create a daily summary.  Then the query below references that CTE twice to pull the summaries for yesterday and the previous year.
mwjones75Author Commented:
Thank you very much Sir!   I will probably have a similar question on "previous week" and "month to date" but I am not done trying to get it to work yet myself.  I really like CTE for the  ability to condense the code like that instead of running a batch of single commands.  However having been in Exchange for the past decade + and now moving to SQL my learning intake is quite high.  I really do appreciate your time and effort on providing solution!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.