February 6, 2014 at 8:55 am
I'm trying to run counts for dates prior to today's date. I want to run a count from a table from the day before.
Here is what I have tried to no avail.
Select 'MHD Provider Count' as 'Provider Count', count(PrimaryEntityID) as 'Provider Count'
from ODS1Stage.Base.PartnerToEntity pte with(nolock)
inner join ODS1Stage.base.Partner pt on pt.partnerid = pte.PartnerID and pt.PartnerCode = 'MHD'
where cast(sysdatetime() as date) = cast(getdate() - 1 as date)
How can I pull a result set from the prior day without a column in either of the tables that stores a date?
Thanks!
February 6, 2014 at 9:10 am
caippers (2/6/2014)
How can I pull a result set from the prior day without a column in either of the tables that stores a date?
Are you saying you don't have any date or datetime columns in your tables? If you don't have a column that stores the record's creation date, there is no way to return table records created on a given date.
February 6, 2014 at 9:23 am
caippers (2/6/2014)
I'm trying to run counts for dates prior to today's date. I want to run a count from a table from the day before.Here is what I have tried to no avail.
Select 'MHD Provider Count' as 'Provider Count', count(PrimaryEntityID) as 'Provider Count'
from ODS1Stage.Base.PartnerToEntity pte with(nolock)
inner join ODS1Stage.base.Partner pt on pt.partnerid = pte.PartnerID and pt.PartnerCode = 'MHD'
where cast(sysdatetime() as date) = cast(getdate() - 1 as date)
How can I pull a result set from the prior day without a column in either of the tables that stores a date?
Thanks!
where cast(sysdatetime() as date) = cast(getdate() - 1 as date)
1) the cast's are completely unnecessary
2) today's date [sysdatetime()] will NEVER equal YESTERDAY's date [getdate() - 1], right? Sure you don't want to filter on a field in one of those tables?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 6, 2014 at 9:28 am
All that is contained in the tables are the last updated dates. I will try and work around that but there are no columns in any of the tables that have creation dates for the record.
February 6, 2014 at 1:41 pm
You MUST have SOMETHING on that table to use as a filter. Without that you can't possibly be getting meaningful output.
If the data does not contain a date field you can use to identify "yesterday's records" there could be another way. If you have an identity field you could keep the last inserted identity field stored in another table. Then you could just look for records after yesterday's identity value. This would also allow you to find ANY day's records too.
If that isn't the case, you could still make it work with a complex storage system involving the primary key, but that goes beyond the realm of a forum post I think.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply