Finding Highest Current Number Only For Increasing Range

Jan 26, 2012

RecordNo Speed
-------- -----
1 0
2 0
3 0
4 0
5 23
6 66
7 48
8 0
9 31
10 0
11 34
12 23

The above data shows the speed of vehicle over a time period, given the above data I need to achieve the result below:

RecordNo Speed LastAcceleration
-------- ----- ----------------
1 0 0
2 0 0
3 0 0
4 0 0
5 23 23
6 66 66
7 48 66
8 0 66
9 31 31
10 0 31
11 34 34
12 23 34

The code below is almost there but falls over on Recordno 8:

select
curr.recordno,curr.speed
,CASE WHEN curr.speed >= ISNULL(prev.speed,0) THEN curr.speed
ELSE (
SELECT MAX(speed) FROM speedtest
WHERE recordno between (CASE WHEN curr.speed >= prev.speed then curr.recordindex else prev.recordno end ) and curr.recordno

[code]...

View 4 Replies


ADVERTISEMENT

Finding The Highest Or Largest Number In A Column Problem

Dec 19, 2006

Example data
CA1000
CA10001
CA10002
CA10003
CA11597
CA11603
CA1001
CA998
CA999
As you can see, CA11603 is the largest number in this list. 
 When I try the follow sql code,
SELECT
MAX([MyCode])
FROM
[MyTable]
WHERE (SUBSTRING([MyCode], 1, 2) = 'CA')
The largest number comes back as CA997
When I try
MAX([MyCode])
FROM
[MyTable]
WHERE [MyCode] LIKE 'CA%'
 The largest number comes back as CA997
 
SELECT
TOP 1 (SchoolMasterCode)
FROM
SchoolMaster
WHERE (SUBSTRING(SchoolMasterCode, 1, 2) = 'CA') ORDER BY Schoolmastercode
 The largest comes back as CA10001
When I try....
SELECT
TOP 1 (SchoolMasterCode)
FROM
SchoolMaster
WHERE (SUBSTRING(SchoolMasterCode, 1, 2) = 'CA')
 The largest comes back as CA1278
What am I doing wrong?

View 3 Replies View Related

Find Highest Number

Sep 26, 2004

hello all,

i'm not new to SQL but i cant seem to get this right:
hope some one will:

how to find highest unique number of a certain column(val)for a specific name(name is in INPUT parameter)

i.e :

id | name | val
---------------
1 | name1 | 2.7
2 | name1 | 3.5
3 | name1 | 3.5
4 | name1 | 3.5
5 | name1 | 1.3
6 | name2 | 3.1
7 | name2 | 3.1
8 | name2 | 2.9

requested result:
if input param(name)=name1

result:
1 | name1 | 2.7

if input param(name)=name2

result:
8 | name2 | 2.9

hope some one can write the sql cmd for this
i'll be grateful !
thank you!

View 3 Replies View Related

Select The Row With The Highest Number

Aug 26, 2004

Hi all,

Hopefully you can assist this pleb (me) with some (hopefully) basic scripting.

I have a table which has bucket loads of rows in it (funnily enough)
Field1 & Field2
Field1 is repeated numerous times (but also has differing values) through the table
Field2 is a numeric value which is assigned to the Field1

What I need to do is pull back all the unique Field1's but only those with the highest value in Field2

Example:
Field1 Field2
blah 100
blah 400
blah1 12
blah1 9
blah1 2
blah 350

I need the return to be basically:

Field1 Field2
blah 400
blah1 12

I am sure that this has to be straight forward - however my brain just isn't connected the right way at the moment....

Thanks in advance for any assistance on this.

Troy

View 2 Replies View Related

Increasing The Number Of Characters Displayed In Query Analyzer

Feb 26, 2001

Hi,

I am using SQL Server 7.0.

I have a table with one column defined as Varchar(8000).

The table is populated with rows..

However when I do a Select * from the the Table in query analyzer I only see about 200 Characters even though each column has 8000 characters.

How can I increase the number of characters that are diplayed in the Query Analyzer?

Thankx

KP

View 1 Replies View Related

Finding Current Day Is A Nth Businees Day

Jun 10, 2008

Hi All,

I am trying to find the nth business day in a month and in a quarter.
But I am not able to find any solution for this.

Could you please any one provide the scripts for me.

Thanks in advance...........

View 4 Replies View Related

Finding Current User

Jan 21, 2008

Hi,

I have created a DataSet that queries a table containing users names.




Code Block
SELECT * FROM myTbl
WHERE UserName = @Username



How do I automatically set @Username to the current user?

Thanks.

View 3 Replies View Related

Help Finding Current Size Of A Field

Jan 19, 2006

How do I find the current size of each field in a table's column?

I have a table with a field for notes/memos. I need to see which ones are about to reach the size limit and what the current size is.

Does this make sense?

Thank you,
Karen

View 2 Replies View Related

Finding Last Saturday Date Then Range Last 28 Days

Jul 18, 2014

In my query I have a date field and using that date field I want to pass the date to a function, or whatever is most appropriate, then compare that date with a range of the last 4 weeks, which will be numbered 1-4, 1 being earliest and 4 being latest, and then return the week number.

I've determined I need some sort of look up but will need to be dynamic - thinking maybe a temp table, first using the date the report is run on and counting 28 days back from the most recent last saturday then setting number to 1 for first 7 days then 2 for next 7 days etc.

View 8 Replies View Related

Finding If Any Part Of Date Range Falls In Period?

Jan 3, 2014

I'm trying to find if any part of a date range in my table between orig_start_rent and stop_rent falls within a period I specify in two variables: startPeriod and endPeriod

For example if I specify 2013-11-01 as startPeriod and 2013-11-30 as endPeriod, then if any part of the date range between orig_start_rent and stop_rent(stop-rent can be null if hire is open) falls within that period, I want that to be picked up and assigned the value of 1 in my case statement for OnHire. My code is not picking everything up however - using the example above, a record with orig_start_rent of 2013-05-23 and stop_rent of 2013-11-18 is being assigned 0 when it should be 1. My code here:

declare @startPeriod as smalldatetime
declare @endPeriod as smalldatetime
set @startPeriod = '2013-11-01'
set @endPeriod = '2013-11-30';
select dticket, orig_start_rent, stop_rent, case when orig_start_rent >= @startPeriod and orig_start_rent <= @endPeriod then 1 when orig_start_rent < @endPeriod and stop_rent is null then 1 else 0 end [OnHire] from deltickitem

View 5 Replies View Related

Finding Valid Identity Range On Merge Subscriber

Nov 22, 2006

I am using merge replication with automatic identity range management

I want to be able to give a disconnected subscriber a warning message if a certain percentage of the allocated subscriber identity range has been used
- so that they can make sure they reconnect soon to obtain a new identity range

I can get the current identity value OK, but I can't find a good way of finding the current permitted primary and secondary ranges

The information is available in the check constraint, but I don't really want to have to parse this!

CONSTRAINT [repl_identity_range_0DF907AC_40CF_4849_9BD0_2173C90A3805] CHECK NOT FOR REPLICATION (([ContactID]>(10000040000.)
AND [ContactID]<=(10000050000.) OR [ContactID]>(10000050000.) AND [ContactID]<=(10000060000.)))

I can't find any system view which will provide this information

Is there a recommended way of doing this?

Thanks

aero1

View 3 Replies View Related

T-SQL (SS2K8) :: Finding The Item Filled In Prior To Current One

May 13, 2015

I have data similar to the below

CREATE TABLE #TEMP
(
TYPE VARCHAR(10),
SEQ INT,
SUB_TYPE VARCHAR(10))

[Code] ....

Now for each type the seq is very important. Effectively by order of seq the subtype stays the same until another subtype changes it. So for TYPE1 100,110 and 150 are A. 170, 200,220 are B. 230 and 250 are C and so on.

However as you can see the data isnt actually stored in the row. I need a select statement that shows this data.

I have done this:

SELECT t1.*,t3.SUB_TYPE FROM #TEMP t1
CROSS APPLY
(SELECT MAX(SEQ) SEQ FROM #TEMP AS t2 WHERE t1.SEQ >= t2.seq AND t2.SUB_TYPE <>'' AND t1.TYPE = t2.TYPE
GROUP BY t2.TYPE) t2
INNER JOIN
#TEMP t3
ON t3.TYPE = t1.TYPE AND t2.SEQ = t3.SEQ

And it seems to work. Is this the easiest way to do it or am i missing something?

View 3 Replies View Related

Query Of Set Time Range Relative To Current Date

Dec 26, 2013

I come in to work 6.30am, and need to audit what happened from 5pm when I left to 6.30am this morning. I have used code to search 13.5 hours back from any given time:

SELECT * FROM TRANSACTION_HISTORY
WHERE TRANSACTION_HISTORY.ACTIVITY_DATE_TIME > (SELECT DATEADD(hour,-13.5,(SELECT MAX (TRANSACTION_HISTORY.ACTIVITY_DATE_TIME) FROM TRANSACTION_HISTORY)))

Problem is if I run query later, I lose time from the start, eg. If I run query at 7am, I only get results from 5.30pm onwards. Rather than change criteria every day, I wanted to able to search from 6.30am of the current day, back to 5.30pm of the previous day.

View 3 Replies View Related

Rolling Weeks Date Range Of Current Week

Apr 22, 2015

I require some scripting to return data which sits in a rolling weeks date range of the current week.

For example, on a Monday I want to return data from the previous day (Sunday to the previous Monday).
On a Tuesday I want to return data from the previous day (Monday to the up coming Sunday).
On a Wednesday I want to return data from the Monday (previous 2 days ago to the upcoming Sunday)
On a Thursday I want to return data from the Monday (previous 3 days ago to the upcoming Sunday)

And so forth.

So I have my SQL script which works with static, fixed date ranges. But I need this additional dynamic date range logic built in.

View 1 Replies View Related

Analysis :: MDX - Aggregate Measure For Specific Time Range / Filtering And Print Only Current Month

Aug 6, 2015

At the following MDX code , I want to get the aggregate of measure only for  members that are also in the specified last time (like in examp 01/06/2015) . I tried existing and exists, but without any lack.

WITH MEMBER A AS
(b)+(C)
MEMBER [Measures].[Aggregate] AS
Aggregate(DAYTIME].[Month].&[2013-01-01T00:00:00]:[DAYTIME].[Month].&[2015-06-01T00:00:00],
([Measures].[D])

[Code] ....

View 13 Replies View Related

How To Get Number For Current Row

Nov 25, 2013

I am trying to get the current row number using this:

SELECT ROW_NUMBER() OVER (ORDER BY QUANTITY) FROM RDR1 WHERE Row = CurrentRow

However i got error message says row is an invalid column name.

View 11 Replies View Related

Finding Number Of Days...

Oct 30, 2006

i have id, start_date, end_date

View 2 Replies View Related

Finding A Line Number

Sep 7, 2006

I have a large query that I am trying to debug in query analyzer.However, the errors I get have no line number or reference to wherethey are failing. How can I find out what line in the query is causinga particular error message to appear?ThanksJohn IveySouth Pike School District

View 2 Replies View Related

Identity...I Need To Get The Last (or Highest Number In Identity Column)...

Sep 19, 2005

Ok,I just need to know how to get the last record inserted by the highestIDENTITY number. Even if the computer was rebooted and it was twoweeks ago. (Does not have to do with the session).Any help is appreciated.Thanks,Trint

View 2 Replies View Related

Expanding A Number Range

Jul 9, 2012

I have a table (we'll call it Ranges) of number ranges:

ID Start End
1 12345 12347
2 12355 12359
3 12380 12389
4...

Note: Start and End are text values..What I need is to output these ranges with their corresponding ID:

ID Number
1 12345
1 12346
1 12347
2 12355
2 12356
2...

Note: Number needs to be text.

View 2 Replies View Related

Number Of Current Connections?

Jun 26, 2007

How do you find out the current number of connections to a SQL Server 2005 instance in SQL Server Managment Studio? I need to see if my number of connections is exceeding my Maximum Worker Threads.



Thanks,

Joe

View 3 Replies View Related

Finding A Duplicate Transaction Number

Jan 16, 1999

I have a problem with a 3rd party piece of software. Doesn't matter which, really. The problem lies
in a table called payments, with a column called txnumber...the newest version of this software fails
a check during installation with the message "duplicate txnumber in payment table." Not sure how
this could have happened, since there is no way to manually assign the txnumber, but the point is
not important. What I'd like to do is figure out a sql script that will return only the duplicate number(s)
so that I can either remove or change them manually. Unfortunately, I'm not terribly familiar with sql.

Any suggestions?

View 2 Replies View Related

Finding Number Of Hours Between Two Times?

Apr 24, 2014

I need to write a SQL to find number of hours between a begin time and end time. The fields are varchar. There are several date functions in sql, but I am not able to figure out how to get the hours between two times that is not in date format.

BEGIN_TIME END_TIME
0900----- 1500
1000----- 1700
1000----- 1230
0930----- 1030

View 3 Replies View Related

Number Of 'Periods' In A Date Range

Feb 10, 2006

Guys
Date brain killer - I have a startdate and enddate and need, for a given period length in months, to identify the number
of periods betwen the startdate and enddate
so for example

DECLARE @Periods TABLE
(
StartDate datetime,
Enddate datetime,
NumberOfMonthsInPeriod INT ,
TotPeriodsINT
)

-- Initial data
insert @Periods
(
StartDate ,
Enddate ,
NumberOfMonthsInPeriod ,
TotPeriods-- for this illustration, initialised to 0 but need to be UPDATEd as per detail below
)

select
'30-Sep-2005',
'10-Apr-2009',
1 -- 1 month period
0
union all
select
'30-Sep-2005',
'10-Apr-2009',
3 -- 3 month period
0
union all
select
'30-Sep-2005',
'10-Apr-2009',
6 -- 6 month period
0

The following rules regarding periods apply
Each 1 (NumberOfMonthsInPeriod) month period is as would be expected ie
01-Jan - 31 Jan
01-Feb - 28-Feb (ie 1st March - 1 day which would deal with leap years - dateadd(...)
and so on to December

Each 3 (NumberOfMonthsInPeriod) month period is one of the following 'bands' per year
01-Jan - 31-Mar
01-Apr - 30-Jun
01-Jul - 30-Sep
01-Oct - 31-Dec

Each 6 (NumberOfMonthsInPeriod) month period is one of the following
01-Jan - 30-Jun
01-Jul - 31-Dec

I need to derive TotPeriods as follows:

For the row where NumberOfMonthsInPeriod = 1, the first period ie the one the start date falls within is 01-Sep-2005 to 30-Sep-2005, second is 01-Oct-2005 - 31-Oct-2005 and so on until
last period ie the one the end datye falls within is 01-Apr-2009 - 30-Apr-2009, a TotPeriods value of 44

For the row where NumberOfMonthsInPeriod = 3, first period is 01-Jul-2005 to 30-Sep-2005, second is 01-Oct-2005 - 31-Dec-2005 and so on until
last period is 01-Apr-2009 - 30-Jun-2009, a TotPeriods value of 16

For the row where NumberOfMonthsInPeriod = 6, first period is 01-Jul-2005 to 31-Dec-2005, second is 01-Jan-2006 - 30-Jun-2006 and so on until
last period is 01-Jan-2009 - 30-Jun-2009, a TotPeriods value of 8

Hope this is clear and thanks in advance

View 3 Replies View Related

T-SQL (SS2K8) :: Fill In The Gap Between 2 Number Range?

Apr 2, 2014

In my SQL Server database, I have table with the following records

counter, value1
12345, 10.1
12370, 10.5
12390, 9.7

Let's assume that I input a gap value of 5. I need to fill in the data between the Record 1 and Record 2 by increment of 5 as specified in the input parameter on the counter column.

For example using Record 1 and Record 2, here are the additional data needs to be inserted into the table.

1234510.1 --> Record 1
1235010.1
1235510.1
1236010.1
1236510.1
1237010.5 --> Record 2
1237510.5
1238010.5
1238510.5
123909.7 --> Record 3

Currently, I am using a cursor to read from the table and select MIN counter from the table. Then use a LOOP to fill in the gap and insert it into another table. I have over 10000 records and after fill up the gap, I might end up with even more records. Just want to see if I can get any other efficient way to achieve this.

why I want to fill in the gap, I need to calculate the average value for my record set after considering all valid data points in between.

View 9 Replies View Related

How To Find Current Row Number In MS SQL 2000

Jan 17, 2007

hi
How to find current row number in MS SQL 2000 server DB ?
like in oracle rowid is there to get unique no. of each row.
I want to track the last row in the resultset ..
is any fun for this ??

View 7 Replies View Related

Finding Out Database Usage (number Of Transactions)

Nov 20, 2003

Hey,

Is there somewhere in MSDE (or SQL) where you can see how many transaction are made to a sertain database or by a sertain user? At this way i could figure out witch database/user uses most (or least) recources (cpu) over a period of time.

View 5 Replies View Related

Help With Dates-finding Number Of Days Per Month

Mar 28, 2000

I am trying to determine the amount of days in a month to prorate a month end estimate.
We measure service calls and need to approximate how many we will have at month end.
I would like to automate a query to post on our web and need to know how many days are in the current month.

A possible solution would be to piece together a datetime variable using getdate and dateadd then use a datepart.
However , I don't know how to create a datetime variable this way.

Thanks in advance

View 1 Replies View Related

Finding Number Of Business Days Between Two Dates

Nov 27, 1999

Hi and Thank you in advance
I am trying to find a away to calculate the number of business days between two dates. In other word, I do not want to count Saturday nor Sundays if those days are between the two dates.
Example
if Date1 = 11/26/1999
Date2 = 11/30/1999

the DateDiff(dd,Date1,Date2) the result should be 2
I need to do this against a table which might not have a lot of records, but I also need to not count Holidays if they fall within the two Dates.
Thank you in advance
Tomas

View 1 Replies View Related

Finding Number Of Business Days Between Two Dates

Nov 27, 1999

Hi and Thank you in advance
I am trying to find a away to calculate the number of business days between two dates. In other word, I do not want to count Saturday nor Sundays if those days are between the two dates.
Example
if Date1 = 11/26/1999
Date2 = 11/30/1999

the DateDiff(dd,Date1,Date2) the result should be 2
I need to do this against a table which might not have a lot of records, but I also need to not count Holidays if they fall within the two Dates.
Thank you in advance
Tomas

View 1 Replies View Related

Finding Port Number In SQL Server 2005

Feb 13, 2008

I know the ..Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGErrorLog on the server contains the information. But that needs I to RDP to the server or open the file remotely. Is there any other way to find out the port from my SSMS connection?

I remember in SQL Server 2000 you could find the information by choosing Properties of a connection in EM and click on the Network Configuration button. (I know it works when you are ON THE SERVER! But when I tried from my desktop, pressing the button didn't do anything)


Canada DBA

View 2 Replies View Related

T-SQL (SS2K8) :: Hot To Get Number Of Days For Given Date Range

Sep 15, 2014

--From the rows I want to know how many number of days a person was active for the given date range.

create table [dbo].[personstatus]
(
id int identity(1,1),
name varchar(100),
DateAdded date,
InactivationDate date ) ;
insert into [dbo].[personstatus] values

[Code] ....

--The output I am looking for.
/*
1) FromDt = '2014-01-01' ToDt ='2014-01-30'
KRISS = 7
VDENTI = 7 days

2) FromDt = '2013-01-01' ToDt ='2014-01-01'
KRISS = 1
VDENTI = 1 days

3) FromDt = '2013-01-01' ToDt ='2014-01-01'
KRISS = 0
VDENTI = 1 days

4) FromDt = '2013-01-01' ToDt ='2014-12-31'

KRISS = 8+24+8+21 = 61 Days
VDENTI = 31+24+31+30 = 116 Days
*/

View 9 Replies View Related

How To Assing Sequential Number To A Date Range

Oct 7, 2007

I have two tables

1. Po (Purchase Order)
2. PoDet (PoDetails)

Po Table is having a datatime field called "ShipDate", OrderNo and a field called PoNo (which is having number of PO).

PoDet is child table having PoNo and PoQty

Suppose following records are in both tables

Po:-

OrderNo : 1
PoNo :- Po No 1
ShipDate :- Oct 5, 2007

OrderNo : 1
PoNo :- Po No 2
ShipDate :- Dec 5, 2007


OrderNo : 1
PoNo :- Po No 3
ShipDate :- Oct 5, 2007

PoDet:

PoNo :- Po No 1
PoQty :- 2000

PoNo :- Po No 2
PoQty :- 3000

PoNo :- Po No 3
PoQty :- 4000


I want to generate a Delivery No. which will be generated in this way :-

1. Earlier Shipdates should be assigned a Lower Number
2. If Shipdates are same, like in case of PoNo 1 and PoNo3, the higher Qty will be assigned a lower number

So meeting the above two conditions, a single SQL should return

PoNo :- Po No 3
DelNo : 1 (As Dates are same for PoNo1 and PoNo3 but PoNo3 Qty is higher, so this will come first)


PoNo :- Po No 1
DelNo : 2

PoNo :- Po No 2
DelNo : 3

Can anybody help on this.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved