Friday, March 9, 2012
Join to a single date rate table?
Create Table XRates
(
EffectiveDate smalldatetime,
FromCury char(3),
ToCury char(3),
Rate float
)
and Transaction table like
Create Table Trans
(
TranDate smalldatetime,
TranCury char(3),
TranAmt float
)
What would be the best SQL query to join from the Transaction table to the
correct exchange rate based on the transaction date? Any sugestions?
Thanks in advanceHere is one way (SQL Server 2005):
WITH Transactions
AS
(SELECT T.TranDate, T.TranCury, T.TranAmt,
R.ToCury, R.Rate,
ROW_NUMBER() OVER(
PARTITION BY R.FromCury
ORDER BY R.EffectiveDate DESC) AS seq
FROM Trans AS T
JOIN XRates AS R
ON T.TranCury = R.FromCury
AND T.TranDate >= R.EffectiveDate)
SELECT TranDate, TranCury, TranAmt, ToCury, Rate
FROM Transactions
WHERE seq = 1;
HTH,
Plamen Ratchev
http://www.SQLStudio.com
Wednesday, March 7, 2012
join SmallDateTime column very slow
I posted this on programming group, but hasn't got any reply yet.
I have a table and the definition is like:
CarTable(
[RowNumber] [int] IDENTITY(0,1) NOT NULL,
ModelID,
MakeID,
RegisterDate smallDateTime null
PRIMARY KEY CLUSTERED
(
[RowNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
ModelID and MakeID are foreign keys from a reference table (call it
refTable here).
I created an idex on MakeID, modelID and RegisterDate as:
CREATE UNIQUE CLUSTERED INDEX
[IX_vwVehicleMain_ReportingAggregate_Aggregate] ON [CarTable]
(
[MakeID] ASC,
[ModelID] ASC,
[RegisterDate] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB
= OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON
[INDEX_FG]
The CarTable is quite big (50 million records)
When I do query such as
select MakeID, RefTable.MakeName, ModelID, RefTable.ModelName,
RegisterDate from CarTable inner join RefTable
on (CarTable.MakeID= RefTable.MakeID and CarTable.ModelID= RefTable.ModelID)
It is fairly quick - 2 seonds
However, if I put RegisterDate in the join condition, it becomes very
slow. For example:
select MakeID, RefTable.MakeName, ModelID, RefTable.ModelName,
RegisterDate from CarTable inner join RefTable
on (CarTable.MakeID= RefTable.MakeID and CarTable.ModelID= RefTable.ModelID and
CarTable.RegisterDate > '01/01/1980')
This one is much slower 2 minutes.
My index cover MakeID, ModelID and RegisterDate, so why it is so slow?
Do I need to add an non-clustered index just for RegisterDate?
Many ThanksReplied to in .programming.
Please do not multi-post. If you want to ask the same question in
several newsgroups then the prefered method is to cross-post. This
prevents double answers (and double effort).
--
Gert-Jan
DAXU@.hotmail.com wrote:
> Hello,
> I posted this on programming group, but hasn't got any reply yet.
> I have a table and the definition is like:
> CarTable(
> [RowNumber] [int] IDENTITY(0,1) NOT NULL,
> ModelID,
> MakeID,
> RegisterDate smallDateTime null
> PRIMARY KEY CLUSTERED
> (
> [RowNumber] ASC
> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
> = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> ModelID and MakeID are foreign keys from a reference table (call it
> refTable here).
> I created an idex on MakeID, modelID and RegisterDate as:
> CREATE UNIQUE CLUSTERED INDEX
> [IX_vwVehicleMain_ReportingAggregate_Aggregate] ON [CarTable]
> (
> [MakeID] ASC,
> [ModelID] ASC,
> [RegisterDate] ASC
> )WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB
> = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
> ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON
> [INDEX_FG]
> The CarTable is quite big (50 million records)
> When I do query such as
> select MakeID, RefTable.MakeName, ModelID, RefTable.ModelName,
> RegisterDate from CarTable inner join RefTable
> on (CarTable.MakeID= RefTable.MakeID and CarTable.ModelID=> RefTable.ModelID)
> It is fairly quick - 2 seonds
> However, if I put RegisterDate in the join condition, it becomes very
> slow. For example:
> select MakeID, RefTable.MakeName, ModelID, RefTable.ModelName,
> RegisterDate from CarTable inner join RefTable
> on (CarTable.MakeID= RefTable.MakeID and CarTable.ModelID=> RefTable.ModelID and
> CarTable.RegisterDate > '01/01/1980')
> This one is much slower 2 minutes.
> My index cover MakeID, ModelID and RegisterDate, so why it is so slow?
> Do I need to add an non-clustered index just for RegisterDate?
> Many Thanks
join SmallDateTime column very slow
I posted this on programming group, but hasn't got any reply yet.
I have a table and the definition is like:
CarTable(
[RowNumber] [int] IDENTITY(0,1) NOT NULL,
ModelID,
MakeID,
RegisterDate smallDateTime null
PRIMARY KEY CLUSTERED
(
[RowNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
ModelID and MakeID are foreign keys from a reference table (call it
refTable here).
I created an idex on MakeID, modelID and RegisterDate as:
CREATE UNIQUE CLUSTERED INDEX
& #91;IX_vwVehicleMain_ReportingAggregate_
Aggregate] ON [CarTable]
(
[MakeID] ASC,
[ModelID] ASC,
[RegisterDate] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB
= OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON
[INDEX_FG]
The CarTable is quite big (50 million records)
When I do query such as
select MakeID, RefTable.MakeName, ModelID, RefTable.ModelName,
RegisterDate from CarTable inner join RefTable
on (CarTable.MakeID= RefTable.MakeID and CarTable.ModelID=
RefTable.ModelID)
It is fairly quick - 2 seonds
However, if I put RegisterDate in the join condition, it becomes very
slow. For example:
select MakeID, RefTable.MakeName, ModelID, RefTable.ModelName,
RegisterDate from CarTable inner join RefTable
on (CarTable.MakeID= RefTable.MakeID and CarTable.ModelID=
RefTable.ModelID and
CarTable.RegisterDate > '01/01/1980')
This one is much slower 2 minutes.
My index cover MakeID, ModelID and RegisterDate, so why it is so slow?
Do I need to add an non-clustered index just for RegisterDate?
Many ThanksReplied to in .programming.
Please do not multi-post. If you want to ask the same question in
several newsgroups then the prefered method is to cross-post. This
prevents double answers (and double effort).
Gert-Jan
DAXU@.hotmail.com wrote:
> Hello,
> I posted this on programming group, but hasn't got any reply yet.
> I have a table and the definition is like:
> CarTable(
> [RowNumber] [int] IDENTITY(0,1) NOT NULL,
> ModelID,
> MakeID,
> RegisterDate smallDateTime null
> PRIMARY KEY CLUSTERED
> (
> [RowNumber] ASC
> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
> = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> ModelID and MakeID are foreign keys from a reference table (call it
> refTable here).
> I created an idex on MakeID, modelID and RegisterDate as:
> CREATE UNIQUE CLUSTERED INDEX
> & #91;IX_vwVehicleMain_ReportingAggregate_
Aggregate] ON [CarTable]
> (
> [MakeID] ASC,
> [ModelID] ASC,
> [RegisterDate] ASC
> )WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB
> = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
> ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON
> [INDEX_FG]
> The CarTable is quite big (50 million records)
> When I do query such as
> select MakeID, RefTable.MakeName, ModelID, RefTable.ModelName,
> RegisterDate from CarTable inner join RefTable
> on (CarTable.MakeID= RefTable.MakeID and CarTable.ModelID=
> RefTable.ModelID)
> It is fairly quick - 2 seonds
> However, if I put RegisterDate in the join condition, it becomes very
> slow. For example:
> select MakeID, RefTable.MakeName, ModelID, RefTable.ModelName,
> RegisterDate from CarTable inner join RefTable
> on (CarTable.MakeID= RefTable.MakeID and CarTable.ModelID=
> RefTable.ModelID and
> CarTable.RegisterDate > '01/01/1980')
> This one is much slower 2 minutes.
> My index cover MakeID, ModelID and RegisterDate, so why it is so slow?
> Do I need to add an non-clustered index just for RegisterDate?
> Many Thanks
Friday, February 24, 2012
Join query
I have two tables Table1 and Table2
Table1 columns:
CustomerNum varchar(12)
InvDate smalldatetime
Table2 columns
CustomerNum varchar(12)
MaintDate smalldatetime
Dis float
Table1 Data (sample):
CustomerNum InvDate
995169 2/4/2002
995169 11/4/2002
995169 1/1/2003
995169 3/4/2003
995169 10/4/2003
995169 1/4/2005
995169 5/4/2005
995169 11/15/2005
Table2 Data (sample):
CustomerNum MaintDate Dis
995169 10/3/2001 1.07
995169 10/3/2002 1.1
995169 5/16/2003 1.7
995169 9/30/2003 2.9
995169 1/1/2005 2.8
995169 3/31/2005 2.95
995169 10/31/2005 2.85
I want to display customerNum, InvDate from Table1 along with applicable Dis from Table2. Dis for a customer changes form time to time. We need to pick the right one based on Table1.InvDate and Table2.MaintDate. For example, the applicable Dis for customner 995169 invoice date 2/4/2002 is 1.07
After joining the tables, I want the data to be displayed as under:
CustomerNum InvDate Dis
995169 2/4/2002 1.07
995169 11/4/2002 1.1
995169 1/1/2003 1.1
995169 3/4/2003 1.7
995169 10/4/2003 2.9
995169 1/4/2005 2.8
995169 5/4/2005 2.95
995169 11/15/2005 2.85
Any help in constructing sql qury will be appreciated. Thanks in advance.
do you want the closest invdate from table 1 that is before maintdate for each customer. it is not clear how you are wanting the data joined, please explain further.|||What I want is get the applicable Dis from Table2 for the customer's invoice date. So after joining the table2 to table1, I should display table1.customernum, table1.invdate, table2.Dis.|||Lots of subquery hell unless you add a todate on to your second table.
You can then join where invdate between table2.fromdate and table2.todate
|||What you want is described in my request http://sqljunkies.com/WebLog/simons/archive/2006/02/06/Upper_join_request.aspx|||can you not use the maintdate as the to date?|||try:
SELECT sub.CustomerNum, sub.invdate, table2.Dis
FROM table2
INNER JOIN
(
SELECT t1.CustomerNum, MAX(t1.invdate) invdate, t2.maintdate
FROM table2 t2
JOIN table1 t1
ON t1.InvDate < t2.maintdate
AND t1.CustomerNum = t2.CustomerNum
GROUP BY t1.CustomerNum, t2.maintdate
) AS sub
ON table2.CustomerNum = sub.CustomerNum
AND table2.maintdate = sub.maintdate
You can do below in SQL Server 2000 onwards:
select t1.CustomerNum, t1.InvDate
, (select top 1 t2.Dis from table2 as t2
where t2.CustomerNum = t1.CustomerNum and t2.MaintDate <= t1.InvDate
order by t2.MaintDate desc) as Dis
from table1 as t1