How To Use Cursor To Create Re-occuring Numbers?

Create Sequential Numbers In A Column
I have a temp table that's populated with an insert query in as toredprocedure. The temp table has a uniqueID as the primary key.In that table I have a column SortOrder.What I want to do is to create a sequential number in SortOrder butonly for records matching a WHERE statement, for example:(pardon the shorthand...)Insert *.tblPermanent into tblTempIf myField = 1 thenSortOrder = 1(2,3,4,5,.....etc.)elseSortOrder = 0Thankslq

View Replies !

---

Simple Query To Create Column Of Sequential Numbers
Hi,
Can anyone tell me offhand the simplest/most elegant way of updating an integer column to a sequential column of numbers with a query?

e.g given

intval | Description| Cost
0 | Descvalue0| 4.32
2 | Descvalue2| 4.33
3 | Descvalue3| 4.34
8 | Descvalue8| 4.35

change it to:

intval | Description| Cost
0 | Descvalue0| 4.32
1 | Descvalue2| 4.33
2 | Descvalue3| 4.34
3 | Descvalue8| 4.35

I think it might need a stored proc..

Many Thanks
greg

View Replies !

---

Formatting Numbers In A Mixed Column (numbers In Some Cells Strings In Other Cells) In Excel As Numbers
I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.

I already tried to set the value as CDbl which returns error for the cells containing a string.

The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.

Any suggestions?

 

 

View Replies !

---

CHANGED Backup Time, But Still Occuring
Hello. Have a strange one here. I'm backing up databases locally to a driveon the server daily. I needed to change the time of the backup, and didthat, but it's still occuring at the old time.I deleted the old job, and recreated it with the new time. I won't knowuntil tonight at 12:00 whether that worked or not.Can someone tell me where it saves the maintenance schedules and how itkicks it off? I'd like to take a look at it to see if it's still there.I'm a little bit of a newbie on SQL server, so be gentle. :-)--If responding to me directly, please take out "REMOVE" from my e-mailaddress.Thanks!Doug

View Replies !

---

Create View From Cursor
I have multiple locations that I want to create views for eachindividual location.I am using a cursor to create the views for each location. So, thecursor grabs site #1 then <should> create view_site_#1, then grab site#2 and <should> create view_site_#2.For some reason it doesn't like the view name with the @site in it.Any ideas of how to get this done?Here's the cursor...declare @site varchar(5)declare c_site cursor forselect station from VHAISLCAUDIA.VISN_SITEorder by stationopen c_sitefetch from c_siteinto @sitewhile (@@fetch_status = 0)beginCREATE VIEW Site_All_Data_+ @siteASSELECT *FROM dbo.[600_All_Suggested_Data]WHERE (Site = @site)Print 'View for ' + @site + ' Created'fetch next from c_site into @siteendclose c_sitedeallocate c_sitereturnend

View Replies !

---

Declare Or Create Cursor
Hello guys,just wanted to ask a question some might percieve it as a stupid one but I don't know so I will ask anyway?

Is Declare Cursor same as Create Cursor and if not what is the major difference?

View Replies !

---

How Do I Create A &#34;Cursor&#34; In SQL Server 7.0??
How do I create a "Cursor" in SQL Server 7.0 that compares an imported table against a exsisting table? Ex. Table1 is my existing table(Destination), Table2 is my imported table(source). I would like to update records in the Destination from the Source but I have to be aware of these three scenarios.

1. If the record exist in the Destination and the Source I will do nothing.
2. If the record exist in the Source and not in the Destination then I will add the record to the destination.
3. If the record exist in the Destination and not in the Source then I will write to the transaction Log.

Note: I am only concerned about updating one column in Destination which matches the only column in the source.

Someone Please help??

View Replies !

---

Error: Could Not Create An Acceptable Cursor.
I'm trying to run a stored proc on a SQL 2005 SP1 box to return info to a SQL 2000 SP4 box, as a linked server. Both boxes have the latest service packs, and run Windows 2003 Server, again with the latest service packs. The error I get is: OLE DB provider "SQLNCLI" for linked server "192.168.0.126" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".Msg 16955, Level 16, State 2, Line 1Could not create an acceptable cursor. The full script I am running is: CREATE procedure [dbo].[proc_AuditServer] as /* ** Auditing Script for SQL Servers. ** ** D Maxwell, June 2007 ** ** This script takes configuration and job status information ** and writes it to a designated logging server. I'll describe ** each section in detail, below. We write to the local box first, ** Then upload everything to the logging server. ** ** This is the SQL 2005 version. */ /* ** We want to know exactly what server this is, so ** we get the server name, instance name, as well as ** SQL Version, Edition, and Service Pack level. */ truncate table admin.dbo.sql_servers insert into admin.dbo.sql_servers select convert(varchar(15), serverproperty('ServerName')), convert(varchar(25), serverproperty('InstanceName')), convert(char(9), serverproperty('ProductVersion')), convert(varchar(4), serverproperty('ProductLevel')), convert(varchar(20), serverproperty('Edition')), getdate() /* ** Now, having that, we get the list of databases, ** as well as thier creation dates and file names. */ truncate table admin.dbo.databases insert into admin.dbo.databases select convert(varchar(15), serverproperty('ServerName')), dbid, name, crdate, filename from master..sysdatabases where dbid > 4 order by dbid /* ** We need to know how the server is configured, so we ** can compare it to a list of preferred configuration ** values, as well as the defaults. I cut this out of ** sp_configure. */ truncate table admin.dbo.server_config insert into admin.dbo.server_config select convert(varchar(15), serverproperty('ServerName')), name, config_value = c.value, run_value = master.dbo.syscurconfigs.value from master.dbo.spt_values, master.dbo.sysconfigures c, master.dbo.syscurconfigs where type = 'C' and number = c.config and number = master.dbo.syscurconfigs.config and ((c.status & 2 <> 0 ) OR (c.status & 2 = 0) ) order by lower(name) /* ** The next configuration item we want to get is the ** list of jobs that run on the server. We're looking ** specifically for backup and other maintenance jobs. ** (Which will hopefully be named appropriately...) ** We use Neil Boyle's job report script for this. ** My comments and changes prefaced by a 'DM:' */ truncate table admin.dbo.jobs insert into admin.dbo.jobs select convert(varchar(15), serverproperty('ServerName')), --DM: Needed since we'll have lots of servers reporting j.job_id, -- DM: More unique than a name. convert(varchar(22), j.name) as job_name, case freq_type -- Daily, weekly, Monthly when 1 then 'Once' when 4 then 'Daily' when 8 then 'Wk ' -- For weekly, add in the days of the week + case freq_interval & 2 when 2 then 'M' else '' end -- Monday + case freq_interval & 4 when 4 then 'Tu' else '' end -- Tuesday + case freq_interval & 8 when 8 then 'W' else '' end -- etc + case freq_interval & 16 when 16 then 'Th' else '' end + case freq_interval & 32 when 32 then 'F' else '' end + case freq_interval & 64 when 64 then 'Sa' else '' end + case freq_interval & 1 when 1 then 'Su' else '' end when 16 then 'Mthly on day ' + convert(varchar(2), freq_interval) -- Monthly on a particular day when 32 then 'Mthly ' -- The most complicated one, "every third Friday of the month" for example + case freq_relative_interval when 1 then 'Every First ' when 2 then 'Every Second ' when 4 then 'Every Third ' when 8 then 'Every Fourth ' when 16 then 'Every Last ' end + case freq_interval when 1 then 'Sunday' when 2 then 'Monday' when 3 then 'Tuesday' when 4 then 'Wednesday' when 5 then 'Thursday' when 6 then 'Friday' when 7 then 'Saturday' when 8 then 'Day' when 9 then 'Week day' when 10 then 'Weekend day' end when 64 then 'Startup' -- When SQL Server starts when 128 then 'Idle' -- Whenever SQL Server gets bored else 'Err' -- This should never happen end as schedule , case freq_subday_type -- FOr when a job funs every few seconds, minutes or hours when 1 then 'Runs once at:' when 2 then 'every ' + convert(varchar(3), freq_subday_interval) + ' seconds' when 4 then 'every ' + convert(varchar(3), freq_subday_interval) + ' minutes' when 8 then 'every ' + convert(varchar(3), freq_subday_interval) + ' hours' end as frequency -- All the subsrings are because the times are stored as an integer with no leading zeroes -- i.e. 0 means midnight, 13000 means half past one in the morning (01:30:00) , substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),active_start_time), 6), 1, 2) + ':' + substring ( right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_start_time), 6) ,3 ,2) + ':' + substring ( right (stuff (' ', 1, 1, '000000') + convert(varchar(6),active_start_time), 6) ,5 ,2) as start_at ,case freq_subday_type when 1 then NULL -- Ignore the end time if not a recurring job else substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time), 6), 1, 2) + ':' + substring ( right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time), 6) ,3 ,2) + ':' + substring ( right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time), 6) ,5 ,2) end as end_at from msdb.dbo.sysjobs j, msdb.dbo.sysJobSchedules s, msdb.dbo.sysschedules c where j.job_id = s.job_id and s.schedule_id = c.schedule_id order by j.name, start_at /* ** Now that we know what jobs we have, let's find out ** how they did recently. */ truncate table job_status insert into job_status select convert(varchar(15), serverproperty('ServerName')), job_id, run_status, run_date, run_time, run_duration from msdb..sysjobhistory where step_name = '(job outcome)' -- The last 90 days' worth. and run_date > (select replace(convert(varchar(10), (getdate() - 90), 120), '-', '')) order by run_date desc /* ** If this server is already known to the audit server, ** we need to remove the existing data from the audit ** tables. */ declare @known bit set @known = (select count(*) from [192.168.0.126].AUDITDB.dbo.sql_servers where server_name = (select convert(varchar(15), serverproperty('servername')))) /* ** Now we remove the existing information from the audit tables, ** if need be. */ if @known = 1 begin delete from [192.168.0.126].AUDITDB.dbo.sql_servers where server_name = (select convert(varchar(15), serverproperty('ServerName'))) delete from [192.168.0.126].AUDITDB.dbo.databases where server_name = (select convert(varchar(15), serverproperty('ServerName'))) delete from [192.168.0.126].AUDITDB.dbo.server_config where server_name = (select convert(varchar(15), serverproperty('ServerName'))) delete from [192.168.0.126].AUDITDB.dbo.jobs where server_name = (select convert(varchar(15), serverproperty('ServerName'))) delete from [192.168.0.126].AUDITDB.dbo.job_status where server_name = (select convert(varchar(15), serverproperty('ServerName'))) end /* ** Finally, we upload the new info from here to the audit server. */ insert into [192.168.0.126].AUDITDB.dbo.sql_servers select * from admin.dbo.sql_servers insert into [192.168.0.126].AUDITDB.dbo.server_config select * from admin.dbo.server_config insert into [192.168.0.126].AUDITDB.dbo.databases select * from admin.dbo.databases insert into [192.168.0.126].AUDITDB.dbo.jobs select * from admin.dbo.jobs insert into [192.168.0.126].AUDITDB.dbo.job_status select * from admin.dbo.job_status This works fine for other boxes of the same service pack levels. I've already read KB302477, which doesn't appear to apply, since I'm already several revisions beyond that. I'm unable to duplicate this in test. Any ideas as to what I should look at next? Thanks. -D.

View Replies !

---

Create A Cursor Inside A Sproc
I try to create a Sproc which will use a cursor to retrieve a few rows from a table. But the cursor part has given me problem. Here it is:
 

StudentInfo
StudentID StudentName  DeptID
101         John               10
102         Alex               10
103        Beth                20
ClassInfo
ClassID  DeptID
901       10
902       10
225       20
I want to create a Sproc which will retreive the student's classes in DeptID 10
 
Following is the Sproc and cursor:

use master
go
Create PROCEDURE [dbo].[getEnclishClasses]
@StudentID int
AS
Declare @printInsertStatement nvarchar(100)
ECLARE NewRowID int
 
Declare classCursor CURSOR FOR
      SELECT ClassID, DeptID FROM [myTest].dbo.ClassInfo
      WHERE DeptID=(SELECT DeptID FROM [myTest].dbo.StudentInfo
      WHERE StudentID=@StudentID)
 
      DECLARE @ClassID INT
      DECLARE @DeptID INT
 
OPEN classCursor
FETCH NEXT FROM classCURSOR INTO
      @ClassID, @DeptID
WHILE (@@FETCH_STATUs=0)
BEGIN
      PRINT 'SET @newID = Scope_Identity()'
SET @printInsertStatement=
      (Select 'INSERT INTO [myTest].dbo.ClassInfo (ClassID, DeptID) Values('
      +CONVERT(NVARCHAR (10), @ClassID) + ','
      +CONVERT(NVARCHAR (2), @DeptID)+')'
      FROM [myTest].dbo.StudentInfo
      WHERE DeptID=(SELECT DeptID FROM [myTest].dbo.StudentInfo
      WHERE StudentID=@StudentID))
 
      PRINT @printInsertStatement
END
CLOSE classCursor
DEALLOCATE classCursor
EXEC getEnclishClasses 101

Here is what I try to get (text with actual data from the table):
SET @newRowID = Scope_Identity()
INSERT INTO [myTest].dbo.ClassInfo VALUES(901, 10)
SET @newRowID = Scope_Identity()
INSERT INTO [myTest].dbo.ClassInfo VALUES(902, 10)
 
Here is what I had got (returning multiple lines, more than number of records I have):
Msg 512, Level 16, State 1, Procedure getEnclishClasses, Line 19
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
 
Thanks in advance for your help! Or is it a better way (not using a cursor). Each table has over 5,000 records.
 

View Replies !

---

Decision Tree Predictions Occuring At Non-leaf Node
After having built a decision tree model to predict a boolean output attribute using 64-bit SQL Server 2005 (build 9.0.3054), we have observed that predictions for some cases are being done at non-leaf nodes in the tree.

 

Specifically, after executing a prediction join which returns:


- CaseTable.CaseID
- MiningModel.OutputAttribute
- PredictProbability(MiningModel.OutputAttribute)
- PredictNodeId(MiningModel.OutputAttribute)

 

and comparing the values of PredictNodeID(MiningModel.OutputAttribute) with the mining model content column [NODE_UNIQUE_NAME] to determine the actual "rule" used to make the case-level prediction. 

 

We have observed that for a subset of cases, predictions are being made at nodes in the tree that are not leaf nodes.  Specifically, predictions are being made at a node that is 3 levels deep.  The leaf nodes below this inner-tree node are 2 levels further down the tree. 

 

Also supporting the fact that that predictions are being made at this non-leaf node is that the PredictProbability corresponds exactly with the output attribute distribution at this non-leaf node.

 

In this particular application, we would have obtained better results if the predictions were made at the leaf-nodes.

 

A few questions:
1. Why are predictions with decision trees made at non-leaf nodes?
2. Is there a way to "force" predictions to occur at leaf nodes via DMX?

 

Thanks in advance for any information or advice.

- Paul

View Replies !

---

[SQL Server 2000] How Can I Create Cursor For A SQL Statement?
I have a SQL statement stored in a SQL varriable (after a lot of conditions)


Code:

declare @sql char(100)
set @sql = 'select ma_kh, ten from _khang'



Now, I want to create a cursor to recalculate some values
I've tried:



Code:

declare cur_T cursor for exec(@sql) open cur_T




but it doesn't work.
Can I have another way to do that???

View Replies !

---

Do Cursors Lose Their Place If Deletes And Multiple Commits Are Occuring On The Underlying Table?
will my fetches on a cursor continue seamlessly even if deletes and multiple commits are occuring on the underlying source table?  I want to make sure the cursor will not lose it's place as I believe some older dbms's did.

View Replies !

---

Query Analyzer Shows Negative Numbers As Positive Numbers
Why does M$ Query Analyzer display all numbers as positive, no matterwhether they are truly positive or negative ?I am having to cast each column to varchar to find out if there areany negative numbers being hidden from me :(I tried checking Tools/Options/Connections/Use Regional Settings bothon and off, stopping and restarting M$ Query Analyer in betwixt, butno improvement.Am I missing some other option somewhere ?

View Replies !

---

I Need To Update A Table With Random Numbers Or Sequential Numbers
 

I have a table with a column ID of ContentID.  The ID in that column is all NULLs.  I need a way to change those nulls to a number.  It does not matter what type of number it is as long as they are different.  Can someone point me somewhere with a piece of T-SQL that I could use to do that.  There are over 24000 rows so cursor change will not be very efficient.
 
Thanks for any help 

View Replies !

---

Generate List Of All Numbers (numbers Not In Use)
I have an 'ID' column. I'm up to about ID number 40000, but not all are in use, so ID 4354 might not be in any row. I want a list of all numbers which aren't in use. I want to write something like this:

select [numbers from 0 to 40000] where <number> not in (select distinct id from mytable)


but don't know how. Any clues?

View Replies !

---

&"Timeout Expired&" Error Occuring While Fetching The Data With SQL Server 2000
Hello,I have one application which is having written in asp.net & plain asp.I am having one button on asp page,when i will click on that button, then itwill execute one other asp page.And after the execution of that second asp page, I redirect it to someASPX page with some values.On the ASPX page, it will connect to the Database, and insert the values.Thus, sometime, the following error is occuring :"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."Main thing is that i am testing it on my local machine, then also it is giving me this error ...Insert into statement is also not much complicated :just I inserted three values in one table.Is this occuring due to the mixer of asp & aspx??Plz give me some proper solution.I want it in efficient manner, because this application will be accessed by number of users at the same time.Plz help me.Thanks,Sandy

View Replies !

---

Dataflow To Excel - Convert Numbers Stored As Text To Numbers Excel Cell Error
I'm trying to write data to excel from an ssis component to a excel destination.

Even thought I'm writing numerics, every cell gets this error with a green tag:

Convert numbers stored as text to numbers

Excel Cells were all pre-formated to accounting 2 decimal, and if i manually type the exact data Im sending it formats just fine.

I'm hearing this a common problem -

On another project I was able to find a workaround for the web based version of excel, by writing this to the top of the file:

<style>.text { mso-number-format:@; } </style>

is there anything I can pre-set in excel (cells are already formated) or write to my file so that numerics are seen as numerics and not text.

Maybe some setting in my write drivers - using sql servers excel destination.


So close.. Thanks for any help or information.

View Replies !

---

Dynamic Cursor Versus Forward Only Cursor Gives Poor Performance
Hello,I have a test database with table A containing 10,000 rows and a tableB containing 100,000 rows. Rows in B are "children" of rows in A -each row in A has 10 related rows in B (ie. B has a foreign key to A).Using ODBC I am executing the following loop 10,000 times, expressedbelow in pseudo-code:"select * from A order by a_pk option (fast 1)""fetch from A result set""select * from B where where fk_to_a = 'xxx' order by b_pk option(fast 1)""fetch from B result set" repeated 10 timesIn the above psueod-code 'xxx' is the primary key of the current Arow. NOTE: it is not a mistake that we are repeatedly doing the Aquery and retrieving only the first row.When the queries use fast-forward-only cursors this takes about 2.5minutes. When the queries use dynamic cursors this takes about 1 hour.Does anyone know why the dynamic cursor is killing performance?Because of the SQL Server ODBC driver it is not possible to havenested/multiple fast-forward-only cursors, hence I need to exploreother alternatives.I can only assume that a different query plan is getting constructedfor the dynamic cursor case versus the fast forward only cursor, but Ihave no way of finding out what that query plan is.All help appreciated.Kevin

View Replies !

---

Variable Type For Fetcing The Cursor Record In Tsql Cursor
what is the equivalent of the %rowtype in oracle for the tsql

View Replies !

---

Could Not Complete Cursor Operation Because The Set Options Have Changed Since The Cursor Was Declared.
I'm trying to implement a sp_MSforeachsp howvever when I call sp_MSforeach_worker
I get the following error can you please explain this problem to me so I can over come the issue.
 

Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 31

Could not complete cursor operation because the set options have changed since the cursor was declared.

Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 32

Could not complete cursor operation because the set options have changed since the cursor was declared.

Msg 16917, Level 16, State 1, Procedure sp_MSforeach_worker, Line 153

Cursor is not open.
 
here is the stored procedure:
 

Alter PROCEDURE [dbo].[sp_MSforeachsp]

@command1 nvarchar(2000)

, @replacechar nchar(1) = N'?'

, @command2 nvarchar(2000) = null

, @command3 nvarchar(2000) = null

, @whereand nvarchar(2000) = null

, @precommand nvarchar(2000) = null

, @postcommand nvarchar(2000) = null

AS

/* This procedure belongs in the "master" database so it is acessible to all databases */

/* This proc returns one or more rows for each stored procedure */

/* @precommand and @postcommand may be used to force a single result set via a temp table. */

declare @retval int

if (@precommand is not null) EXECUTE(@precommand)

/* Create the select */

EXECUTE(N'declare hCForEachTable cursor global for

SELECT QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_TYPE = ''PROCEDURE''

AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)), ''IsMSShipped'') = 0 '

+ @whereand)

select @retval = @@error

if (@retval = 0)

EXECUTE @retval = [dbo].sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 0

if (@retval = 0 and @postcommand is not null)

EXECUTE(@postcommand)

RETURN @retval

 

GO

 
example useage:
 

EXEC sp_MSforeachsp @command1="PRINT '?' GRANT EXECUTE ON ? TO [superuser]"

GO

View Replies !

---

Join Cursor With Table Outside Of Cursor
part 1

Declare @SQLCMD varchar(5000)
DECLARE @DBNAME VARCHAR (5000)

DECLARE DBCur CURSOR FOR
SELECT U_OB_DB FROM [@OB_TB04_COMPDATA]

OPEN DBCur
FETCH NEXT FROM DBCur INTO @DBNAME


WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @SQLCMD = 'SELECT T0.CARDCODE, T0.U_OB_TID AS TRANSID, T0.DOCNUM AS INV_NO, ' +
+ 'T0.DOCDATE AS INV_DATE, T0.DOCTOTAL AS INV_AMT, T0.U_OB_DONO AS DONO ' +
+ 'FROM ' + @DBNAME + '.dbo.OINV T0 WHERE T0.U_OB_TID IS NOT NULL'
EXEC(@SQLCMD)
PRINT @SQLCMD
FETCH NEXT FROM DBCur INTO @DBNAME

END

CLOSE DBCur
DEALLOCATE DBCur


Part 2

SELECT
T4.U_OB_PCOMP AS PARENTCOMP, T0.CARDCODE, T0.CARDNAME, ISNULL(T0.U_OB_TID,'') AS TRANSID, T0.DOCNUM AS SONO, T0.DOCDATE AS SODATE,
SUM(T1.QUANTITY) AS SOQTY, T0.DOCTOTAL - T0.TOTALEXPNS AS SO_AMT, T3.DOCNUM AS DONO, T3.DOCDATE AS DO_DATE,
SUM(T2.QUANTITY) AS DOQTY, T3.DOCTOTAL - T3.TOTALEXPNS AS DO_AMT
INTO #MAIN
FROM
ORDR T0
JOIN RDR1 T1 ON T0.DOCENTRY = T1.DOCENTRY
LEFT JOIN DLN1 T2 ON T1.DOCENTRY = T2.BASEENTRY AND T1.LINENUM = T2.BASELINE AND T2.BASETYPE = T0.OBJTYPE
LEFT JOIN ODLN T3 ON T2.DOCENTRY = T3.DOCENTRY
LEFT JOIN OCRD T4 ON T0.CARDCODE = T4.CARDCODE
WHERE ISNULL(T0.U_OB_TID,0) <> 0
GROUP BY T4.U_OB_PCOMP, T0.CARDCODE,T0.CARDNAME, T0.U_OB_TID, T0.DOCNUM, T0.DOCDATE, T3.DOCNUM, T3.DOCDATE, T0.DOCTOTAL, T3.DOCTOTAL, T3.TOTALEXPNS, T0.TOTALEXPNS


my question is,
how to join the part 1 n part 2?
is there posibility?

View Replies !

---

Cursor Inside A Cursor
I'm new to cursors, and I'm not sure what's wrong with this code, it run for ever and when I stop it I get cursor open errors




declare Q cursor for
select systudentid from satrans


declare @id int

open Q
fetch next from Q into @id
while @@fetch_status = 0
begin

declare c cursor for

Select
b.ssn,
SaTrans.SyStudentID,
satrans.date,
satrans.type,
SaTrans.SyCampusID,
Amount = Case SaTrans.Type
When 'P' Then SaTrans.Amount * -1
When 'C' Then SaTrans.Amount * -1
Else SaTrans.Amount END

From SaTrans , systudent b where satrans.systudentid = b.systudentid

and satrans.systudentid = @id




declare @arbalance money, @type varchar, @ssn varchar, @amount money, @systudentid int, @transdate datetime, @sycampusid int, @before money

set @arbalance = 0
open c
fetch next from c into @ssn, @systudentid, @transdate, @type, @sycampusid, @amount

while @@fetch_status = 0
begin

set @arbalance = @arbalance + @amount
set @before = @arbalance -@amount

insert c2000_utility1..tempbalhistory1
select @systudentid systudentid, @sycampusid sycampusid, @transdate transdate, @amount amount, @type type, @arbalance Arbalance, @before BeforeBalance
where( convert (int,@amount) <= -50
or @amount * -1 > @before * .02)
and @type = 'P'




fetch next from c into @ssn, @systudentid, @transdate, @type, @sycampusid, @amount
end
close c
deallocate c
fetch next from Q into @id

end
close Q
deallocate Q


select * from c2000_utility1..tempbalhistory1
truncate table c2000_utility1..tempbalhistory1

View Replies !

---

Client Side Cursor Vs Sever Side Cursor?
I having a difficult time here trying to figure out what to do here.I need a way to scroll through a recordset and display the resultswith both forward and backward movement on a web page(PHP usingADO/COM)..I know that if I use a client side cursor all the records get shovedto the client everytime that stored procedure is executed..if thisdatabase grows big wont that be an issue?..I know that I can set up a server side cursor that will only send therecord I need to the front end but..Ive been reading around and a lot of people have been saying never touse a server side cursor because of peformance issues.So i guess im weighing network performance needs with the client sidecursor vs server performance with the server side cursor..I am reallyconfused..which one should I use?-Jim

View Replies !

---

Even And Odd Numbers
I need to figure out how to pull records if the id is even or odd.

ie

ID Name
1 bill
2 john
3 joe
4 jane

for example i need a idea of a select statement that will pull the even records 2 and 4.

is this possible?

View Replies !

---

Row Numbers Etc.
 

I have a sales report that includes dollar amount, tonnage, and profit margin among other things.  They are currently sorted by tonnage sold from highest to lowest.  I'd like to be able to place a number in a column counting 1 up for tonnage ranking.  I'd also like to get a number ranking for sales amount ranking along with profit margin ranking.  The most tonnage sold might not have been the biggest sale nor had the highest profit margin.
 
Does this sound like something that can be done within SSRS?
 
I should ad I'm runing MDX queries against a cube so I can't use T-SQL for ranking.

View Replies !

---

Numbers Less Than Zero
In a pretty standard select statement (as shown), i want to return 0 when "dbo.v_AgentOrderTotals.Total - dbo.v_AgentAmmountPaid.total - dbo.v_AgentCommClean.total AS amount_outstanding_commission" is less than 0.

SELECT     dbo.t_Agents.agent_code, dbo.v_CurrentParamPaymentTotal.ammount AS weekley_payment_total,
                      dbo.v_AgentNumberOfCustomers.count AS number_of_cust, dbo.v_AgentAmmountPaid.total AS total_paid,
                      dbo.v_AgentOrderTotals.Total AS ytd_order_total, dbo.v_AgentOrderTotals.Total - dbo.v_AgentAmmountPaid.total AS amount_outstanding,
                      ISNULL(dbo.v_AgentAmmountPaid.total / dbo.v_AgentOrderTotals.Total, 0) * 100 AS ytd_percentage,
                      dbo.v_AgentOrderTotals.Total - dbo.v_AgentAmmountPaid.total - dbo.v_AgentCommClean.total AS amount_outstanding_commission,
                      ISNULL(dbo.v_AgentOrderChange.amount, 0) AS net_weekly_order
FROM         dbo.t_Agents LEFT OUTER JOIN
                      dbo.v_AgentOrderChange ON dbo.t_Agents.AGENT_ID = dbo.v_AgentOrderChange.AGENT_ID LEFT OUTER JOIN
                      dbo.v_AgentCommClean ON dbo.t_Agents.AGENT_ID = dbo.v_AgentCommClean.AGENT_ID LEFT OUTER JOIN
                      dbo.v_AgentNumberOfCustomers ON dbo.t_Agents.AGENT_ID = dbo.v_AgentNumberOfCustomers.AGENT_ID LEFT OUTER JOIN
                      dbo.v_AgentOrderTotals ON dbo.t_Agents.AGENT_ID = dbo.v_AgentOrderTotals.AGENT_ID LEFT OUTER JOIN
                      dbo.v_AgentAmmountPaid ON dbo.t_Agents.AGENT_ID = dbo.v_AgentAmmountPaid.AGENT_ID LEFT OUTER JOIN
                      dbo.v_CurrentParamPaymentTotal ON dbo.t_Agents.AGENT_ID = dbo.v_CurrentParamPaymentTotal.AGENT_ID

 

Any ideas how i do this?

Cheers
Anthony Swift

View Replies !

---

Add, Sub, Mul, Div With Big Numbers
You can Add, Sub, Mul, Div With Big Numbers
Ex:
SELECT dbo.xDiv('127435627567236572365726354625763275623756237657236572365273', '68436783476843674387683476843434367346', 10)

The output is:
1862092592507007503619.7638832121

CREATE FUNCTION xDiv(@xText VARCHAR(8000), @yText VARCHAR(8000), @n INT)
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @i INT, @j INT, @sx VARCHAR(8000), @sy VARCHAR(8000), @strRet VARCHAR(8000)
SELECT @i = dbo.xSign(@xText), @j = dbo.xSign(@yText), @sx = dbo.xAbs(@xText), @sy = dbo.xAbs(@yText)
SELECT @sx = dbo.xCutFraction(@sx), @sy = dbo.xCutFraction(@sy)

IF @i = @j
SELECT @strRet = dbo.DivFraction(@sx, @sy, @n)
ELSE
SELECT @strRet = '-' + dbo.DivFraction(@sx, @sy, @n)
RETURN @strRet
END


CREATE FUNCTION xMul(@xText VARCHAR(8000), @yText VARCHAR(8000))
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @i INT, @j INT, @sx VARCHAR(8000), @sy VARCHAR(8000), @strRet VARCHAR(8000)
SELECT @i = dbo.xSign(@xText), @j = dbo.xSign(@yText), @sx = dbo.xAbs(@xText), @sy = dbo.xAbs(@yText)
SELECT @sx = dbo.xCutFraction(@sx), @sy = dbo.xCutFraction(@sy)

IF @i = @j
SELECT @strRet = dbo.MulFraction(@sx, @sy)
ELSE
SELECT @strRet = '-' + dbo.MulFraction(@sx, @sy)
RETURN @strRet
END

CREATE FUNCTION xSub(@xText VARCHAR(8000), @yText VARCHAR(8000))
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @j INT, @sy VARCHAR(8000), @strRet VARCHAR(8000)
SELECT @j = dbo.xSign(@yText), @sy = dbo.xAbs(@yText)
IF @j = 1
SELECT @strRet= dbo.xAdd(@xText, '-' + @sy)
ELSE
SELECT @strRet= dbo.xAdd(@xText, @sy)

RETURN @strRet
END

CREATE FUNCTION xAdd(@xText VARCHAR(8000), @yText VARCHAR(8000))
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @i INT, @j INT, @sx VARCHAR(8000), @sy VARCHAR(8000), @strRet VARCHAR(8000)
SELECT @i = dbo.xSign(@xText), @j = dbo.xSign(@yText), @sx = dbo.xAbs(@xText), @sy = dbo.xAbs(@yText)
SELECT @sx = dbo.xCutFraction(@sx), @sy = dbo.xCutFraction(@sy)

IF @i = 1
BEGIN
IF @j = 1
SELECT @strRet = dbo.AddFraction(@sx, @sy)
ELSE
IF dbo.zCompare(@sx, @sy) <> -1
SELECT @strRet = dbo.SubFraction(@sx, @sy)
ELSE
SELECT @strRet = '-' + dbo.SubFraction(@sy, @sx)
END
ELSE
BEGIN
IF @j = 1
IF dbo.zCompare(@sy, @sx) <> -1
SELECT @strRet = dbo.SubFraction(@sy, @sx)
ELSE
SELECT @strRet = '-' + dbo.SubFraction(@sx, @sy)
ELSE
SELECT @strRet = '-' + dbo.AddFraction(@sx, @sy)
END
RETURN @strRet
END


CREATE FUNCTION xCompare(@xText VARCHAR(8000), @yText VARCHAR(8000))
RETURNS INT AS
BEGIN
DECLARE @i INT, @j INT, @sx VARCHAR(8000), @sy VARCHAR(8000), @iRet INT
SELECT @i = dbo.xSign(@xText), @j = dbo.xSign(@yText), @sx = dbo.xAbs(@xText), @sy = dbo.xAbs(@yText)
SELECT @sx = dbo.xCutFraction(@sx), @sy = dbo.xCutFraction(@sy)

IF @i = 1
BEGIN
IF @j = 1
SELECT @iRet = dbo.zCompare(@sx, @sy)
ELSE
IF @sx = '0' AND @sy = '0'
SELECT @iRet = 0
ELSE
SELECT @iRet = 1
END
ELSE
BEGIN
IF @j = 1
IF @sx = '0' AND @sy = '0'
SELECT @iRet = 0
ELSE
SELECT @iRet = -1
ELSE
SELECT @iRet = -1*dbo.zCompare(@sx, @sy)
END
RETURN @iRet
END


CREATE FUNCTION zCompare(@xText VARCHAR(8000), @yText VARCHAR(8000))
RETURNS INT AS
BEGIN
DECLARE @iRet INT
DECLARE @sI VARCHAR(8000), @xIText VARCHAR(8000), @xFText VARCHAR(8000), @yIText VARCHAR(8000), @yFText VARCHAR(8000), @l INT
SELECT @xIText = dbo.xInt(@xText), @yIText = dbo.xInt(@yText), @xFText = dbo.xFraction(@xText), @yFText = dbo.xFraction(@yText)
IF LEN(@xFText) > LEN(@yFText) SET @yFText = @yFText + REPLICATE('0', LEN(@xFText) - LEN(@yFText))
IF LEN(@xFText) < LEN(@yFText) SET @xFText = @xFText + REPLICATE('0', LEN(@yFText) - LEN(@xFText))
SELECT @iRet = dbo.Compare(@xIText + @xFText, @yIText + @yFText)
RETURN @iRet
END


CREATE FUNCTION xAbs(@xText VARCHAR(8000))
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @strRet VARCHAR(8000)
SELECT @strRet = CASE WHEN dbo.xSign(@xText) = -1 THEN RIGHT(@xText, LEN(@xText) - 1) ELSE @xText END
RETURN @strRet
END

CREATE FUNCTION xSign(@xText VARCHAR(8000))
RETURNS INT AS
BEGIN
DECLARE @iRet INT
SELECT @iRet = CASE WHEN LEFT(@xText, 1) = '-' THEN -1 ELSE 1 END
RETURN @iRet
END

CREATE FUNCTION DivFraction(@xText VARCHAR(8000), @yText VARCHAR(8000), @n INT)
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @sI VARCHAR(8000), @sF VARCHAR(8000), @xIText VARCHAR(8000), @xFText VARCHAR(8000), @yIText VARCHAR(8000), @yFText VARCHAR(8000), @x INT
SET @x = @n + 1
IF @x < LEN(@yText) + 1 SET @x = LEN(@yText) + 1
SELECT @xIText = dbo.xInt(@xText), @yIText = dbo.xInt(@yText), @xFText = dbo.xFraction(@xText), @yFText = dbo.xFraction(@yText)
IF LEN(@xFText) > LEN(@yFText) SET @yFText = @yFText + REPLICATE('0', LEN(@xFText) - LEN(@yFText))
IF LEN(@xFText) < LEN(@yFText) SET @xFText = @xFText + REPLICATE('0', LEN(@yFText) - LEN(@xFText))
SELECT @sI = dbo.DivInt(@xIText + @xFText + REPLICATE('0', @x), @yIText + @yFText)
WHILE LEN(@sI) <= @x SET @sI = '0' + @sI
SET @sI = LEFT(@sI, LEN(@sI) - @x) + '.' + RIGHT(@sI, @x)
SET @sI = dbo.xCutFraction(@sI)
SET @sF = dbo.xFraction(@sI)
IF LEN(@sF) > @n
BEGIN
IF CAST(SUBSTRING(@sF, @n + 1, 1) AS INT) >= 5
BEGIN
IF @n = 0
SET @sI = dbo.AddFraction(LEFT(@sI, LEN(@sF) - @n), '1')
ELSE
SET @sI = dbo.AddFraction(LEFT(@sI, LEN(@sI) - LEN(@sF) + @n), '0.' + REPLICATE('0', @n - 1) + '1')
END
ELSE
SET @sI = LEFT(@sI, LEN(@sI) - LEN(@sF) + @n)
END
SET @sI = dbo.xCutFraction(@sI)
RETURN @sI
END

CREATE FUNCTION MulFraction(@xText VARCHAR(8000), @yText VARCHAR(8000))
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @sI VARCHAR(8000), @xIText VARCHAR(8000), @xFText VARCHAR(8000), @yIText VARCHAR(8000), @yFText VARCHAR(8000), @l INT, @k INT
SELECT @xIText = dbo.xInt(@xText), @yIText = dbo.xInt(@yText), @xFText = dbo.xFraction(@xText), @yFText = dbo.xFraction(@yText)
SELECT @l = LEN(@xFText), @k = LEN(@yFText), @sI = dbo.MulInt(@xIText + @xFText, @yIText + @yFText)
WHILE LEN(@sI) <= @l + @k SET @sI = '0' + @sI
SET @sI = LEFT(@sI, LEN(@sI) - @l - @k) + '.' + RIGHT(@sI, @l + @k)
SET @sI = dbo.xCutFraction(@sI)
RETURN @sI
END

CREATE FUNCTION SubFraction(@xText VARCHAR(8000), @yText VARCHAR(8000))
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @sI VARCHAR(8000), @xIText VARCHAR(8000), @xFText VARCHAR(8000), @yIText VARCHAR(8000), @yFText VARCHAR(8000), @l INT
SELECT @xIText = dbo.xInt(@xText), @yIText = dbo.xInt(@yText), @xFText = dbo.xFraction(@xText), @yFText = dbo.xFraction(@yText)

