Still can't get this 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

SQL Server 2000 Forums

SQL Server Development (2000)

Still can't get this query

Author

Topic

Karl
Starting Member

8 Posts

Posted - 2002-09-25 : 14:07:26

I am trying to write a statement that will return the records which have a date older than a specified date from a group of records (ie. the most current record (advi_date) in the group is more than 30 days old).

The database is fairly simple. It consists of the following;

dbs_id(int), dest_id(int), cate_id(int), advi_text(ntext), advi_date_updated(datetime)

A unique group would be identified by the same dbs_id and dest_id.

Here's what I have so far ...

SELECT dbs_id, dest_id, MAX(advi_date_updated) FROM advisory GROUP BY dbs_id, dest_id

This returns only the most current record in each grouping. Now what I need to do is just get the records that are more than 30 days old.

Anyone feel up to this little problem ????

Thanks ... Karl

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-09-25 : 14:15:18

You don't need a GROUP BY, just use the DateAdd function:

SELECT dbs_id, dest_id, advi_date_updated
FROM advisory
WHERE advi_date_updated <= DateAdd(day, -30, GetDate())

Karl
Starting Member

8 Posts

Posted - 2002-09-25 : 14:20:46

Thanks but this doesn't work as I need to select one item out of a grouping which is defined by my group statement. If I use this select it will return multiple entries for a single group which I can't use.

Any other ideas ?

Thanks ... Karl

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-09-25 : 14:22:18

You have to post some sample data and an example of the exact output you need.

Karl
Starting Member

8 Posts

Posted - 2002-09-25 : 14:28:00

Ok,

Here is what the database looks like,

dbs_id dest_id cate_id advi_date_updated
1 1 3 2002-04-19
1 1 4 2002-02-03
1 1 5 2002-01-01
1 2 4 2001-01-13
1 2 5 2002-03-01
1 2 7 2002-05-17

In the case above, the result I need is as follows;

1 1 3 2002-04-19
1 2 7 2002-05-17

The above 2 records are the most current within their groups and are more than 30 days old.

Hope this makes sense ... Karl

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-09-25 : 16:08:54

Karl,

I hope this is what you mean. Test it on your full data and let me know.

DECLARE @date datetime
Set @date = '05/17/2002'

Select a.dbs_id, a.dest_id, b.Last_Update_Days,
MAX(a.advi_date_updated) advi_date_updated
FROM advisory a INNER JOIN
(SELECT dbs_id, dest_id, datediff(dd, cast(substring(advi_date_updated,6,2)+'/'+substring(advi_date_updated,9,2)+'/'+Left(advi_date_updated,4) as datetime), @date) Last_Update_Days
FROM advisory) b
ON a.dbs_id = b.dbs_id
and a.dest_id = b.dest_id
WHERE b.Last_Update_Days BETWEEN 0 and 30
GROUP BY a.dbs_id, a.dest_id, b.Last_Update_Days

Jeremy

Karl
Starting Member

8 Posts

Posted - 2002-09-25 : 16:25:43

Thanks Jeremy,

I think we are on the right track here.

Unforturately this gives me an error - The data type datetime is invalid for the substring function.

Any ideas ?

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-09-25 : 16:36:40

I am sorry Karl. I did not notice that the advi_date_updated column was a datetime datatype. I assumed it was a char(10) since the timestamp was truncated in the sample data.

You should be able to use the following:

Select a.dbs_id, a.dest_id, b.Last_Update_Days,
MAX(a.advi_date_updated) advi_date_updated
FROM advisory a INNER JOIN
(SELECT dbs_id, dest_id, datediff(dd, advi_date_updated, @date) Last_Update_Days
FROM advisory) b
ON a.dbs_id = b.dbs_id
and a.dest_id = b.dest_id
WHERE b.Last_Update_Days BETWEEN 0 and 30
GROUP BY a.dbs_id, a.dest_id, b.Last_Update_Days

Let me know.

Jeremy

Edited by - joldham on 09/25/2002 16:37:03

Karl
Starting Member

8 Posts

Posted - 2002-09-25 : 17:28:20

Jeremy,

That's better, but I'm still getting some duplications in the data (ie. several items returned for the same dbs_id and dest_id combination).

This is not happening in every case, so I'm not sure why.

Thanks ... Karl

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-09-25 : 18:05:31

Karl,

Can you provide me with data for the items that are getting duplicates? Then I can load them up and modify the query.

Jeremy

Karl
Starting Member

8 Posts

Posted - 2002-09-25 : 20:52:33

Jeremy,

Thanks for your help, but I'm going to have to pick this up again later.

I have a family emergency which I must deal with.

Thanks again .... Karl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources