update statement ? - 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

Transact-SQL (2000)

update statement ?

Author

Topic

aspag
Starting Member

6 Posts

Posted - 2004-01-26 : 11:00:41

Table that has the following layout or customer id, begindate and enddate.

Cust begindt enddt
abc 1/16/2003 1/16/2003
abc 2/20/2003 2/20/2003
abc 3/11/2003 3/13/2003
abc 3/25/2003 3/27/2003
abc 4/15/2003 4/15/2003
abc 4/17/2003 4/17/2003
abc 4/22/2003 4/22/2003
abc 4/24/2003 4/24/2003
abc 5/13/2003 5/15/2003
abc 5/22/2003 5/22/2003
abc 5/27/2003 5/29/2003
abc 8/5/2003 8/7/2003
abc 8/12/2003 8/14/2003
abc 8/25/2003 8/25/2003
abc 8/27/2003 8/27/2003

1.updated field prevenddt
select a.cust,a.begindt,a.enddt,daysprev = 0
,(select max(enddt) from #t1 as c where( c.cust = a.cust and c.enddt < a.enddt)) as prevenddt
into #t2 from #t1as a

2.calculated daysprev = datediff(day,prevenddt,begindt)+1
3.If the customer days were either zero or more than 30 days, it was considered as a new eposide.
4. Updated NewBeginDt = begindt where neweposide = 1

Cust begindt enddt prevenddt daysprev NewEposide newbegindt
abc 1/16/2003 1/16/2003 1/16/2003 0 1 1/16/2003
abc 2/20/2003 2/20/2003 1/16/2003 36 1 2/20/2003
abc 3/11/2003 3/13/2003 2/20/2003 20 0 NULL
abc 3/25/2003 3/27/2003 3/13/2003 13 0 NULL
abc 4/15/2003 4/15/2003 3/27/2003 20 0 NULL
abc 4/17/2003 4/17/2003 4/15/2003 3 0 NULL
abc 4/22/2003 4/22/2003 4/17/2003 6 0 NULL
abc 4/24/2003 4/24/2003 4/22/2003 3 0 NULL
abc 5/13/2003 5/15/2003 4/24/2003 20 0 NULL
abc 5/22/2003 5/22/2003 5/15/2003 8 0 NULL
abc 5/27/2003 5/29/2003 5/22/2003 6 0 NULL
abc 8/5/2003 8/7/2003 5/29/2003 69 1 8/5/2003
abc 8/12/2003 8/14/2003 8/7/2003 6 0 NULL
abc 8/25/2003 8/25/2003 8/14/2003 12 0 NULL
abc 8/27/2003 8/27/2003 8/25/2003 3 0 NULL

Stuck with this query:
I still have to update a new additional column "NewEndDt"
which should be the max enddate prior to the next new eposide or the same cust
Is there a way to write an update or select statement that will return "newenddt" into into 1 record like:

Cust NewBegindt new enddt
abc 1/16/2003 1/16/2003
abc 2/20/2003 5/29/2003
abc 8/5/2003 8/27/2003

I hope this makes sense. Thanks for you help.

stephe40
Posting Yak Master

218 Posts

Posted - 2004-01-26 : 11:54:25

Can you explain in words what your starting with and what you want to end up with?

aspag
Starting Member

6 Posts

Posted - 2004-01-26 : 13:06:00

All of customers order dates(begindt) and shipdates (enddate) is extracted to define how many episodes a customer had with the specified period. An episode ends when a customer has not received services for 30 or more days (ie. from the shipdt(begindt) to the next order dt(end)). Days will be calculated from their first shipdate(enddt) to next orderdate(begindt). If days is greater then 30 days, it is determined as a new episode,

Finally, output should result for a customer with their earliest order date(begindt) and the last ship date(enddate) prior to the next episode.

Hope I am clear enough. If not please let me know.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-26 : 13:22:59

Aspag, please provide the table definitions in the form of CREATE TABLE statements. Also, please provide INSERT INTO statements for your data. Once we have that, you'll find that your question is answered rather quickly. Without the information though, it means that we have to do all of the typing which could mean that your question isn't answered.

Have a look at this thread to see what I mean:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=29090

The thread references another thread where the question was posted similarly to yours. Once the needed information (DDL and DML) were provided, the question was answered very fast.

Tara

aspag
Starting Member

6 Posts

Posted - 2004-01-26 : 14:56:03

create table and insert into values are:

Create table #t1 ( cust varchar(5), begindt datetime ,enddt datetime)
Insert into #t1 values ( 'abc','20030116','20030116')
Insert into #t1 values ( 'abc','20030220','20030220')
Insert into #t1 values ( 'abc','20030311','20030313')
Insert into #t1 values ( 'abc','20030325','20030327')
Insert into #t1 values ( 'abc','20030415','20030415')
Insert into #t1 values ( 'abc','20030417','20030417')
Insert into #t1 values ( 'abc','20030422','20030422')
Insert into #t1 values ( 'abc','20030424','20030424')
Insert into #t1 values ( 'abc','20030513','20030515')
Insert into #t1 values ( 'abc','20030522','20030522')
Insert into #t1 values ( 'abc','20030527','20030529')
Insert into #t1 values ( 'abc','20030805','20030807')
Insert into #t1 values ( 'abc','20030812','20030814')
Insert into #t1 values ( 'abc','20030825','20030825')
Insert into #t1 values ( 'abc','20030527','20030527')

select a.cust,a.begindt,a.enddt,daysprev = 0
,(select max(enddt) from #t1 as c where( c.cust = a.cust and c.enddt < a.enddt)) as prevenddt
into #t2 from #t1 as a
update #t2
set prevenddt = begindt where prevenddt is null
update #t2 set daysprev = datediff(day,prevenddt,begindt)+1
alter table #t2 add neweposide int
update #t2 set neweposide = 1 where daysprev > 30
alter table #t2 add newbegindt datetime
Updated #t2 NewBeginDt = begindt where neweposide = 1
alter table #t2 add newenddt datetime

Stuck here.
need to update the newenddt to read max end date prior to the next eposide.

See if you help.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-01-27 : 00:09:57

What you trying to do is an extended version of the concept described in the excellent article by Jeff: "Detecting runs or streaks in your data" . Read the article, it should point you in the right direction.

OS

aspag
Starting Member

6 Posts

Posted - 2004-01-28 : 15:50:33

Thank you OS for directing me into the right direction. Article was excellent and helped me in resolving my problems.

AS

X002548
Not Just a Number

15586 Posts

Posted - 2004-01-28 : 16:49:34

quote:
Originally posted by mohdowais

What you trying to do is an extended version of the concept described in the excellent article by Jeff: "Detecting runs or streaks in your data" . Read the article, it should point you in the right direction.

OS

HEY, when did you become an OS unto your own...does it make communicating with the HD easier?

Like do you even need to touch the keyboard now?

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources