Help with where condition on pre or post row

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)

Help with where condition on pre or post row

Author

Topic

NoNulls
Starting Member

14 Posts

Posted - 2005-06-02 : 15:17:25

Ok, Here is the data example. I want to delete 4/5 to 4/12 and I think I need to base this on whether the previous row has count1=0 and count2 = 0 OR the next row has count1 = 0 and count2 = 0
How do I do this?????

delete
from #alldates
where days > @enddate and count1= 0 and count2= 0
and exists( ???????????

@enddate in this case is 3/31

days site count1 count2
2005-03-26 00:00:00.000 20 0 150
2005-03-27 00:00:00.000 20 0 0
2005-03-28 00:00:00.000 20 320 328
2005-03-29 00:00:00.000 20 252 362
2005-03-30 00:00:00.000 20 122 414
2005-03-31 00:00:00.000 20 315 517
2005-04-01 00:00:00.000 20 324 0
2005-04-02 00:00:00.000 20 287 0
2005-04-03 00:00:00.000 20 272 0
2005-04-04 00:00:00.000 20 468 0
2005-04-05 00:00:00.000 20 0 0
2005-04-06 00:00:00.000 20 0 0
2005-04-07 00:00:00.000 20 0 0
2005-04-08 00:00:00.000 20 0 0
2005-04-09 00:00:00.000 20 0 0
2005-04-10 00:00:00.000 20 0 0
2005-04-11 00:00:00.000 20 0 0
2005-04-12 00:00:00.000 20 0 0
2005-03-01 00:00:00.000 21 0 323
2005-03-02 00:00:00.000 21 0 34

NoNulls
Starting Member

14 Posts

Posted - 2005-06-03 : 18:26:02

Ok,
This is what I did....

I created a temp table that had the existing days, previous day, next day. Then I did a delet based on the existence of the day in the necessary column.

Not pretty but it got me the results I needed. Suprised no one had a response on this

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-03 : 18:29:26

No one had a response since you didn't post the DDL for your table, the DML for the sample data, or the expected result set from the sample data. Check this out for details:

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

If you post it in the format shown in the link, there is a better chance that someone will answer your question and also that it'll be answered fast. Without this information, we have to do all of the typing on our own so that we can reproduce your situation on our machines. You won't always find someone willing to do this for you.

Tara

NoNulls
Starting Member

14 Posts

Posted - 2005-06-06 : 15:06:37

Never saw this.... wish it was somewhere obvious

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources