Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







Greatest Function In TSQL


Hi All,
I need to find the highest of two numbers and lowest of the two numbers.
I read on the web that Greatest and least functions in sql can help me do that.
but when i use
greatest in my query it gives me this error

GREATEST is not a recognized built-in function name.
all I have is

declare @first int, @second int
set @first='12'
set @second='14'
select GREATEST(@first,@second)

Can somebody point me in the right direction.
Thanks so much


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Any Function In T-SQL Thats Similar To GREATEST() IN Orcl.
 
Is there any Function in T-SQL thats similar to GREATEST() IN Orcl?
 
Iam trying to find the greatest of two DATETIME datatype Fields in my 'select's.
If there is no in built function, Whats the best approach?

View Replies !   View Related
[OT?] TSQL Function
Hi,
I need some pointers, I am trying to create a SQL function which will check to see if a column in a table will allow null values to be inserted.
I've tried searching the net but to no avail.
Kind Regards
 

View Replies !   View Related
TSQL Function
Hey guys,
I have been trying to get a function to work but have been having a hard time. The tables that are used are a translation and validation tables. But the translation can be from different tables. What i would like the function to do is take in a 'description of a type' and a language code and send back the translated version. The issue is that the table is dynamic and there is my issue with my function.

ALTER FUNCTION [dbo].[Trans]
(
@Trans_description AS NVarchar(200),
@languageid AS nvarchar(10)
)
RETURNS nvarchar(50)
AS
BEGIN
DECLARE
@return AS nvarchar(50),
@TABLENAME AS nvarchar(50),
@sql AS nvarchar(100)
set @TableName = (select top(1) listtable from validationType where validationtypeDesc = @Trans_description)

SET @return =
(SELECT TOP (1) ValidationDesc FROM [translationvalidationlist] WHERE validationpartyid IN
(SELECT validationpartyid FROM @TableName WHERE validationtypePartyid IN
(select validationtypepartyid FROM validationtype WHERE validationtypeDesc = @Trans_description
))AND LANGUAGEID = @languageid)


RETURN @return



END

Is there anyway to do a @return = exec(@sql) of course sql would be the current @return. Right now i get the error Must declare the table variable "@TableName".


Thank you so much for all your help.

View Replies !   View Related
TSQL - Using Sum Function
Hi guys,
I am looking for a solution to the problem described below (SQL SERVER 2000).
Thanks in advance for any help,
Aldo.
 
The script below:



Code Snippet
SELECT
Data.[Invoice No] AS 'InvoiceNo',
[JurnalTrans.REF2] AS 'JurnalTrans.REF2',
[JurnalTransMoves.SUF] AS 'JurnalTransMoves.SUF',
[RowIDDataLevel] AS 'RowIDDataLevel',
 
CASE WHEN RowIDDataLevel = 1 THEN CAST(Paid AS int) ELSE '0' END AS 'Paid' ,
 
CASE WHEN RowIDDataLevel = 1 THEN CAST([JurnalTransMoves.SUF] - [Paid] AS int) ELSE '0' END AS 'OutstandingBalance'
 
FROM CTE AS Data
INNER JOIN (SELECT [Invoice No], SUM([JurnalTransMoves.SUF]) AS Paid FROM CTE GROUP BY [Invoice No]) AS SUMSUF ON SUMSUF.[Invoice No] = DATA.[Invoice No]
 
ORDER BY [Accounts.ACCOUNTKEY], Data.[Invoice No], [RowIDDataLevel]
 
Gives these results:

Invoice No    JurnalTrans.REF2    JurnalTransMoves.SUF     RowIDDataLevel     Paid        OutstandingBalance   

-----------          ----------------                ----------------------                   --------------                 -----------    ------------------

5752             NULL                        1155                                 1                            1810        -655

5752             2032                         400                                   2                            0              0
5752             2033                         155                                   3                            0              0

5752             2034                         100                                   4                            0              0

5754             NULL                        1732.5                              1                            2482        -750

5754             2035                         750                                   2                            0             0

16687           NULL                        2555.8                              1                            2555        0

 
Where:
1810 (Paid) = 1155 + 400 + 155 + 100

2482           = 1732 + 750
and so on...
 
What I need is to get:
instead of 1810 ==> 655 = 400 + 155 + 100
instead of 2482 ==> 750
and so on...
 
In order to do that, I tried inserting "WHERE RowIDDataLevel Not In (1) " in the line:


Code Snippet
INNER JOIN (SELECT [Invoice No], SUM([JurnalTransMoves.SUF]) AS Paid FROM CTE WHERE RowIDDataLevel Not In (1) GROUP BY [Invoice No]) AS SUMSUF ON SUMSUF.[Invoice No] = DATA.[Invoice No]
 
and then getting the following:

InvoiceNo       JurnalTrans.REF2         JurnalTransMoves.SUF     RowIDDataLevel      Paid          OutstandingBalance

-----------            ----------------                    ----------------------                    --------------                  -----------      ------------------

5752               NULL                            1155                                 1                               655          500

5752               2032                             400                                   2                               0              0

5752               2033                             155                                   3                               0              0

5752               2034                             100                                   4                               0              0

5754               NULL                            1732.5                              1                               750          982

5754               2035                             750                                   2                              0               0

 
That it is almost what I need, but not good enough because it makes the line marked in red to disappear because of RowIDDataLevel  =1
16687             NULL                              2555.8                             1                              2555         0
to disappear.
 
 
 
 
 

View Replies !   View Related
DLookup Function In TSQL
 

Hi all,
 

Access has a DLookup function that allow you to look up a value in another table based on a criteria, how is this functionality achieved in TSQL?
 

Thanks
 

Regards
Melt

View Replies !   View Related
TSQL - Using More Than One Argument In LIKE Function
Hi guys,
 
I am using the LIKE function combined with a CASE WHEN to change a long list of words, but the list is too long...
Is there any posibility to insert more than one argument into one like function...?
Any other good ideas?
Below an example of the code I am using..
 
Thanks in advance,
Aldo.
 



Code Snippet
Case
WHEN JurnalTrans.DESCRIPTION LIKE '%myArgument01%' THEN 'Result'
WHEN JurnalTrans.DESCRIPTION LIKE '%myArgument02%' THEN 'Result'
WHEN JurnalTrans.DESCRIPTION LIKE '%myArgument03%' THEN 'Result'
WHEN JurnalTrans.DESCRIPTION LIKE '%myArgument04%' THEN 'Result'
ELSE ''
END AS 'Result'

 
 

View Replies !   View Related
Problem With Round Function In TSQL
Trying below instructions

create table t

(


indexvalue float

)
go

insert t

values (109.1)

insert t

values (109.3)

insert t

values (109.5)

insert t

values (109.9)

go
select *

from t

 
select sum (indexvalue) / count (*)

from t

 
select round (sum(indexvalue)/count (*), 1) -- this line is result of round (109.45) shows 109.4 that is incorrect
from t                                                       --why round function  doesn't work correctly in this select

 
select round (109.45, 1)  -- but this line results 109.5 that is correct

 
 

View Replies !   View Related
Greatest Value
Hi all. I'm looking for assistance to get the greatest value from 3 ormore different columns. I'm assuming that the best way is to put thecolumns into a temp table and use 'max' function to return the greatestvalue but don't know how to code it. Thanks in advance.*** Sent via Developersdex http://www.developersdex.com ***

View Replies !   View Related
Greatest Value
Hi all. I'm looking for assistance to get the greatest value from 3 or more different columns. I'm assuming that the best way is to put the columns into a temp table and use 'max' function to return the greatest value but don't know how to code it. Thanks in advance.

View Replies !   View Related
GREATEST AND LEAST
Hi All,

Is there any equivalent of GREATEST(something) and LEAST(something)
in SQL server 2000

Thanks in advance.

vishu
Bangalore

View Replies !   View Related
SQL With Greatest Date
Here is my SQL statementSELECT a.name, b.meterno, c.meterstatus, d.accountno, d.creation_dateFROM fmdata.location a, cisdata.service_meters b, cisdata.meter_master c,cisdata.Account_Master dWHERE a.location_id = b.location_id AND b.meterno IS NOT NULL AND b.meterno= c.meterno AND a.location_id = d.location_idORDER by meterstatus, nameHere is my resultsNAME METERNO METERSTATUS ACCOUNTNO CREATION_DATE01010004 20512944 Active 101000402 7-Feb-200201010004 20512944 Active 101000401 8-May-199701010005 25917180 Active 101000501 27-May-200301011001 13646231 Active 101100102 17-Mar-199901011002 18389246 Active 101100201 29-Apr-199401011003 84473845 Active 101100301 24-Apr-199701012002 47511850 Active 101200202 26-Jan-199601013001 35653963 Active 101300101 28-Feb-1979If you notice I'm getting two 01010004 under the NAME column. I would likethe SQL statement to select the greatest CREATION_DATE if there areduplicate NAME'S. So in this case, it would select the top 01010004 since ithas the greatest CREATION_DATE.thanks in advancebart

View Replies !   View Related
Greatest And Least Functions
1.I am trying to write following query in SQLserver(this is oracle query),
IS their any equivalent for GREATEST and LEAST functions in SQLServer ???

Update mwebtemp
Set Temp_amount1 = (SELECT SUM(AuthAttr_Amount*(num_Business_Days
(GREATEST(AuthAttr_Start_Date, @dtstartdate),
LEAST(AuthAttr_Finish_Date, @dtenddate))))
.....where...
etc...
.....

Note: this is part of a stored procedure...

2. Also how do you write and call a function in SQLServer (syntax ??), eg.num_Business_Days
is a function returning number of business days between the 2 dates....

thanks a lot

View Replies !   View Related
Very Urgent Greatest And Least Functions
I have to to find the greatest and least values from the set of values which are from the different columns.

Eg. Select greatest(tab1.date1, tab2.date2), least(tab1.date3, tab2.date4)
from tab1, tab2
where....

tab1 and tab2 are the tables and
date1, date3 are the columns from tab1
date2, date4 are the columns from tab2

Is there any easy way to do this in SQL server ?

MAX and MIN will not work here as these are not the max and min from the same column they are from the different tables and columns.

Thanks

View Replies !   View Related
Selecting One Greatest Number From Two Columns?
I have table1 and table2.In table1 I have a column of numbers, numbers1.In table2 I have a column of numbers, numbers2.I'd like to select the highest number represented in either column.Example:table1:column1--------------345565643656555676table2:column2--------------3456564556456456456456The number I would want would be 56456 since it's the largest numberout of all combined.How can I get that number with one select statement?--[ Sugapablo ][ http://www.sugapablo.com <--music ][ http://www.sugapablo.net <--personal ][ Join Bytes! <--jabber IM ]

View Replies !   View Related
Summing Only The Greatest 5 Values In Each Group
Hello,
Here's one way to sum only the top 5 (greatest 5) values per group.
I assume there is a table called IdValues that contains two columns: id int, value int.

declare @lastId int
declare @value int
declare @count int
declare @idList varchar(5000)
declare @valuelist varchar(5000)
set @count=0
set @lastId = -1
set @value = 0
set @idList=''
set @valuelist=''

select
@count=(case when @lastId<>id then 1 else @count+1 end),
@value=(case when @lastId<>id then value when @count<=5 then @value+value else @value end),
@idList=(case when @lastId<>id then cast(id as varchar)+','+@idList else @idList end),
@valuelist=(case when @lastId<>id then cast(@value as varchar)+','+@valuelist else cast(@value as varchar)+','+right(@valuelist,len(@valuelist)-charindex(',',@valuelist)) end),
@lastId=id
from IdValues
order by id desc, value desc

select @idList,@valuelist

It's a funny approach. I'd be interested to see a better method. In MySQL it is possible to do this much better and have it produce an actual resultset (since MySQL allows you to assign variables and product a resultset in the same query).

I also noticed something interesting. If you do any operation on the order-by columns, the query doesn't work. For example, if I do:
order by id+0 desc, value desc
something funny happens and you only get one id and one value in the list variables. Maybe someone else who actually some idea of how SQL Server works can explain this.

Thanks,
Thomas

View Replies !   View Related
Find The Greatest Of Three Columns Per Each Row And Display
Hi,

i have a problem where in i have to display the greatest marks scored by each student.

How can i do this?? Is there any built in TSQL function.

Rgds..,

Aazad

View Replies !   View Related
Implementing Oracle's GREATEST And LEAST Functions In T-SQL
Has anybody been successful in doing this?  I've seen a few articles on the web, but none for free....

View Replies !   View Related
TSQL Function To Return Numeric Value Of Non Numeric Field
I need to replace Access Val() functions with similiar function in sql.

i.e.   Return 123 from the statement:    SELECT functionname(123mls)

        Return 4.56 from the satement:    SELECT functionname(4.56tonnes)

 Any one with ideas please

 Thanks

 George


 

View Replies !   View Related
What Happens When Autoincrement (auto Increment) Reaches Its Highest (greatest, Last) Value?
What happens when autoincrement reaches its last value? Does my application crash? Will autoincrement wrap?
 
Let us assume I figure that my database table will be much smaller than 2^16 records. Since it will be nowhere near the limit, I select smallint (2 bytes wide) as my ID field. I set the seed value to -32,767. I set the increment value to 1. Over the course of actual useage, many records are added and deleted many times. Although I correctly anticipated that the number of records in my database is far less than 65,535, autoincrement has reached 65,535. What will happen next? Ideally I would like autoincrement to wrap and take the next unused value. But does it do that? Or does it freeze at the highest value? What happens?

View Replies !   View Related
Equivalent Tsql For Sql Server 2000 Is Needed [from Sql Server 2005 Only Tsql]
Can anyone please give me the equivalent tsql for sql server 2000 for the following two queries which works fine in sql server 2005

1
-- Full Table Structure

select t.object_id, t.name as 'tablename', c.name as 'columnname', y.name as 'typename', case y.namewhen 'varchar' then convert(varchar, c.max_length)when 'decimal' then convert(varchar, c.precision) + ', ' + convert(varchar, c.scale)else ''end attrib,y.*from sys.tables t, sys.columns c, sys.types ywhere t.object_id = c.object_idand t.name not in ('sysdiagrams')and c.system_type_id = y.system_type_idand c.system_type_id = y.user_type_idorder by t.name, c.column_id


2
-- PK and Index
select t.name as 'tablename', i.name as 'indexname', c.name as 'columnname' , i.is_unique, i.is_primary_key, ic.is_descending_keyfrom sys.indexes i, sys.tables t, sys.index_columns ic, sys.columns cwhere t.object_id = i.object_idand t.object_id = ic.object_idand t.object_id = c.object_idand i.index_id = ic.index_idand c.column_id = ic.column_idand t.name not in ('sysdiagrams')order by t.name, i.index_id, ic.index_column_id

This sql is extracting some sort of the information about the structure of the sql server database[2005]
I need a sql whihc will return the same result  for sql server 2000

View Replies !   View Related
Retrieving Result Set From Dynamically Called Stored Procedure Or Function In A Function
Is there any way I can retrieve the result set of a Stored Procedurein a function.ALTER FUNCTION dbo.fn_GroupDeviceLink(@groupID numeric)RETURNS @groupDeviceLink TABLE (GroupID numeric, DeviceID numeric)ASBEGINDeclare @command nvarchar(255)SELECT @command = Condition// @command is an SQL string or stored procedue nameFROM DeviceGroupWHERE GroupID = @groupIDINSERT @groupDeviceLinkEXEC @commandRETURNENDIs there any way i can do anything like this. @command is a variableholding the name of a stored produre. I need to run that storedprocure and return the values in such a way that they can be used in aSELECT StatementMy goal is SELECT * FROM Device INNER JOINdbo.fn_GroupDeviceLink(@groupID) ON ....this fn_GroupDeviceLink should run the proper stored procedure andreturn the values. What i also want to do is play with that result setof the specific stored procedure before i return it. Is this possible?If not, what is the work arround?ThanksMark

View Replies !   View Related
Help Convert MS Access Function To MS SQL User Defined Function
I have this function in access I need to be able to use in ms sql.  Having problems trying to get it to work.  The function gets rid of the leading zeros if the field being past dosn't have any non number characters.For example:TrimZero("000000001023") > "1023"TrimZero("E1025") > "E1025"TrimZero("000000021021") > "21021"TrimZero("R5545") > "R5545"Here is the function that works in access:Public Function TrimZero(strField As Variant) As String   Dim strReturn As String   If IsNull(strField) = True Then      strReturn = ""   Else      strReturn = strField      Do While Left(strReturn, 1) = "0"         strReturn = Mid(strReturn, 2)      Loop   End If  TrimZero = strReturnEnd Function

View Replies !   View Related
In-Line Table-Valued Function: How To Get The Result Out From The Function?
Hi all,
 
I executed the following sql script successfuuly:
 
shcInLineTableFN.sql:

USE pubs

GO

CREATE FUNCTION dbo.AuthorsForState(@cState char(2))

RETURNS TABLE

AS

RETURN (SELECT * FROM Authors WHERE state = @cState)

GO
 
And the "dbo.AuthorsForState" is in the Table-valued Functions, Programmabilty, pubs Database.
 
I tried to get the result out of the "dbo.AuthorsForState" by executing the following sql script:
 
shcInlineTableFNresult.sql:

USE pubs

GO

SELECT * FROM shcInLineTableFN

GO

 
I got the following error message:

Msg 208, Level 16, State 1, Line 1

Invalid object name 'shcInLineTableFN'.

 
Please help and advise me how to fix the syntax

"SELECT * FROM shcInLineTableFN"
and get the right table shown in the output.
 
Thanks in advance,
Scott Chang

View Replies !   View Related
A Function Smilar To DECODE Function In Oracle
I need to know how can i incoporate the functionality of DECODE function like the one in ORACLE in mSSQL..
please if anyone can help me out...


ali

View Replies !   View Related
Using RAND Function In User Defined Function?
Got some errors on this one...

Is Rand function cannot be used in the User Defined function?
Thanks.

View Replies !   View Related
I Want A Function Like IfNull Function To Use In Expression Builder
Hi,

I wonder if there a function that i can use in the expression builder that return a value (e.g o)  if the input value is null ( Like ifnull(colum1,0)  )

 

i hope to have the answer because i need it so much.

 

Maylo

View Replies !   View Related
ROW_NUMBER() Function Is Not Recognized In Store Procedure.(how To Add ROW_NUMBER() Function Into SQL SERVER 2005 DataBase Library )
Can anybody know ,how can we add  builtin functions(ROW_NUMBER()) of Sql Server 2005  into database library.
I get this error when i used into storeprocedure :
ROW_NUMBER() function is not recognized in store procedure.
i used MS SQL SERVER 2005 , so i think "ROW_FUNCTION()" is not in MS SQL SERVER 2005 database library.
I need to add that function into MS SQL SERVER 2005 database library.
Can anbody know how we can add that function into MS SQL SERVER 2005 database library?
 

View Replies !   View Related
Function To Call Function By Name Given As Parameter
I want to write function to call another function which name isparameter to first function. Other parameters should be passed tocalled function.If I call it function('f1',10) it should call f1(10). If I call itfunction('f2',5) it should call f2(5).So far i tried something likeCREATE FUNCTION [dbo].[func] (@f varchar(50),@m money)RETURNS varchar(50) ASBEGINreturn(select 'dbo.'+@f+'('+convert(varchar(50),@m)+')')ENDWhen I call it select dbo.formuła('f_test',1000) it returns'select f_test(1000)', but not value of f_test(1000).What's wrong?Mariusz

View Replies !   View Related
Error While Creating Inline Function - CREATE FUNCTION Failed Because A Column Name Is Not Specified For Column 1.
 

Hi,

 

I am trying to create a inline function which is listed below.

 

USE [Northwind]

SET ANSI_NULLS ON

GO

CREATE FUNCTION newIdentity()

RETURNS TABLE

AS

RETURN

(SELECT ident_current('orders'))

GO

 

while executing this function in sql server 2005 my get this error

CREATE FUNCTION failed because a column name is not specified for column 1.

 

Pleae help me to fix this error

 

thanks

Purnima

 

View Replies !   View Related
TSQL Or SQL CLR?
Hello Friends,    I am not sure if this is the right place to post this question but if not please suggest me when it can be posted.
I have been thinking of writing Stored procs in SQL CLR/ changing all of my Stored Procs to SQL CLR.
Is there any thing that I need to keep in mind about the size of the sotred proc (like calculation intensive) before I do that? I mean can even change a TSQL stored proc which is  relatively small Stored Proc, that simply said Select * from Customers to SQL CLR? or the SQL CLR does only useful/makes difference with calculation intensive stored procs and big stored procs?
When I talked to our Architects they said every small sized stored proc can be written using SQL CLR and more over forget about the classic TSQL stored procs and get used to write SQL CLR when ever writing any database related stuff.
And also there are so many articles that discussed about the advantages of SQL CLR over the TSQL but I would appreciate if some one could put few bulletted points why do you think SQL CLR is more powerful.
Please advise.Thanks in advance,-L

View Replies !   View Related
TSQL Please Help
Hi
I am creating some dynamic sql by passing variouse parametrs to my Stored Procedure.  One of my Parameters is string has lots of values seperated by a comma to help build an 'IN' statement.
SET @SQL = 'SELECT * FROM areas'SET @p1 = '10,20'If @p1 IS NOT NULLBEGINSET @sSQL = @sSQL + ' WHERE (Areas IN (''' + Replace(@p1,',',''',''') + '''))'END
The above query runs perfecly well in Query Analyser, however when I put it into my ASP.NET application I get an error of "Error converting data type varchar to numeric."
So I think I need to do some sort of casting or Converting but im not sure how to do it.  Or do I need to use a INSTRING?
I did manage to work out a method by using the follwoing
SELECT * FROM Areas WHERE PATINDEX('%,' + CAST(ArType AS VARCHAR) + ',%',',' + @p1 + ',') > 0
But I cant seem to convert the above line into coherent dynamic statement.  My feeble attempt is below but I keep getting errors
SET @sql = @sql + ' WHERE PATINDEX(''%,'' + CAST(ArType AS VARCHAR) + '',%'','','' + @p1 + '','') > 0'
IM strugging to understand all the '''. My TSQL is pretty basic, any help would be much appreciated
Many thanks in advance
 

View Replies !   View Related
TSQL Help
A stored procedure was running slowly so I took the code, removed thesubselect and included a join, then took the max and included as partof a correlated subquery.The result is below, however, this is no improvement over the original.An advice would be greatly appreciated.SELECT FSALT.FUNDING_LINE_TYPE_ID,A.PAYMENT_PERIOD_ID,A.CASH AS CASH,A.VOLUME AS VOLUMEFROM ACTUALS AINNER JOIN (SELECT MAX(COLLECTION_PAYMENT_PERIOD_*ID) AS CPP FROMACTUALS ACT WHERE COLLECTION_PAYMENT_PERIOD_ID<=*456) AS O ON O.CPP =A.COLLECTION_PAYMENT_PERIOD_IDINNER JOIN FS_ACTUAL_LINE_TYPES FSALT ON FSALT.FS_ACTUAL_LINE_TYPE_ID =A.FS_ACTUAL_LINE_TYPE_IDINNER JOIN PAYMENT_PERIODS PP ON PP.PAYMENT_PERIOD_ID =A.PAYMENT_PERIOD_IDWHEREA.ORG_ID=24771AND A.LSC_ORG_ID=5816AND PP.FUNDING_STREAM_ID=5AND PP.FUNDING_PERIOD_ID=6GROUP BYFSALT.FUNDING_LINE_TYPE_ID,A.PAYMENT_PERIOD_ID, A.CASH, A.VOLUME

View Replies !   View Related
TSQL Help
I need help with a query that I want to incorporate into a scheduled DTS within SQL Server 2000. I have a process that brings in records from an Excel file, compares that day's records with any related information already stored in the database, then saves a table of joined records. This leaves me with any matches to the data. I need to filter out all but the most current record, which would be identified by a column named [DATE]. Ex:


Table tblMatchedRecords

M BLah Blah2 [DATE]
1 this that 20040101
2 this them 20040102
3 this that 20040630


In this example, I would like to have the record with [DATE] = 20040630 kept, and the others deleted. All columns do not always have to match exactly. I want to be able to specify which fields that need to match and pull only the latest date.

Make sense? Can anyone offer any suggestions?

View Replies !   View Related
TSQL Help
I need help with a query that I want to incorporate into a scheduled DTS within SQL Server 2000. I have a process that brings in records from an Excel file, compares that day's records with any related information already stored in the database, then saves a table of joined records. This leaves me with any matches to the data. I need to filter out all but the most current record, which would be identified by a column named [DATE]. Ex:

Table tblMatchedRecords

M BLah Blah2 [DATE]
1 this that 20040101
2 this them 20040102
3 this that 20040630

In this example, I would like to have the record with [DATE] = 20040630 kept, and the others deleted. All columns do not always have to match exactly. I want to be able to specify which fields that need to match and pull only the latest date.

Make sense? Can anyone offer any suggestions?

View Replies !   View Related
Tsql Help
I have a few variables declared

@amount=100000 (any big int)
@pamount=100000
@a1,@a2 - thorugh @25 -- each a bigint
@p1,@p2,@p3
@id - int

if @id is an odd number I need to randomly change @amount-@a1 (randomly to @a1 - @a25 so it ends up being any of 25 values)
and then @pamount randomly= to either @p1,@p2 or @p3


Can someone help me do the sql for this?
it will be within my stored procedure

View Replies !   View Related
TSQL Help...please
I am trying to determine concurrency of a program running based on start and stop times. Right now I have 8863 records to compare, but here is a small sample.

Start Time Finish Time
1)2003-12-01 00:31:12.0002003-12-01 01:14:23.000
2)2003-12-01 06:55:43.0002003-12-01 15:50:47.000
3)2003-12-01 07:19:12.0002003-12-01 16:30:06.000
4)2003-12-01 07:22:10.0002003-12-01 15:56:44.000
5)2003-12-01 07:27:46.0002003-12-01 18:36:05.000
6)2003-12-01 07:34:53.0002003-12-01 09:57:15.000

I need to compare the times for overlap to determine concurrency. For instance:
Comparing record 1 to record 2:
If record1.starttime <= record2.finishtime AND record1.finishtime >= record2.starttime, then count = 1, esle 0
If record1.starttime <= record3.finishtime AND record1.finishtime >= record3.starttime, then count = 1, else 0
AND SO ON AND SO ON...
when you encounter a 0, SUM the count as Concurrent.

Then, I need it to move to record to and do the same thing...

Comparing record 2to record 1:
If record2.starttime <= record1.finishtime AND record2.finishtime >= record1.starttime, then count = 1, esle 0
Comparing record2 to record 3:
If record2.starttime <= record3.finishtime AND record2.finishtime >= record3.starttime, then count = 1, else 0
AND SO ON AND SO ON...
when you encounter a 0, SUM the count as Concurrent.

Finally, when the loop (or cursor??) is finished, select MAX(count) as maximum concurrency.

I tend to stay more on the system side and less on programming so I am really out of my realm. Any help polishing off the logic, and some pointers on how to write this in T-SQL would be much appreciated.

Thanks in advance.

Ryan Hunt

View Replies !   View Related
TSQL Help
Hi,

I am trying to break up a column "name" into 2 columns "first name" and "last name". The name colum is currently "lastname, firstname". Is there an easy way to do this in SQL 6.5?

