Select records within last 14 days AND most recent

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 2000 Forums

Transact-SQL (2000)

Select records within last 14 days AND most recent

Author

Topic

Eric T Lund
Starting Member

4 Posts

Posted - 2004-08-18 : 19:06:15

I'm trying to come up with a query that will allow me to do the following:

Select all the records that have a add_date within the last 14 days as well as the 10 most recent. The idea is that I always want at least 10 records returned, even if there are none in the last two weeks.

I've got the two individual queries working, but I can't get them to UNION together:

SELECT client_id, add_date
FROM clients
WHERE datediff(dd, add_date, getdate()) <= 14

SELECT client_id, add_date
FROM clients
WHERE client_id IN(
SELECT TOP 10 client_id
FROM clients
WHERE (datediff(dd, add_date, getdate()) > 14)
ORDER BY add_date DESC
)

I understand WHY the UNION won't work (because of the ORDER BY in the subquery), but I can't figure out any other way to pull this off.

Any suggestions? Thanks!

Eric

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-08-18 : 19:35:42

Try this

SELECT client_id, add_date FROM
(SELECT TOP 10 * FROM clients WHERE (datediff(dd, add_date, getdate()) > 14) ORDER BY add_date DESC) tableA

mk_garg

chadmat
The Chadinator

1974 Posts

Posted - 2004-08-18 : 19:37:29

Why won't UNION work?

SELECT client_id, add_date
FROM clients
WHERE datediff(dd, add_date, getdate()) <= 14

UNION

SELECT TOP 10 client_id, add_date
FROM clients
ORDER BY add_date DESC

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

Eric T Lund
Starting Member

4 Posts

Posted - 2004-08-18 : 19:51:18

That only ends up giving me a TOTAL of ten records. What I really need is a MINIMUM of ten records.

For example, there may be 30 clients with an add date in the last 14 days, so I want those PLUS the 10 most recent BEYOND 14 days.

Thanks for your input...
Eric

quote:
Originally posted by mk_garg20

Try this

SELECT client_id, add_date FROM
(SELECT TOP 10 * FROM clients WHERE (datediff(dd, add_date, getdate()) > 14) ORDER BY add_date DESC) tableA

mk_garg

Eric T Lund
Starting Member

4 Posts

Posted - 2004-08-18 : 19:58:19

The reason the UNION won't work is that I actually need the final results ordered by client_id and not add_date.

So if I do:

SELECT TOP 10 client_id, add_date
FROM clients
ORDER BY add_date DESC

to get the most RECENT clients, the results are ordered by the date and not the client_id. And you can only have one 'ORDER BY' clause if you do a UNION.

FYI, the reason I need the list sorted in a specific way is that I'm actually using the 'FOR XML AUTO' statement and the order by is important for getting that to do what I need.

Thanks anyway,
Eric

quote:
Originally posted by chadmat

Why won't UNION work?

SELECT client_id, add_date
FROM clients
WHERE datediff(dd, add_date, getdate()) <= 14

UNION

SELECT TOP 10 client_id, add_date
FROM clients
ORDER BY add_date DESC

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-08-18 : 20:08:54

You can still use UNION as chad mentions, just wrap it in a derived table then sort the result by client_id:


SELECT client_id, add_date
FROM clients 
WHERE datediff(dd, add_date, getdate()) <= 14

UNION

SELECT client_id, add_date
FROM
(
  SELECT TOP 10 client_id, add_date
  FROM clients 
  ORDER BY add_date DESC
) d

ORDER BY client_id ASC

chadmat
The Chadinator

1974 Posts

Posted - 2004-08-18 : 20:22:58

quote:
Originally posted by ehorn

You can still use UNION as chad mentions, just wrap it in a derived table then sort the result by client_id:

SELECT client_id, add_date
FROM clients
WHERE datediff(dd, add_date, getdate()) <= 14

UNION

SELECT client_id, add_date
FROM
(
SELECT TOP 10 client_id, add_date
FROM clients
ORDER BY add_date DESC
) d

ORDER BY client_id ASC


SELECT client_id, add_date
FROM
(
SELECT client_id, add_date
FROM clients 
WHERE datediff(dd, add_date, getdate()) <= 14

UNION

SELECT TOP 10 client_id, add_date
FROM clients 
ORDER BY add_date DESC
) a
ORDER BY client_id

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

Eric T Lund
Starting Member

4 Posts

Posted - 2004-08-19 : 00:46:47

Ahhhh, I see...

So really the only difference between what I was trying and what you're suggesting is the "a" (or "d) after the second select (the 'derived table').

I think I get it. It definitely works for my example so that's great! Thank you!

Now it seems that perhaps I over simplified my example (compared to my REAL query) so I'll have to meddle with it a bit more and see if I can work it out.

Thanks for your help!
Eric

quote:
Originally posted by chadmat

quote:
Originally posted by ehorn

You can still use UNION as chad mentions, just wrap it in a derived table then sort the result by client_id:

SELECT client_id, add_date
FROM clients
WHERE datediff(dd, add_date, getdate()) <= 14

UNION

SELECT client_id, add_date
FROM
(
SELECT TOP 10 client_id, add_date
FROM clients
ORDER BY add_date DESC
) d

ORDER BY client_id ASC

Or

SELECT client_id, add_date
FROM
(
SELECT client_id, add_date
FROM clients
WHERE datediff(dd, add_date, getdate()) <= 14

UNION

SELECT TOP 10 client_id, add_date
FROM clients
ORDER BY add_date DESC
) a
ORDER BY client_id

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

chadmat
The Chadinator

1974 Posts

Posted - 2004-08-19 : 13:00:49

Yeah, the a (or d) can be anything. It is just an alias for the derived table.

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources