SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Length of stay		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

regelos
Starting Member

USA
13 Posts
Posted - 01/28/2013 :  20:36:23  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
1419 Posts
Posted - 01/28/2013 :  23:43:35  Show Profile  Reply with Quote
Post sample data which you have now and also expected output........

--
Chandu
Go to Top of Page

regelos
Starting Member

USA
13 Posts
Posted - 01/29/2013 :  00:22:15  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
1419 Posts
Posted - 01/29/2013 :  00:29:45  Show Profile  Reply with Quote
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
Edited by - bandi on 01/29/2013 00:33:04
Go to Top of Page

regelos
Starting Member

USA
13 Posts
Posted - 01/29/2013 :  00:36:12  Show Profile  Reply with Quote
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.
Edited by - regelos on 01/29/2013 00:37:10
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
1419 Posts
Posted - 01/29/2013 :  00:42:39  Show Profile  Reply with Quote
>> 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
Edited by - bandi on 01/29/2013 00:44:52
Go to Top of Page

regelos
Starting Member

USA
13 Posts
Posted - 01/29/2013 :  00:44:37  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
1419 Posts
Posted - 01/29/2013 :  00:46:14  Show Profile  Reply with Quote
Ok then it works..........

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000