|
|
Posted by Roy Harvey on 08/14/07 14:18
Comments in-line.
On Tue, 14 Aug 2007 06:58:42 -0700, Yas <yasar1@gmail.com> 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 <columns that identify the user>
FROM TheTable
GROUP BY <columns that identify the user>
HAVING MAX(dateCol) < dateadd(day,datediff(day,0,getdate()),0)
>Many thanks for any help or assistance :-)
>
>Yas
Roy Harvey
Beacon Falls, CT
Navigation:
[Reply to this message]
|