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 different Group's aggregate Values

Hi All,
In my report, i've 3 groups and i want to hide a group footer by
comparing a textbox value in group1 and a textbox value in group2, for
eg., i've Sum(Fields!Amount.Value,"table1_group1") in one of group1
footer's textbox and Sum(Fields!Amount.Value,"table1_group2") in one of
group2 footer's textbox, if both these values are same i want to hide
group1 footer, but i am getting "The value expression for the textbox
has a scope parameter that is not valid for an aggregate function. The
scope parameter must be set to a string constant that is equal to
either the name of a containing group, the name of a containing data
region, or the name of a data set.", any workaround methods for this?.
Your help is highly appreciated.
Thanks in advance
SenTextboxes are ReportItems. To hide a footer base on the value of two
textboxes set the Visible(Hidden) property to:
1. =ReportItems!textbox1.Value + ReportItems!textbox2.Value = 10 or
=IIF(ReportItems!textbox1.Value + ReportItems!textbox2.Value = 10,
False, True)
"Sen" wrote:
> Hi All,
> In my report, i've 3 groups and i want to hide a group footer by
> comparing a textbox value in group1 and a textbox value in group2, for
> eg., i've Sum(Fields!Amount.Value,"table1_group1") in one of group1
> footer's textbox and Sum(Fields!Amount.Value,"table1_group2") in one of
> group2 footer's textbox, if both these values are same i want to hide
> group1 footer, but i am getting "The value expression for the textbox
> has a scope parameter that is not valid for an aggregate function. The
> scope parameter must be set to a string constant that is equal to
> either the name of a containing group, the name of a containing data
> region, or the name of a data set.", any workaround methods for this?.
> Your help is highly appreciated.
> Thanks in advance
> Sen
>|||Hi,
Thanks for your suggestion.
I tried your option also, but i'm getting the following error,
"Report item expressions can only refer to other report items within the
same grouping scope or a containing grouping scope". The problem here is i'm
trying to compare 2 different group's textboxes. So i'm getting this error.
Any help is highly appreciated. I tried with other options of hidden
expressions and all, but it didn't help me. I cannot modify the Stored Procs
used for this report.
Thanks and Regards,
Sen
"OriginalStealth" wrote:
> Textboxes are ReportItems. To hide a footer base on the value of two
> textboxes set the Visible(Hidden) property to:
> 1. =ReportItems!textbox1.Value + ReportItems!textbox2.Value = 10 or
> =IIF(ReportItems!textbox1.Value + ReportItems!textbox2.Value = 10,
> False, True)
>
> "Sen" wrote:
> > Hi All,
> >
> > In my report, i've 3 groups and i want to hide a group footer by
> > comparing a textbox value in group1 and a textbox value in group2, for
> > eg., i've Sum(Fields!Amount.Value,"table1_group1") in one of group1
> > footer's textbox and Sum(Fields!Amount.Value,"table1_group2") in one of
> > group2 footer's textbox, if both these values are same i want to hide
> > group1 footer, but i am getting "The value expression for the textbox
> > has a scope parameter that is not valid for an aggregate function. The
> > scope parameter must be set to a string constant that is equal to
> > either the name of a containing group, the name of a containing data
> > region, or the name of a data set.", any workaround methods for this?.
> > Your help is highly appreciated.
> >
> > Thanks in advance
> > Sen
> >
> >sqlsql

compare row with previous row value in RDL

Hi All,
I have a table, which displays the rows binded, now I need to compare a
textbox value in the table with the previous value and if matches then I need
to display the text box values with paranthesis [value].
How do I compare values in the RDL
Thanks in advance
Balaji
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200509/1Not sure if this works in your case, but if you just need to get the
previous value in a table detail row, you could use the Previous(...)
aggregate function.
E.g. =iif(Fields!A.Value = Previous(Fields!A.Value), "(" & Fields!A.Value &
")", Fields!A.Value)
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"BALAJI K via SQLMonster.com" <u5178@.uwe> wrote in message
news:54f11b486df84@.uwe...
> Hi All,
> I have a table, which displays the rows binded, now I need to compare a
> textbox value in the table with the previous value and if matches then I
> need
> to display the text box values with paranthesis [value].
> How do I compare values in the RDL
> Thanks in advance
> Balaji
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200509/1

Compare results from SQL to textbox?

I am trying to make a user authentication system which pulls data from a SQL table (that has columns "UserID" and "PIN" in it).

How can I check to see what the user entered into the "UserID" and "PIN" textbox's on the page and compare those entries to the database to see if they match up? I have been able to make the below code to do the query itself, but I don't know how to check back and see if it actually returned a match or not.

Function CheckLogin(ByVal userID As String, ByVal pIN As String) As System.Data.DataSet
 Dim connectionString As String = "server='(local)'; trusted_connection=true; database='test'"
 Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
 Dim queryString As String = "SELECT [UserAuth].[UserID], [UserAuth].[PIN] FROM [UserAuth] WHERE (([UserAuth].["& _
"UserID] like @.UserID) AND ([UserAuth].[PIN] like @.PIN))"
 Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
 dbCommand.CommandText = queryString
 dbCommand.Connection = dbConnection
 Dim dbParam_userID As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_userID.ParameterName = "@.UserID"
 dbParam_userID.Value = userID
 dbParam_userID.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_userID)
 Dim dbParam_pIN As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_pIN.ParameterName = "@.PIN"
 dbParam_pIN.Value = pIN
 dbParam_pIN.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_pIN)
 Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter
 dataAdapter.SelectCommand = dbCommand
 Dim dataSet As System.Data.DataSet = New System.Data.DataSet
 dataAdapter.Fill(blah)
 Return dataSet
End Function

First, don't use "like". Next, you can check dataSet.Tables[0].Rows[0].Count and if it is non-zero, there is a match.

You might want to look at using a query that returns the Count, and then just call ExecuteScaler() on the command rather than doing what you are doing.

Another thought: rather than storing the PID in clear text, excrypt it, or better yet, use a one-way hash.|||Well I just switched from using the free development tool on www.asp.net to ASP.NET included with Visual Studio and now I'm totally confused. Does anyone have any code snippets to do anything remotely close to this that I could use as an example? I'm so lost.|||This article will show you most of what you need to know.

command textbox in job has limitations ?

Hi ,
Is there any word limitations in the command text box of a job Management --
> SQL Server Agent --> Jobs '
it seems there is and it has given me some issue.
i have pasted all the SQL statements into a .sql file but how do i call that
file ?
i know using Osql is able to do so but is there other easier method (i.e i
dun have to create a batch file with the osql commands) w/o having to create
too many files
apreciate ur advice
tks & rdgs
Message posted via http://www.webservertalk.commaxzsim via webservertalk.com (u14644@.uwe) writes:
> it seems there is and it has given me some issue.
> i have pasted all the SQL statements into a .sql file but how do i call
> that file ?
> i know using Osql is able to do so but is there other easier method (i.e
> i dun have to create a batch file with the osql commands) w/o having to
> create too many files
You could put the stuff in a stored procedure, although this has some
implications, and depending on what your script does it may not be
practical.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||tks Erland Sommarskog , i have used DTS and put the script in the SQLTask
Erland Sommarskog wrote:
>[quoted text clipped - 4 lines]
>You could put the stuff in a stored procedure, although this has some
>implications, and depending on what your script does it may not be
>practical.
>
Message posted via http://www.webservertalk.com

Combo box to exclude blank input

Hi
I have a combo box that displays a list of company names. Some entries do
not have a company name on the form so the textbox remains blank. Therefore
in the combo box there are alot of blanks in the list. How do I get the
combo box to just list out the company names without the blanks?
At the moment I have this:
SELECT Contacts.ID, Contacts.CompanyName
FROM Contacts;
Thanks in advance
SeanThe following will exclude NULL or blank CompanyName rows:
SELECT Contacts.ID, Contacts.CompanyName
FROM Contacts
WHERE
Contacts.CompanyName IS NOT NULL AND
Contacts.CompanyName <> '';
Hope this helps.
Dan Guzman
SQL Server MVP
"Sean" <Sean@.discussions.microsoft.com> wrote in message
news:9930ACDB-8E70-45B8-94B1-89A8DF57B60F@.microsoft.com...
> Hi
> I have a combo box that displays a list of company names. Some entries do
> not have a company name on the form so the textbox remains blank.
> Therefore
> in the combo box there are alot of blanks in the list. How do I get the
> combo box to just list out the company names without the blanks?
> At the moment I have this:
> SELECT Contacts.ID, Contacts.CompanyName
> FROM Contacts;
> Thanks in advance
> Sean|||Isn't there a Company table that, by definition, wouldn't have empty
company names?
If not, then one should be added - basic normalization.
Sean wrote:
> Hi
> I have a combo box that displays a list of company names. Some entries do
> not have a company name on the form so the textbox remains blank. Therefor
e
> in the combo box there are alot of blanks in the list. How do I get the
> combo box to just list out the company names without the blanks?
> At the moment I have this:
> SELECT Contacts.ID, Contacts.CompanyName
> FROM Contacts;
> Thanks in advance
> Sean