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
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?
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
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.
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
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
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?
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.
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.
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])
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
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
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.
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.
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.
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
-- 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
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.
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 ??
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.
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.
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
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
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)
--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
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)
