Set Repeat values to NULL - 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)

Set Repeat values to NULL

Author

Topic

oahu9872
Posting Yak Master

112 Posts

Posted - 2006-07-20 : 12:26:32

I am using the following SQL Statement to pull data from a table ...

SELECT Zone, [Beginning Date], [Customer Name], Location, Category, City + ', ' + State AS City_State, Time, Publication
FROM dbo.DMG_Event
WHERE (Publication = N'Auction Exchange') AND ([Beginning Date] > DATEADD(d, 10, GETDATE())) AND ([Beginning Date] < DATEADD(d, 90, GETDATE()))
ORDER BY [Zone], [Beginning Date]

For the first field in that statement .. (Zone), I need it to display the first time it comes up but have its value set to null everytime thereafter. For example...

Zone, Beginning Date, City
1, 7/26/06, Chicago
1, 7/26/06, New York
1, 7/27/06, Miami

should now look like ...

Zone, Beginning Date, City
1, 7/26/06, Chicago
NULL, 7/26/06, New York
NULL, 7/27/06, Miami

I hope this makes sense.

Thanks...

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-20 : 12:37:42

SELECT seq = 0, Zone, [Beginning Date], [Customer Name], Location, Category, City + ', ' + State AS City_State, Time, Publication
into #a
FROM dbo.DMG_Event
WHERE (Publication = N'Auction Exchange')
AND ([Beginning Date] > DATEADD(d, 10, GETDATE()))
AND ([Beginning Date] < DATEADD(d, 90, GETDATE()))

update #a
set seq = (select count(*) from #a t2 where t.Zone = t2.Zone and (t2.[Beginning Date] <= t.[Beginning Date]
or (t.[Beginning Date] = t2.[Beginning Date] and t2.City_State <= t.City_State))

select Zone = case when Seq = 1 then Zone else null end, [Beginning Date], [Customer Name], Location, Category, City_State, Time, Publication
from #a
ORDER BY [Zone], [Beginning Date], City_State

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

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-07-20 : 12:55:58

"For the first field in that statement .. (Zone), I need it to display the first time it comes up but have its value set to null everytime thereafter"

Do your display corrections on the client, not the back end.

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

gugarc
Starting Member

17 Posts

Posted - 2006-07-21 : 10:31:21

You need a key to uniquely identify the first occurence of it - otherwise it will be impossible (we can´t know which city was the first in that cenario).

May be we can use the column time as a uniqueness criteria - if true, folowwing will work (pay attention on the columns used as a key for the table - i used only beginning and city):

update DMG_EVENT set Zone=
Case
When time < (select top 1 time from dmg_Event A where a.Beginning=dmg_event.beginning and a.city=dmg_event.City order by time desc) then null
else Zone
end

select zone, time
from dmg_Event
order by time, beginning, city

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources