Time duration calcul - 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 2008 Forums

Transact-SQL (2008)

Time duration calcul

Author

Topic

Delila
Starting Member

11 Posts

Posted - 2011-04-24 : 13:15:18

Hello,

I have two tables the first one named TT have two date time columns called :

-TTID
-Start date (let's call it X)
-End date (and this we will call it Y)

the second table TTX have similar columns :

-TTXID
-actual start date
-actual end date

what i'm trying to do is a stored procedure that will run each end of month (that's not a problem), this SP must calculate the difference between the X and Y and if it's superior to 30 days it must put in the second table :

actual start date = X
actual end date = end of actual month or Y

until cosuming the hole difference between X and Y

for example :

X= 12/01/2011

Y= 02/03/2011
it must insert in the second table the folowing intervals:

12/01/2011(as actual start date) to 31/01/2011 (as actual end date)
01/02/2011(as actual start date) to 28/02/2011 (as actual end date)
01/03/2011 (as actual start date) to 02/03/2011 (as actual end date)

and if the TT interval that was splited have an ID like : 21354
the three interval inserted into the TTX table must have ids like this : 213541 213542 213543 and so on ....

of course the SP must do this operation for all records in the table.

Thanks very much for your help.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-24 : 20:40:30

[code]
Sample Table + data for testing
declare @TT table
(
TTID varchar(10),
start_date datetime,
end_date datetime
)

insert into @TT select '21354', '2011-01-12', '2011-03-02'
insert into @TT select '21355', '2011-04-12', '2011-04-27'

-- The Query
select TTXID = t.TTID + convert(varchar(10), row_number() over (partition by t.TTID order by v.number)),
actual_start_date = case when v.number = 0
then t.start_date
else dateadd(month, datediff(month, 0, t.start_date) + v.number, 0)
end,
actual_end_date = case when month(t.end_date) = month(dateadd(month, datediff(month, 0, t.start_date) + v.number + 1, -1))
then t.end_date
else dateadd(month, datediff(month, 0, t.start_date) + v.number + 1, -1)
end
from @TT t
inner join master..spt_values v on v.type = 'P'
where v.number >= 0
and v.number <= datediff(month, start_date, end_date)
[/code]

if you have a number table, us it instead of the spt_values

KH
[spoiler]Time is always against us[/spoiler]

Delila
Starting Member

11 Posts

Posted - 2011-04-25 : 05:36:48

Hello,

Thanks very much Khtan for your answer.

I have two question :

1) what do you mean by number table? (sorry i am ...)
2) i didn't understand very well your code could please show me where is the insertion of the FIRST interval (i need to control this :) )

and again thank you

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-25 : 05:44:14

1) number table or tally table is just a table that contains a numbers in its rows. You may also refer to this article for further detail http://www.sqlservercentral.com/articles/T-SQL/62867/

2) starting from the SELECT. The part in blue and replace @TT with your actual table name

KH
[spoiler]Time is always against us[/spoiler]

Delila
Starting Member

11 Posts

Posted - 2011-04-27 : 06:12:22

ok but could it be possible with this logic to insert just the firt resultant interval in the example given it woulf be :
12/01/2011 to 31/01/2011

thanks in advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-27 : 06:31:45

quote:
Originally posted by Delila

ok but could it be possible with this logic to insert just the firt resultant interval in the example given it woulf be :
12/01/2011 to 31/01/2011

thanks in advance

You mean you only wanted one row of the result ?

KH
[spoiler]Time is always against us[/spoiler]

Delila
Starting Member

11 Posts

Posted - 2011-04-27 : 07:07:11

Yes to be more clear (sorry if i wasn't) i just need that every month FOR every time interval >= a month if this condition is fulfiled it must insert into the second table the first interval so if we return to our example : if we the Sp is executed in the 31/01/2011 when he find that 12/01/2011 02/03/2011 it must put in the second table :
12/01/2011 as actual start date to 31/01/2011 as actual end date (this row must have the ID of the relative record from TT table + '1' at the end)

next run of the SP 28/02/2011(next month) it will put the second interval wich is :

01/02/2011 to 28/02/2011 (this must have the ID of the relative record from TT table + '2' at the end)

as i said it is a monthly runed SP.
the results must be insert to the second table (TTX)

sorry if i ask to much (this sp will generate a special school session bill so may understand that the end date isn't fix so we can't generate all sub-intervals at once)

many thanks for your help

Delila
Starting Member

11 Posts

Posted - 2011-04-27 : 11:31:53

please any helps for this final step would be great

Delila
Starting Member

11 Posts

Posted - 2011-04-27 : 16:30:45

a little bump :)

any help please

hawk1992
Starting Member

3 Posts

Posted - 2011-04-28 : 04:10:41

Did you find the answer i am facing the same issue

Delila
Starting Member

11 Posts

Posted - 2011-04-28 : 05:21:36

No sorry i tried to edit khtan code but failed

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-29 : 00:18:19

[code]
-- Sample Table + data for testing
declare @TT table
(
TTID varchar(10),
start_date datetime,
end_date datetime
)

insert into @TT select '21354', '2011-01-12', '2011-03-03'
insert into @TT select '21355', '2011-02-12', '2011-04-27'

declare @TTX table
(
TTXID varchar(10),
start_date datetime,
end_date datetime
)

-- The Query
declare @today datetime

-- test for Feb
select @today = '2011-02-05'

insert into @TTX (TTXID, start_date, end_date)
select TTXID = t.TTID
+ convert(varchar(10), convert(int, replace(isnull(x.TTXID, ''), t.TTID, '')) + 1),
start_date = case when t.start_date > dateadd(month, datediff(month, 0, @today), 0)
then t.start_date
else dateadd(month, datediff(month, 0, @today), 0)
end,
end_date = case when t.end_date < dateadd(month, datediff(month, 0, @today) + 1, -1)
then t.end_date
else dateadd(month, datediff(month, 0, @today) + 1, -1)
end
from @TT t
cross apply
(
select TTXID = max(TTXID)
from @TTX x
where x.TTXID like t.TTID + '%'
) x
where t.start_date <= dateadd(month, datediff(month, 0, @today) + 1, -1)
and t.end_date >= dateadd(month, datediff(month, 0, @today), 0)

-- test for Mar
select @today = '2011-03-06'

insert into @TTX (TTXID, start_date, end_date)
select TTXID = t.TTID
+ convert(varchar(10), convert(int, replace(isnull(x.TTXID, '1'), t.TTID, '')) + 1),
start_date = case when t.start_date > dateadd(month, datediff(month, 0, @today), 0)
then t.start_date
else dateadd(month, datediff(month, 0, @today), 0)
end,
end_date = case when t.end_date < dateadd(month, datediff(month, 0, @today) + 1, -1)
then t.end_date
else dateadd(month, datediff(month, 0, @today) + 1, -1)
end
from @TT t
cross apply
(
select TTXID = max(TTXID)
from @TTX x
where x.TTXID like t.TTID + '%'
) x
where t.start_date <= dateadd(month, datediff(month, 0, @today) + 1, -1)
and t.end_date >= dateadd(month, datediff(month, 0, @today), 0)

select *
from @TTX
order by TTXID

/*

TTXID start_date end_date
---------- ---------- ----------
213541 2011-02-01 2011-02-28
213542 2011-03-01 2011-03-03
213551 2011-02-12 2011-02-28
213552 2011-03-01 2011-03-31

(4 row(s) affected)

*/
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources