April 27, 2011 at 10:33 am
This my table :
CREATE TABLE TAB1
(
curr tinyint NOT NULL ,
ddate smalldatetime NOT NULL ,
rate decimal(15, 4) NOT NULL ,
primary key (curr, ddate)
)
If "rate" didn't change in comparision with previous value of "ddate" then table doesn't contain new record for that unchanged date.
I need to write a query that returns all "currency" for the specified date. This query MUSTN'T contain any aggregate functions and no TOP/BOTTOM words are allowed there as well. Any ideas how to write this query?
April 27, 2011 at 10:58 am
Igor Savin (4/27/2011)
This my table :
CREATE TABLE TAB1
(
curr tinyint NOT NULL ,
ddate smalldatetime NOT NULL ,
rate decimal(15, 4) NOT NULL ,
primary key (curr, ddate)
)
If "rate" didn't change in comparision with previous value of "ddate" then table doesn't contain new record for that unchanged date.
I need to write a query that returns all "currency" for the specified date. This query MUSTN'T contain any aggregate functions and no TOP/BOTTOM words are allowed there as well. Any ideas how to write this query?
With those arbitrary restrictions, this has to be homework or a challenge. Igor, how about some sample data to test against? Make it up. It must contain several groups (which might otherwise be aggregated) and several elements within each group.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 27, 2011 at 12:23 pm
ddate curr rate
----------------------- ---- ---------------------------------------
2000-01-01 00:00:00 1 5
2000-01-02 00:00:00 1 6
2000-01-02 00:00:00 2 3
2000-01-03 00:00:00 2 4
2000-01-14 00:00:00 1 7
2000-01-15 00:00:00 3 8
THis is the sample I gonna test the query against.
April 27, 2011 at 12:33 pm
Igor check out the first link in my signature to show how to make your test data consumable.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 27, 2011 at 10:03 pm
Ok, here's my table and data definition:
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
(
curr tinyint NOT NULL ,
ddate smalldatetime NOT NULL ,
rate decimal(15, 4) NOT NULL ,
primary key (curr, ddate)
)
--===== Setup any special required conditions especially where dates are concerned
SET DATEFORMAT DMY
--===== Insert the test data into the test table
INSERT INTO #mytable (curr,ddate, rate)
SELECT '1','Jan 1 2000 12:00AM','5' UNION ALL
SELECT '2','Jan 1 2000 12:00AM','55' UNION ALL
SELECT '1','Jan 2 2000 12:00AM','6' UNION ALL
SELECT '2','Jan 2 2000 12:00AM','3' UNION ALL
SELECT '2','Jan 3 2000 12:00AM','4' UNION ALL
SELECT '1','Jan 14 2000 12:00AM','7' UNION ALL
SELECT '2','Jan 15 2000 12:00AM','8'
April 27, 2011 at 10:29 pm
And here're some pseudo-queries and the results I expect from them:
select curr, rate
from (................)
where ddate='01-01-2000'
curr rate
---- ---------------------------------------
1 5.0000
2 55.0000
select curr, rate
from (................)
where ddate='02-01-2000'
curr rate
---- ---------------------------------------
1 6.0000
2 3.0000
select curr, rate
from (................)
where ddate='03-01-2000'
curr rate
---- ---------------------------------------
1 6.0000
2 3.0000
select curr, rate
from (................)
where ddate='04-01-2000'
curr rate
---- ---------------------------------------
1 6.0000
2 3.0000
select curr, rate
from (................)
where ddate='05-01-2000'
curr rate
---- ---------------------------------------
1 6.0000
2 3.0000
select curr, rate
from (................)
where ddate='13-01-2000'
curr rate
---- ---------------------------------------
1 6.0000
2 3.0000
select curr, rate
from (................)
where ddate='14-01-2000'
curr rate
---- ---------------------------------------
1 7.0000
2 3.0000
select curr, rate
from (................)
where ddate='15-01-2000'
curr rate
---- ---------------------------------------
1 7.0000
2 8.0000
select curr, rate
from (................)
where ddate='16-01-2000'
curr rate
---- ---------------------------------------
1 7.0000
2 8.0000
April 28, 2011 at 1:52 pm
Thanks Igor, that'll help tremendously. There are a few ways to completely avoid aggregation methodology and using top 1 cross apply techniques as well... but they're uncommon, at best. A few approaches are using Tally or Calendar tables or Loop propogation into a temp table, maybe even CTE recursion within a limit of date ranges (I can vaguely see that one, I'd have to test if it's viable).
What chapter is this question from, what topic is that chapter on? If we're going to help you locate the solution, we're going to need to know what they're trying to teach you at the moment. No TOP and no AGG functions is against most best practices, so we might as well know what we're up against.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 28, 2011 at 1:59 pm
In addition to the questions which Craig has asked, can you confirm which version of SQL Server you are using? This is the SQL Server 7, 2000 section.
Cheers.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 30, 2011 at 2:43 am
OP appears to have lost interest :ermm:
There's a solution to this without using TOP or any aggregate functions: variable assignment.
This query
DECLARE @curr tinyint, @ddate smalldatetime, @rate decimal(15, 4)
SELECT @curr = curr, @ddate = ddate, @rate = rate
FROM RatesTable
WHERE ddate <= '02-01-2000'
AND curr = 1
ORDER BY ddate asc
SELECT curr = @curr, ddate = @ddate, rate = @rate
returns values from the last row of two rows. The ORDER BY determines which row is last.
So whatever date you use in the WHERE clause, the most recent row will be returned, whether or not it matches the date.
So, how about using this little query as a correlated subquery, part of a larger construct, so it returns the rate for any currency/date? Well, you can't mix variable assignment queries with data retrieval queries. You can easily get around this by using a function, passing in the date and currency and returning the rate.
Here's the function:
CREATE FUNCTION [dbo].[GetMostRecentRateRow]
(@curr tinyint, @ddate smalldatetime)
RETURNS decimal(15, 4)
AS
BEGIN
DECLARE @rate decimal(15, 4)
SELECT @rate = rate
FROM RatesTable
WHERE ddate <= @ddate
AND curr = @curr
ORDER BY ddate asc
RETURN @rate
END
Ok, so the function will return the most recent rate for a curr/ddate, but the table has date 'holes', not every date is represented in the table. If we want to pass any currency and a date into the function as part of a query then we need a sequence of currency/dates without gaps. Craig suggested a tally table, so here it is:
SELECT c.curr, ddate = DATEADD(dd,1-t.n,DATEADD(dd,0,DATEDIFF(dd,0,GETDATE())))
FROM Testing.dbo.Tally t
CROSS JOIN (SELECT DISTINCT curr FROM RatesTable) c
WHERE t.N < 4500
t.N < 4500 restricts the number of rows output (or days history) and just covers the dates in the sample data.
Putting it all together yields a simple query which matches the OP's requirements:
SELECT curr, ddate, Rate
FROM (
SELECT c.curr, ddate = DATEADD(dd,1-t.n,DATEADD(dd,0,DATEDIFF(dd,0,GETDATE()))),
Rate = dbo.GetMostRecentRateRow(c.curr, DATEADD(dd,1-t.n,DATEADD(dd,0,DATEDIFF(dd,0,GETDATE()))))
FROM Testing.dbo.Tally t
CROSS JOIN (SELECT DISTINCT curr FROM RatesTable) c
) d
WHERE ddate = '14-01-2000'
Horribly inefficient compared to using aggregate functions but it works.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply