How Can I Take Millions Of Records Without Time Out Error?
Sep 19, 2007
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 error
Someone has said that you can solve this problem with < cursors > but I can't find enough article.
Thanks...
View 3 Replies
ADVERTISEMENT
Apr 25, 2014
Sample 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.
View 7 Replies
View Related
Sep 3, 2006
Hi all,I was given a task to create a houseHolding logic under a table thathave millions records.first let me explain what is a house holding:let's say I have 2 records that have the same phone number, that meanthat both records are under the same household, but this can get morecomplicatedthis article explain ithttp://www.teradata.com/t/page/115924/index.htmlif anyone worked with household he knows that you need to scan thetable many time to get all the house holds, I used a dts to do it.I tested the dts on 11 records like the article did and that workgreat, but once I went to million records each loop is taking me 2 houror so....a and I have no idea how how many loops I will have to do.if anyone out there worked with household queries and used sql, yourimput would help me allotthanks.
View 3 Replies
View Related
Aug 19, 2007
Hi
Please guide me for the below given problem.
While doing migration by using cursors for the below given sample data its taking more hours to complete the process. Therefore want to know is there any way I can do it in simple query.
ACNo Amount Balance CalType
A001 10 10 +
A001 10 20 -
A001 40 40 +
A001 10 30 -
A002 90 90 +
A002 20 110 +
A002 40 150 +
A003 10 30 +
A003 10 40 +
A003 10 30 -
A004 40 40 +
A004 10 30 -
Iam having Amount value alone and Balance has to be calculated value based on CalType. At the same time the Balance has to be reset as 0 when AcNo has changed.
Please guide me for the faster approach.
Regards,
Mohanraj
View 3 Replies
View Related
Mar 15, 2004
If there is 13 million records in one table and 40 thousand records in another table then what is the fastest way of joining these two tables????
This was a question to me from somebody to which i cudn't answer back properly. Cud anybody tell the answer with properreasons behind the answer??????
Thanx.
View 7 Replies
View Related
Jul 23, 2012
i have a query to delete millions of records. I whant to delete in batches of a 1000. My Select join statement will return millions of records so this takes alot of time how to i select a 1000 records delete everything that his not in those record and loop and not select the same records again.Here is what i have :
DECLARE @i INT
WHILE (1=1)
BEGIN
BEGIN TRAN
DELETE TOP(1000) FROM dbo.ABC123
WHERE SUBSTRING(dumbdumb,1,8) NOT IN
[code]....
View 3 Replies
View Related
May 14, 2008
Hi Teachies,
I am using SQL Server Standard Edition .
in one of my table , there i am inserting around 25 millions records and that takes time around more than 3 hrs.
same thing is happening while fetching records from that table.
this database contains only single file group i.e primary
and that table contains .. Clustered as well as non clustered index.
it doesnot have any Triggers.
How do i increase this performance.
Paritioning of table cannot be use in SQL Server Standard Edition.
Or Dropping all non clustered index before insert operation will improve my performance.
Please suggest me.
Thanks
Rajesh Varma
View 2 Replies
View Related
Feb 27, 2007
The iussue:Sql 2KI have to keep in the database the data from the last 3 months.Every day I have to load 2 millions records in the database.So every day I have to export (in an other database as historical datacontainer) and delete the 2 millions records inserted 3 month + one day ago.The main problem is that delete operation take a while...involvingtransaction log.The question are:1) How can I improve this operation (export/delete)2) If we decide to migrate to SQL 2005, may we use some feature, as"partitioning" to resolve the problems ? In oracle I can use the "truncatepartition" statement, but in sql 2005, I'm reading, it cant be done.This becouse we can think to create a partition on the last three mounts tosplit data. The partitioning function can be dinamic or containing afunction that says "last 3 months ?" I dont think so.May you help usthank youMastino
View 5 Replies
View Related
Sep 11, 2006
I'm trying to compare about 28 million records (270 length) from table A and B using the Lookup task as described in this forum. The process works fine with about two million records or so on my desktop ( p.4 3.39GHz, 1.5 GB Ram), but hangs with the amount of data I'm trying to process. I tried using full and partial caching, but to no avail. I'm thinking this is a hardware resource problem. So, does anyone has any recommendation on the hardware needed for this kind of operation and/or suggestion? Thanks in advance...
View 8 Replies
View Related
Sep 4, 2007
Hello all
quick question-- Im looking for the most effiecient way to extract data daily from a table with some 9.5 mill records and growing. These are transaction records and ideally I would like to bring over the last days transactions and add them to my existing table. I cannot use the transaction date as sometime we have to operate in an "Offline" mode where the records are brought over sometime later. This could be days are unfortunetaly a week or more. there are some 30 fields in the transaction table so is there a more efficient way to do this simply creating a concatenated key?? Would it be more effiecient to drop and recreate the table daily? that sounds extreme so wanted to get a few ideas.
Thanks in Advance
km
View 2 Replies
View Related
May 19, 2008
Hi Teachies,
I am using SQL Server Standard Edition with good HardWare configuration.
In one of table i am inserting around 25 millions records and that takes time around more than 3 hrs.
same thing is happening while fetching records from that table.
this database contains only single file group i.e primary
and that table contains .. Clustered as well as non clustered index.
it doesnot have any Triggers.
How do i increase this performance.
Paritioning of table cannot be use in SQL Server Standard Edition.
Or Dropping all non clustered index before insert operation will improve my performance.
Please find the details ..
SERVER CONFIGURATION:
Intel pentium (R) 4 CPU
2.88 GHZ,
2.79 GHZ ,2 GB RAM
Operating System: WINDOWS 2003 R2 STANDARD SERVICE PACK 2
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
DAtABASE DETAILS:
MDF and LDF located on C: Drive
Available Space on C: DRIVE 2.94 GB
TABLES DETAILS
CREATE TABLE [dbo].[TIX_PAYMENT_SCHEDULE](
[PaymentScheduleId] [bigint] IDENTITY(1,1) NOT NULL,
[OwedAmountId] [int] NULL, --NonClusteredIndex
[ProposalId] [int] NOT NULL, --NonClusteredIndex
[BrandId] [int] NULL, -- NonClusteredIndex
[DueDate] [datetime] NULL, --NonClusteredIndex
[OverdueDate] [datetime] NULL,--NonClusteredIndex
[ExpectedAmount] [decimal](18, 2) NULL,
[TransactionStatusId] [tinyint] NULL,--NonClusteredIndex
[IsLate] [char](1) NULL,
[IsPaymentReceived] [char](1) NULL,
[ScheduleBatchJournalId] [bigint] NULL,--NonClusteredIndex
[IsValidSchedule] [char](1) NULL,
[RuleId] [int] NULL,
[ActionId] [int] NOT NULL,
[ReasonId] [tinyint] NULL,
[Comments] [nvarchar](2000) NULL,
[NoofDays] [int] NULL,
[ActualAmountReceived] [decimal](18, 2) NULL,
[CreatedBy] [uniqueidentifier] NULL,
[CreatedDateTime] [datetime] NOT NULL,
[LastUpdatedBy] [uniqueidentifier] NULL,
[LastUpdatedDateTime] [datetime] NOT NULL,
[CaseScheduleId] [bigint] NULL,--NonClusteredIndex
[ActionDate] [datetime] NULL,
[HasExactMatch] [char](1) NULL,
[IsCatchupBalanced] [char](1) NULL,
[HasModified] [char](1) NULL,--NonClusteredIndex
[PendDate] [datetime] NULL,
[IsAutoAccept] [char](1) NULL,
[CatchupBalanceIdentifier] [uniqueidentifier] NULL,--NonClusteredIndex
CONSTRAINT [PK_TIX_PAYMENT_SCHEDULE] PRIMARY KEY CLUSTERED
(
[PaymentScheduleId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
TABLE 2
CREATE TABLE [dbo].[TIX_PAYMENT_CASE_SCHEDULE](
[CaseScheduleId] [bigint] IDENTITY(1,1) NOT NULL,
[ProposalId] [int] NOT NULL,--NonClusteredIndex
[DueDate] [datetime] NOT NULL,
[OverDueDate] [datetime] NOT NULL,
[TotalExpectedAmount] [decimal](18, 2) NOT NULL,
[TotalActualPaymentReceived] [decimal](18, 2) NOT NULL,
[TransactionStatusId] [int] NOT NULL,--NonClusteredIndex
[ActionId] [int] NULL,
[CreatedBy] [uniqueidentifier] NULL,
[CreatedDateTime] [datetime] NULL,
[LastUpdatedBy] [uniqueidentifier] NULL,
[LastUpdatedDateTime] [datetime] NULL,
[IsValidSchedule] [char](1) NULL,
[ScheduleBatchJournalId] [bigint] NULL,
[IsCatchupBalanced] [char](1) NULL,
[HasModified] [char](1) NULL,--NonClusteredIndex
[CatchupBalanceIdentifier] [uniqueidentifier] NULL,
CONSTRAINT [PK_TIX_PAYMENT_CASE_SCHEDULE] PRIMARY KEY CLUSTERED
(
[CaseScheduleId] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
STORED PROCEDURE:
CREATE PROC [dbo].[TIX_PRC_GENERATE_PAYMENTSCHEDULE_DATA]
(
@XMLParams XML,
@ToDate datetime,
@HasModified char(1)
)
AS
BEGIN
SET NOCOUNT ON
--Exception Handling Variable Declaration
DECLARE @ErrorMessage NVARCHAR(200),
@ErrorNumber INT,
@ErrorSeverity INT,
@ErrorState INT,
@ErrorProcedure NVARCHAR(50),
@ErrorLine INT,
@ErrorDesc NVARCHAR(100)
DECLARE @XMLPayment INT
BEGIN TRY
IF @XMLParams IS NOT NULL
BEGIN --BEGIN IF
SET @ErrorDesc='Error Occured While Inserting into TIX_PAYMENT_SCHEDULE FROM XML'
INSERT INTO TIX_PAYMENT_SCHEDULE
(
OwedAmountId,
ProposalId,
BrandId,
DueDate,
OverdueDate ,
CreatedDateTime,
LastUpdatedDateTime,
ExpectedAmount,
ActualAmountReceived,
ScheduleBatchJournalId,
RuleId,
TransactionStatusId,
ActionId,
IsLate,
IsPaymentReceived ,
IsValidSchedule,
--Added by DC : 119
IsCatchupBalanced,
CatchupBalanceIdentifier,
HasModified
---------------------------------------------------
)
SELECT
Main.ELEMENT.value('(OwedAmountId)[1]','int') AS OwedAmountId,
Main.ELEMENT.value('(ProposalId)[1]','int') AS ProposalId,
Main.ELEMENT.value('(BrandId)[1]','int') AS BrandId,
convert(datetime,Main.ELEMENT.value('(DueDate)[1]','varchar(100)')) AS DueDate,
convert(datetime,Main.ELEMENT.value('(OverdueDate)[1]','varchar(100)')) AS OverdueDate,
@ToDate AS CreatedDateTime,
@ToDate AS LastUpdatedDateTime,
convert(decimal(18,2),Main.ELEMENT.value('(ExpectedAmount)[1]','varchar(100)')) AS ExpectedAmount,
convert(decimal(18,2),Main.ELEMENT.value('(ActualAmountReceived)[1]','varchar(100)')) AS ActualAmountReceived,
Main.ELEMENT.value('(ScheduleBatchJournalId)[1]','bigint') AS ScheduleBatchJournalId,
Main.ELEMENT.value('(RuleId)[1]','int') AS RuleId,
Main.ELEMENT.value('(TransactionStatusId)[1]','int') AS TransactionStatusId,
Main.ELEMENT.value('(ActionId)[1]','int') AS ActionId,
Main.ELEMENT.value('(IsLate)[1]','char(1)') AS IsLate,
Main.ELEMENT.value('(IsPaymentReceived)[1]','char(1)') AS IsPaymentReceived,
Main.ELEMENT.value('(IsValidSchedule)[1]','char(1)') AS IsValidSchedule
--Added by DC for 119
,Main.ELEMENT.value('(IsCatchupBalanced)[1]','char(1)') AS IsCatchupBalanced
,Main.ELEMENT.value('(CatchupBalanceIdentifier)[1]','nvarchar(1000)') AS CatchupBalanceIdentifier
,@HasModified
---------------------------------------------------------------------
FROM @XMLParams.nodes ('(/ROOT/DATA)') AS Main(ELEMENT)
END--END IF
END TRY--Main END TRY
BEGIN CATCH --Main BEGIN CATCH
SELECT @ErrorMessage = @ErrorDesc+Char(13)+Error_Message(),
@ErrorSeverity = Error_Severity(),
@ErrorState = Error_State(),
@ErrorNumber = Error_Number(),
@ErrorProcedure = Error_Procedure(),
@ErrorLine = Error_Line()
RAISERROR(
@ErrorMessage,
@ErrorSeverity,
@ErrorState,
@ErrorNumber,
@ErrorProcedure,
@ErrorLine
)
END CATCH --Main END CATCH
END --Main END
STOREDPROCEDURE 2
CREATE PROCEDURE [dbo].[TIX_PRC_GET_PAYMENTSCHEDULE_SCHEDULE_FOR_DATE_RANGE]
(
@ToDate datetime,
@IsValidSchedule char(1)
)
AS
BEGIN
SET NOCOUNT ON
--Execption Handling Variable Declaration
DECLARE @ErrorMessage NVARCHAR(200),
@ErrorNumber INT,
@ErrorSeverity INT,
@ErrorState INT,
@ErrorProcedure NVARCHAR(50),
@ErrorLine INT,
@ErrorDesc NVARCHAR(100)
BEGIN TRY --Exception Handling
SET @ErrorDesc='Error Occured while fetching records from TIX_PAYMENT_SCHEDULE'
SELECT
PaymentScheduleId,
OwedAmountId,
ProposalId,
DueDate,
OverdueDate,
ExpectedAmount,
TransactionStatusId,
IsPaymentReceived,
IsLate,
ActionId,
ActualAmountReceived,
IsValidSchedule,
BrandId,
CaseScheduleId,
ReasonId,
Comments,
NoOfDays,
ActionDate,
IsCatchupBalanced,
CatchupBalanceIdentifier,
HasModified
from TIX_PAYMENT_SCHEDULE with (nolock)
WHERE DUEDATE <=@ToDate AND IsValidSchedule=@IsValidSchedule
SELECT DISTINCT OwedAmountId,proposalId,brandId from TIX_PAYMENT_SCHEDULE with (nolock) WHERE DUEDATE <=@ToDate AND IsValidSchedule=@IsValidSchedule Order By OwedAmountId,ProposalId,BrandId asc
SELECT DISTINCT ProposalId from TIX_PAYMENT_SCHEDULE with (nolock) WHERE DUEDATE <=@ToDate AND IsValidSchedule=@IsValidSchedule Order By ProposalId asc
END TRY
BEGIN CATCH
SELECT @ErrorMessage=@ErrorDesc+CHAR(13)+ Error_Message(),
@ErrorNumber=Error_Number(),
@ErrorState=Error_State(),
@ErrorProcedure=Error_Procedure(),
@ErrorLine=Error_Line(),
@ErrorSeverity=Error_Severity()
RAISERROR(
@ErrorMessage,
@ErrorSeverity,
@ErrorState,
@ErrorNumber,
@ErrorProcedure,
@ErrorLine
)
END CATCH
END
Thanks & Regards
Rajesh Varma
View 6 Replies
View Related
Jun 21, 2007
hi!
i am using sql 2005 k with sp1.
I want to delete 30-40 million rows from a transactional table. Whats the fastest way to delete these rows. just to delete 300,000 rows it takes 30 min. also i don't want to truncate the table.
any help would be appreciated
Thanks
View 9 Replies
View Related
May 14, 2008
Hi Teachies,
I am using SQL Server Standard Edition with good HardWare configuration.
In one of table i am inserting around 25 millions records and that takes time around more than 3 hrs.
same thing is happening while fetching records from that table.
this database contains only single file group i.e primary
and that table contains .. Clustered as well as non clustered index.
it doesnot have any Triggers.
How do i increase this performance.
Paritioning of table cannot be use in SQL Server Standard Edition.
Or Dropping all non clustered index before insert operation will improve my performance.
Please suggest me.
Thanks
Rajesh Varma
View 17 Replies
View Related
Apr 25, 2008
Hi,
My full text search on 2 millions records is taking time to show the result.
I have created full text catalog in RAM drive to make the retrival process faster.
But still its taking more than 1 minute to get the matching pattern.
I am using SQL server 2005. I have 2 columns (id,text) in my table..
This is my unique index script
USE [SAMPLE]
GO
CREATE UNIQUE NONCLUSTERED INDEX [ui_productid] ON [dbo].[Products]
(
[id] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]..
This is my primary key index script..
USE [SAMPLE]
GO
ALTER TABLE [dbo].[Products] ADD CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]..
This is my query..
SELECT D.[id], D.productname
FROM dbo.Products AS D
WHERE CONTAINS(productname, 'ford')
What should i do to show the result in 3-4 seconds.
View 1 Replies
View Related
Aug 5, 2015
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.
View 5 Replies
View Related
Jan 25, 2008
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 will appreciate any help you could provide me.
Thanks.
View 15 Replies
View Related
Aug 6, 2013
In my example, i have a table as below:
Time|Machine
2013-08-05 10:12:00 |M1
2013-08-05 10:15:00 |M1
2013-08-05 10:20:00 |M1
2013-08-05 10:11:00 |M2
2013-08-05 10:15:00 |M2
2013-08-05 10:16:00 |M2
2013-08-05 10:17:00 |M2
2013-08-06 11:12:00 |M1
2013-08-05 11:42:00 |M1
2013-08-05 12:02:00 |M1
I want to have a report for every machine by time that will show how long each machine worked. The result is something like below:
M1|8 (minutes)
M2|6
M1|50
View 5 Replies
View Related
Oct 8, 2007
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.
View 1 Replies
View Related
Aug 30, 2007
Hello All,
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.
Really appreciate your help!
Thank you!
Sebastijan
View 1 Replies
View Related
May 29, 2008
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.
Thanks in advance
View 2 Replies
View Related
Oct 8, 2003
Hello
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..
Thanks!!!<%@ Page Language="C#"%>
<%@ Import Namespace="System.Data.SqlClient" %>
<script runat=server>
void Page_Load(Object sender , EventArgs e)
{
SqlConnection conPubs;
string strDelete;
SqlCommand cmdDelete;
conPubs = new SqlConnection( @"Server=localhost;Integrated Security=SSPI;database=Book" );
strDelete = "Delete tblbook Where Posted_Date =???????????";
cmdDelete = new SqlCommand( strDelete, conPubs );
conPubs.Open();
cmdDelete.ExecuteNonQuery();
conPubs.Close();
Response.Write("Records Deleted!");
}
</script>
//
</script>
<html>
<head>
</head>
<body>
<form runat="server">
<!-- Insert content here -->
</form>
</body>
</html>
View 6 Replies
View Related
Aug 4, 2004
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.
Thanks
View 6 Replies
View Related
Nov 15, 2013
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.
Sample Data:
CREATE TABLE #t (LoanID VARCHAR(5), BorrowerName VARCHAR(20), BorrowerOrder INT);
GO
INSERT INTO #t VALUES
('::E', 'John Smith', 0)
, ('::E', 'Jane Smith', 1)
, ('::E', 'Rob Jackson', 2)
, ('AF_CF', 'Sloan Burton', 1)
[code]...
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?
View 8 Replies
View Related
May 14, 2008
Hi Guys,
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.
Please help me out.
Regards
View 3 Replies
View Related
May 25, 2008
I have a DateTime field, I need to find out how many records are in 8am-11am, 12pm-5pm, 6pm-7am regardless of date. How can I do this?
Kamran Shahid
Sr. Software Engineer(MCSD.Net)
www.netprosys.com
View 2 Replies
View Related
Dec 21, 2005
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.
View 4 Replies
View Related
Jul 20, 2005
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!
View 1 Replies
View Related
Jun 7, 2012
I have a Select TOP 1 statement which works fine, but i also want to count the total records at the same time. This is my SQL
Code:
SELECT TOP (1) dbo.ClientOffers.ProductID, dbo.ClientOffers.ClientID, dbo.Offers.Offertext, dbo.Offers.Offerlink, dbo.Offers.Image, dbo.Offers.Cost
FROM dbo.ClientOffers INNER JOIN
dbo.Offers ON dbo.ClientOffers.OfferID = dbo.Offers.OfferID
WHERE (dbo.ClientOffers.ProductID = 1284) AND (dbo.ClientOffers.ClientID = 0)
How do i also count the total records?
View 7 Replies
View Related
Oct 29, 2015
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,
emp_idstart_date end_date
1001 1998-01-01 2003-06-21
1002 1999-05-23 2008-03-28
1001 2004-08-19 NULL
1003 2004-10-12 2006-07-25
1004 2005-04-28 NULL
1002 2008-11-02 NULL
1003 2009-05-17 NULL
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.
View 9 Replies
View Related
May 8, 2008
I'm looking for a way to link records that would be related to an employee that worked during that shift.
we have 2 shifts days and nights going from 6:00AM to 6:00PM
we have records that are written to a table as events happen on equipment on site.
"Equip_Trans"
"Start_time" "Equip_ID" "Shift_date" "Status" "End_Time"
Date-Time, int, Date, varchar(3), Date-time
5/8/2008 4:23:25 AM, 0200, 5/8/2008, M20, 5/8/2008 5:15:34 AM
5/8/2008 2:18:45 AM, 0206, 5/8/2008, M24, 5/8/2008 3:10:03
5/8/2008 5:15:34 AM, 0200, 5/8/2008, M13, 5/8/2008 7:24:36 AM
5/8/2008 3:10:03 AM, 0206, 5/8/2008, M20, Null
5/8/2008 7:24:36 AM, 0200, 5/8/2008, O21, 5/8/2008 7:55:34 AM
5/8/2008 7:55:34 AM, 0200, 5/8/2008, M24, 5/8/2008 8:36:34 AM
I have another transaction table "Operator_Trans"
"Operator" "Date" "Shift"
jane Doe, 5/6/2008, 1
Pete Who, 5/6/2008, 2
Sam What, 5/7/2008, 1
Pete Who, 5/7/2008, 2
john Doe, 5/8/2008, 1
jane Doe, 5/8/2008, 2
and last a third table "Shift"
Shift, Start_Time, End_Time
1, 6:00AM, 6:00PM
2, 6:00PM, 6:00AM
I need out put that will show all Equipment status's that started during the shifts worked by an operator
Pete Who, 5/8/2008 4:23:25 AM, 0200, 5/8/2008, M20, 5/8/2008 5:15:34 AM
Pete Who, 5/8/2008 2:18:45 AM, 0206, 5/8/2008, M24, 5/8/2008 3:10:03 AM
Pete Who, 5/8/2008 5:15:34 AM, 0200, 5/8/2008, M13, 5/8/2008 7:24:36 AM
Pete Who, 5/8/2008 3:10:03 AM, 0206, 5/8/2008, M20, Null
john Doe, 5/8/2008 7:24:36 AM, 0200, 5/8/2008, O21, 5/8/2008 7:55:34 AM
john Doe, 5/8/2008 7:55:34 AM, 0200, 5/8/2008, M24, 5/8/2008 8:36:34 AM
I am not completely sure the direction I should start off with. I have some SQL knowlege but I would still consider myself new.
Thanks
View 1 Replies
View Related
Nov 19, 2015
I need a boost to understand how to get started on solving this SQL Query.
TicketId Seq StatusDateTime
T10001 1 2015-05-04 15:54:00.000
T10001 2 2015-05-04 16:35:00.000
T10001 3 2015-05-04 16:42:00.000
T10001 4 2015-05-05 08:19:00.000
T10002 1 2015-05-04 15:56:00.000
T10002 2 2015-05-04 15:57:00.000
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.
View 6 Replies
View Related
Mar 26, 2012
I have 2 tables:
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
View 2 Replies
View Related
May 26, 2004
hi everybody,
i have an application that generate a lot rows from 1 mellion to 2 mellions rows
i wana insert this record in MS SQL server in a fast way
i am currentlly loop through this records while it is loaded in dataset
building a command text that generate insert query for each row
and run it against SQL server
but it takes a lot of time to be finished
is there r a way to bulk insert this data?
thanks 4 ur help.
Bolos
View 7 Replies
View Related