I successfully extracted the first name using:

firstname = rtrim(substring(name, (charindex(',', name)), 25))

But I am having trouble doing the lastname. Please help.

Thanks so much!
Laura

View Replies !   View Related
TSQL ?
I am using the following statement to do an extract for a data security matrix. I can get the select to work,
but not the insert . Any help appreciated.

set nocount on
declare @dbname varchar(30)
declare @sqlperm varchar(50)
declare @objname varchar (50)
declare @secgroup varchar (50)
declare @printline varchar(80)
declare @sql varchar(255)
declare @sql1 varchar(255)

if @dbname is NULL
begin
declare dbcursor cursor
for select name from master..sysdatabases
where name not in ('master','msdb','pubs','model','northwinds')

open dbcursor
fetch next from dbcursor into @dbname
while (@@FETCH_STATUS <> -1)

begin
select @dbname, case spt.action
when 26 then "REFERENCES " when 193 then "SELECT " when 195 then "INSERT " when 196 then "DELETE " when 197 then "UPDATE " when 224 then "EXECUTE " end
, so.name
, (select su.name from ois..sysusers su where su.uid = spt.uid) from ois..sysobjects so join ois..sysprotects spt on so.id = spt.id where so.type in ("P","U","V")
select @sqlperm = case spt.action
Select @sqlobject = so.name
select @sqlgroup - su.name
insert into bsrdict..tbl_sql_sec
values
(@dbname @sqlperm, @sqlobject,@sqlgroup)

fetch next from dbcursor into @dbname
end
close dbcursor
deallocate dbcursor
end

View Replies !   View Related
TSQL
I'm new in T-SQL...

Is there way I can do loops in SQL?
I’m trying to do something like:
"for each a.description in account_codes a where clientId in(1,0)
select b.balance from account_balance b".....

Right now I'm trying to execute this:
"select a.description,b.balance
from
account_balance b, account_codes a
where
a.clientid in (1,0)"

the result that I get is:
description balance
-------------------- ---------------------
A income 123.1200
A income 235.1200
B income 123.1200
B income 235.1200

but what I want to see is:
description balance
-------------------- ---------------------
A income 123.1200
B income 235.1200


Any help will be greatly appreciated...
Thank you,
Nastya.

View Replies !   View Related
Tsql Help
Hi,

I want to write a query which selects ind and
clcode where
ind(individual) has taken all 3 classes(clcode) 15, 16 and 17.
for example: it should only return data for ind 1 from the following data as only ind 1 has taken all three classes.
How can I write it?

table: ind_history

ind clcode
----------- -----------
1 16
1 17
2 17
1 15
2 16
3 11
4 15

View Replies !   View Related
Tsql Help!
simple update, I want to update max_seq with max(0rdr_seq) from another table.
how do you?

update h
set max_seq = d.max(ordr_seq)
from h_drug_stage_dup h
join drug_ordr_stage d
on h.patkey = d.patkey and
h.ordr_dtm= d.ordr_dtm and
h.h_drug = d.h_Drug

View Replies !   View Related
TSQL Help !
How do I say example, adding three business days with TSQL ?

Help is greatly appreciated.

Thanks,
Jeff

View Replies !   View Related
TSQL Help !
Please see below ( in my sub-query I need to say settle_date = post_date +
3 business days )
How would this be done ? Pleas help !!!

declare @PD datetime, @MY_SD datetime
--SELECT @PD = SELECT POST_DATE FROM TRANSACTION_HISTORY
--select @MY_SD = @PD + 3 --T+3
--select @MY_SD = @MY_SD + case datepart(dw, @MY_SD) when 7 then 2 when 1 then 1 else 0 end*/
SELECT
WIRE_ORDER_NUMBER FROM TRANSACTION_HISTORY
WHERE POST_DATE BETWEEN '02/01/2001' AND '02/28/2001' AND
WIRE_ORDER_NUMBER IN
(
SELECT ORDER_NUMBER
FROM TRANSACTION_ARCHIVE WHERE TRANSACTION_ARCHIVE.ORDER_NUMBER = TRANSACTION_HISTORY.WIRE_ORDER_NUMBER
SETTLE_DATE = DATEADD(day, 3, POST_DATE ) case datepart(dw, POST_DATE) when 7 then 2 when 1 then 1 else 0 end))

View Replies !   View Related
Tsql
Sql Server 7.0
==============
Hi all!
To find out the duplicate entries in a particular column,
I used the following tsql

select pno ,count(pno) from table1 group by pno
having count(pno)>1

But now I have another case where i have to test duplicity as a combination of 3 columns.
ie, for Eg :I have 3 columns with the following values.

colA colB colC
1 2 3===============row 1
1 3 5===============row 2
1 2 3===============row 3
1 4 5===============row 4
8 9 0===============row 5

I want to pick up all the duplicate rows(combo of colA,colB,colC)
duplicate rows here would be row 1 and row 3.

Can somebody give me a clue as to how to achieve this via TSQL.
Any help greatly appreciated.

TIA
Kinnu

View Replies !   View Related
TSQL
HI,
I am creating a stored procedure where i need to update a table.
I have a field which has year as 2000-2001 i need to remove -2001
and just store it as 2000.I created a Procedure but i am getting an
error.

create PROCEDURE prdUpdate_Year
@year varchar(40)
As
@year = Mid(Newfiels,1,4)
UPDATE AddressBook SET AddressBook.Year = @year;
GO

Thanks

View Replies !   View Related
SP Or TSQL
Hi
Is there any sp or command to deactivate and activate all constraints in a database...


Thanks in Advance

RKNAIR

View Replies !   View Related
Tsql Help
I have 2 sql server that are registered and linked.

If I have a table in server 1 that I want to make a backup of to server 2, how would I do that in t-sqlv(if the table did not already exisit on the second server)

Thanks

"Impossible is Nothing"

View Replies !   View Related
How Can This Be Done Using TSQL?
I have a table that has a datetime field that needs a calculated time difference created when querying the table.  I'm new to TSQL and I've been banging my head on this one.  There is a time window that must be dealt with as well, so that any time that falls outside if the time window gets assigned a zero value (not calculated).  The last record inside the window and the first record inside the window get calculated based on a given start and end time.  The time outside the window is 7PM to 7AM.  Here's an example:



 
in/outside window    Time Value                   Calculation              Time Diff (in minutes)
inside window          time1 = '13:15:00'           null                     the first record is always null
inside window          time2 = '14:15:00'        time2-time1                         60 
inside window          time3 = '18:50:00'        time3-time2                         275
outside window        time4 = '19:10:00'        18:59:59 - time3                  10
outside window        time5 = '21:00:00'           0                                      0
outside window        time6 = '06:30:00'           0                                      0
outsidw window       time7 = '06:45:00'            0                                     0
inside window          time8 = '07:45:00'        time8 - 07:00:00                   15
 

How can this be done using TSQL?  Let me know if more info is needed.  Thanks!
 
 

View Replies !   View Related
TSQL - Using WHERE
Hi guys,
The query below is running ok.


Code Block
USE test2006mdt
 
if object_id('AuxTable20071224132300') is not null exec('DROP TABLE AuxTable20071224132300')
 
SELECT DISTINCT
Stock.ACCOUNTKEY AS 'Stock.ACCOUNTKEY',
Stock.DOCNUMBER AS 'Stock.DOCNUMBER',
Stock.DOCUMENTID AS 'Stock.DOCUMENTID',
CASE WHEN Cast(Stock.DOCUMENTID as int) In ('1','35') THEN 'IN# ' + Cast(Stock.DOCNUMBER as varchar) WHEN Cast(Stock.DOCUMENTID as int) In ('3','120') THEN 'CM# ' + Cast(Stock.DOCNUMBER as varchar) ELSE '' END AS 'Invoice #',
Stock.VALUEDATE AS 'Stock.VALUEDATE',
Stock.TFTAL AS 'Stock.TFTAL',
Stock.CURRENCY AS 'Stock.CURRENCY',
CASE WHEN ReceiptJurnalMatch.SUF Is NULL THEN '0' ELSE -1 * ReceiptJurnalMatch.SUF END AS 'ReceiptJurnalMatch.SUF',
Stock.ID AS 'Stock.ID',
CASE WHEN ReceiptJurnalMatch.FULLMATCH Is NULL THEN '0' ELSE ReceiptJurnalMatch.FULLMATCH END AS 'ReceiptJurnalMatch.FULLMATCH'
 
INTO AuxTable20071224132300
FROM
RECEIPTJURNALMATCH
RIGHT OUTER JOIN JURNALTRANSMOVES ON RECEIPTJURNALMATCH.JURNALTRANSID = JURNALTRANSMOVES.ID
RIGHT OUTER JOIN STOCK
INNER JOIN ACCOUNTS ON STOCK.ACCOUNTKEY = ACCOUNTS.ACCOUNTKEY
INNER JOIN JURNALTRANS ON STOCK.ID = JURNALTRANS.STOCKID ON JURNALTRANSMOVES.TRANSID = JURNALTRANS.TRANSID
LEFT OUTER JOIN STOCKMOVES ON STOCK.ID = STOCKMOVES.STOCKID
 
WHERE
Stock.ACCOUNTKEY Between 'Account01' AND 'Account01'
AND Stock.Status Not In ('0','2')
AND Stock.DOCUMENTID In ('1','35','3','120')
ORDER BY Stock.ACCOUNTKEY
 
SELECT DISTINCT
[Stock.ACCOUNTKEY] AS 'Stock.ACCOUNTKEY',
[Stock.DOCNUMBER] AS 'Stock.DOCNUMBER',
[Stock.DOCUMENTID] AS 'Stock.DOCUMENTID',
[Invoice #] AS 'Invoice #',
[Stock.VALUEDATE] AS 'Stock.VALUEDATE',
[Stock.TFTAL] AS 'Stock.TFTAL',
[Stock.CURRENCY] AS 'Stock.CURRENCY',
Sum([ReceiptJurnalMatch.SUF]) AS 'Sum([ReceiptJurnalMatch.SUF])',
CASE WHEN CAST(DATEDIFF(dd,[Stock.VALUEDATE],GETDATE()) AS int) <= 30 THEN [Stock.TFTAL] - Sum([ReceiptJurnalMatch.SUF]) ELSE '' END AS '1-30',
CASE WHEN CAST(DATEDIFF(dd,[Stock.VALUEDATE],GETDATE()) AS int) BETWEEN 31 AND 60 THEN [Stock.TFTAL] - Sum([ReceiptJurnalMatch.SUF]) ELSE '' END AS '31-60',
CASE WHEN CAST(DATEDIFF(dd,[Stock.VALUEDATE],GETDATE()) AS int) BETWEEN 61 AND 90 THEN [Stock.TFTAL] - Sum([ReceiptJurnalMatch.SUF]) ELSE '' END AS '61-90',
CASE WHEN CAST(DATEDIFF(dd,[Stock.VALUEDATE],GETDATE()) AS int) > 90 THEN [Stock.TFTAL] - Sum([ReceiptJurnalMatch.SUF]) ELSE '' END AS '>> 90',
[Stock.TFTAL] - Sum([ReceiptJurnalMatch.SUF]) AS 'Balance'
 
FROM AuxTable20071224132300
 
WHERE Balance <> 0  ==> Getting ERROR!!!
 
GROUP BY [Stock.ACCOUNTKEY], [Stock.DOCNUMBER], [Stock.DOCUMENTID], [Invoice #], [Stock.VALUEDATE], [Stock.CURRENCY], [Stock.TFTAL]
The results are:

ACCOUNTKEY DOCNUMBER DOCUMENTID   Invoice #       VALUEDATE                  TFTAL  CURRENCY SUF  1-30   31-60   61-90   >> 90   Balance

Account01        16917                 35                    IN# 16917   2007-12-25 00:00:00.000 15000 Euro            15000  0        0          0          0           0

Account01        16918                 35                    IN# 16918   2007-12-25 00:00:00.000 300     Euro             300     0        0          0          0           0

Account01        16919                 35                    IN# 16919   2007-12-25 00:00:00.000 110     Euro             110     0        0          0          0           0

Account01        16920                 35                    IN# 16920   2007-11-10 00:00:00.000 5         Euro             5         0        0          0          0           0
 
I inted to add a WHERE clause in order to Show Only Invoices with Balance <> 0, but getting Error.
Thanks in advance for any help.
Note: I am working under SQL SERVER 2000
Aldo.

View Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved