|
Home > Archive > MS SQL Server > November 2006 > How to select previous entered values MS-SQL Server 2000
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
How to select previous entered values MS-SQL Server 2000
|
|
| Sharon 2006-11-27, 7:12 pm |
| Hello EveryOne,
I have to write a stored procedure for selecting staff timings, the
criteria will be selecting the staff timings based on current month and
username(he has to be an active user). If the person did not enter the
values for the current month then the values from his last entry are
selected.
I have succeded in selecting the values for the current month, but i am
stuck at how to select the last entered values for that user. As i am
new please help me out with this. See below for the procedure i have
written.
ALTER PROCEDURE c_sp_GetTime
@UserName varchar(100),
@Month int
As
BEGIN
Select Starttime, EndTime, LunchStart, LunchStartHour,
LunchStartMin, LunchDuration
from t_staffworkinghours
WHERE UserName=@UserName
AND Month = @Month
And Exists
(Select *
from t_StaffDetails
where EmpStatus='Active'
And t_StaffDetails. StaffId=t_StaffWorki
nghours.StaffID)
RETURN -1
END
Thanks in advance, Shilpa.
| |
| Arnie Rowland 2006-11-27, 7:13 pm |
| Something like this might work for you: (first look for current month values, and if none found, retrieve the last entry for the user.)
ALTER PROCEDURE c_sp_GetTime
( @UserName varchar(100),
@Month int
)
AS
BEGIN
SELECT
h.Starttime,
h.EndTime,
h.LunchStart,
h.LunchStartHour,
h.LunchStartMin,
h.LunchDuration
FROM t_staffworkinghours h
JOIN t_StaffDetails d
ON h.StaffID = d.StaffID
WHERE ( h.UserName = @UserName
AND h.[Month] = @Month
AND d.EmpStatus = 'Active'
)
IF @@ROWCOUNT = 0
SELECT TOP 1
h.Starttime,
h.EndTime,
h.LunchStart,
h.LunchStartHour,
h.LunchStartMin,
h.LunchDuration
FROM t_staffworkinghours h
JOIN t_StaffDetails d
ON h.StaffID = d.StaffID
WHERE ( h.UserName = @UserName
AND d.EmpStatus = 'Active'
)
ORDER BY h.[Month] DESC
RETURN -1
END
As a side note, you should NOT use reserved words for table and column names, i.e., [Month].
Also, if you were to store the Starttime/Endtime as DATETIME datatypes, you could eliminate the LunchStartHour, LunchStartMin, LunchDuration, and Month columns since those values are always easily derived from the StartTime/EndTime.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the top yourself.
- H. Norman Schwarzkopf
"Sharon" <sharonrao123@yahoo.com.au> wrote in message news:1164671574.212226.78480@f16g2000cwb.googlegroups.com...
> Hello EveryOne,
>
> I have to write a stored procedure for selecting staff timings, the
> criteria will be selecting the staff timings based on current month and
> username(he has to be an active user). If the person did not enter the
> values for the current month then the values from his last entry are
> selected.
>
> I have succeded in selecting the values for the current month, but i am
> stuck at how to select the last entered values for that user. As i am
> new please help me out with this. See below for the procedure i have
> written.
>
> ALTER PROCEDURE c_sp_GetTime
> @UserName varchar(100),
> @Month int
>
> As
> BEGIN
> Select Starttime, EndTime, LunchStart, LunchStartHour,
> LunchStartMin, LunchDuration
> from t_staffworkinghours
> WHERE UserName=@UserName
> AND Month = @Month
> And Exists
> (Select *
> from t_StaffDetails
> where EmpStatus='Active'
> And t_StaffDetails. StaffId=t_StaffWorki
nghours.StaffID)
>
> RETURN -1
> END
>
> Thanks in advance, Shilpa.
>
| |
| Sharon 2006-11-28, 12:12 am |
| Hello Arnie,
Thanks for your time and i have implemented all your suggestions, But
when i use this procedure in a webform I get this error message "No
data found". Any suggestions on where i am going wrong.
Arnie Rowland wrote:
> Something like this might work for you: (first look for current month values, and if none found, retrieve the last entry for the user.)
>
>
> ALTER PROCEDURE c_sp_GetTime
> ( @UserName varchar(100),
> @Month int
> )
> AS
> BEGIN
> SELECT
> h.Starttime,
> h.EndTime,
> h.LunchStart,
> h.LunchStartHour,
> h.LunchStartMin,
> h.LunchDuration
> FROM t_staffworkinghours h
> JOIN t_StaffDetails d
> ON h.StaffID = d.StaffID
> WHERE ( h.UserName = @UserName
> AND h.[Month] = @Month
> AND d.EmpStatus = 'Active'
> )
>
> IF @@ROWCOUNT = 0
>
> SELECT TOP 1
> h.Starttime,
> h.EndTime,
> h.LunchStart,
> h.LunchStartHour,
> h.LunchStartMin,
> h.LunchDuration
> FROM t_staffworkinghours h
> JOIN t_StaffDetails d
> ON h.StaffID = d.StaffID
> WHERE ( h.UserName = @UserName
> AND d.EmpStatus = 'Active'
> )
> ORDER BY h.[Month] DESC
>
> RETURN -1
> END
>
> As a side note, you should NOT use reserved words for table and column names, i.e., [Month].
>
> Also, if you were to store the Starttime/Endtime as DATETIME datatypes, you could eliminate the LunchStartHour, LunchStartMin, LunchDuration, and Month columns since those values are always easily derived from the StartTime/EndTime.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
>
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> You can't help someone get up a hill without getting a little closer to the top yourself.
> - H. Norman Schwarzkopf
>
>
> "Sharon" <sharonrao123@yahoo.com.au> wrote in message news:1164671574.212226.78480@f16g2000cwb.googlegroups.com...
> ------ =_NextPart_000_00C8_
01C71240.92B3EAE0
> Content-Type: text/html; charset=iso-8859-1
> Content-Transfer-Encoding: quoted-printable
> X-Google-AttachSize: 7586
>
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> <HTML><HEAD>
> <META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
> <META content="MSHTML 6.00.5730.11" name=GENERATOR>
> <STYLE></STYLE>
> </HEAD>
> <BODY>
> <DIV><FONT face=Arial size=2>Something like this might work for you: (first look
> for current month values, and if none found, retrieve the last entry for the
> user.)</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><BR><FONT face="Courier New" size=2>ALTER PROCEDURE
> c_sp_GetTime<BR> ( @UserName
> varchar(100),<BR> @Month int<BR>
> )<BR>AS<BR> BEGIN<BR> SELECT
> <BR> h.Starttime,
> <BR> h.EndTime,
> <BR> h.LunchStart,
> <BR>
> h.LunchStartHour,<BR>
> h.LunchStartMin, <BR>
> h.LunchDuration<BR> FROM t_staffworkinghours
> h<BR> JOIN t_StaffDetails
> d<BR> ON
> h.StaffID = d.StaffID<BR> WHERE (
> h.UserName =
> @UserName<BR>
> AND h.[Month] =
> @Month<BR> AND
> d.EmpStatus =
> 'Active'<BR>
> )<BR>
> <BR> IF @@ROWCOUNT = 0 </FONT></DIV>
> <DIV><FONT face="Courier New" size=2></FONT> </DIV>
> <DIV><FONT face="Courier New"
> size=2> SELECT TOP
> 1<BR>
> h.Starttime,
> <BR>
> h.EndTime,
> <BR>
> h.LunchStart,
> <BR>
> h.LunchStartHour,<BR>
> h.LunchStartMin,
> <BR>
> h.LunchDuration<BR> FROM
> t_staffworkinghours
> h<BR> JOIN
> t_StaffDetails
> d<BR>
> ON h.StaffID = d.StaffID<BR>
> WHERE ( h.UserName =
> @UserName<BR>
> AND d.EmpStatus =
> 'Active'<BR>
> )<BR> ORDER BY h.[Month]
> DESC<BR>
> <BR> RETURN -1<BR> END</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2>As a side note, you should NOT use reserved words
> for table and column names, i.e., [Month]. </FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2>Also, if you were to store the Starttime/Endtime as
> DATETIME datatypes, you could eliminate the <FONT
> face="Courier New">LunchStartHour,& amp;nbsp;LunchStartM
in,& amp;nbsp;LunchDurati
on, <FONT
> face=Arial>and</FONT> Month </FONT><FONT face=Arial>columns since those values
> are always easily derived from the StartTime/EndTime.<BR></DIV></FONT></FONT>
> <DIV><FONT face=Arial size=2>-- <BR>Arnie Rowland, Ph.D.<BR>Westwood Consulting,
> Inc</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2>Most good judgment comes from experience. <BR>Most
> experience comes from bad judgment. <BR>- Anonymous</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2>You can't help someone get up a hill without
> getting a little closer to the top yourself.<BR>- H. Norman
> Schwarzkopf</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2>"Sharon" <</FONT><A
> href=" mailto:sharonrao123@
yahoo.com.au"><FONT face=Arial
> size=2>sharonrao123@yahoo.com.au</FONT></A><FONT face=Arial size=2>> wrote in
> message </FONT><A
> href="news:1164671574.212226.78480@f16g2000cwb.googlegroups.com"><FONT
> face=Arial
> size=2>news:1164671574.212226.78480@f16g2000cwb.googlegroups.com</FONT></A><FONT
> face=Arial size=2>...</FONT></DIV><FONT face=Arial size=2>> Hello
> EveryOne,<BR>> <BR>> I have to write a stored procedure for selecting
> staff timings, the<BR>> criteria will be selecting the staff timings based on
> current month and<BR>> username(he has to be an active user). If the person
> did not enter the<BR>> values for the current month then the values from his
> last entry are<BR>> selected.<BR>> <BR>> I have succeded in selecting
> the values for the current month, but i am<BR>> stuck at how to select the
> last entered values for that user. As i am<BR>> new please help me out with
> this. See below for the procedure i have<BR>> written.<BR>> <BR>>
> ALTER PROCEDURE c_sp_GetTime<BR>> @UserName varchar(100),<BR>>
> @Month int<BR>> <BR>> As<BR>>
> BEGIN<BR>> Select Starttime, EndTime,
> LunchStart, LunchStartHour,<BR>> LunchStartMin,
> LunchDuration<BR>> from
> t_staffworkinghours<BR>> WHERE </FONT><A
> href=" mailto:UserName=@Use
rName"><FONT face=Arial
> size=2>UserName=@UserName</FONT></A><BR><FONT face=Arial
> size=2>> AND Month =
> @Month<BR>> And
> Exists<BR>>
> (Select
> *<BR>>
> from
> t_StaffDetails<BR>>
> where
> EmpStatus='Active'<BR>>
> And
> t_StaffDetails. StaffId=t_StaffWorki
nghours.StaffID)<BR>>
> <BR>> RETURN -1<BR>> END<BR>> <BR>> Thanks in advance,
> Shilpa.<BR>></FONT></BODY></HTML>
>
> ------ =_NextPart_000_00C8_
01C71240.92B3EAE0--
| |
| Sharon 2006-11-28, 12:12 am |
| Hello Arnie,
Just elobarating on the issue below, Please see below for the methods i
have used in the webpage to display the values using the procedure. I
am getting the error " No Data Found" not able to figure out where i am
doing wrong
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
If Not IsPostBack() Then
Call displaydata()
End If
End Sub
Private Sub GetTime()
Dim UserName As String = "Megan Sproats"
Dim Month As Integer = 11
Try
Dim sqlconnection3 As New
System.Data.SqlClient.SqlConnection("connectionstring")
Dim cmd2 As New SqlClient.SqlCommand
cmd2.CommandText = "c_sp_GetTime"
cmd2.CommandType = CommandType.StoredProcedure
cmd2.Connection = sqlconnection3
cmd2.Parameters.Clear()
Dim myparam As New SqlClient.SqlParameter
Dim myparam2 As New SqlClient.SqlParameter
myparam.ParameterName = "@UserName"
myparam.Direction = ParameterDirection.Input
myparam.SqlDbType = SqlDbType.VarChar
myparam.Value = UserName
cmd2.Parameters.Add(myparam)
myparam2.ParameterName = "@Month"
myparam2.Direction = ParameterDirection.Input
myparam2.SqlDbType = SqlDbType.Int
myparam2.Value = Month
cmd2.Parameters.Add(myparam2)
DATime.SelectCommand = cmd2
sqlconnection3.Open()
Catch ex As Exception
Dim param As String
param = "message=" & Server.UrlEncode("GetRego Error -
" & ex.Message)
End Try
End Sub
Private Sub displaydata()
Dim dailyStartTime, dailyEndTime As DateTime
Dim lunchstarthour, lunchstartmin, dailyLunchPeriod As
Integer
Call GetTime()
Dim reader As Data.SqlClient.SqlDataReader
reader = DATime.SelectCommand.ExecuteReader
If reader.HasRows Then
While reader.Read
dailyStartTime = reader.Item("StartTime")
dailyEndTime = reader.Item("EndTime")
lunchstarthour = reader.Item("LunchStartHour")
lunchstartmin = reader.Item("LunchStartMin")
dailyLunchPeriod = reader.Item("LunchDuration")
End While
reader.NextResult()
reader.Close()
Label1.Text = dailyStartTime
Label2.Text = dailyEndTime
Else
Dim param As String
param = "message=" & Server.UrlEncode("No data found")
lblfeedback.Text = param
End If
End Sub
Sharon wrote:[color=darkred
]
> Hello Arnie,
>
> Thanks for your time and i have implemented all your suggestions, But
> when i use this procedure in a webform I get this error message "No
> data found". Any suggestions on where i am going wrong.
>
>
> Arnie Rowland wrote:
| |
| Uri Dimant 2006-11-28, 5:16 am |
| Sharon
Can you add another column as Last_Modified or somethinmg like that and
having a trigger that updates the column with GETDATE() ?
"Sharon" <sharonrao123@yahoo.com.au> wrote in message
news:1164693449.988382.219130@80g2000cwy.googlegroups.com...
> Hello Arnie,
>
> Just elobarating on the issue below, Please see below for the methods i
> have used in the webpage to display the values using the procedure. I
> am getting the error " No Data Found" not able to figure out where i am
> doing wrong
>
> Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles MyBase.Load
> 'Put user code to initialize the page here
> If Not IsPostBack() Then
> Call displaydata()
> End If
> End Sub
>
> Private Sub GetTime()
> Dim UserName As String = "Megan Sproats"
> Dim Month As Integer = 11
>
> Try
> Dim sqlconnection3 As New
> System.Data.SqlClient.SqlConnection("connectionstring")
> Dim cmd2 As New SqlClient.SqlCommand
> cmd2.CommandText = "c_sp_GetTime"
> cmd2.CommandType = CommandType.StoredProcedure
> cmd2.Connection = sqlconnection3
> cmd2.Parameters.Clear()
> Dim myparam As New SqlClient.SqlParameter
> Dim myparam2 As New SqlClient.SqlParameter
> myparam.ParameterName = "@UserName"
> myparam.Direction = ParameterDirection.Input
> myparam.SqlDbType = SqlDbType.VarChar
> myparam.Value = UserName
> cmd2.Parameters.Add(myparam)
> myparam2.ParameterName = "@Month"
> myparam2.Direction = ParameterDirection.Input
> myparam2.SqlDbType = SqlDbType.Int
> myparam2.Value = Month
> cmd2.Parameters.Add(myparam2)
> DATime.SelectCommand = cmd2
> sqlconnection3.Open()
> Catch ex As Exception
> Dim param As String
> param = "message=" & Server.UrlEncode("GetRego Error -
> " & ex.Message)
> End Try
> End Sub
>
> Private Sub displaydata()
> Dim dailyStartTime, dailyEndTime As DateTime
> Dim lunchstarthour, lunchstartmin, dailyLunchPeriod As
> Integer
> Call GetTime()
> Dim reader As Data.SqlClient.SqlDataReader
> reader = DATime.SelectCommand.ExecuteReader
> If reader.HasRows Then
>
> While reader.Read
> dailyStartTime = reader.Item("StartTime")
> dailyEndTime = reader.Item("EndTime")
> lunchstarthour = reader.Item("LunchStartHour")
> lunchstartmin = reader.Item("LunchStartMin")
> dailyLunchPeriod = reader.Item("LunchDuration")
> End While
> reader.NextResult()
> reader.Close()
> Label1.Text = dailyStartTime
> Label2.Text = dailyEndTime
> Else
> Dim param As String
> param = "message=" & Server.UrlEncode("No data found")
> lblfeedback.Text = param
> End If
> End Sub
>
> Sharon wrote:
>
| |
| mdGandhi 2006-11-28, 5:16 am |
| Check the output of the stored procedure in query analyzer. It should
return rows.
>From the code on the webpage, it is likely that the data reader for
executing the stored procedure has a different scope.
The datareader being used for reading the values is in a different
function.
Let me know if this helps.
Manisha Gandhi
Uri Dimant wrote:[color=darkred
]
> Sharon
> Can you add another column as Last_Modified or somethinmg like that and
> having a trigger that updates the column with GETDATE() ?
>
>
>
>
>
>
>
> "Sharon" <sharonrao123@yahoo.com.au> wrote in message
> news:1164693449.988382.219130@80g2000cwy.googlegroups.com...
| |
| Sharon 2006-11-28, 7:14 pm |
| i checked in the query analyser the procedure returns an empty row for
the first select statement and one row for the second select statement,
Now how do i use this stored procedure in a webform to display the
data. The data reader seems to read the first select statement and as
it does not have any rows it returns false thats what i think so.
Please let me know any suggestions .
Thanks in advance, Shilpa.
mdGandhi wrote:[color=darkred
]
> Check the output of the stored procedure in query analyzer. It should
> return rows.
> executing the stored procedure has a different scope.
> The datareader being used for reading the values is in a different
> function.
>
> Let me know if this helps.
>
> Manisha Gandhi
>
> Uri Dimant wrote:
| |
| Arnie Rowland 2006-11-28, 7:14 pm |
| The procedure code that I provided you will return ONLY one row. Only one of
the SELECT statements will execute -not both.
It will return an empty row only if there is no history for the UserName.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Sharon" <sharonrao123@yahoo.com.au> wrote in message
news:1164753102.911214.9760@l39g2000cwd.googlegroups.com...
>i checked in the query analyser the procedure returns an empty row for
> the first select statement and one row for the second select statement,
> Now how do i use this stored procedure in a webform to display the
> data. The data reader seems to read the first select statement and as
> it does not have any rows it returns false thats what i think so.
>
> Please let me know any suggestions .
>
> Thanks in advance, Shilpa.
> mdGandhi wrote:
>
| |
| Sharon 2006-11-30, 12:12 am |
| Hello Arnie,
Please ignore me if i am wrong, you have said in your reply that only
one select statement is executed not both. I have executed the
procedure in the query analyser with two senarios
First Senario: The user has entered the staff timings for the current
month
The first Select statement is executed and the message i get is
(1 row(s) affected)
Second Senario: The user has entered the staff timings for the previous
month, no entry for current month. The messages i get in query analyser
(0 row(s) affected)
(1 row(s) affected)
what does the message 0 rows affected mean, I am confused here if only
one select statement is executed why i am i getting two messages.
Please advice.
Thank you, Shilpa.
Arnie Rowland wrote:[color=darkred
]
> The procedure code that I provided you will return ONLY one row. Only one of
> the SELECT statements will execute -not both.
>
> It will return an empty row only if there is no history for the UserName.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
>
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> You can't help someone get up a hill without getting a little closer to the
> top yourself.
> - H. Norman Schwarzkopf
>
>
> "Sharon" <sharonrao123@yahoo.com.au> wrote in message
> news:1164753102.911214.9760@l39g2000cwd.googlegroups.com...
| |
| Arnie Rowland 2006-11-30, 12:12 am |
| Let me try to correct the misunderstanding. I see that my hasty response was
very confusing since I did not reread my original post and was not properly
responding to your last question.
You are correct, in the second scenario, an empty resultset is returned for
the first select, and a potentially empty resultset is returned for the
second select.
Now how to handle that in the application.
(Here is a simplified VB.NET example...)
Do While dr.Read
'Assign the dr.fields
Loop
dr.NextResult()
Do While dr.Read
'Assign the dr.fields
Loop
Obviously, if the first resultset is empty, the code skips to the second
resultset. And if the second resultset is also empty, then nothing is
displayed.
I hope this helps.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Sharon" <sharonrao123@yahoo.com.au> wrote in message
news:1164851774.595746.50840@l39g2000cwd.googlegroups.com...
> Hello Arnie,
>
> Please ignore me if i am wrong, you have said in your reply that only
> one select statement is executed not both. I have executed the
> procedure in the query analyser with two senarios
>
> First Senario: The user has entered the staff timings for the current
> month
>
> The first Select statement is executed and the message i get is
>
> (1 row(s) affected)
>
> Second Senario: The user has entered the staff timings for the previous
> month, no entry for current month. The messages i get in query analyser
>
> (0 row(s) affected)
> (1 row(s) affected)
>
> what does the message 0 rows affected mean, I am confused here if only
> one select statement is executed why i am i getting two messages.
> Please advice.
>
> Thank you, Shilpa.
>
>
> Arnie Rowland wrote:
>
| |
| Sharon 2006-11-30, 12:12 am |
| Hi Arnie,
Thanks for your patience it worked perfect.
cheers, Shilpa.
Arnie Rowland wrote:[color=darkred
]
> Let me try to correct the misunderstanding. I see that my hasty response was
> very confusing since I did not reread my original post and was not properly
> responding to your last question.
>
> You are correct, in the second scenario, an empty resultset is returned for
> the first select, and a potentially empty resultset is returned for the
> second select.
>
> Now how to handle that in the application.
>
> (Here is a simplified VB.NET example...)
>
> Do While dr.Read
> 'Assign the dr.fields
> Loop
> dr.NextResult()
> Do While dr.Read
> 'Assign the dr.fields
> Loop
>
> Obviously, if the first resultset is empty, the code skips to the second
> resultset. And if the second resultset is also empty, then nothing is
> displayed.
>
> I hope this helps.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
>
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> You can't help someone get up a hill without getting a little closer to the
> top yourself.
> - H. Norman Schwarzkopf
>
>
> "Sharon" <sharonrao123@yahoo.com.au> wrote in message
> news:1164851774.595746.50840@l39g2000cwd.googlegroups.com...
|
|
|
|
|