Find rows by date. Compare today"s row with yesterdays etc

Hello,

I have a table containing user data. Each row has user data and last
column of each row has current date in the format: 2007-07-04
00.00.00.000. I don't need to specify time. I'm using
dateadd(day,datediff(day,0,getdate()),0) to build this column.

Each day new user data is updated and the data that is more than 2
days old deleted from the table.

So there could be 2 columns for user_1 Day1 and Day2 with either
similar user data or some different fields eg location may be
different. Or user_1 not be present at all on Day2 if removed from
source.

I would like to ask 3 questions...

1. To find the data for today, do I need to add something like Where
date > (getDate()-1) ? eg select * from Table1 Where dateCol >
(getDate()-1) ?

2. What if the table was updated twice or more on the same day from
the same data, using update/insert SQL seems to not overwrite the
columns with today's date, its as if sql is secretly inserting the
time by it self and even though to my eyes the row is exactly the same
SQL adds a new row thinking it is distinct. With even the date column
having the same date. I may end up with a table with 2 rows for the
same day like...
userName, userLocation, userTitle,2007-07-04 00.00.00.000
userName, userLocation, userTitle,2007-07-04 00.00.00.000
How do I alter the above statement in Question 1 to not give me
duplicates?

3. I would like to compare today's rows with yesterday and find rows
that were there yesterday but not present today. eg if a user was
active yesterday but today has been deleted in the source from where
the table is updated each day. How can I do this? This should
basically give me a list of rows that were there yesterday but not
today.

Many thanks for any help or assistance :-)

Yas

Re: Find rows by date. Compare today"s row with yesterdays etc

Comments in-line.

On Tue, 14 Aug 2007 06:58:42 -0700, Yas wrote:

>Hello,
>
>I have a table containing user data. Each row has user data and last
>column of each row has current date in the format: 2007-07-04
>00.00.00.000. I don't need to specify time. I'm using
>dateadd(day,datediff(day,0,getdate()),0) to build this column.
>
>Each day new user data is updated and the data that is more than 2
>days old deleted from the table.
>
>So there could be 2 columns for user_1 Day1 and Day2 with either
>similar user data or some different fields eg location may be
>different. Or user_1 not be present at all on Day2 if removed from
>source.
>
>I would like to ask 3 questions...
>
>1. To find the data for today, do I need to add something like Where
>date > (getDate()-1) ? eg select * from Table1 Where dateCol >
>(getDate()-1) ?

Since you say the dateCol is stored with zeroes for the time portion
that would work. If there was a time portion you would want to remove
it from the getrdate() the same way you already showed.

>2. What if the table was updated twice or more on the same day from
>the same data, using update/insert SQL seems to not overwrite the
>columns with today's date, its as if sql is secretly inserting the
>time by it self and even though to my eyes the row is exactly the same
>SQL adds a new row thinking it is distinct. With even the date column
>having the same date. I may end up with a table with 2 rows for the
>same day like...
>userName, userLocation, userTitle,2007-07-04 00.00.00.000
>userName, userLocation, userTitle,2007-07-04 00.00.00.000
>How do I alter the above statement in Question 1 to not give me
>duplicates?

"Thinking" is one thing SQL Server does not do. If you run an INSERT
it creates a new row. If you run an UPDATE it changes an existing
row. There is no other way data gets into a table, and SQL Server
does not determine which of the two happens. It is up to the
application that is written to use SQL Server to INSERT or UPDATE as
appropriate, and if there are duplicate rows someone executed INSERTs
to get them there.

>3. I would like to compare today's rows with yesterday and find rows
>that were there yesterday but not present today. eg if a user was
>active yesterday but today has been deleted in the source from where
>the table is updated each day. How can I do this? This should
>basically give me a list of rows that were there yesterday but not
>today.

SELECT
FROM TheTable
GROUP BY
HAVING MAX(dateCol) < dateadd(day,datediff(day,0,getdate()),0)

>Many thanks for any help or assistance :-)
>
>Yas

Roy Harvey
Beacon Falls, CT

Re: Find rows by date. Compare today"s row with yesterdays etc

Please post DDL, so that people do not have to guess what the keys,
constraints, declarative referential integrity, data types, etc. In
your schema are. If you know how, follow ISO-11179 data element naming
conventions and formatting rules. Sample data is also a good idea,
along with clear specifications. It is very hard to debug code when
you do not let us see it.

CREATE TABLE UserLogs
(user_id INTEGER NOT NULL,
login_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
CHECK (login_date
= CAST(FLOOR(CAST(login_date AS FLOAT)) AS DATETIME)),
PRIMARY KEY (user_id, login_date),
etc.);

There are some other tricks for clipping off the time, but you must
put it in the DDL to have data integrity. Here is another one:
DATEADD(DAY, DATEDIFF(DAY, 0, login_date), 0)

Just don't convert to strings and then cast back to DATETIME --
overhead is a bit much. Remember to use the Standard CURRENT_TIMESTAMP
and not the proprietary getdate() from the old UNIX days of Sybase.

>> 1. To find the data for today, <<

CREATE VIEW CurrentUserLogs (user_id, login_date, ..)
AS
SELECT user_id, login_date, ..
FROM UserLogs
WHERE login_date = CURRENT_TIMESTAMP;

This VIEW will always be correct when you invoke it.

Another VIEW might help, if you need this two-day window for other
things. Why would you want to constantly be updating this table to
materialize this two-day window? You can clean it out later with a
DELETE FROM Userlogs WHERE login_date < (CURRENT_TIMESTAMP,
DATEADD(DAY, CURRENT_TIMESTAMP, -10); so that you have some history
just in case.

CREATE VIEW RecentUserLogs (user_id, login_date, ..)
AS
SELECT user_id, login_date, ..
FROM UserLogs
WHERE login_date IN (CURRENT_TIMESTAMP, DATEADD(DAY,
CURRENT_TIMESTAMP, -1);

>> 2. What if the table was updated twice or more on the same day from the same data, using UPDATE/INSERT SQL seems to not overwrite the columns with today's date, it's as if SQL is secretly inserting the time by itself <<

And that is why we have a proper key and trim to the date in the DDL.
You cannot violate that business rule now that it is a constraint.

>> 3. I would like to compare today's rows with yesterday and find rows that were there yesterday but not present today. Go with Roy's answer:

SELECT user_id, etc, ..
FROM UserLogs -- or use RecentUserLogs instead
GROUP BY user_id
HAVING MAX(login_date) = DATEADD(DAY, CURRENT_TIMESTAMP, -1);

Re: Find rows by date. Compare today"s row with yesterdays etc

--CELKO-- (jcelko212@earthlink.net) writes:
>>> 1. To find the data for today, <<
>
> CREATE VIEW CurrentUserLogs (user_id, login_date, ..)
> AS
> SELECT user_id, login_date, ..
> FROM UserLogs
> WHERE login_date = CURRENT_TIMESTAMP;
>
> This VIEW will always be correct when you invoke it.

No. You need to change it to:

CREATE VIEW CurrentUserLogs (user_id, login_date, ..)
AS
SELECT user_id, login_date, ..
FROM UserLogs
WHERE login_date = convert(char(8), CURRENT_TIMESTAMP, 112)

(Or some other way to strip the time portion from CURRENT_TIMESTAMP)




--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Find rows by date. Compare today"s row with yesterdays etc

>> No. You need to change it to:

CREATE VIEW CurrentUserLogs (user_id, login_date, ..)
AS
SELECT user_id, login_date, ..
FROM UserLogs
WHERE login_date = convert(char(8), CURRENT_TIMESTAMP, 112)

(Or some other way to strip the time portion from CURRENT_TIMESTAMP)<<

Look at the DDL; I did that in the base table of the VIEW. I think it
is a better idea to protect data integrity in the base tables than in
apps or VIEWs.

Re: Find rows by date. Compare today"s row with yesterdays etc

--CELKO-- (jcelko212@earthlink.net) writes:
>>> No. You need to change it to:
>
> CREATE VIEW CurrentUserLogs (user_id, login_date, ..)
> AS
> SELECT user_id, login_date, ..
> FROM UserLogs
> WHERE login_date = convert(char(8), CURRENT_TIMESTAMP, 112)
>
> (Or some other way to strip the time portion from CURRENT_TIMESTAMP)<<
>
> Look at the DDL; I did that in the base table of the VIEW. I think it
> is a better idea to protect data integrity in the base tables than in
> apps or VIEWs.

Yes, but CURRENT_TIMESTAMP still returns both hours and minutes. Your
view definition is not going to help you.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx