i have a column of dates.
i make an index or key for this column so that
i can perform binary search i hope.
If the search is not succesful how do
i get the query to return the two dates
surrounding the date i was searching for.
Okay, I have a table that is arranged like so:
ID: (primary, auto generated)
Work order : (non unique foreign key)
Status: Internal status
Modified date: date time of status change to work order
This keeps track of our work order status changes. We'd like to trend our internal orders and monitor status durations (so to know how long each work order spend on a particular status). Now, the only thing is missing is an "end date/time", thus I must generate it in either a query or a SQL report form. The issue, is that i need the "modified date" from the next highest combination of work order + ID values (since they are the only reference). Is there any way to get this to happen? So essentially, if I sort by "work order" and then "id", i'd want the "modified date" value from line 2 to be on line 1, and so on. Now, if the "work order" number changes, or there isn't a higher work order+ID, we leave it blank and move on. Any ideas?? I'm really stuck.
Thanks in advance,
Josh
I have a need to execute a query in T-SQL on a numeric field in a SQL table.
However if there is no exact match I'd likea query that will return the row that is just below my value.
As an example if the table has values: 1,2,4,5,7,9, 15 and 20 for instance and I am matching with a varibale containing the value 9 then I'd like it to return that row. however if my variable has the value 19 I want the row containign 15 returned.
Is this possible? Can anyone help me with such a query?
Regards
Clive
I'm looking for a way to search for return characters (CHAR (13) or CHAR(10)) in VARCHAR fields. More specifically, either a way to remove them from the result set or replace them throughout the database.
Bonus Question:
Why can't users take a minute and fix their cut and paste into the address 1 field so that address 2 is in the right field (instead of on address 1 with returns in the middle of the data)?! Sheesh!
~Charles
Hi,
I have a table which has a few fields, one being "datetime_traded". I need to write a query which returns the row which has the closest time (down to second) given a date/time. I'm using MS SQL.
Here's what I have so far:
Code:
select * from TICK_D
where datetime_traded = (select min( abs(datediff(second,datetime_traded , Convert(datetime,'2005-05-30:09:31:09')) ) ) from TICK_D)
But I get an error - "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.".
Does anyone know how i could do this? Thanks a lot for any help!
Dear Sir,
I am building a website some what like B2B portal using asp.net and access database. I want to provide a search facility to the user through which they can search products in our database.
Can you provide me a strong SQL Query for that. Or is there any other way of doing that.
Please help me.
Thanking You
Suraj
Hello Gang,
I have a strange problem that I haven't dealt with before.
I need to execute a piece of code based on date ranges. If the date range is:
Scenario 1:between 02/28 (Feb 28) and 07/31 (July 31) do x
-----------------------------------------------------------
Scenario 2:between 08/01 (Aug 1) and 01/31 (Jan 31) do y
Can anyone help me with this code. I am having a SCD (Stupid Coder Day) and can't seem to do anything that is scalable - like accounting for leap years (Feb issue) and the fact that Scenario # 2 above is between 2 different calender years.
Your help is much appreciated.
JJOSHI
Hello Gang,
I have a strange problem that I haven't dealt with before.
I need to execute a piece of code based on date ranges. If the date range is:
Scenario 1:between 02/28 (Feb 28) and 07/31 (July 31) do x
-----------------------------------------------------------
Scenario 2:between 08/01 (Aug 1) and 01/31 (Jan 31) do y
I am trying to automate a report. The report is supposed to generate a result that will differ based on the date ranges going into the future. E.g.
[1]. If the run date of the report is between '2/1/20xx' and '7/31/20xx' display <ABC> or
[2]. If the run date of the report is between '8/1/20xx' and '1/31/20xx' display <PQR>
In example # 2. I am moving from one year to the next (July to Dec and the one extra month of Jan). So for example, if the guy runs the report between August of 2008 and January of 2009, display <PQR>.
How do I achieve both # 1 & 2 above in a code? Does this explain better.
Hi,
How do you do a case sensitive searching without having the database case sensitive or is this even possible. There are times we want to perform case sensitive searching and case insensitive searching.
I found this article, but is that the suggested way. seems a bit bad, but if it is the way I am willing to use it.
http://sqlserver2000.databases.aspfaq.com/how-can-i-make-my-sql-queries-case-sensitive.html
Thanks,
Matt
hi;
i want to get some results for new my stored proce is below;
create Procedure HaberleriGetir
@Kelime varchar(50),
@tarih1 smalldatetime,
@tarih2 smalldatetime,
@KatID int,
@lang char(5)
as
if @lang = 'Hepsi'
select * from Haberler where HKatID = @KatID and Metin like %@Kelime% or Baslik like %@Kelime% and Tarih between @tarih1 and @tarih2
else
select * from Haberler where HKatID = @KatID and lang = @lang and Metin like %@Kelime% or Baslik like %@Kelime% and Tarih between @tarih1 and @tarih2
incorrect near @Kelime....
and i can't get a result between two dates like @tarih1 & @tarih2
In my asp.net web application I used two textboxes for date1 and date2 to search records I have used two variable in my stored procedure @OrderDate1 datetime,@OrderDate2 datetime,for todays record (values for date1 and Date2)set @OrderDate1='09/20/2007 12:00 AM';set @OrderDate2='09/20/2007 11:59 PM';for records between (values for date1 and Date2)set @OrderDate1='09/20/2007 12:00 AM';set @OrderDate2='09/24/2007 11:59 PM';I have to search the todays records OR records between these two days (OrderDate >= @OrderDate1 ) AND (OrderDate <= @OrderDate2) It
works fine in my local server>>It finds the todays records by
ginving same date (@OrderDate1 and @OrderDate2) and records between two
dates but it does not work for sql server which is outside india ...if it is problem of date format how i can solve that.. Thanks
I would like to search MySQL table for names by selecting the first letter of the name.
eg: SELECT * FROM names WHERE lastName "begins with" M (or Mi, etc)
Right now if I use LIKE I get all names that contain "M".
Thanks.
PeterM
I am interested in creating a query that will test if a value is the same in a particular field.
For example, if the value is "0", or "000", or "000000" or "333", "444444", I would like to extract it. Otherwise omit the value.
I want to do a calc using the closest birthday...
declare @bday datetime ,@past_bday int ,@next_bday int ,@age int
set @bday = '12/25/1973'
--
-- some code goes here to assign values to past & next bday
--
if @past_bday < @next_bday
set @age = @age
else if @past_bday > @next_bday
set @age = @age + 1
TIA
JeffP...
Is there any way in SQL of selecting a date closest to another date.
i.e. I have a date say 4/4/2002, and in the database I have dates 4/2/2002 and earlier and 4/7/2002 and later.
I want to do a query and return the date 4/2/2002 as it is only 2 days away even though it is an earlier date.
So short of writing a loop and saying if the date is eq then good else, take date and add a day if not correct take initial date and subtract a day etc etc. This way is proving to be a little too processor heavy.
Thanks
Iβm trying to write a query that will select closest value to Amount field in #TestValue without go over #SM.GMAdj value.Outcome of the query should be something like
PercRate Amount Code GMAdjPayment
-------------- ----------- ---------------------------------
6.00 ----172715 -----13-------171314.36------900
7.00 ----200200 -----18-------200299.00------500
Tables are:
SELECT PercRate, Amount, Code
INTO #TestValues
FROM (
SELECT 6.00, 172715, 13 UNION ALL
SELECT 6.50, 172716, 13 UNION ALL
SELECT 7.00, 181351, 13 UNION ALL
SELECT 7.50, 190419, 13 UNION ALL
[code]...
Hello,I need help in writing a SQL statement in MS SQL Server 2000 to selectthe latest date (i.e., the date closest to or equal to the current date)for a given date.For example, in a table I have the following records:Date Exchange-Rate01/Sep/03 0.5505/Sep/03 0.59If the given date is 02/Sep/03, then the rate 0.55 should be return.If the given date is 03/Sep/03, then the rate 0.55 should be return.If the given date is 04/Sep/03, then the rate 0.59 should be return.Thanks in advanced,Benny*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 8 Replies View RelatedI'm trying to manipulate the data in my Service_2 column to round off the Estimated_Miles to the closest 5K miles. I tried the round function (Round(EstimatedMiles, -4)) but this does not exactly give me the results I want. Below is my result set:
Row_ID
Service_2
Estimated_Miles
1
40000
44012
2
50000
46124
3
120000
121512
4
30000
31857
5
10000
7547
6
80000
79500
And below is what I'd like to have
Row_ID
Service_2
Estimated_Miles
1
45000
44012
2
50000
46124
3
125000
121512
4
35000
31857
5
10000
7547
6
80000
79500
The range of data I'm working with is pretty broad meaning my Min & Max Estimated_Miles is between 0 - 1,000,000. In essence, I would like to say is if the estimated mileage is between 0-4999 then Service_2 = the closest 5K miles otherwise round to the closest 10K miles. Any help is truly appreciated. Thanks
I apologise if this is a stupid question, but I would appreciated anyhelp on this subject.I want to create a view (VIEW_1 in example below) where I take numericvalues from a field in one table (DEPTH_FROM in TABLE_1) and find theclosest matching values from another field in another table (DEPTH_ATin TABLE_2) - the higher matching value and the lower matching value.TABLE_1BH_ID DEPTH_FROMB1 10.6B1 14.2B1 16.1B1 17.0TABLE_2BH_ID DEPTH_ATB1 9B1 10B1 11B1 12B1 13B1 14B1 15B1 16B1 17B1 18VIEW_1BH_ID DEPTH_FROM DEPTH_LOWER DEPTH_HIGHERB1 10.6 1011B1 14.2 1415B1 16.1 1617B1 17.0 1717Any assistance would be appreciated.Regards,Hennie
View 2 Replies View RelatedI have included the create table statements, sample data and a stored procedure.
I have a stored procedure that walks the associations so that I can assemble all the nodes of the tree. I think this is working properly.
I have two requests/needs/problems that I need to solve:
(1)
I want to create a NEW stored procedure that takes a WhoID and an AssocID.
This new stored procedure would show the shortest or closest path between WhoID and AssocID.
For example, if I pass in 6,1 to this new proc I want the stored procedure to return the hierarchy with the closest path between them. Based on the sample data below, the path would be
6 - 8
8 - 2
2 - 3
3 - 1
(2)
I might also need a proc that accepts two inputs (again, lets use 6,1) but would show all paths / branches of the tree where person 1 exists. Based on the stored procedure procCTE_Assoc 6, this solution would exclude the "fred and jeff" node but would include the other two nodes because person 1 is within the other two nodes.
Please provide your suggestions, comments, or solutions!
CREATE TABLE dbo.Who(
WhoID bigint IDENTITY(1,1) NOT NULL,
FName varchar(50) NOT NULL
)
GO
CREATE TABLE dbo.Assoc(
WhoID bigint NOT NULL,
AssocID bigint NOT NULL
) ON PRIMARY
SET IDENTITY_INSERT dbo.Who ON
GO
INSERT INTO Who (WhoID,FName) VALUES (1,'user1')
INSERT INTO Who (WhoID,FName) VALUES (2,'user2')
INSERT INTO Who (WhoID,FName) VALUES (3,'admin1')
INSERT INTO Who (WhoID,FName) VALUES (4,'admin2')
INSERT INTO Who (WhoID,FName) VALUES (5,'admin3')
INSERT INTO Who (WhoID,FName) VALUES (6,'Keith')
INSERT INTO Who (WhoID,FName) VALUES (7,'fred')
INSERT INTO Who (WhoID,FName) VALUES (8,'Joe')
INSERT INTO Who (WhoID,FName) VALUES (9,'Jack')
INSERT INTO Who (WhoID,FName) VALUES (10,'Doug')
INSERT INTO Who (WhoID,FName) VALUES (11,'Mark')
INSERT INTO Who (WhoID,FName) VALUES (12,'Al')
INSERT INTO Who (WhoID,FName) VALUES (13,'Jeff')
INSERT INTO Who (WhoID,FName) VALUES (14,'Brandon')
INSERT INTO Who (WhoID,FName) VALUES (15,'Ashley')
INSERT INTO Who (WhoID,FName) VALUES (16,'Lisa')
INSERT INTO Who (WhoID,FName) VALUES (17,'Julie')
INSERT INTO Who (WhoID,FName) VALUES (18,'Brian')
go
SET IDENTITY_INSERT dbo.Who OFF
GO
INSERT INTO Assoc (WhoID,AssocID) VALUES (1,4)
INSERT INTO Assoc (WhoID,AssocID) VALUES (2,1)
INSERT INTO Assoc (WhoID,AssocID) VALUES (2,3)
INSERT INTO Assoc (WhoID,AssocID) VALUES (6,7)
INSERT INTO Assoc (WhoID,AssocID) VALUES (6,8)
INSERT INTO Assoc (WhoID,AssocID) VALUES (7,13)
INSERT INTO Assoc (WhoID,AssocID) VALUES (8,2)
INSERT INTO Assoc (WhoID,AssocID) VALUES (14,8)
INSERT INTO Assoc (WhoID,AssocID) VALUES (6,15)
INSERT INTO Assoc (WhoID,AssocID) VALUES (15,18)
INSERT INTO Assoc (WhoID,AssocID) VALUES (15,17)
INSERT INTO Assoc (WhoID,AssocID) VALUES (17,5)
INSERT INTO Assoc (WhoID,AssocID) VALUES (5,1)
go
create proc dbo.procCTE_Assoc
@WhoID bigint
AS
WITH GroupsCTE (WhoID, AssocID, FName, FNameAssoc, Lvl, Sort, GroupWithIndent)
AS
(
-- Anchor Who definition
SELECT M.WhoID, MA.AssocID, M.FName, AssocWho.FName, 0 AS Lvl,
CONVERT(varchar(2000),AssocWho.FName) AS Sort,
CONVERT(varchar(255), AssocWho.FName)
FROM Who M
INNER JOIN Assoc MA ON M.WhoID = MA.WhoID
INNER JOIN Who AssocWho ON AssocWho.WhoID = MA.AssocID
WHERE M.WhoID = @WhoID
UNION ALL
-- Recursive Who definition
SELECT g1.WhoID, MA1.AssocID, g1.FName, g2.FName, Lvl + 1,
CONVERT(varchar(2000),RTRIM(Sort) + '|' + g2.FName),
CONVERT(varchar(255), REPLICATE ('| ' , Lvl+1) + g2.FName)
FROM Who g1
INNER JOIN Assoc MA1 ON g1.WhoID = MA1.WhoID
INNER JOIN Who g2 ON g2.WhoID = MA1.AssocID
INNER JOIN GroupsCTE CTE
ON MA1.WhoID = CTE.AssocID
)
SELECT *
FROM GroupsCTE
ORDER BY Sort
RETURN (0)
GO
exec procCTE_Assoc 6
I have two tables and I am trying to pull a value closest to another date. Β There could be instances where the Eval_date in TableA matches the date in TableB so how would that be handled?
TableA
PA_IDEval_Date
12015-01-08 00:00:00.000
22015-01-25 00:00:00.000
TableB
IDPA_IDDate Stat
112015-01-15 00:00:00.000 14
222015-01-28 00:00:00.000 8
312015-01-16 00:00:00.000 2
422015-01-029 00:00:00.000 3
512015-01-09 00:00:00.000 11
612015-03-14 00:00:00.000 14
Expected Results
PA_IDDate Stat
12015-01-09 00:00:00.000 11
22015-01-28 00:00:00.000 8
Hi all,
I am seeking your expertise to create SQL codes (SQL server 2005) that can help me to answer the problem below.
I have two tables (points and station), presented in form of SQL codes below. Id like to find the 6 closest panels for each of the station. As can be seen in the result table below, the 6 closest panel names are arranged from the first closest (P1) to the sixth closest (P6). Similar procedure also applies for the distance column arrangement. This distance column (D1 D6) is the distance of panels P1 P6 to the station. The distance between two points (with x-y coordinates) can be calculated using a simple Cartesian formula:
Distance = ( (X1 X2)2 + (Y1 - Y2)2 ) 0.5 . As the sample, distance between station A and panel P19-04W is = ((737606.383 - 737599.964)2 + (9548850.844 - 9548856.856)2) 0.5 = 8.79.
The expected result of the work is presented in the table below:
Panel
distance
Station
P1
P2
P3
P4
P5
P6
D1
D2
D3
D4
D5
D6
A
P19-04W
P19-06E
P19-05E
P19-05W
P19-03W
P19-07E
8.79
12.00
13.54
19.02
21.00
27.43
B
P19-07W
P19-09E
P19-08E
P19-06W
P19-08W
P19-07E
9.50
11.58
12.92
21.15
24.85
29.11
C
P19-11E
P19-10W
P19-12E
P19-09W
P19-10E
P19-11W
8.45
11.42
16.06
16.38
23.15
25.30
Table 1:
create table 1 (
Panels varchar(20),
X_Coord float,
Y_Coord float
)
go
set nocount on
insert into 1 values('P19-03E','737640.722','9548882.875')
insert into 1 values('P19-04E','737630.166','9548868.3')
insert into 1 values('P19-05E','737619.611','9548853.726')
insert into 1 values('P19-06E','737609.054','9548839.15')
insert into 1 values('P19-07E','737598.495','9548824.571')
insert into 1 values('P19-08E','737587.941','9548809.998')
insert into 1 values('P19-09E','737577.386','9548795.425')
insert into 1 values('P19-10E','737563.359','9548776.163')
insert into 1 values('P19-11E','737552.795','9548761.578')
insert into 1 values('P19-12E','737542.256','9548746.919')
insert into 1 values('P19-13E','737531.701','9548732.345')
insert into 1 values('P19-14E','737521.146','9548717.772')
insert into 1 values('P19-03W','737610.519','9548871.43')
insert into 1 values('P19-04W','737599.964','9548856.856')
insert into 1 values('P19-05W','737589.404','9548842.275')
insert into 1 values('P19-06W','737578.849','9548827.702')
insert into 1 values('P19-07W','737568.294','9548813.128')
insert into 1 values('P19-08W','737554.274','9548793.77')
insert into 1 values('P19-09W','737543.718','9548779.195')
insert into 1 values('P19-10W','737533.157','9548764.614')
insert into 1 values('P19-11W','737522.603','9548750.041')
set nocount off
go
Table 2:
create table 2 (
Station varchar(20),
X_Coord float,
Y_Coord float
)
go
set nocount on
insert into 2 values('A','737606.383','9548850.844')
insert into 2 values('B','737575.41','9548806.838')
insert into 2 values('C','737544.437','9548762.832')
set nocount off
go
Thanks alot in advance!
I have a temperature table with a column that shows the how many hours old a newborn was when his/her temperature was taken.
Example lets say once per hour.
I want to join to a table called Weight that records the newborns weight at any given time.
Example lets say 3x during the day.
1 @ 8:45am
2 @ 11:15am
3 @ 4:30pm
I want to figure out which weight recording is the closest to a given temperature recording and return that one row.
What I'm trying to select is the closest value from a list given by a parameter or select the matched value.
declare @compare as int
set @compare = 8
declare @table table
(
Number int
)
insert into @table
values(1),
(2),
(3),
(4),
(5),
(10)
If the parameter value match one of the values from the table list, select that matched one.If the value does not exist in the table list, select the closest lower value from the table list, in this case, it would be value 5.
I'd like to get some ideas for the following:
I am writing a quick mini-application that searches for records in a database, which is easy enough. However, if the search term comes up empty, I need to return 10 records before the positon the search term would be in if it existed, and 10 records after. (Obviously the results are ordered on the search term column)
So for example, if I am searching on "Microsoft", and it doesn't exist in my table, I need to return the 10 records that come before Microsoft alphabetically, and then the 10 that come after it.
I have a SP that does this, but it is pretty messy and I'd like to see if anyone else had some ideas that might be better.
Thanks!
I'm trying to write a Stored Procedure that have to calculate the closest date for manufacturing.
What I have:
- The BOM (bill of materials) with the needed quantity for production
DECLARE @BOM TABLE
(
ItemIDINT
,neededQuantityfloat
)
INSERT INTO @BOM (ItemID, neededQuantity)
SELECT 1, 10
UNION ALL SELECT 2, 10
UNION ALL SELECT 3, 5
- a calculated table that told me the availability for each component of the BOM, sorted by date. (each row have a plus or minus of the quantity so it can by summarized)
DECLARE @WhareHouseMovement TABLE
(
ItemIDINT
,Quantityfloat
,DateDATETIME
)
INSERT INTO @WhareHouseMovement (ItemID, Quantity, Date)
SELECT 1, 10, '2015-03-01'
[Code] ....
My question is: how do I check when is the closest date to manufacturing? I have to check that the quantity of ALL the components of the BOM is enough to produce the product, but I can't get how to do it.
If I'm not wrong the example should give the result 2015-03-26.
I'm having a bit of a trouble explaining what I'm trying to do here.
I have 3 "source" tables and a "connecting" table that I'm going to use
tblContacts - with contactID, ContactName etc
tblGroups - with GroupID, GroupName
tblSubGroups - with SubGroupID, GroupID and SubGroupName (groupID is the ID for the parent Group from tblGroups)
They are related in a table called
tblContactsGroupConnection - with ContactID, GroupID and SubGroupID
One contact can be related to many subgroups.
What I want is a list of all contacts, with their IDs, names and what groups they are related to:
ContactID, ContactName, [SubGroupName1, SubGroupName2, SubGroupName3]
ContactID, ContactName, [SubGroupName1, SubGroupName3]
ContactID, ContactName, [SubGroupName3]
I'm sure there's a simple solution to this, but I can't find it. Any help appreciated. :)
Kirikiri
Hi,
I got a problem.
I installed Microsoft SQL Server Management Studio Express 2005 version.
And I created a Compact database.
I created an connection in SSMSE to connect the database and opened a query form.
then, i run the following sql:
Select * from Table1
It returned 3 records to me.
After that, I used program to insert record into this table.
Then i ran this sql again, it still show me 3 records.
I closed the query form, and re-created a new query form, then run the sql, it returned 4 records to me.
Why? It's very strange and difficult to operate, right?
Is there anyone know how to make the SSMSE to return whole records without any close query form and re-create query form operation?
Thanks a lot!
And Merry X'max!!!
Hi
How can I know whether a query or a stored procedure is successfully executed? I mean like in delete case?
here is an example
sql_delete = "DELETE FROM UserData where U_ID='" & tempID & "'"
rstIDChk.Open sql_delete, cnn, adOpenStatic, adLockOptimistic
How can I make sure that the record is deleted so that I can proceed with other jobs? How can I catch it in the program like from VB? Is there any return like true or false in SQL? :(
Tks alot..
Hi! create table testReturn(id int identity(100,1),name varchar(10)) How can I return the value of identity column after inserting the value. Dim objConn As SqlConnection Dim SQLCmd As SqlClient.SqlCommand Dim ds As New DataSet Dim strsql As String Try objConn = New SqlConnection objConn.ConnectionString = _ "Network Library=DBMSSOCN;" & _ "Data Source=localhost;" & _ "Initial Catalog=mydb;" & _ "User ID=userid;" & _ "Password=pass" objConn.Open() strsql = "insert into testReturn values ('a')" SQLCmd = New SqlClient.SqlCommand(strsql, objConn) Dim rowsAffected As Integer = 0 rowsAffected = SQLCmd.ExecuteNonQuery Dim rv As String rv = SQLCmd.Parameters(0).Value.ToString() Response.Write(rv) Catch ex As Exception Response.Write(ex.ToString) End Try
View 5 Replies View RelatedHii I am Varun i have a problem with the dynamic stored procedure
This is my stored procedureALTER PROCEDURE dbo.sp_TimeTableAdjustment1
(@TeacherID_OnLeave numeric(9),
@DateFrom datetime ,@DateTo datetime , @UserID numeric(9)
)
AS
declare @flag as numeric(9)
declare @year as varchar(4)
set @year=(select batch from batchmaster where iscurrent=1 and isdeleted=0)
if( @year=null or len(@year)=0)
set @year = year(getdate())exec ('if not exists(select * from timetableadjustments_'+@year+' where datefrom='''+@datefrom+''' and dateto='''+@dateto+''' and teacherid_onleave='+@teacherid_onleave+')
begin
insert into TimeTableAdjustments_'+@year+' (TeacherID_OnLeave,DateFrom,DateTo,UserID) values ('+@TeacherID_OnLeave+','''+@DateFrom+''','''+@DateTo+''','+@UserID+');
end')
else
set @flag=(select timetableadjustmentid from timetableadjustments_2007 where datefrom=@datefrom and dateto=@dateto and teacherid_onleave=@teacherid_onleave)
return @flag
--exec('select timetableadjustmentid from timetableadjustments_'+@year+' where datefrom='''+@DateFrom+''' and dateto='''+@DateTo+''' and teacherid_onleave='+@TeacherID_OnLeave+'
--')
--return @flag
--exec('@flag=select timetableadjustmentid from timetableadjustments_'+@year+' where datefrom='''+@DateFrom+''' and dateto='''+@DateTo+''' and teacherid_onleave='+@TeacherID_OnLeave+'')
This has got to be a simple one, but I could not find out how
I have this simple proc for test purposes only...
ALTER PROCEDURE dbo.RotoTest( @strSSN VARCHAR(11), @blnUseACHDate BIT = 0, @intInvestorId int = 0)ASreturn 5
Thats right it does nothing but return 5 and thats because I removed all the real code to simplify my question.
When I hit this proc in SQL Query Analyzer..
exec RotoTest '123-45-6789', 0, 1 No return value is displayed in Query Analyzer.
When I try something like this
exec RotoTest '123-45-6789', 0, 1, intRetValue Output (in this situation I get wrong number of arguments)
How can I get a return value displayed on my screen in SQL QUERY ANALYZER with out modifying the proc? I do not want to modify the proc in anyway (because I can't), I just want to display the return value
Hi
I have written a query for viewing the results of an on-line survey. I have three tables involved in this query: answers, answerpossibilities and users. So I use a few joins and made this query:
ALTER PROCEDURE dbo.GeefAntwoordenMeerkeuze ( @question_id int ) AS SET NOCOUNT ON; SELECT answerpossibilities.answerpossibility_content AS[Answerpossiblity], COUNT(answers.answers_id) AS [Times chosen] FROM answers right OUTER JOIN answerpossibilities ON answers.answerpossibility_id = answerpossibilities.answerpossibility_id left join users on answers.user_id = users.user_id WHERE ((answerpossibilities.question_id = @question_id AND nswerpossibilities.answerpossibility_content!='-- choose answer --')) GROUP BY nswerpossibilities.answerpossibility_content ORDER BY [Times chosen] desc
The above query works fine. The data returned by this query is shown in a gridview. When an answerpossibilty was never chosen it shows 0 as times chosen. So that's fine. But the problem is, only answers of users who completed the survey should be shown. In the users table there's a field user_completed. So the query should check whether this field is 1 (true).
ALTER PROCEDURE dbo.GeefAntwoordenMeerkeuze ( @question_id int ) AS SET NOCOUNT ON; SELECT answerpossibilities.answerpossibility_content AS[Answerpossiblity], COUNT(answers.answers_id) AS [Times chosen] FROM answers right OUTER JOIN answerpossibilities ON answers.answerpossibility_id = answerpossibilities.answerpossibility_id left join users on answers.user_id = users.user_id WHERE ((answerpossibilities.question_id = @question_id AND nswerpossibilities.answerpossibility_content!='-- choose answer --') and users.user_completed = 1) GROUP BY nswerpossibilities.answerpossibility_content ORDER BY [Times chosen] desc
Using this query only answers of users who completed the survey are shown but answer possibilities that were never chosen are no longer shown with 0 as times chosen. The gridview simply doesn't show them anymore.
Thanks for helping me!
Something went wrong by posting this message I guess, all blank lines were gone.. maybe because I used Safari on my iMac