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.
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 RelatedHi
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
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...
View 3 Replies View Relatedi 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]....
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
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 RelatedI'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
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
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
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
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
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.
I'm writing a program that allows users to upload a csv file. This file is then seperated into 4 datatables based on certain criteria then each datatable is uploaded into my database. I'm essentially adding new rows to the datatables then running an update command on each using a tableadapter. The problem is that these csv files can be large and can end up with 4000+ new records being added to the database and the update commands take a while to do it. I've sat for about five minutes on one run while it updated. I put in some time variable to see where all the time is spent and it takes only seconds to parse the data and seperate into the datatables, but minutes on the update commands. Is there a more efficient way to insert this much data?
View 4 Replies View RelatedHello,
What is the fastest way to update 20million records in our database.
I have tried to do a simple update statement like this:
update trail_log with (tablockx, holdlock)
set trail_log .entry_by = users.user_identity
from users
where trail_log.entry_by = users.user_id
but it take 10 plus hours to run since it cannot commit the transactions until the very end. So was was thinking that I need to commit in batch like after 50K but that is slow as well.
Set rowcount 50000
Declare @rc int
Set @rc=50000
While @rc=50000
Begin
Begin Transaction
update trail_log With (tablockx, holdlock)
set trail_log.entry_by = users.user_identity
from users
where trail_log.entry_by = users.user_id
and trail_log.entry_by not like '%[0-9]%'
Select @rc=@@rowcount
--Commit the transaction
Commit
End
go
I have let the above statement run for 1.5 hours and it only update 450000 rows. Any ideas...
Maybe I'm doing it wrong. Please Help!!
Very often we have 50'000+ rows which you need to pull values from different source (e.g. CityNames from citycode in Excel file). Currently we are using Lookup - but the questions is which of those 3 options is best in performance wise?
View 24 Replies View RelatedSample 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.
Hello
Im searching for a solution to set all matrix row or cell the same height.
it schoud looks like this example:
This is a simple matrix
test a
text b
text c
text d
text e
text f
text g
This is a matrix with all the same row-height.
test a
text b
.
text c
.
.
text d
text e
text f
text g
.
.
Thx you a lot
We are using a presence checker to register when our employees show up at work, when they leave and whenever they change their working section.
The raw data has this format:
TABLE
-----
TIMESTAMP USERID SECTION INCIDENCE
------------------------------------------------------
2014-06-27 14:52:40.000 77675669 7 0
2014-06-27 15:08:33.000 77675669 6 8
2014-06-27 15:30:58.000 77675669 7 12
2014-06-27 18:52:00.000 77675669 11 15
2014-06-27 19:47:38.000 77675669 7 12
2014-06-27 23:30:18.000 77675669 7 0
In order to determine the exact time people had been working in the sections I have to match every record with the record of the successive timestamp I do:
SELECT TIMESTAMP as START, USERID, SECTION, INCIDENCE, MIN(TIME_END) as STOP
FROM (
SELECT *, TABLE2.TIMESTAMP as TIME_END
FROM TABLE
LEFT OUTER JOIN TABLE as TABLE2 on TABLE.USERID = TABLE2.USERID and
TABLE2.TIMESTAMP > Table.TIMESTAMP and
[Code] ....
How do I get the incidence of the successive timestamp in my query?
The key trick is that I first do a self join which increases the amount of records, including senseless matches from the first timestamp with the last one of that day. Afterwards I group by all columns of TABLE in order to get only the successive timestamp.
But once I include TABLE2.INCIDENCE in the GROUP BY clause, of course the query stops working correctly.
I can't use the TIMESTAMP isself as it is not singular (there are several terminals in the plant); and I prefer not to use Timestamp in combination with Userid, as the userid is actually not stored directly and has to be retrieved through a couple of joined tables. Any smarter way to include a second column once the successive timestamp has been determined.
Hi ,
I've got two tables.. the first table carried a ProductID, and amongst other things a TradePrice
The other tbl carries a ProductID, a IndivPrice and a CustomerID
The second tbl lists prices for products for indiv Customers.
My Query needs to bring back ALL the products from the first tbl...
It also needs to show the TradePrice for that product.
I need to join my query to the second tbl...
And finally, if the second tbl has a price for that product AND the customerID is the same as one I pass into the query.. show that price also..
So here's my first query:
SELECT dbo.Products.ProductID, ProductName, ProductTradePrice, IndivPrice, dbo.Trade_PriceLists.CustomerID AS PLCustomerID FROM dbo.Products LEFT OUTER JOIN dbo.Trade_PriceLists ON dbo.Products.ProductID = dbo.Trade_PriceLists.ProductID WHERE (ProductType = 'Trade' OR ProductType = 'Both') AND (Replace(Lower(ProductBrand),' ','') = 'brandname') AND (CustomerID IS NULL OR CustomerID = 'teste' OR CustomerID = '') ORDER BY TradeOrder
I thought that would work, but what happens is that, if that particular customer has no indiv prices set.. then it only shows the ones that have no records at all in that second tbl..
So unless there is a record for a particular product in that second tbl and it doesn't have a CustomerID assigned to (which would never happen as that tbl is only every for indiv customer prices) then it doesn't show.
Examples:
First Tbl
ProductID Name TradePrice
1 Jumper £1.00
2 Jeans £3.00
3 Shoes £5.00
4 Hat £2.00
Second Tbl
ProductID CustomerID IndivPrice
1 teste £0.50
2 othercustomer £2.50
3 teste £4.50
What I want in the results is:
ProductID ProductName TradePrice IndivPrice CustomerID (PLCustomerID)
1 Jumper £1.00 £0.50 teste
2 Jeans £3.00
3 Shoes £5.00 £4.50 teste
4 Hat £2.00
See? - The 2nd product should not get an indiv price as although it's in that second tbl, the customerID assigned to it is different. The 4th product should not get an indiv price as it's not in that second tbl at all.
however, with my query above I'd only get Products 1and 3... and if I did a query on a customer with no indiv prices I'd only get product 4 as it's not in the indiv at all...
HELP!!!!!
I have some data that looks like this
Col1 Col2 SumCol
XXX,YYY, 5
XXX,ZZZ,6
ZZZ,XXX,7
YYY,XXX,2
I want to do a inner join on that data so I get this
XXX,YYY, 7
XXX,ZZZ,13
Right now I'm using a CTE set to do that, is there a better way??
I have 2 tables:
create table #InsuredLoc (
PolGK int,
InsuredLocGK varchar(20),
InsuredLocEffDt datetime,
InsuredLocType varchar(20),
InsuredLocStatus varchar(20),
InsuredLocAddress varchar(50),
PolEdrGk varchar(20),
[Code] ...
I will need to join the #InsuredLoc table to the #PolicyEndorsement table using PolGk and PolEdrGk and get the min(BkgDt) and min(PolEdrRowUpdateDt) for the distinct list of InsuredLocType, InsuredLocStatus, and InsuredLocAddress fields from the #InsuredLoc table above.
 I will also need the min(InsuredLocEffDt) and the max(InsuredLocUpdateDt) from the #InsuredLoc table for those records. So after the first run, i should get the following:
I tried to use CTE's with ranking, but some records are dropping off and I'm not sure why. Â
I am having problem with Inner joining of tables
my query is..
Select j.jobSubject,e.eOrganization ,jv.JobClick,j.jobID from dbo.tbl_Jobs jinner join dbo.tbl_Employer e on e.mId=j.jobCreatedByIDinner join dbo.tbl_JobView jv on jv.JobID=j.jobID
order by jv.JobClick desc This query returns 1 to many records
But I need the query should return 0 to many record . .yes I have already know inner join does not handle my problem so plz suggest me which type of join would solve my problem
I have my SQL call:
SELECT CallLog.CallID, Journal.HEATSeqFROM CallLog INNER JOIN Journal ON CallLog.CallID = Journal.CallID
There are multiple enteries in the Journal table for every entry in the CallLog table, so I receive multiple records:
CallID HEATSeq00000164 983290904 00000164 983291548 00000164 983295209 00000231 984818271 00000231 985194317 00000231 985280248
I only want to return the LAST record in the Journal table, so the output will be:
CallID HEATSeq00000164 983295209 00000231 985280248
Can this be done directly in the SQL call?
Hi All,
I need a bit of help with a join. I have 2 tables :
TradeSummary
has fields : SymbolID, CurrentPrice, TotalValue
Trades
has fields : SymbolID, TradeID, ExecutionTime, TradeValue
TradeSummary has one entry for each SymbolID, while Trades contains one or more entries per SymbolID
and what I want to retreive is :
For every item in TradeSummary get CurrentPrice, TotalValue from TradeSummary
and also get TradeValue from Trades for the record for max(ExecutionTime)
tables are joined on TradeSummary.SymbolID = Trades.SymbolID
Every attempt of mine so far returns multiple rows for each SymbolID - I want only one row per SymbolID
thanks in advance
[URL]
I had a problem before of not been able to find the rows with 0 values. I've now managed this although it's brought up duplicate rows due to the discounts been different on the same Mfr_part_number. I tried using the max function on the isnull (Exhibit_Discount.Discount, 0.00) AS Discount instead but to no success. i think i maybe something to do with PK keys not been used in the set-up of the database.
Use Sales_Builder
Go
SELECT DISTINCT
GBPriceList.[Mfr_Part_Num],
[Code]....
Is there a way to create one field from multiple records using sql.For exampleTable 1John 18Peter 18David 18Now I want an sql query that when executed will return a field thatlooks like thisQuery1John Peter DavidSo basically it will return one record with all the name in one field
View 4 Replies View RelatedHi,
I have the folowing 3 (SS2005) tables:
CREATE TABLE [dbo].[tblSubscription](
[SubscriptionID] [int] IDENTITY(1000000,1) NOT NULL,
[SubscriberID] [int] NOT NULL,
[Status] [int] NOT NULL,
[JournalID] [int] NOT NULL,
CREATE TABLE [dbo].[tblTransaction](
[TransactionID] [bigint] IDENTITY(100000000,1) NOT NULL,
[TransactionTypeID] [int] NOT NULL,
[SubscriptionID] [int] NOT NULL,
[Created] [datetime] NOT NULL,
CREATE TABLE [dbo].[tblMailing](
[MialingID] [bigint] IDENTITY(1000000000,1) NOT NULL,
[SubscriptionID] [int] NOT NULL,
[MailTypeID] [int] NOT NULL,
[MailDate] [datetime] NOT NULL
So for each subscription there can be 1 or more transactions and 0 or
more mailings, and the mailings are not necassarily related to the
transactions. What I am having difficulty doing is this:
I wish to select tblMailing.MailingID, tblMailing.MailDate,
tblMailing.SubscriptionID (or tblSubscription.SubscriptionID),
tblSubscription.SubscriberID, tblSubscription.Status,
tblTransaction.TransactionID, tblTransaction.Created, but I only wish
to retrieve rows from the transaction table where
tblTransaction.Created is the latest dated transaction for that
subscription.
I.E. (maybe this makes more sense..:) I wish to select all rows from
tblMailing along with each mailing's relevent subscription details,
including details of the LATEST TRANSACTION for each of those
subscriptions.
I am currently working along the lines of MAX(tblTransaction.Created)
and possibly GROUP BY in a subquery, but cannot quite figure out the
logic.
Any help appreciated.
Thanks, KoG
Hi All,
Can anybody tell me which of the following is the most efficient query if i have huge tables.
SELECT *FROM Tab1 Inner join Tab2 ON Tab1.Col1 = Tabl2.Col1 AND Tab1.Col1 = 5
OR
SELECT *FROM Tab1 Inner join Tab2 ON Tab1.Col1 = Tabl2.Col1WHERE Tab1.Col1 = 5
As long as i explored this, Sql Server Query Execution Plan shows the similar cost for both cases. Is there any difference?
If yes why?
Thanks in advance.
Regards,
Sulaman Riaz
SELECThorse1.horse_name AS Child_Horse,
horse2.horse_name AS Sire_Horse,
event.event_name
FROMhorse AS horse1 INNER JOIN horse AS horse2 ON horse1.sire = horse2.horse_id,
entry,
event
WHEREhorse1.horse_id = entry.horse_id
ANDentry.event_id = event.event_id
[Code] ....
I need to list the names of the horses that has entered in the same event as its sire and above is the result that I've got.I'm meant to get only the first two lines of the result since Boxer is father of Flash and Star and they all entered the same event called Dressage. (below is the evidence and refer to the first three rows)
SELECTentry.event_id,
entry.horse_id,
horse.horse_name
FROMentry,
horse
WHEREhorse.horse_id = entry.horse_id
[Code] ....
with my code in the first box, what should I add in order to get the result that I want?
Hi, all experts here,
Thank you very much for your kind attention.
I got a problem with the records returned by inner join query. It is weird as in table a I with records r1, and in table b with records r2 (r1>r2), but the returned record number r3 is greater than r1? That is so strange. As the reasonable records returned should be less than r1?(inner join on attribute a). What probably is the cause of the problem? I am looking forward to hearing from you and thank you very much.
With best regards,
Yours sincerely,
I have a sql statement that joins two tables and I get back a few thousand records when I run it in query tool in management studio.
But when I use SSIS merge join to join the two tables my output is 0 records.
I did sort the key column in both tables by setting 'sortkeyposition' property to 1 in advanced editor for output of both tables.
however the merge join returns nothing to my destination tables. Also I am doing a inner join. The task runs without error but returns nothing as well.. any ideas?
I have 100k + records in table usr_table and I want to select all of them that do not have the same ID as the ID's indicated in table Usr_Type_Data
Select * From usr_table As t1 Join Company_Sales.dbo.Usr_Type_Data As t2 ON t2.user_id = t1.user_id Where CustomerTypeId <> 7
I get 0 returned.
If I change it to:
Select * From usr_table As t1 Join Company_Sales.dbo.Usr_Type_Data As t2 ON t2.user_id = t1.user_id Where CustomerTypeId = 7
I get all records from table Usr_Data_Type returned.
Do I need to specify a special Join type?
Or perhaps change the T-SQL around?