Find Next Value And Store The Result With Incremental
May 27, 2008
Please help!
Please look at the sample data below; I need help to change the value of data from (Before) - (After)
Before
Station Cycle_Index
D_CC 1
C_CC 1
C_CV 1
REST 1
D_CC 1
C_CC 1
C_CV 1
REST 1
D_CC 1
When Station = 'D_CC' then Cycle_Index suppose to start a new cycle. So my problem is trying to find the next 'D_CC' and store the incremental cycle # in a New_Cycle column.
After
Station Cycle_Index New_Cycle
D_CC 1 1
C_CC 1 1
C_CV 1 1
REST 1 1
D_CC 1 2
C_CC 1 2
C_CV 1 2
REST 1 2
D_CC 1 3
Thanks mucho for your help!
Updated:
Sorry to confuse you by changing the text color!
Case when Station = 'D_CC' then Cycle_Index = Cycle_Index + 1; based on the prior cycle # and continuos to find next 'D_CC' until the end; regardles whatever in between the prior D_CC to next D_CC.
Bottom line is searching the next value from Station = D_CC then cycle # in Cycle_Index column need to be incremented by 1 and stored the new cycle # in New_Cycle column as the actual cycle.
View 5 Replies
ADVERTISEMENT
Jan 16, 2008
I have a store procedure that works fine when tested in SQL Management Studio and Visual Studio but when I actually running the page in a browser, it does not work. There is no result generated. Below is my store procedure.1 ALTER PROCEDURE [dbo].[spSearch]
2 -- Add the parameters for the stored procedure here
3 @schoolID int = NULL,
4 @scholarship varchar(250) = NULL,
5 @major varchar(250) = NULL,
6 @requirement varchar(250) = NULL
7 --@debug bit = 0
8 AS
9 BEGIN
10 -- SET NOCOUNT ON added to prevent extra result sets from
11 -- interfering with SELECT statements.
12 SET NOCOUNT ON;
13
14 -- Insert statements for procedure here
15
16 Declare @SQL as NVarchar(4000);
17 Declare @Params as NVarchar(3000);
18 Set @SQL = N'SELECT * FROM [scholarship] WHERE [sectionID] = @schoolID';
19 Set @Params = N'@schoolID int,@scholarship varchar(250),@major varchar(250),@requirement varchar(250)'
20
21 If @scholarship IS NOT NULL
22 Set @SQL = @SQL + N' AND [scholarship].[schlrName] LIKE + ''%'' + @scholarship + ''%'''
23 If @major IS NOT NULL
24 Set @SQL = @SQL + N' AND [scholarship].[Specification] LIKE + ''%'' + @major + ''%'''
25 If @requirement IS NOT NULL
26 Set @SQL = @SQL + N' AND ([scholarship].[reqr1] LIKE + ''%'' + @requirement + ''%'''
27 If @requirement IS NOT NULL
28 Set @SQL = @SQL + N' OR [scholarship].[reqr2] LIKE + ''%'' + @requirement + ''%'''
29 If @requirement IS NOT NULL
30 Set @SQL = @SQL + N' OR [scholarship].[reqr3] LIKE + ''%'' + @requirement + ''%'''
31 If @requirement IS NOT NULL
32 Set @SQL = @SQL + N' OR [scholarship].[reqr4] LIKE + ''%'' + @requirement + ''%'''
33 If @requirement IS NOT NULL
34 Set @SQL = @SQL + N' OR [scholarship].[reqr5] LIKE + ''%'' + @requirement + ''%'')'
35 --If @debug = 1
36 --PRINT @SQL
37 Exec sp_executesql @SQL, @Params, @schoolID, @scholarship, @major, @requirement
38 END
39
View 27 Replies
View Related
Jul 5, 2001
Hello,
I must get the content of dbcc inputbuffer (nn).
The structure of the result is well described in the BOL.
However, I don't find a way to store the result in temporary table or other stuff.
Thanks in advance.
Regards
Patrick
View 2 Replies
View Related
Aug 24, 2000
Hi guys. I have been struggling for days now to store the result of a stored procedure from a linkedserver. To make a long story short, here is my code...
CREATE PROCEDURE F_GET_KRONOS_HRS @wono varchar(12)
AS
BEGIN
declare @str nvarchar(2000)
declare @a varchar(10)
select @str = 'select * from openquery(kronos," SELECT decode(a.PAYCATID,1,'+ "'ST'"+",'OT'"+') paycode ,f_calc_hrs(sum(a.AMOUNT)) hrs '
select @str = @str + 'FROM TOTALEDPAYCATEDIT a ,LABORACCT b ,LABORLEVELENTRY c '
select @str = @str + 'where ( c.NAME =' + "'"+'' + @wono +"') "
select @str = @str + 'and (a.LABORACCTID = b.LABORACCTID) '
select @str = @str + 'and c.LABORLEVELENTRYID = b.LABORLEV3ID group by a.PAYCATID ' + '' + '")'
/*
exec sp_executesql @str
*/
create table #t (paycode varchar(7), hrs varchar(255))
insert into #t exec sp_executesql @str
select @a = hrs from #t where paycode='ST'
drop table #t
print @a
if i call exec sp_execute @str, it will output this
PAYCODE HRS
------- --------
ST 08: 30
OT 54: 00
(2 row(s) affected)
I want those #'s store into a temp variable OR be passed to another procedure.
HOW DO I DO IT. This is the last step holding me back from completing my DataWareHouse.
Thanks
View 2 Replies
View Related
Sep 3, 2004
hai..
is it possible to store the result the following statement in a table
execute master..xp_cmdshell 'dir'
when i execute this in QA i get the result. but i want to store it in a table
thnks in advance
with regards
Sudar
View 6 Replies
View Related
Jun 13, 2008
One column in my table stores SQL queries(QueryCoulmn). Another coulmn supposed to store the result of those queries(ResultColumn). Can I run an update query or how can I do that? I could not figure out the syntax.
update tablename
set ResultColumn=exec(QueryCoulmn)
thanks
View 6 Replies
View Related
Feb 26, 2007
HI, I'm a simple store procedure that returns a result such as this one:
AM
AM-1
AM-2
AM-n
and in other store procedure I need to filter result from this list.
I think that some query like this is impossibile
select fields from table where id in (execute sp)
how can I make this?
Thanks a lot.
View 4 Replies
View Related
Mar 18, 2008
Help - I am having a moment!
I am building a simple package that looks at the results of a sql query, prior to running the next step.
Basically Outline and settings
SQL task
Result Set: Full result set
SQL Source: Direct Input
SQL Statement: Select Count (*) FROM X
Parameter Mapping
Variable Name: User::C
Direction: Return Value
Data Type: Numeric
Parameter Name: NewParameterName
Result Set = NewResultName: User::C
Precedence Constraint
Evaluation Operation: Expression and Constraint
Value: Success
Expression: @C > 0 ( I originally tried User::C and received an error message)
Execute SQL Server Agent Job
If step one results greater than 1, then execute the SQL agent job (SRS Report)
Using this setup I receive an error message stating "
TITLE: Package Validation Error
------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at Package: The expression "@C > 0" must evaluate to True or False. Change the expression to evaluate to a Boolean value.
Error at Package: There was an error in the precedence constraint between "Execute SQL Task" and "Execute SQL Server Agent Job Task".
(Microsoft.DataTransformationServices.VsIntegration)
Any thought or suggestions would be greatly appreciated.
r/ Anthony
View 7 Replies
View Related
Oct 31, 2007
I excute sp_columns in my Stored Procedure script to get the data type of a table column.
EXEC sp_columns @table_name = 'XXX', @column_name='YYY'
How do i store the column 'TYPE_NAME' in the return row into a variable so that i can use it later in my stored procedure?
Thanks
Hannah
View 1 Replies
View Related
Nov 23, 2007
Hi,
I'm wondering is it possible to store query result to column directly?
for example I have 2 tables
Cache
----------
Cache_ID (int)
Cache_content (Text)
cache_created (Datetime)
Orders
-------------
Order_ID
Order_amount
Order_Date
Order_Delivered
and the query is
select * from order where order_date = '2007-11-23' and 'Order_delivered='N'
I want the result to be stored in cache table either csv or xml
I'm using sql 2000 server.
any idea?
View 7 Replies
View Related
Feb 10, 2000
Hi All,
I need to store the row count from two different servers (one 6.5 and one 7.0)
to compare by doing this:(in T-SQL)
declare @kount1 int, @kount2 int
exec master..xp_cmdshell 'isql -Sserver65 -d dbA -T -Q "select count(*) from tableA",no_output
exec master..xp_cmdshell 'isql -Sserver70 -d dbA -T -Q "select count(*) from tableA",no_output
How can I save the rowcount in @kount1, @kount2 respectively for comparison?
Appreciate your feedback.
David Nguyen
View 4 Replies
View Related
Apr 30, 2008
Hi,
Can I take the [index] result from a table and store it in a local var?
For example a table
declare @keyWords(
word varchar(40),
wordLen smallint
)
word len
---- ---
test 4
t 1
e 1
declare @index smallInt = 2
SET @keyWord = SELECT TOP @INDEX word FROM @KeyWords
This obviously is not correct for several reasons (TOP returns multiple results, TOP doesn't work with local var etc)
View 2 Replies
View Related
Oct 16, 2007
I wrote a stored procedure that finds a number. I want to store the number it finds into a variable so i can use it within another procedure.
I hope i'm being clear.
Any help will be appreciated.
Here is an example of how i am finding my number
Employee is the name of my table and Number is the name of my column.
SELECT Max(Number)
FROM Employee
View 5 Replies
View Related
Dec 19, 2000
I have a dynamic query which returns me a result and I want to capture that value to make further use of it in the same code. Is that possible??
exec ('select col_nm from table_name'). i want the result of this query to be captured.
DP
View 4 Replies
View Related
Mar 28, 2008
All,
I'm trying to store the results of my store procedure in a temp table. when I try it, I got the error saying...
"Insert exec cannot be nested"
I suspsect this is because I have a Insert Exec statement inside my stored procedure...
Is there any way to come over this issue ?
syntax of my code...
create table #Temp1 (ID int)
insert into #Temp1
EXEC SP1
when I try to run the above code I get the error "Insert exec cannot be nested"
SP syntax :
Create Procedure SP1
as
Begin
create table #Temp2
statements.....
Insert into #temp2
exec SP<Name>
staments.. cont...
END
View 1 Replies
View Related
Jul 22, 2015
I use new query to execute my store procedure but didnt return any value is that any error for my sql statement??
USE [Pharmacy_posicnet]
GO
/****** Object: StoredProcedure [dbo].[usp_sysconf] Script Date: 22/07/2015 4:01:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[usp_sysconf]
[Code] ....
View 6 Replies
View Related
Nov 6, 2007
I'm sure this is a very simple piece of code, but I'm having trouble understanding how to do this.
First I have a database with three columns
ContactID
View 1 Replies
View Related
Feb 29, 2008
name age weightaaa 23 50bbb 23 60ccc 22 70ddd 24 20 eee 22 30i need the output that calculate the sum of weight group by name input : age limit ex: 22 - 23 output : age total weight 23 11022 100 this output must stored in a sql declared variable for some other further process .
View 7 Replies
View Related
Jul 4, 2012
Code:
SELECT ((ACOS(SIN(34.37769 * PI() / 180) * SIN(latitude * PI() / 180)
+ COS(34.37769 * PI() / 180) * COS(latitude * PI() / 180) * COS((132.404738 - longitude)
* PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS ranges FROM completeyuubinwithlatlon having ranges<=3
First, I have that code whenever i disregard the "having ranges<=3" it selects record but when I include it it returns error. The problem is I want to just select records with ranges less than 3 miles..how do I do that.. and I want to count the number of selected rows and store it on a column on the same table.
View 3 Replies
View Related
Feb 16, 2012
I have a stored procedure that returns XML using FOR XML Explicit. I need to use the output of this procedure in another procedure, and modify the xml output before it is saved somewhere.
Say StoredProc1 is the one returning xml output and StoredProc2 needs to consume the output of StoredProc1
I declared a nvarchar(max) variable and trying to saved the result of StoredProc1
Declare @xml nvarchar(max)
EXEC @xml = StoredProc1 @Id
This doesn't work as expected as @xml doesn't get any value assigned or rather I would say
EXEC @xml = StoredProc1 @Id
outputs the entire xml whereas it should just save the xml in a variable.
View 7 Replies
View Related
Apr 7, 2013
I wanted to insert the result-set of a Exec(@sqlcommand) into a temp table. I can do that by using:
Insert into #temp
Exec(@sqlcommand)
For this to accomplish we need to define the table structure in advance. But am preparing a dynamic-sql command and storing that in variable @sqlcommand and the output changes for each query execution. So my question is how to insert/capture the result-set of Exec(@sqlcommand) into a temp table when we don't know the table structure.
View 17 Replies
View Related
Feb 13, 2008
I cannot find the data type for parameter mapping from Execute SQL Task Editor to make this works.
1. Execute SQL Task 1 - select max(columnA) from tableA. ColumnA is varbinary(8); set result to variable which data type is Object.
2. Execute SQL Task 2 - update tableB set columnB = ?
What data type should I use to map the parameter? I tried different data types, none working except GUI but it returned wrong result.
Does SSIS variable support varbinary data type? I know there's a bug issue with bigint data type and there's a work-around. Is it same situation with varbinary?
Thanks,
-Ash
View 8 Replies
View Related
May 11, 2006
We have some reports run quite slow because the queries are complicate and tables are large. So we create a materialized view in Oracle which store the result from the query and refresh it occationally. How to do this in MSSQL? I try the indexed view but seems it has lots of restrictions, our query has sub queries and cross database table joins so can't use the indexed view. Any other object or temp table can be used to cache the report data and be accessed globally by other procedures?
Any suggestions are welcome,
Cheers
View 5 Replies
View Related
Aug 10, 2015
I have two tables in my database. Some matching rules are associated with these tables and I want a query which will retrieve those data based on the matching rules provided below:
1)
Table1.Firstname=Table2.Firstname,Table1.Lastname=Table2.Lastname,Table1.StartDate=Table2.StartDate,Table1.EndDate=
Table2.EndDate
2)
Table1.Firstname=Table2.Firstname,Table1.Lastname=Table2.Lastname,Table1.StartDate=Table2.StartDate,Table1.EndDate!=
Table2.EndDate
3)
Table1.Firstname!=Table2.Firstname,Table1.Lastname!=Table2.Lastname,Table1.StartDate!=Table2.StartDate,
Table1.EndDate=Table2.EndDate
Table 1 & Table 2 contains the following data:
Table1
First Name
Last Name
Start Date
End Date
City
[code]...
I want this output by writing a single query.
View 4 Replies
View Related
Apr 16, 2014
Our company purchased a app. Is there any way to find out which store procedures were fired once a button was clicked?
View 3 Replies
View Related
Nov 23, 2015
I would like to know all the columns used in a store procedures.I have used dynamic sql to create these procedures .
View 4 Replies
View Related
Mar 20, 2007
Hi,
Is anyone has store procedure that find city and zipcode by radius (10 miles, 30 miles, 50 miles.......)
and also table that has all zipcodes. I finded one, but it's look like very old one, not updated since 2005
Thanks
Kate
View 1 Replies
View Related
Nov 21, 2006
hi friends
i want to know how to calculate year ranging from 1900 to 2000 using store procedure in sql server
View 8 Replies
View Related
Mar 5, 2005
For Backup Database i used script
BACKUP DATABSE.
Then after that, i need to check Database inigrity, for that
i used DBCC CHECKDB and DBCC CHECKCATALOG
but, i want to store the result in a text file, after executing these two commends, how to write commend line please help me.
View 1 Replies
View Related
Dec 1, 2006
I have one control table to store all related table name
Table ID TableName
1 TableA
2 TableB
In Table A:
RecordID Value
1 1
2 2
3 3
In Table B:
RecordID Value
1 1
2 2
3 3
How can I get the result by select the Table list first and then combine the data in table A and table B?
Thank you!
View 1 Replies
View Related
Nov 12, 2003
Hi guys,
I am trying to get a result like below without using the cursor.
col1 col2 col3 col4
1111 date uniquenumber 6
1111 date uniquenumber 5
1111 date uniquenumber 4
1111 date uniquenumber 3
1111 date uniquenumber 2
1111 date uniquenumber 1
2222 date uniquenumber 4
2222 date uniquenumber 3
2222 date uniquenumber 2
2222 date uniquenumber 1
3333 date uniquenumber 2
3333 date uniquenumber 1
the column that say unique number is unique and is not duplicated and date column might have duplicates
Please advise whether it is possible to write a query without cursor.
Thanks,
Anu
View 5 Replies
View Related
Apr 16, 2005
Hi!
I have a request of having a select statement displaying a list of values in descending order and another column that tells what record it is.
something like this:
id | value | no
4 345 1
7 234 2
2 143 3
9 32 4
3 4 5
...
is this possible?
can it be something like this:
select id, value, increment(1,1) as no from tblTable
order by value desc
thanks.
View 5 Replies
View Related
Nov 16, 2007
I am looking to create a incremental value based on the resulting insert that I am using. There already is another field being used as an identity field. I have a beginning value that I just want to add the row number to for the insert.
insert into lineitem select substring(group_id,4,len(ltrim(rtrim(group_id)))-3) as co_code,
0,0,(case when enddate < cast(month(getdate()) as varchar(10))+cast(day(getdate()) as varchar(10))
then 'Prior' else 'Current' end ),
left(acct_type,2) as bene_type, convert(smalldatetime,left(ltrim(rtrim(eff_date)), 8)),
0,trans_amt,0,0,convert(smalldatetime,left(ltrim(r trim(sett_date)),8)),
ltrim(rtrim(b.fname)) + ' ' + ltrim(rtrim(b.lname)) as payee,0,0,a.ssn,'Y',999+count(*),
(case when isnull(b.location,'') = '' then '' else b.location end) as location
from mbi_tran_temp a
left join enrollees b on a.ssn = b.ssn and
ltrim(rtrim(a.group_id)) = ltrim(rtrim(b.mbicode))
The '999+count(*)' is where I would like to have the incremental value.
View 13 Replies
View Related