IF LEN(@xFText) > LEN(@yFText) SET @yFText = @yFText + REPLICATE('0', LEN(@xFText) - LEN(@yFText))
IF LEN(@xFText) < LEN(@yFText) SET @xFText = @xFText + REPLICATE('0', LEN(@yFText) - LEN(@xFText))

SELECT @l = LEN(@xFText), @sI = dbo.SubInt(@xIText + @xFText, @yIText + @yFText)
WHILE LEN(@sI) <= @l SET @sI = '0' + @sI
SET @sI = LEFT(@sI, LEN(@sI) - @l) + '.' + RIGHT(@sI, @l)
SET @sI = dbo.xCutFraction(@sI)
RETURN @sI
END

CREATE FUNCTION AddFraction(@xText VARCHAR(8000), @yText VARCHAR(8000))
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @sI VARCHAR(8000), @xIText VARCHAR(8000), @xFText VARCHAR(8000), @yIText VARCHAR(8000), @yFText VARCHAR(8000), @l INT
SELECT @xIText = dbo.xInt(@xText), @yIText = dbo.xInt(@yText), @xFText = dbo.xFraction(@xText), @yFText = dbo.xFraction(@yText)

IF LEN(@xFText) > LEN(@yFText) SET @yFText = @yFText + REPLICATE('0', LEN(@xFText) - LEN(@yFText))
IF LEN(@xFText) < LEN(@yFText) SET @xFText = @xFText + REPLICATE('0', LEN(@yFText) - LEN(@xFText))

SELECT @l = LEN(@xFText), @sI = dbo.AddInt(@xIText + @xFText, @yIText + @yFText)
WHILE LEN(@sI) <= @l SET @sI = '0' + @sI
SET @sI = LEFT(@sI, LEN(@sI) - @l) + '.' + RIGHT(@sI, @l)
SET @sI = dbo.xCutFraction(@sI)
RETURN @sI
END


CREATE FUNCTION xInt(@xText VARCHAR(8000))
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @n INT, @strRet VARCHAR(8000)
SET @n = CHARINDEX('.', @xText)
IF @n = 0
SET @strRet = @xText
ELSE
SET @strRet = LEFT(@xText, @n - 1)
RETURN @strRet
END


CREATE FUNCTION xFraction(@xText VARCHAR(8000))
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @n INT, @strRet VARCHAR(8000)
SET @n = CHARINDEX('.', @xText)
IF @n = 0
SET @strRet = '0'
ELSE
SET @strRet = RIGHT(@xText, LEN(@xText) - @n)
RETURN @strRet
END

CREATE FUNCTION DivInt(@xText VARCHAR(8000), @yText VARCHAR(8000))
RETURNS VARCHAR(8000) AS
BEGIN
SELECT @xText = dbo.xCut(@xText), @yText = dbo.xCut(@yText)
DECLARE @i INT,@n INT, @strRet VARCHAR(8000), @strTemp VARCHAR(8000)
SELECT @strRet = '', @strTemp = '', @i = 1
WHILE @i <= LEN(@xText)
BEGIN
SET @strTemp = dbo.xCut(@strTemp + SUBSTRING(@xText, @i, 1))
WHILE dbo.Compare(@strTemp, @yText) = -1 And @i < LEN(@xText)
BEGIN
SELECT @strRet = @strRet + '0', @i = @i + 1, @strTemp = @strTemp + SUBSTRING(@xText, @i, 1)
SET @strTemp = dbo.xCut(@strTemp)
END
IF dbo.Compare(@strTemp, @yText) = -1 AND @i = LEN(@xText)
BEGIN
SELECT @strRet = @strRet + '0', @i = @i + 1
END

IF dbo.Compare(@strTemp, @yText) <> -1
BEGIN
SELECT @n = dbo.xCeiling(@strTemp, @yText)
SELECT @strRet = @strRet + RTRIM(LTRIM(STR(@n))), @strTemp = dbo.SubInt(@strTemp, dbo.MulInt(@yText, RTRIM(LTRIM(STR(@n))))), @i = @i + 1
END
END
SET @strRet = dbo.xCut(@strRet)
RETURN @strRet
END

CREATE FUNCTION xCeiling(@xText VARCHAR(8000), @yText VARCHAR(8000))
RETURNS INT AS
BEGIN
DECLARE @iRet INT
SET @iRet = 0
WHILE dbo.Compare(dbo.MulInt(@yText, RTRIM(LTRIM(STR(@iRet + 1)))), @xText) <> 1
BEGIN
SET @iRet = @iRet + 1
END
RETURN @iRet
END

CREATE FUNCTION MulInt(@xText VARCHAR(8000), @yText VARCHAR(8000))
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @i INT, @j INT, @l INT, @k INT, @strRet VARCHAR(8000)
SELECT @i = LEN(@xText), @j = LEN(@yText), @strRet = '0'
SELECT @l = LEN(@xText), @k = @j
WHILE @k >= 1
BEGIN
SET @strRet = dbo.AddInt(dbo.MulChar(@xText, SUBSTRING(@yText, @k, 1)) + REPLICATE('0', @j - @k), @strRet)
SET @k = @k - 1
END
RETURN @strRet
END

CREATE FUNCTION MulChar(@xText VARCHAR(8000), @yText VARCHAR(1))
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @i INT, @j INT, @l INT, @m INT, @r INT, @strRet VARCHAR(8000)
SELECT @l = LEN(@xText), @m = 0, @strRet = ''
SET @i = @l
WHILE @i >= 1
BEGIN
SET @r = CAST(SUBSTRING(@xText, @i, 1) AS INT) * CAST(@yText AS INT) + @m
SELECT @m = 0
WHILE @r >= 10
BEGIN
SET @m = @m + 1
SET @r = @r - 10
END
SET @strRet = RTRIM(LTRIM(STR(@r))) + @strRet
SET @i = @i - 1
END
IF @m > 0 SET @strRet = RTRIM(LTRIM(STR(@m))) + @strRet
SET @strRet = dbo.xCut(@strRet)
RETURN @strRet
END

CREATE FUNCTION SubInt(@xText VARCHAR(8000), @yText VARCHAR(8000))
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @i INT, @l INT, @m INT, @r INT, @strRet VARCHAR(8000)
SET @yText = REPLICATE('0', LEN(@xText) - LEN(@yText)) + @yText
SELECT @l = LEN(@xText), @m = 0, @strRet = ''
SET @i = @l
WHILE @i >= 1
BEGIN
SET @r = 10 + CAST(SUBSTRING(@xText, @i, 1) AS INT) - CAST(SUBSTRING(@yText, @i, 1) AS INT) - @m
SELECT @m = CASE WHEN @r >= 10 THEN 0 ELSE 1 END
SET @strRet = RIGHT(RTRIM(LTRIM(STR(@r))), 1) + @strRet
SET @i = @i - 1
END
SET @strRet = dbo.xCut(@strRet)
RETURN @strRet
END

CREATE FUNCTION AddInt(@xText VARCHAR(8000), @yText VARCHAR(8000))
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @i INT, @j INT, @l INT, @m INT, @r INT, @strRet VARCHAR(8000)
SELECT @i = LEN(@xText), @j = LEN(@yText)
IF @i < @j SET @xText = REPLICATE('0', @j - @i) + @xText
IF @i > @j SET @yText = REPLICATE('0', @i - @j) + @yText
SELECT @l = LEN(@xText), @m = 0, @strRet = ''
SET @i = @l
WHILE @i >= 1
BEGIN
SET @r = CAST(SUBSTRING(@xText, @i, 1) AS INT) + CAST(SUBSTRING(@yText, @i, 1) AS INT) + @m
SELECT @m = CASE WHEN @r >= 10 THEN 1 ELSE 0 END
SET @strRet = RTRIM(LTRIM(STR(@r - @m*10))) + @strRet
SET @i = @i - 1
END
IF @m > 0 SET @strRet = '1' + @strRet
SET @strRet = dbo.xCut(@strRet)
RETURN @strRet
END

CREATE FUNCTION Compare(@xText VARCHAR(8000), @yText VARCHAR(8000))
RETURNS INT AS
BEGIN
DECLARE @iRet INT, @i INT
IF LEN(@xText) > LEN(@yText) SET @iRet = 1
IF LEN(@xText) < LEN(@yText) SET @iRet = -1
IF LEN(@xText) = LEN(@yText)
BEGIN
SELECT @i = 1, @iRet = 0
WHILE @i <= LEN(@xText)
BEGIN
IF SUBSTRING(@xText, @i, 1) <>SUBSTRING(@yText, @i, 1)
BEGIN
IF SUBSTRING(@xText, @i, 1) > SUBSTRING(@yText, @i, 1)
SET @iRet = 1
ELSE
SET @iRet = -1
BREAK
END
SET @i = @i + 1
END
END
RETURN @iRet
END

CREATE FUNCTION xCut(@s VARCHAR(8000))
RETURNS VARCHAR(8000) AS
BEGIN
WHILE LEN(@s) > 1 AND LEFT(@s, 1) = '0'
BEGIN
SET @s = RIGHT(@s, LEN(@s) - 1)
END
RETURN @s
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE FUNCTION xCutFraction(@s VARCHAR(8000))
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @i INT, @n INT
SET @n = CHARINDEX('.', @s)
IF @n > 0
BEGIN
SET @i = LEN(@s)
WHILE @i > @n AND SUBSTRING(@s, @i, 1) = '0'
BEGIN
SET @i = @i - 1
END
IF @i = @n SET @i = @i -1
SET @s = LEFT(@s, @i)
END
RETURN @s
END

View Replies !

---

Numbers
Hello,

I have the following:

insert into dbo.Categories (CategoryID, [Name])
select
newid(),
'Category ' + right('000' + convert(varchar(3), n + 1), 3)
from @Numbers

Numbers is a table with 1000 numbers.
How can I create only 10 records in Categories?
I need to restrict the number of @Numbers used.

Thanks,
Miguel

View Replies !

---

Calculating ID Numbers
 I needed to come up with an algorithm to create unique user- friendly account numbers such as AC0000000001, AC0000000002, etc...Where they increment by 1. I created a SQL function that retrieves the previous number generated, adds 1 to it, inserts the new value into  the table, then returns the new value. I started thinking, what if the function is ran at the same time? What if function # 1 creates the new number, and function #2 creates a new number as well before function #1 inserts it? Is this a possible scenario? If so, how do I lock the process until the function completes to prevent this?  Thanks for any help you provide.

View Replies !

---

Expression.Like For Numbers?
Hi,
Is it possible to search in columns with a number datatype (I'm using an MS SQL database with bigint columns) with the ICriterion Expression.Like?
Normally the Expression.Like is used for varchar columns. However, if there's a bigint column with the value 167829 I want to search for example on %678%.
Cheers,
koekie

View Replies !

---

Generating Numbers In SQL
hi,

I am developing a ASP.NET application using SQL Server as my database.
I want to create a number that is unique.

The number will have 8 digits.

first 2 digits correspond to year. ex:04 or 03

the next 6 digits start with 000001 and it should get added for each new entry of data.

i am not able to generate number in the way i said. I am relatively new to SQL. so any suggestions as how to go about solving the problem???. Are there any samples/codes available for this.


Any help would be highly appreciated.

thanks,
-sriram

View Replies !

---

Row Numbers For A View
I've been given a task that I believe is, basically, impossible, butI'd like to see if there's a way to do it.What my boss wants me to do is to create a view, in SQL Server 2000,that will provide not only a row number field of some sort, but thatwill produce sequential ordering for arbitrary selects and orderings.So, if my data is a table with values from A thru D and my user doesSELECT data FROM vwTable, the result would be:Row Data--- ----1 A2 B3 C4 DBut is they did SELECT data FROM vwTable ORDER BY data DSC, they wouldgetRow Data--- ----1 D2 C3 B4 AAnd if the did SELECT data FROM vwTable WHERE Data IN ('B', 'C'), theywould getRow Data--- ----1 B2 CIn SQL 2005, of course, this would be fairly trivial since I could usethe ROW_NUMBER function. In 2000, though, it seems to be utterlyimpossible. My boss, however, is convinced that there must be some wayto create a calculated field to do it.I'll be cursed if I can figure out a way to do so.Any suggestions would be appreciated.

View Replies !

---

Float Numbers
Hi!How do I do to make t-sql not rounding the result that i returned?For example:0.9616458*60 = 57,698748 (in any calculator)while following:--------------------------------declare @a floatdeclare @b intset @a=0.9616458set @b=60print @a*@b---------------------------------will show :57.6987How do I do to make MSSQL to show me the value whothout rounding it?Thanks!

View Replies !

---

Grouping Numbers
I have a table which lists player names, teams played for and theyears they played there and my code looks like thisSELECT AlsoPlayedFor.playerID, AlsoPlayedFor.teamID,AlsoPlayedFor.TeamName, Min([AlsoPlayedFor].[Year]) & "-" &Max([AlsoPlayedFor].[Year]) AS [Year]FROM AlsoPlayedForGROUP BY AlsoPlayedFor.playerID, AlsoPlayedFor.teamID,AlsoPlayedFor.TeamName;which takes the Min year and the Max Year and displays it like "Year-Year"But lets say for example the player played for 5 years so it 1990,1991, 1992, 1993, 1995It would display as 1990-1995 but I want it to display as 1990-1993,1995, is this possiable??? Also I need it to gothe other wayso if theyears are 1990, 1992, 1993, 1994, 1995 I want that to display as 1990,1992-1995.PLEASE HELP

View Replies !

---

ADP Revision Numbers
I have an ADP connected to an SQL 2000 database that works fine on mymachine,Opening the ADP show lists of stored procedures alaup_ListProgramsbut, when I copy it to another computer, and open the ADP with thesame connection propertiesall of the stored procedures and views have a ?revision? number afterthem alaup_ListPrograms,1this breaks all my forms, reports etc.anyone know if this can be controlled somehow?Thans

View Replies !

---

Formatting Numbers
I am using a MS Access ADP connected to SQL Server Data

In a view I have the following formula:

dbo.tblQuoteItem.Cost + dbo.tblQuoteItem.Markup * dbo.tblQuoteItem.Cost * .01

this calculates cost + markup

I cannot get it to format in Currency

Ex:

Cost is $1.75
Markup is 2.00 (2%)
Total shows - 1.785000

I want $1.78

Also - I am using this formula to calculate the Quoted price for the Qty Entered

dbo.tblQuoteItem.Qty * dbo.tblQuoteItem.Cost + dbo.tblQuoteItem.Markup * dbo.tblQuoteItem.Cost * .01

Using a Qty of 2 for above, I get 3.535000

I want $3.53


Any help is appreciated - AB

View Replies !

---

Negative Numbers
I`m using an ascii file to bcp into sql tables. A balance field comes in as leading sign (-435). It`s loaded into tables as -435. I bcp it out to a file later and it comes out as -435. Is there a command or a way to get trailing signs without having to parse through the field and moving the negative sign to the end (435-)?

TIA,
Morgan

View Replies !

---

Random Numbers.
I am trying to get random numbers to have a unique value for different processes, then I can identify each process. What happens is that I use rand() function without seed, so I got my random numbers, but after shutting down SQLServer and try to get again another random number after booting up, the same series of random numbers is given again and again. So if anyone knows how I can get unique values,even though reseting the server, and using random function or any other method which automatically provides unique values,I'll really appreciate it if you let me know it.

This is the function: select rand()

Alberto.

View Replies !

---

Getting MAX From Char Numbers
I have a table which will recieve and interface file to be loaded. All the fields are there except an account_id field which is a char(12), but the data is a number 000000000001, 000000000002, etc. These numbers are to be incremented by one. I understand how to write the process using a cursor to run through all the records and to increment them before loading. My question is the best way to get the max number in the existing table which is the char(12) , and icrement the number by one, but retain the leading zeroes I will need. Anyone had the pleasure of doing this providing input greatly appreciated.

View Replies !

---

Deleting Odd And Even Numbers
I have two identical databases. I need to delete the even customer numbers from the first database and the odd numbers from the other database. I know I can do this writing a delete statement for each table. However there are a total of 54 tables.

Does anyone know a faster way to write the script without writing 54 scripts?

I would appreciate your help.

As always you guys are the greatest.

Dianne

View Replies !

---

Subselect With Numbers
I have the following table:
CREATE TABLE ITEMS ([ITEMID] int, [itRULE] varchar(1))
INSERT INTO ITEMS (ITEMID, itRULE) VALUES (11, 3)
INSERT INTO ITEMS (ITEMID, itRULE) VALUES (12, 3)
INSERT INTO ITEMS (ITEMID, itRULE) VALUES (21, 2)
INSERT INTO ITEMS (ITEMID, itRULE) VALUES (22, 2)
INSERT INTO ITEMS (ITEMID, itRULE) VALUES (31, 1)
INSERT INTO ITEMS (ITEMID, itRULE) VALUES (32, 1)
INSERT INTO ITEMS (ITEMID, itRULE) VALUES (41, 0)
INSERT INTO ITEMS (ITEMID, itRULE) VALUES (42, 0)

-- Those works and gives me 11,12,21,22
SELECT ITEMID FROM ITEMS WHERE itRULE IN (2,3)
SELECT ITEMID FROM ITEMS WHERE itRULE IN ('2','3')

-- This doesn't works
declare @Rule varchar(10)
set @Rule='2,3'
SELECT ITEMID FROM ITEMS WHERE itRULE IN (@Rule)
Any idea?
I don't mind to change the data type if it works.

View Replies !

---

Grouping Or SUM Of Numbers
Hi All,

After working out the basics and getting a report to give me back rows based upon a dropdown list, i know want to ask how do i show the sum of values for a customer.

So the out put would look something like this

Customer Total
0001 12234.56
0002 232.98
0003 456.78

I have two tables "customer" and "trades" i all ready have them linked and giving me all the individual items, oh and i have got it doing BETWEEN to dates, but i would really like to know how to show just the TOTAL trades for each cutomer on each line not every single trade for the customer.

Many thanks for any help with this

Dave C :confused:

View Replies !

---

Rounding Numbers
I have a db with prices stored in US Dollars. I've had numerous problems using the "small money" data type, so i've moved on to Decimal.

I'm using the Decimal data type with a scale of 2 in order to force a 2 digit decimal, even if the decimal is ".00". The problem i'm having is that the numbers with .00 as the amount of cents always get rounded to the nearest whole dollar and the .00 is not listed in the record itself. Due to the backend requirements of the transaction processing, the db needs to store a 2 digit decimal, even if it is ".00".

Any thoughts would be appreciated.

View Replies !

---

Numbers Truncated
Hello,
I am trying to run a query
select logid, count(logid) from temp2
group by logid
order by logid
compute sum(count(logid))
when I get the result the numbers are being truncated
eg instead of 10471066 it shows 104710 so last two digits
get truncated. Any ideas or hints appreciated.
Thanks
HP

View Replies !

---

Rounding Numbers
Looking for a way to round numbers to a specified number of significant digits. The ROUND function rounds to a specific decimal place but does not take into account the level of significance of the remaining numbers. (i.e.
ROUND(7.12435,2)=7.12000) The type of function I need would round the number in the following manner: SigFigRound(7.12435,3)=7.12 or
SigFigRound(7.12345,1)=7.

Any solutions?

View Replies !

---

Rounding Numbers
Whats the best way to handle Rounding of numbers.
I have the following real number 27.277777777777779, I wish to store this to two decimal places.
If I use the round function I get 27.280000000000001.
What I actually wish to store is 27.28
I don't belive there is a standard SQL function which will do what I want.

Whats the best way to handle this. Is there any articles whict look at this subject.

View Replies !

---

Again ! Serial Numbers
How to generate serial numbers ? I had already tired ident. i am getting the error. Can any people who is willing to write a syntax for me.

In sybase if we use Number * function. It will automatically generates the serial numbers from 1 to n. similarly i need the same function in SQL server 7.0 so that my problem will be solved...

I am converting sybase stored procedure into sql server stored procedure that is why i am asking about that. i am struggling hard to find an answer...

please help me in this...

urs
vj

View Replies !

---

Hi ! Once Again With Serial Numbers
Hi all,

I used the identity function, but still it is not worked. I need to use in select statement, instead of creating the table and inserting the value..

I am cutting and pasting the querry, instead of number (*) which function i can use to solve this querry. please help me in this. Please return in ASAP....

insert into em_a_word
select @plant,number(*)+@start,
em_a_pmsc.equip_id=@equip_id,null,null,null,@EOD_D ay,null,em_a_pmsc.description,'P1',
@plant,em_a_mast.serialno,'P',
hours=(if em_a_pmsc.schedtype='H'
execute sf_calculate_value @plant,@equip_id,@EOD_Day,@schedtype
else
null
),
null,null,null,em_a_mast.empno,em_a_pmsc.pm_code,e m_a_pmsc.schedtype,em_a_aceql.cost_amt,'N','N','N' ,
null,null,null,null,'N',null,'Y',null,@plant,
miles=(if em_a_pmsc.schedtype='M'
execute sf_Calculate_Value @plant,@equip_id,@EOD_Day,@schedtype
else
null
),
null,value1=exec sf_Calculate_Value@plant,@equip_id,@EOD_Day,@sched type,
value2=exec sf_Calculate_Value@plant,@equip_id,@EOD_Day,@sched type, null,null
from em_a_aceql,em_a_pmsc,em_r_schtyp,em_a_mast where
em_a_mast.equip_id=em_a_pmsc.equip_id
and em_r_schtyp.schedtype=em_a_pmsc.schedtype
and em_a_aceql.equip_id=em_a_pmsc.equip_id
and em_a_aceql.accum_type=em_r_schtyp.accum_type
and em_a_aceql.cost_amt>em_a_pmsc.last_service+em_a_pmsc.interval-em_r_schtyp.lead_time
and em_a_mast.activity='A'
and em_a_mast.plant_id=@plant
and not exists(select equip_id,em_a_word.pm_code from
em_a_word where
em_a_word.equip_id=em_a_pmsc.equip_id
and em_a_word.pm_code=em_a_pmsc.pm_code
and em_a_word.close_flag_yn='N')

View Replies !

---

Performance Numbers
Hi all ... wondering if there are any good performance benchmarks out there that I could use. Here's my scenario. I'm running SQL 7 on an Windows 2000 box, a 4-processor 550MHz with 4GB RAM. I'm wondering what performance increase I would get just switching to SQL Server 2000 on my current platform. Then I wonder how much more performance I would get at throwing the SQL Server 2000 on an 8-processor Xeon 700MHz with 4GB RAM also. I would next expect a double increase in performance as # CPUs and increased speed do not 1-1 translate into performance. Hopefully there are some resources out there I could use to estimate my performance increases (in broad terms).

Thanks all

View Replies !

---

Finding The Numbers
I have a column of data of type varchar. it contains a mix of text id's and numeric id's.
all i want is the highest purely numeric value
The closest i can get is to use a regex expression, but it don;t work and i just know there is a better way of doing it.

SELECT * FROM customers WHERE custref LIKE 'd*d';

I am using MS SQL 7

thanks so much for any help

View Replies !

---