Update query duplicate data & different dates

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

Update query duplicate data & different dates

Author

Topic

XTC77
Starting Member

4 Posts

Posted - 2012-06-14 : 06:31:03

Here's a new challenge!

I have a table with 3 fields account_id, zone & expiry_date. The account_id field can contain multiples of a given account for different zone's and then have a date for each in the expiry_date field.

I need to select records for a particular zone that are older than a given date but if there is another entry for that account_id for a different zone i need to ensure that the expiry_date for that other row is after another specific date.

The data can look like this:

account_id | zone | expiry_date
1 | 0 | 2010-05-15
1 | 1 | 2011-06-16
1 | 2 | 2011-06-16
2 | 0 | 2010-05-15
2 | 1 | 2013-06-16
2 | 2 | 2013-06-16

account_id '1' has dates all in the past so i want to ignore these records.

account_id '2' has dates for zone '1' & '2' that are in the future so i want to update the field expiry_date for that account_id for the row with zone '0' with a new expiry_date of my choosing.

Part of what i have so far but that doesnt work is as follows:

SELECT *
FROM zone_subscriptions
WHERE expiry_date < '2012-06-14' AND zone LIKE '0'
AND EXISTS (SELECT account_id, zone, expiry_date
FROM LAS.dbo.zone_subscriptions
WHERE expiry_date > '2012-06-14'
AND zone NOT LIKE '0')

I am somewhat stuck and ask graciuosly for any assistance with this.

Many thanks in advance.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-14 : 06:50:06

;with cte as (select account_id, maxexpiry_date = max(expiry_date), minexpiry_date = max(expiry_date) from zone_subscriptions group by account_id)
select
from cte s2.*, newexpirydate = s1.maxexpiry_date
join zone_subscriptions s2
on s1.account_id = s2.account_id
where s1.maxexpiry_date > '20120614' -- has rows in the future
and s1.minexpiry_date < '20120614' -- has rows in the past
and s2.expiry_date < '20120614' -- this entry in past

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

XTC77
Starting Member

4 Posts

Posted - 2012-06-14 : 06:58:07

Hi Nigel,

Thank you for this. If i put it into my SQL i get a failure on the syntax near 'from' as follows:

from cte s2.*

In principle it looks like it should be doing what i want though and i thank you for your effort here.

Many thanks,

Bryn

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-06-14 : 07:13:37

just looks like a hasty cut and paste error:

;with cte as (select account_id, maxexpiry_date = max(expiry_date), minexpiry_date = max(expiry_date) from zone_subscriptions group by account_id)
select s2.*, newexpirydate = s1.maxexpiry_date
from cte 
join zone_subscriptions s2
on s1.account_id = s2.account_id
where s1.maxexpiry_date > '20120614' -- has rows in the future
and s1.minexpiry_date < '20120614' -- has rows in the past
and s2.expiry_date < '20120614' -- this entry in past

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

XTC77
Starting Member

4 Posts

Posted - 2012-06-14 : 07:21:44

Hi - thank you for your help. This seems to have just shifted the error to the following:

Msg 4104, Level 16, State 1, Line 7
The multi-part identifier "s1.account_id" could not be bound.
Msg 4104, Level 16, State 1, Line 8
The multi-part identifier "s1.maxexpiry_date" could not be bound.
Msg 4104, Level 16, State 1, Line 9
The multi-part identifier "s1.minexpiry_date" could not be bound.
Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "s1.maxexpiry_date" could not be bound.

??

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-14 : 07:32:30

;with cte as (select account_id, maxexpiry_date = max(expiry_date), minexpiry_date = max(expiry_date) from zone_subscriptions group by account_id)
select s2.*, newexpirydate = s1.maxexpiry_date
from cte s1
join zone_subscriptions s2
on s1.account_id = s2.account_id
where s1.maxexpiry_date > '20120614' -- has rows in the future
and s1.minexpiry_date < '20120614' -- has rows in the past
and s2.expiry_date < '20120614' -- this entry in past

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

XTC77
Starting Member

4 Posts

Posted - 2012-06-14 : 07:33:44

Hi,

It now runs but i get no records returned?

I have tried editing the dates to '2012-06-14' as well but again no records returned when there should be thousands.

Thanks for your help so far.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-14 : 07:52:43

Had two max rather than max and min

declare @zone_subscriptions table (account_id int, zone int, expiry_date datetime)
insert @zone_subscriptions select 1, 0, '2010-05-15'
insert @zone_subscriptions select 1, 1, '2011-06-16'
insert @zone_subscriptions select 1, 2, '2011-06-16'
insert @zone_subscriptions select 2, 0, '2010-05-15'
insert @zone_subscriptions select 2, 1, '2013-06-16'
insert @zone_subscriptions select 2, 2, '2013-06-16'

;with cte as (select account_id, maxexpiry_date = max(expiry_date), minexpiry_date = min(expiry_date) from @zone_subscriptions group by account_id)
select s2.*, newexpirydate = s1.maxexpiry_date
from cte s1
join @zone_subscriptions s2
on s1.account_id = s2.account_id
where s1.maxexpiry_date > '20120614' -- has rows in the future
and s1.minexpiry_date < '20120614' -- has rows in the past
and s2.expiry_date < '20120614' -- this entry in past

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources