Selecting certain top items - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Selecting certain top items

Author

Topic

Adam Maras
Starting Member

6 Posts

Posted - 2007-07-24 : 17:57:51

[code]Table: Sessions
------------------------------------------
int PK Session | varchar(30) Description
------------------------------------------
1 | First Session
2 | Second Session
3 | Third Session
------------------------------------------

Table: Records
------------------------------------------------------
int PK RecordNo | int Session | datetime OpeningDate
------------------------------------------------------
1 | 1 | 1/1/2007
2 | 2 | 2/1/2007
3 | 3 | 3/1/2007
4 | 1 | 6/1/2007
5 | 2 | 7/1/2007
------------------------------------------------------
[/code]

I have an application that manages n (in this example, 3) sessions worth of records per period. However, at any given time, there may not be records for the entire period; I need to be able to filter out any non-year records. Also, the number of sessions is entirely dynamic. It could be 3 or 30, I won't know. In the above example, I would only want to select records 4 and 5. Unfortunately, I don't have the option to have a Periods table that gives me per-period date data. Can you help me write a query or SP/UDF that accomplishes this for me? Thank you.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-24 : 18:03:07

Can you explain what is a "non-year" record? And what is the logic to arrive at records 4 and 5 from table Records above.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-24 : 18:05:07

You want 4 and 5 back?
Why not 1 and 2?

E 12°55'05.76"
N 56°04'39.42"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-24 : 18:05:51

Here is a start

-- Prepare sample data
DECLARE	@Sessions TABLE (Session INT PRIMARY KEY, Description VARCHAR(30))

INSERT	@Sessions
SELECT	1, 'First Session' UNION ALL
SELECT	2, 'Second Session' UNION ALL
SELECT	3, 'Third Session'

DECLARE	@Records TABLE (RecordNo INT PRIMARY KEY, Session INT, OpeningDate DATETIME)

INSERT	@Records
SELECT	1, 1, '1/1/2007' UNION ALL
SELECT	2, 2, '2/1/2007' UNION ALL
SELECT	3, 3, '3/1/2007' UNION ALL
SELECT	4, 1, '6/1/2007' UNION ALL
SELECT	5, 2, '7/1/2007'

E 12°55'05.76"
N 56°04'39.42"

Adam Maras
Starting Member

6 Posts

Posted - 2007-07-24 : 18:06:01

Okay. Looking at the data above, we can see that the Session column kinda "restarts" with record 4. This is because a new period has started, so we go back to the first session. Every record before that "restart" is now an old record- I don't want to display them anymore, but they need to remain in the database. That's why 4 and 5 would appear now- 1, 2, and 3 are before the restart cutoff. And by non-year record, I meant non-period (like 1, 2, and 3 aren't of the same period as 4 and 5.)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-24 : 18:16:38

[code]-- Prepare sample data
DECLARE @Sessions TABLE (Session INT PRIMARY KEY, Description VARCHAR(30))

INSERT @Sessions
SELECT 1, 'First Session' UNION ALL
SELECT 2, 'Second Session' UNION ALL
SELECT 3, 'Third Session'

DECLARE @Records TABLE (RecordNo INT PRIMARY KEY, Session INT, OpeningDate DATETIME)

INSERT @Records
SELECT 1, 1, '1/1/2007' UNION ALL
SELECT 2, 2, '2/1/2007' UNION ALL
SELECT 3, 3, '3/1/2007' UNION ALL
SELECT 4, 1, '6/1/2007' UNION ALL
SELECT 5, 2, '7/1/2007'

-- Stage the data
;WITH Peso (RecGrp, RecordNo, Items)
AS (
SELECT (ROW_NUMBER() OVER (ORDER BY r.RecordNo) - 1) / x.Items AS RecGrp,
r.RecordNo,
x.Items
FROM @Records AS r
CROSS JOIN (
SELECT COUNT(*) AS Items
FROM @Sessions
) AS x
)

-- Do the work
SELECT p1.RecordNo
FROM Peso AS p1
INNER JOIN (
SELECT RecGrp
FROM Peso
GROUP BY RecGrp
HAVING COUNT(*) < MIN(Items)
) AS p2 ON p2.RecGrp = p1.RecGrp
ORDER BY p1.RecordNo[/code]

E 12°55'05.76"
N 56°04'39.42"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-24 : 18:17:58

What is a "restart"? What is a "period"?

E 12°55'05.76"
N 56°04'39.42"

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-24 : 18:21:45

If your sessions always start with 1 then :


Select * 
from @records 
Where Recordno >= (Select max(Recordno) 
	from @Records 
	Where session = 1
	Group by Session
	)

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources