Quick Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Quick Query

Author

Topic

theroversreturn
Starting Member

12 Posts

Posted - 2010-08-30 : 06:28:37

Hi,
I have a query in which I want to return certain records between 2 different years. The problem I have is that for certain years I have 2 records and all I want is the one with the latest yearend so if there was one for 1/1/01 and another for 31/12/01 I want to return only the latter one. My query goes as follows
________
SELECT f1.*
FROM
dbo.f_records f1
WHERE year(f1.yearend_datetime) > 1991
AND year(f1.yearend_datetime) <= 1994
ORDER by yearend_datetime DESC
________

All help greatly appreciated.
Thanks

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-08-30 : 06:44:23

I think you might have to give some sample data for this one.

For instance: What key splits the records that you want to report "one with the latest yearend"

I'm assuming that the result set contains a lot of seperate objects (people, orders, whatever) and you want only the most recent per object.

From a strictly performance point of view your WHERE clause is badly formed. You can't use any index on [yearEnd_datetime] (because you are using a function on the column)

This is analogous and can use an index


SELECT
	f1.*
FROM
	dbo.f_records AS f1
WHERE
	f1.[yearEnd_dateTime] >= '1992-01-01T00:00:00.000'
	AND f1.[yearEnd_dateTime] < '1995-01-01T00:00:00.000'
ORDER BY
	f1.[yearEnd_dateTime] DESC

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

theroversreturn
Starting Member

12 Posts

Posted - 2010-08-30 : 07:01:10

Basically the table just holds a record_id, version, yearend_datetime field and a couple of other minor fields. The record_id then links to another table where i save the relevant data for each record. I just want to return one record for each year though and can't get my head around how to pull just this one.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-08-30 : 07:13:19

Can you post some simple sample data? just three different distinct record_Id

So maybe 6 or 7 lines of data would do and explain which line you want to find.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Sachin.Nand

2937 Posts

Posted - 2010-08-30 : 07:15:50

Something like this


SELECT max(f1.yearend_datetime),year(f1.yearend_datetime)
FROM
dbo.f_records f1
WHERE year(f1.yearend_datetime) > 1991
AND year(f1.yearend_datetime) <= 1994
group by  year(f1.yearend_datetime)
ORDER by yearend_datetime DESC

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

theroversreturn
Starting Member

12 Posts

Posted - 2010-08-30 : 07:26:56

Record_id Version Yearend_datetime .......
12588 1 1994-12-31 00:00:00.000
12586 1 1993-12-31 00:00:00.000
12587 1 1993-01-01 00:00:00.000
12585 1 1992-12-31 00:00:00.000.........

Basically I want all rows returned except the third one (ie. one with yearend of 1993-01-01).

Got an error from you response Idera. Thanks for the quick replies as well by the way.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-08-30 : 07:30:07

why don't you want the third one. ALl the record_Id's are different. Do you not want the most recent per record_Id????

Or do you just want the most recent line for each year? Whatever record_Id that points to?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

theroversreturn
Starting Member

12 Posts

Posted - 2010-08-30 : 07:34:31

I just want the most recent per year no matter what the record_id is.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-08-30 : 07:36:05

Do you have SQL server 2005 or newer (2008). If so you could do this:

(Ive wrapped your data in an example table variable -- you can just cut and paste into a query window to play around)

This won't work if you are running SQL SERVER 2000 or you database is in compatibility 80


DECLARE @foo TABLE (
	[record_Id] INT
	, [version] INT
	, [yearEnd_dateTime] DATETIME
	)
INSERT @foo ([record_Id], [version], [yearEnd_dateTime])
      SELECT 12588, 1, '1994-12-31 00:00:00.000'
UNION SELECT 12586, 1, '1993-12-31 00:00:00.000'
UNION SELECT 12587, 1, '1993-01-01 00:00:00.000'
UNION SELECT 12585, 1, '1992-12-31 00:00:00.000'


SELECT
	f.[record_Id]
	, f.[version]
	, f.[yearEnd_dateTime]
FROM
	(
	SELECT
		[record_Id] AS [record_Id]
		, [version] AS [version]
		, [yearEnd_dateTime] AS [yearEnd_dateTime]
		, ROW_NUMBER() OVER (
			PARTITION BY
				YEAR([yearEnd_dateTime])
			ORDER BY
				[yearEnd_dateTime]DESC
			)
			AS [rankInYear]
	FROM
		@foo
	)
	AS f
WHERE
	f.[rankInYear] = 1

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

theroversreturn
Starting Member

12 Posts

Posted - 2010-08-30 : 07:44:04

Ok Charlie I will have a play around with that. Its 2005 we are running. Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-31 : 06:13:49

quote:
Originally posted by Idera

Something like this

SELECT max(f1.yearend_datetime),year(f1.yearend_datetime)
FROM
dbo.f_records f1
WHERE year(f1.yearend_datetime) > 1991
AND year(f1.yearend_datetime) <= 1994
group by year(f1.yearend_datetime)
ORDER by yearend_datetime DESC

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

If the date column has index, it wont be used if use use year function. See the approach used by TC

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources