October 30, 2009 at 4:55 am
Hi
I've got to bring some data through to a program from a database that I was hoping to do using SQL. I've got a table which contains data concerning services provided to customers. This table amongst others contains 3 columns, one which contains the date of the last callout, one which contains the callout interval e.g. 1,2,4,6,8,10 months, and one which contains the end date of the service (when it should expire).
What I need to do with is bring through all records where the last callout + the callout interval is less than the end date. I've managed to bring through the first occurence of when this occurs by using the following code where the end date is '2010-02-28':
WHERE '2010-02-28' >= (DATEADD(month,cast(U_Interval as int),U_LastCallGen).
Now what I need to do is basically do the same thing as above however I need to change the interval so that it is continously doubled until the date is greater than the End Date and if so bring through this as a new line. E.g. If the U_LastCallGen = '2009-05-22' and the U_Interval = 4 then I would need to bring through 2 records one for '2009-09-22' and one for '2010-01-22'. This would list all of the callouts that need to be provided to the customer before their service ends.
I think I may have to use Cursors but I'm not sure if I could do this using a simple While loop.
Thanks a lot
Steve
October 30, 2009 at 5:14 am
Hi Can you supply the DDL and sample data , with the expected output to?
October 30, 2009 at 5:25 am
The service table contains the following data, which relates to the columns ContractID, ItemCode, ItemName, StartDate, U_LastCallGen, U_Interval:
1,SYSTEM 1B,CCTV,2009-10-26 00:00:00.000,2010-12-25 00:00:00.000,1
From the above if an Service end date of '2009-12-30' was provided I would expect the following results, which contains an additional column showing the actual servicing date.
1,SYSTEM 1B,CCTV,2009-10-26 00:00:00.000,2010-12-25 00:00:00.000,1, 2009-11-26 00:00:00.000
1,SYSTEM 1B,CCTV,2009-10-26 00:00:00.000,2010-12-25 00:00:00.000,1, 2009-12-26 00:00:00.000
1,SYSTEM 1B,CCTV,2009-10-26 00:00:00.000,2010-12-25 00:00:00.000,1, 2010-01-26 00:00:00.000
1,SYSTEM 1B,CCTV,2009-10-26 00:00:00.000,2010-12-25 00:00:00.000,1, 2010-02-26 00:00:00.000
1,SYSTEM 1B,CCTV,2009-10-26 00:00:00.000,2010-12-25 00:00:00.000,1, 2010-03-26 00:00:00.000
1,SYSTEM 1B,CCTV,2009-10-26 00:00:00.000,2010-12-25 00:00:00.000,1, 2010-04-26 00:00:00.000
1,SYSTEM 1B,CCTV,2009-10-26 00:00:00.000,2010-12-25 00:00:00.000,1, 2010-05-26 00:00:00.000
1,SYSTEM 1B,CCTV,2009-10-26 00:00:00.000,2010-12-25 00:00:00.000,1, 2010-06-26 00:00:00.000
1,SYSTEM 1B,CCTV,2009-10-26 00:00:00.000,2010-12-25 00:00:00.000,1, 2010-07-26 00:00:00.000
1,SYSTEM 1B,CCTV,2009-10-26 00:00:00.000,2010-12-25 00:00:00.000,1, 2010-08-26 00:00:00.000
1,SYSTEM 1B,CCTV,2009-10-26 00:00:00.000,2010-12-25 00:00:00.000,1, 2010-09-26 00:00:00.000
1,SYSTEM 1B,CCTV,2009-10-26 00:00:00.000,2010-12-25 00:00:00.000,1, 2010-10-26 00:00:00.000
1,SYSTEM 1B,CCTV,2009-10-26 00:00:00.000,2010-12-25 00:00:00.000,1, 2010-11-26 00:00:00.000
1,SYSTEM 1B,CCTV,2009-10-26 00:00:00.000,2010-12-25 00:00:00.000,1, 2010-12-26 00:00:00.000
October 30, 2009 at 7:14 am
Something like this ?
create table #Service
(
StartDate smalldatetime,
ServiceInterval integer,
EndDate smalldatetime
)
go
insert into #Service values('2009-10-26 00:00:00.000',1,'2010-10-26 00:00:00.000')
insert into #Service values('2009-10-20 00:00:00.000',2,'2010-10-20 00:00:00.000')
go
with cteInterval
as(
select number from
master..spt_values
where
TYPE='p' and number between 0 and 100
)
select startdate,dateadd(mm,cteInterval.number,startdate)
from #service
join cteInterval on cteInterval.number % #service.Serviceinterval =0
where dateadd(mm,cteInterval.number,startdate)<EndDate
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply