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].[A01dAcco untDates] 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].[A01dAcco untDates] 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].[A01dAcco untDates] 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'
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].[A01dAcco
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].[A01dAcco
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].[A01dAcco
WHERE AcctDate.DateType = 'PTRDATE'
AND
AcctDate.DateValue = '2015-05-19'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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!
ASKER
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!