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
 General SQL Server Forums
 New to SQL Server Programming
 Length of stay

Author  Topic 

regelos
Starting Member

13 Posts

Posted - 2013-01-28 : 20:36:23
I need to extract some information for total length of stay from our orders table.

Basically what I want to get to is a member number, total time (can be just days or "years,month,days" I have multiple lines with cycle_begin_date and same line should have a cycle_end_date what I want to get is the total actual time so the breaks need to be not counted.

Thank you for your help in advance.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-28 : 23:43:35
Post sample data which you have now and also expected output........

--
Chandu
Go to Top of Page

regelos
Starting Member

13 Posts

Posted - 2013-01-29 : 00:22:15
input

Member number cycle begin date cycle end date
111111 01/01/2013 01/31/2012
111111 02/01/2012 02/28/2012
222222 01/01/2013 01/31/2012

output
member no length of stay
111111 59 (days) (or if possible year/month/day but I doubt that would be possible,days would probably work better anyway.)
222222 31 (days)
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-29 : 00:29:45
I think
Member number cycle begin date cycle end date
111111 01/01/2012 01/31/2012 --------------31days
111111 02/01/2012 02/28/2012 --------------28days
222222 01/01/2012 01/31/2012 --------------31days

DECLARE @tab TABLE(MemberNumber int, cycleBeginDate date, cycleEndDate date)
insert into @tab
SELECT 111111, '01/01/2012', '01/31/2012' union all
SELECT 111111, '02/01/2012', '02/28/2012' union all
SELECT 222222, '01/01/2012', '01/31/2012'

SELECT MemberNumber, SUM( DATEDIFF(dd, cycleBeginDate, cycleEndDate)+1 ) AS NoOfDays
FROM @tab
GROUP BY MemberNumber


--
Chandu
Go to Top of Page

regelos
Starting Member

13 Posts

Posted - 2013-01-29 : 00:36:12
Thank you. I'll try that in the morning just one question. in some cases this table can have over 100 record lines for it. Would there any problem with that? also it can have breaks (thats what I had the 2012 and 2013 in there.
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-29 : 00:42:39
>> it can have breaks (thats what I had the 2012 and 2013 in there means?

am not getting your point. If the BeginDate year is 2013 and EndDate year is 2012? How come this would happen?

MAy be you can have 01/01/2012 as BeginDate and 01/31/2013 as EndDate. Then the number of days is 425
--
Chandu
Go to Top of Page

regelos
Starting Member

13 Posts

Posted - 2013-01-29 : 00:44:37
quote:
Originally posted by bandi

>> it can have breaks (thats what I had the 2012 and 2013 in there means?

am not getting your point. If the BeginDate year is 2013 and EndDate year is 2012? How come this would happen?
Chandu



sorry it should have read
Member number cycle begin date cycle end date
111111 01/01/2013 01/31/2013
111111 02/01/2012 02/28/2012
222222 01/01/2013 01/31/2013
--
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-29 : 00:46:14
Ok then it works..........

--
Chandu
Go to Top of Page
   

- Advertisement -