Comparing Dates in Previous and Current Rows

Dear All,
I am having textboxes on two detail rows (as an example), I want to calculate the date difference between two textbox date values. The date difference is between the previous row and the current row. As an example:

A B

5/2/2005 09:39:02 AM	5/3/2005 06:29:32 PM
5/4/2005 08:21:31 AM	5/5/2005 07:02:29 PM
5/6/2005 09:52:33 AM	5/6/2005 07:04:31 PM
5/7/2005 09:20:33 AM	5/8/2005 08:26:36 AM

I want to do A2-B1 in the report. I need to calculate the date difference between 5/4/2005 8:21:31 AM and 5/3/2005 6:29:32 PM as an example. The same thing for A3 and B2. ...
Do you have any idea how I can achieve this? Is there a way to do that? Do I need some special code to accomplish this?
Thank you for your help.
BTHi,
a way is to create a store proc that make the calculation
(fetching results in a temp table).|||
yes you can
you can use formula like this
=ReportItems!A.Value.Subtract(ReportItems!B.Value)
where A and B are the name of the cells in your table
noteSubtractfunction in datetime returns timespan|||

Hi,
IMHO the formula
ReportItems!A.Value.Subtract(ReportItems!B.Value)
calculate difference between value of the same row.
I've understood that Salmiya have to calculate between a value of column A with
value of column B, but of previous row.

|||Yes you are right. I have to compare previous row with current one.
Thank you.|||tblDates :
PK A B
----------------

1 5/2/2005 09:39:02 AM	5/3/2005 06:29:32 PM
2 5/4/2005 08:21:31 AM	5/5/2005 07:02:29 PM
3 5/6/2005 09:52:33 AM	5/6/2005 07:04:31 PM
4 5/7/2005 09:20:33 AM	5/8/2005 08:26:36 AM

You can do :
Select [Difference] = A-IsNull((Select B from tblDates where PK=2 ),0) from tblDates where PK=3
HTH,
Best Regards,
Hemchand|||Hi.....
U can use =ReportItems!textbox1.Value - ReportItems!textbox2.Value
Then U will get it..........
Best Regards....

Comparing 2 files

Dear All,
Periodically, i need to copy a file from the source folder to destination
folder. the source file will be updated from time to time. Before copying
the file to destination folder, i need to compare the modified date of the
file, mean only copy the file to destination folder if the source file is
updated. How can we do this in SQL?
I will use the xp_cmdShell to copy the file. If use the xp_getfiledetails,
how can i get the information return fromt the stored procedure to check the
modified date? Or, is there any better method of doing this?
I am using SQL Server 2000.
Thanks & Best Regards,
EdwardInsert as below..and then you can compare ..
CREATE TABLE #FILE
(
name varchar(30) null,
size int null,
creationdate int null,
creationtime int null,
lastwrittendate int null,
lastwrittentime int null,
lastaccesseddate int null,
lastaccessedtime int null,
attributes int null
)
INSERT #FILE EXEC master..xp_getfiledetails @.FILE_NAME
MJKulangara
http://sqladventures.blogspot.com|||Hi
You could do the file copy and comparison using a DTS package and an ActiveX
script. See
http://www.sqldts.com/default.aspx?292
John
"Edward Low" <wc_low@.hotmail.com> wrote in message
news:eRZ8g9qLGHA.3936@.TK2MSFTNGP12.phx.gbl...
> Dear All,
> Periodically, i need to copy a file from the source folder to destination
> folder. the source file will be updated from time to time. Before copying
> the file to destination folder, i need to compare the modified date of the
> file, mean only copy the file to destination folder if the source file is
> updated. How can we do this in SQL?
> I will use the xp_cmdShell to copy the file. If use the xp_getfiledetails,
> how can i get the information return fromt the stored procedure to check
> the modified date? Or, is there any better method of doing this?
> I am using SQL Server 2000.
> Thanks & Best Regards,
>
> Edward
>

compare two text files

Dear all,

What software can compare two text files? They are contains about 100k data generated from Visual Basic 6.0 program and MS SQL 2000. I have many files to compare daily. Please give me some suggestions. Thanks.

Alex

I think VSS (source safe) is suitable for that|||

thanks Eisa. it is helpful.

Could you suggest some freewares?

|||Hi Alex,
I don't know if it is allowed to share free software here or not, howere you can serach google for compare text files free|||Thank your for your help|||Windows has a command line utility (fc) that compares two files. Type "fc /?" at a command prompt, and it will list the options/switches. It's a good solution if it has the features you need, because it's built into Windows, and you won't have to bother installing it everywhere you need it. If you want to use it in a job, you can invoke it with an operating system type step, or use xp_cmdshell if you need to call it after dynamically building the command at run-time. In a job, you would have to pipe the results to another text file, for review.|||

CompareIt! - cool

http://www.grigsoft.com/

|||thanks a lot

compare two text files

Dear all,

What software can compare two text files? They are contains about 100k data generated from Visual Basic 6.0 program and MS SQL 2000. I have many files to compare daily. Please give me some suggestions. Thanks.

Alex

I think VSS (source safe) is suitable for that|||

thanks Eisa. it is helpful.

Could you suggest some freewares?

|||Hi Alex,
I don't know if it is allowed to share free software here or not, howere you can serach google for compare text files free|||Thank your for your help|||Windows has a command line utility (fc) that compares two files. Type "fc /?" at a command prompt, and it will list the options/switches. It's a good solution if it has the features you need, because it's built into Windows, and you won't have to bother installing it everywhere you need it. If you want to use it in a job, you can invoke it with an operating system type step, or use xp_cmdshell if you need to call it after dynamically building the command at run-time. In a job, you would have to pipe the results to another text file, for review.|||

CompareIt! - cool

http://www.grigsoft.com/

|||thanks a lot

Compare two tables query result

dear all

i need to write SQL Query that compares 2 tables as folows:

lets say i have:

table A with A.A, A.B, A.C columns (Table A with Columns A,B,C)

table B with B.A, B.B, B.C columns (Table B with Columns A,B,C)

i need to compare and to mark (get in result Query) each row lets say in table B that was changed from table A

For Example

Table A

A B C

12 15 hello

15 17 adv

asd 19 23

14 rer 89

Table B

A B C

12 15 hello

15 19 adv

*** 19 23

14 rer 89

the result is the records which was changed from A to B

A B C

15 19 adv

*** 19 23

Thnks alot!

That is probably non-trivial in terms of performance:

Select

CASE WHEN A.A != A.A THEN '***' ELSE A.A AS A
CASE WHEN A.B != A.B THEN '***' ELSE A.B AS B

CASE WHEN A.C != A.C THEN '***' ELSE A.C AS C
FROM TableA
INNER Join TableB
ON A.IdentifierWhichcannotchange = b.IdentifierWhichcannotchange

You will need the IdentifierWhichcannotchange as elsewhere you cannot identify the matching rows, e.g. how to know that the if two rows look the same after the change from which rows they were actually sourced on.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

can you explain in more details the A.IdentifierWhichCannotchange, B.IdentifierWhichCannotchange?

the query result dows not change anything on the tables

p.s i run the query in msAccess

And very important thing that i forgotten to mention

The firsrt 3 columns are my Unique (Primary Key)

|||

You need an identitfier in both tables which cannot change, otherwise you won′t be able to match the rows. its like building a sum from multiple values and then afterwards trying to identity which values the sum is based on. In your case, if you have two rows containg the values

1;23;27
3;23;59

which are changed to

4 (changed);23;54 (changed)
4 (changed);23;55 (changed)

How would you know which row refers to which in the other table. they have lost their identifying attribute here (the combination of values). if you had some identifying value which is not supposed to change then you can easily compare the values in the rows refering to the non-changeable attribute (like I did in the query above)

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

OK

first thanks for the replies

so here is my tables and data:

t04 A B C D E F H I J 019 003 000028 1 004604070 1 06/05/2007 01/01/2001 003 019 003 000036 1 004604070 1 06/05/2007 01/01/2001 003 019 003 000044 1 030557268 2 06/05/2007 01/01/2001 003 t05 A B C D E F H I J 019 001 000004 1 028354470 1 15/05/2007 28/02/2002 001 019 003 000028 1 004604070 1 06/05/2007 31/03/1986 003 019 003 000036 1 004604070 1 06/05/2007 31/12/1989 002 019 003 000044 1 030557268 2 06/05/2007 31/03/1986 003 result A B C D E F H I J 019 001 000004 1 028354470 1 15/05/2007 28/02/2002 001 019 003 000036 1 004604070 1 06/05/2007 31/12/1989 002

my Id is Columns A,B,C

in the result we can notice 2 things

1) 019 001 000004 was added because it exists in t05 and not t04

2) the value in col j for 019 003 000036 was changed from 003 to 002

the goal is to create table of diffrences!

in general i can unified columns A,B,C to one column (if it helps) 019001000004 for examle

i quess i clarified my self better

|||

That will be something like:

Select

CASE WHEN A.D != A.D THEN '***' ELSE A.D AS D,

CASE WHEN A.E != A.E THEN '***' ELSE A.E AS E,

CASE WHEN A.F != A.F THEN '***' ELSE A.F AS F,

CASE WHEN A.H != A.H THEN '***' ELSE A.H AS H,

CASE WHEN A.I != A.I THEN '***' ELSE A.I AS I,

CASE WHEN A.J != A.JTHEN '***' ELSE A.J AS J

FROM TableA A
INNER Join TableB B
ON

A.A = B.A AND

A.B = B.B AND
A.C = C.C

Jens K. Suessmeyer

http://www.sqlserver2005.de

Compare row data and input data into a new field

Dear All
I have a simple query which I cannot seem to work out and would like some
help.
I have a small table with data like such
IDWeek_as_IntWeekEnding Check
ASome 5235/06/2005
ASome 53131/07/2005
ASome 54827/11/2005
ASome 5494/12/2005
ASome 60422/01/2006 1
what I need to do is to create a new column called check and put a "1" check
filed in the last one of the selection. In the example above it would be the
row with "604" in it but it is not always going to be "604" it could be
anything. So what I need to do is to look at each row and when it gets to the
last row that has the same id then to place a "1" in a check field.
The answer is easy using excel but I would like to take that intervension
out an incorporate it in a SQL script as part of the automation
Thanking you in advance
Regards
Newman
>>when it gets to the
last row that has the same id then to place a "1" in a check field. <<
With ref. to your above statement, how will you be defining last row, will
it be on the basis of the maximum number of week_as_int column for respective
id
OR will it be on the basis of maximum date from "weekending" column for
respective id?
If its on the basis of max of week_As_int column your query will look like
as shown in the following example, you can change the same query by selecting
max of weekending column.
--table and sample data
create table t
(IDvarchar(50),Week_as_Int int,WeekEnding datetime, chk int)
insert into t (id, week_As_int,weekending)
select 'ASome',523,'2005/06/5' union all
select 'ASome',548,'2005/11/27' union all
select 'ASome',549,'2005/12/4' union all
select 'ASome',604,'2005/01/22' union all
select 'bSome',700,'2005/06/5' union all
select 'bSome',549,'2005/12/4' union all
select 'bSome',604,'2005/01/22' union all
select 'cSome',700,'2005/06/5' union all
select 'cSome',701,'2005/12/4' union all
select 'cSome',704,'2005/01/22'
--update statement to update the column chk .
update t
set chk = 1
from t a join
(select "id", max(week_as_int) week_as_int
from t
group by "id") b on a.id = b.id and a.week_as_int = b.week_as_int
"Newman Emanouel" wrote:

> Dear All
> I have a simple query which I cannot seem to work out and would like some
> help.
> I have a small table with data like such
> IDWeek_as_IntWeekEnding Check
> ASome 5235/06/2005
> ASome 53131/07/2005
> ASome 54827/11/2005
> ASome 5494/12/2005
> ASome 60422/01/2006 1
> what I need to do is to create a new column called check and put a "1" check
> filed in the last one of the selection. In the example above it would be the
> row with "604" in it but it is not always going to be "604" it could be
> anything. So what I need to do is to look at each row and when it gets to the
> last row that has the same id then to place a "1" in a check field.
> The answer is easy using excel but I would like to take that intervension
> out an incorporate it in a SQL script as part of the automation
> Thanking you in advance
> Regards
> Newman
>
|||On Thu, 26 Jan 2006 19:45:02 -0800, Newman Emanouel wrote:

>Dear All
>I have a simple query which I cannot seem to work out and would like some
>help.
>I have a small table with data like such
>IDWeek_as_IntWeekEnding Check
>ASome 5235/06/2005
>ASome 53131/07/2005
>ASome 54827/11/2005
>ASome 5494/12/2005
>ASome 60422/01/2006 1
>what I need to do is to create a new column called check and put a "1" check
>filed in the last one of the selection. In the example above it would be the
>row with "604" in it but it is not always going to be "604" it could be
>anything. So what I need to do is to look at each row and when it gets to the
>last row that has the same id then to place a "1" in a check field.
>The answer is easy using excel but I would like to take that intervension
>out an incorporate it in a SQL script as part of the automation
>Thanking you in advance
>Regards
>Newman
>
Hi Newman,
Why store it in the table and recalculate it each time you need it?
CREATE VIEW YourView
AS
SELECT a.ID, a.Week_as_Int, a.WeekEnding,
CASE WHEN Week_as_Int = (SELECT MAX(b.Week_as_Int)
FROM YourTable AS b
WHERE b.ID = a.ID)
THEN 1
END AS Check
FROM YourTable AS a
Of course, if you insist on storing it in the table:
UPDATE YourTable
SET Check = 1
WHERE Week_as_Int = (SELECT MAX(b.Week_as_Int)
FROM YourTable AS b
WHERE b.ID = YourTable.ID)
Hugo Kornelis, SQL Server MVP
|||Hugo/Vishal
Thank you
your suggestions worked well
"Hugo Kornelis" wrote:

> On Thu, 26 Jan 2006 19:45:02 -0800, Newman Emanouel wrote:
>
> Hi Newman,
> Why store it in the table and recalculate it each time you need it?
> CREATE VIEW YourView
> AS
> SELECT a.ID, a.Week_as_Int, a.WeekEnding,
> CASE WHEN Week_as_Int = (SELECT MAX(b.Week_as_Int)
> FROM YourTable AS b
> WHERE b.ID = a.ID)
> THEN 1
> END AS Check
> FROM YourTable AS a
>
> Of course, if you insist on storing it in the table:
> UPDATE YourTable
> SET Check = 1
> WHERE Week_as_Int = (SELECT MAX(b.Week_as_Int)
> FROM YourTable AS b
> WHERE b.ID = YourTable.ID)
> --
> Hugo Kornelis, SQL Server MVP
>

Company-calendars

Dear All,

How to store a separated company-calendar for each client-company (about
500).
Company-calendar: workingdays, non-workingdays, meetings, etc.

Thanks,

FilipsCheck out this link:

http://www.aspfaq.com/show.asp?id=2519

HTH

--
Message posted via http://www.sqlmonster.com

Communication Link Failure

Dear experts~
My client workstation recently receives "ODBC failed -
Connection bluh bluh bluh... SQL Server: Communication
Link Failure" error message while trying to insert/update
a record. This happens on two workstations. This problem
would soon go away in few seconds and come back few hours
latter. What's interesting is when users reported
Communication Link Failure, I am still able to connect to
the SQL Server through other workstations.
I checked my Network Config. Both TCP and Name Pipes are
enabled. My server runs well for a longest time and I
haven't make any changes on Configs recently. I'd like to
know what would cause this problem occurs and Could this
be a result of Bad Network Connection rather than SQL
Server Config. Or, What I can do on SQL Server to get
rid of this problem.
I am running SQL Server 2000 SP2. The client workstion
uses MS Access .mdb file with linked table to connect to
SQL Server 2000.
Thank you soooooo much for your help~~~
Annie
.Annie,
More than likely you have a network error. I say this because the error is confined to 2 workstations and you are able to access the server from your workstation. A couple of things to try:
you can do a simple test of the network connection by using the ping command. Use the -n parameter to send a large number of echo requests. Pay attention to the statistics on lost packets and round-trip time.
If packets are being lost or the returns times are higher check the event logs on the workstations with the problems for network related software or hardware problems.
Make sure you are not having any DNS problems. Check if you can consistently link to the server by specifying the server ip address in the ODBC Datasource defintion
My experience with Named Pipes is that it can be slow on a tcp/ip network. I would configure the ODBC Datasource defintions to use only tcp/ip.
Good Luck
Lewis DiFelice
DBA
-- piyomeme@.yahoo.com wrote: --
Dear experts~
My client workstation recently receives "ODBC failed -
Connection bluh bluh bluh... SQL Server: Communication
Link Failure" error message while trying to insert/update
a record. This happens on two workstations. This problem
would soon go away in few seconds and come back few hours
latter. What's interesting is when users reported
Communication Link Failure, I am still able to connect to
the SQL Server through other workstations.
I checked my Network Config. Both TCP and Name Pipes are
enabled. My server runs well for a longest time and I
haven't make any changes on Configs recently. I'd like to
know what would cause this problem occurs and Could this
be a result of Bad Network Connection rather than SQL
Server Config. Or, What I can do on SQL Server to get
rid of this problem.
I am running SQL Server 2000 SP2. The client workstion
uses MS Access .mdb file with linked table to connect to
SQL Server 2000.
Thank you soooooo much for your help~~~
Annie
..