Link to home
Start Free TrialLog in
Avatar of mwjones75
mwjones75

asked on

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'
ASKER CERTIFIED SOLUTION
Avatar of Brian Crowe
Brian Crowe
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mwjones75
mwjones75

ASKER

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!
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.
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!