Posted by Yas on 08/14/07 13:58

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

[Back to original message]