Hi,
I get 5000 records on executing a query.
In the form, I would like to display 50 records at a time in the grid from this resulted query, so i create 100 link labels(dynamically created based on the no. of records resulted from the query) within a panel.
When clicked on link1, first 50 records should be displayed,on clicking link2 next 50 records(ie, 51 to 100) should be displayed and so on.
So , i wrote the query as---
select top(50)* from tblindividual where id not in (select top(intValue)id from tblindividual )
where intValue would be 50 for link2,100 for link3,150 for link4 and so on
If i want to fetch last 50 records of the query, intValue would be 4950.Here the "not in" list becomes very big(1,2,3,.......,4949,4950) and hence the query is becoming a bit slow...
Is there any other method(query) to get the same result??.. because i heard that using "not in" keyword would make query execution slow.
ID DeviceName Status 1 Sony Good 2 Toshiba OK 3 Sony Bad 4 Tata OK
I need to return the following records
ID DeviceName Status 2 Toshiba OK 3 Sony Bad 4 Tata OK
If there are more than one record for the Device, then record with the latest ID should be returned. If there is only one record for the Device, then that record should be returned.
Can this be achieved through a single query. Any help is appreciated.
CREATE TABLE [dbo].[DR_Test]( [source_item_id] [int] NOT NULL, [source_line_no] [int] NULL, [buyer_id] [int] NOT NULL, [seller_member_id] [int] NULL,
[code]...
the table contains more than 80 million records so when i fetch the data using buyer_id & timezone its taking lot of more than 1 hours or so....& where buyer_id is not unique.how to fetch the data fast or need to change the structure of the table
I have a scenario to fetch records for each ID on 2 conditions. There are 2 types of Product type for each ID. PFE and PRI. I need only latest active PFE and at the same time all the latest PRI should be closed. (meaning, only PFE should be in Active status currently)
1.) Latest Product type PFE should be A (active) status for the particular ID 2.) At the same time ALL the latest PRI should be C(closed) status for the same ID
I have give example with 3 scenarios and desired output
1.) For ID 101, Latest PFE is active and all latest PRI is closed ----> Should come in result 2.) For ID 102, Latest PFE is Closed and all latest PRI is closed ---->Should NOT come in result
I'm looking for a way of taking a query which returns a set of date time fields (probable maximum of 20 rows) and looping through each value to see if it exists in a separate table.
E.g.
Query 1
Select ID, Person, ProposedEvent, DayField, TimeField from MyOptions where person = 'me'
Table
Select Person, ExistingEvent, DayField, TimeField from MyTimetable where person ='me'
Loop through Query 1 and if it finds ANY matching Dayfield AND Timefield in Query/Table 2, return the ProposedEvent (just as a message, the loop could stop there), if no match a message saying all is fine can proceed to process form blah blah.
I'm essentially wanting somebody to select a bunch of events in a form, query 1 then finds all the days and times those events happen and check that none of them exist in the MyTimetable table.
USE [Testing] GO /****** Object: Table [dbo].[Testing] Script Date: 4/25/2014 11:08:18 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
[Code] ....
It seems to work fine with one million records.
Each primary key is unique, but the begindate is non-unique, and i guess even if i use datetime2 and add nanoseconds, from what i have read, there is a chance that i could have a duplicate datetime since the date is imported via XML from multiple sources.
I have an SQL table with the following structure (this is an example):
Event ID Date Server Status 200 01/01/2008 15:00:00 server1 Up 201 01/01/2008 15:00:01 server2 Up 202 01/01/2008 15:00:02 server3 Up 203 01/01/2008 15:00:03 server1 Up 204 01/01/2008 15:00:04 server2 Up 205 01/01/2008 15:00:05 server3 Up 206 01/01/2008 15:00:06 server1 Down 207 01/01/2008 15:00:07 server2 Up 208 01/01/2008 15:00:08 server3 Up
I made a view that brings all the events of an especific server, let say server 1, the view shows:
Event ID Date Server Status 200 01/01/2008 15:00:00 server1 Up 203 01/01/2008 15:00:03 server1 Up 206 01/01/2008 15:00:04 server1 Down
The objective: Calculate the uptime for a server.
The question is:
How do I calculate the time elapsed between one record of the view and the previous one?
I have some difficulties with TSQL where i need to calculate time between two records in seconds. Records look like this: ID IP DATE CHANNEL_ID #CALCTIME 1 10.132.184.226 12/06/2007 08:00:00 406 35 2 10.96.121.230 12/06/2007 08:00:10 1 45 3 10.128.242.214 12/06/2007 08:00:20 4 39 4 10.132.184.226 12/06/2007 08:00:30 2 5 10.96.121.230 12/06/2007 08:00:55 3 6 10.96.121.214 12/06/2007 08:00:59 3 21 7 10.96.121.214 12/06/2007 08:01:20 4 .........................................................................................................
Basically i would need to calculate the time between 2 records with the same IP (next one with the same IP) and calculate the time between the DATE which is basically attached to the first record. As you can see the first #CALCTIME is done between the ID #1 and ID#4 which means that the time difference must be done between the current and the next record.
Hi,There are about 30 millions records on my mssql server and I want to access 2 million of them at one time. However, when I try to access with sql command I get time out error. I want to select first 100 record and select the other 100 and so on. May I obtain this?For example;select * from tbl_Customer where name = @name_ ->time out errorSomeone has said that you can solve this problem with < cursors > but I can't find enough article. Thanks...
Hello all, I have a DTS package set up to import a text file on a daily basis. I need to dump the data in 2 table after 7 days of the last import .this is the code that I have Delete From TblTemp date(Day(-7), CurrentStamp). But for some reason it deleting the data right after it imports it. And it doesn't delete anything out of the other table.
I want to delete my record after ten days of the Posted date(field)..how do I do it? I'm able to insert date(short date , long time) to the database. I'm using sql server and C#. this is what I have so far.I would appreciate your help..
I want to create sql for retrieving 10 records per time but need to know the total result matches. And I have previous and next button for retrieving previous or next 10 records.
I am developing a form for a mortgage company. There can be any number of borrowers on a given loan, and the business has asked that this form return only 2 borrowers at a time for a loan. For example, if there are 3 borrowers for a loan, they want the first copy of the form to print the first 2 borrowers and then another copy of the form to print the 3rd. No matter how many copies are printed, they want the borrower information to be labeled as 'Borrower1' xyz and 'Borrower2' xyz. Also, there will be a LOT more fields returned on the real form, so the sample information below is very simplified test data.
I don't want that 2nd record to return. This result is what makes me think of gaps and islands, but I don't know if the 2nd record is really an island since it's (1) not stored this way...it's returning this way because of the query and (2) it's not sequential data..I tried restricting this by putting this into a CTE and then returning only the odd numbered records like I have below. This runs pretty quickly when dealing with one loan. But...I am concerned that the CTE will be slow when we run batches of loans.
Attempt with CTE: --With CTE ;WITH cte AS (SELECT Borrower1 = BorrowerName , Borrower2 = LEAD(BorrowerName) OVER(ORDER BY BorrowerOrder) , RowNumber = ROW_NUMBER() OVER(ORDER BY BorrowerOrder)
[code]...
Is there a better, cleaner way to do this? Or is the CTE the best way to go?
I want to group the records on the time difference
declare @tbl as table(id int,intid int,val int,dt datetime) insert into @tbl select 1,1,10,'03/31/2006 15:05:22' union all select 2,1,12,'03/31/2006 15:10:22' union all select 3,1,15,'03/31/2006 15:15:22' union all select 4,1,12,'03/31/2006 15:25:22' union all select 5,1,8,'03/31/2006 15:30:22' union all select 6,1,6,'03/31/2006 15:35:22' union all select 7,1,4,'03/31/2006 15:40:22' union all select 8,1,3,'03/31/2006 15:45:22' union all select 9,1,10,'03/31/2006 15:50:22'
declare @tbl1 as table(intid int,Tm int,val int) insert into @tbl1 select 1,5,10
I want a output such that when the val in @tbl goes below the val in @tbl1 for the Tm mentioned in @tbl1 then the time difference should be shown.For example record 1 it starts with 10 the records remain more than 10 till record number 5.From 5 the records remains lower than 10 till record number 9.So I need to show the the time difference from record number 5 till 9. But there is a catch.In @tbl1 there is column named Tm.The time difference sould be calculated only if the diff more than Tm value in @tbl1. For example if the value of Tm is changed to say 25 then the there is no need to show the time difference since the time difference value from record 5 to record 9 is less than 25. Hope I am clear.
I have a data set like so:UTC_TIME Timestamp NodeID Message FlagLineStation11/19/2005 10:45:07 1132397107.91 1 3 5 1028103411/3/2005 21:05:35 1131051935.20 2 3 5 1009104311/25/2005 21:12:16 1132953136.59 3 3 5 10371049I added the UTC_TIME column in as aconversion of the unix timestamp inthe TIMESTAMP column.Keeping things simple and straightforward, I need to be able tocalculate the difference from one record to the next (ordered byTIMESTAMP or UTC_TIME) and output the result into another column in thetable.NODEID is the unique id.First, what is the function to do so if, say, I only wanted tocalculate the difference between 2 records as just a basic SELECTstatement. That way I can answer quick question based on any one or twoNODEID's.Second, how would I further that to continually calculate (as statedabove)?WOuld this be a stored procedure? A trigger? A cursor?I am learning as I go here. Any help is greatly appreciated.R.
I have a shopping cart that will get full from time to time becausecustomers click out of the site before they confirm their purchase,therefore leaving a full cart behind. I'd like to have a timestamp onthis table, so that I can delete anything that I find is more than a dayold.How can I do this?Thanks,Bill*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I have a table that contains an employee id and dates signifying time periods that those employees were working. I need to calculate anniversaries, such as 20 year, which are the sum of all periods spend working projected out to 20 years. For example,
The periods in which the employees were inactive (the time period between active ranges) would push back their anniversary date, obviously. I'm only concerned with employees that are currently active (ie. the most recent record has a NULL end date). I thought about trying to use datediff to calculate the time between active periods, but I'm not sure how to go about doing it.
I need to find the total StatusDateTime for each TicketId I need to find the average StatusDateTime for all TicketIDs Ex. TicketId, "T10001", has 4 records based on the Seq column.
By using this, I should be able to find the amount of time between the first Seq and the last Seq to get a total time span for Ticket.
Expanding on this, I should be able to add up all of the Ticket's calculated time spans and divide by the number of tickets to get the average time span.
TransactionsImport (which is the destination table) TransactionsImportDelta
I need to do the following:
Get the records with the latest date and time in the destination table TransactionsImport Get the records with the latest date and time in the destination table TransactionsImportDelta table Insert the records from the TransactionsImportDelta table into TransactionsImport that have a greater date & time than the current records in TransactionsImport table.
Problem is date & time are in separate columns:
Table structure:
Date Time ID 2011121305154107142201008300100 2011121305154122B1L13ZY0000A07YD 2011121304504735142201090002600 2011121304504737142201095008300 2011121304504737142201090002600
I have a table set of records. Its contains some customerID,SportsGoods,Price in different datetime. I want to add customer spent. If crossed 1000 means i have to show purchase time when it is crossed 1000. I need query without while and looping.
creating the missing records in a date/time range.
However, I need to return different groups for each span of records.
here's some data....
aaa1 aaa7 bbb2 bbb5 bbb6
The numbers are the hour of the day.
I need to return
aaa 0 0 aaa 1 1 aaa 2 0 aaa 3 0 ... bbb 0 0 bbb 1 0 bbb 2 1 ... and so on.
I've got a numbers table and I can left join with it but I just get nulls for the missing hours instead of having it as above.....I can't think of a way of repeating the groups for each of the 'missing' hours - other than creating a length insert statement to fill in the gaps....unless that is the only way of doing it.
ID - INT Machine - TINYINT StartTime - DATETIME EndTime - DATETIME
What I am trying to do is figure out how much time is used for production per day. The problem is, there are production runs that run over midnight and possible multiple days without ending. For example, if I have the following data:
i have a table of phonecall records detailing amongst other things the time and duration of the call. there is a relationship linking this table to a table of handsets and from there to a table of users. this users table lists the start date the user had the phone. as ever sample data might make this easier to show:
PhoneUsers table
PhoneUserID Name StartDate PhoneID
1 john 1/08/2006 1
2 bob 20/08/2006 1
3 fred 2/08/2006 2
etc
Phones table:
PhoneID
1
2
etc
PhoneCalls table:
PhoneCallID PhoneID CallDate Duration
1 1 10/08/2006 25
2 1 23/082006 20
3 2 23/08/2006 20
i want the following result set:
user calldate duration
john 10/08/2006 25
bob 23/08/2006 20
fred 23/08/2006 20
essentially i am trying to link the phonecalls to the user of the phone at the time of the call. i make the following assumptions regarding the data:
the user is responsible for all calls for a given handset from the 'startdate' until the next 'startdate' for a different user, if such a record exists, if not then for all calls after that date
I am developing an application in vb.net 2005 using SQL Server 2000. In this I have two tables SessionMaster and SessionChild. Fields of session master - SessionMastId, Start_Date, End_Date, Session_Type, Fields of session child - SessionChildId, SessionMastId, UserName, Comment. SessionMastId and SessionChildId are primary keys of respective tables and also they are auto increment fields. Please how to write trigger to insert record into both tables at a time.
I'm trying to get a count of Employed and Available contractors per time period, and I have a table of Contracts... something like:
CREATE TABLE empContract( empContractID INT IDENTITY(10000,1) PRIMARY KEY, StartDate DATE NOT NULL, EndDate DATE, ContractorAssigned INT, FOREIGN KEY ContractorID REFERENCES Contractor(ContractorID) );
I don't think this is possible without the existence of some kind of Calendar table. Given the existence of a calendar table, the query seems really simple - just something like:
SELECT cal.CalendarDate, ec.ContractID FROM Calendar cal LEFT JOIN empContract ec ON cal.CalendarDate BETWEEN ec.StartDate AND ec.EndDate
The left join forces the existence of all dates in a range (@StartDate and @EndDate), so that when I try to create a graph with counts by day, I don't have any gaps in my time series.
Hi all!I'm trying to write a T-SQL statement that will allow me to do a maintenance for all user table for all databases on my server.This is what I got so far :DECLARE @cStatement varchar(255)DECLARE @dStatement varchar(255)DECLARE T_database CURSOR FOR SELECT '[' + CONVERT(varchar(64),name) + ']' FROM master.dbo.sysdatabases WHERE dbid>6SET nocount ONOPEN T_databaseFETCH NEXT FROM T_database INTO @dStatement WHILE (@@FETCH_STATUS <> -1)begin-- LOOP IMBRIQUÉ POUR PASSER AU TRAVERS DE CHAQUES ELEMENTS-- DE LA BASE DE DONNÉES ACTUELLE.EXEC ('DECLARE T_cursor CURSOR FOR SELECT ''UPDATE STATISTICS ['' + CONVERT(varchar(64),name) + '']'' FROM ' + @dStatement + '.dbo.sysobjects WHERE type = ''U''')OPEN T_cursorFETCH NEXT FROM T_curosr INTO @cStatementWHILE (@@FETCH_STATUS <> -1)beginPRINT(@cStatement)FETCH NEXT FROM T_cursor INTO @cStatementendDEALLOCATE T_cursorFETCH NEXT FROM T_database INTO @dStatement endDEALLOCATE T_databaseBut I get a sh**t load of errors :Msg 16916, Level 16, State 1, Line 15A cursor with the name 'T_curosr' does not exist.Msg 16916, Level 16, State 1, Line 15A cursor with the name 'T_curosr' does not exist.Msg 16916, Level 16, State 1, Line 15A cursor with the name 'T_curosr' does not exist.Msg 16916, Level 16, State 1, Line 15A cursor with the name 'T_curosr' does not exist.Msg 16916, Level 16, State 1, Line 15A cursor with the name 'T_curosr' does not exist.Msg 16916, Level 16, State 1, Line 15A cursor with the name 'T_curosr' does not exist.Msg 16916, Level 16, State 1, Line 15A cursor with the name 'T_curosr' does not exist.Msg 16916, Level 16, State 1, Line 15A cursor with the name 'T_curosr' does not exist.Please help!!! :beer:
I would like to know that, I have three instances of the same database at three different servers and I am trying to fetch the data from the select query. "select * from table_name"
I would like to know, whether the order of rows fetched by this query will be different on different servers of sql server or the same order of rows will be fetched.
For me the output is coming different on each server database with he same query . Pls let me know, is there any default order by or it takes it randomly.
i have a task that has a ole-db destination. it inserts some data to a table. it's always 1 record that is gonna be inserted.after that the task is finished. however i want to use that same record in the followup-task. is there a way to retrieve the id of that specific record.
Please note that the id of the record is an int-identity from sql-server itself.
at the moment i'm fiddling with "remembering" the inserted data and hopefully it will be a 100%match in the next task, but i have a dark feeling it's not 100%.
