Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Find average subscription time (calculate dates)

Posted on 2013-11-14
3
Medium Priority
?
356 Views
Last Modified: 2013-11-15
Hi!

I have a table called "tblUserLog" that contains information about deleted users:

---------------------
tblUserLog
---------------------
UserName (varchar)
CreateDate (datetime)
LogDate (datetime)
SiteId (smallint)

I need to create a Stored Procedure that returns the average time between CreateDate (user registration) and LogDate (user deleted) where  SiteId = '2' and CreateDate > '2013-01-01'.

I need this to find out how many days an average users is registered. Hope someone please can help me solving this :)

Thanks!
0
Comment
Question by:webressurs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 400 total points
ID: 39648756
select avg(timediff) as avgtime from
(select datediff('dd',createdate, logdate) as timediff
 from yourtable
 where siteid = '2' and createdate > '2013-01-01') as x
0
 
LVL 11

Accepted Solution

by:
jasonduan earned 1600 total points
ID: 39648766
Try this:

SELECT AVG(DATEDIFF(DAY, CreateDate, LogDate))
FROM tblUserLog
where  SiteId = '2' and CreateDate > '2013-01-01'
0
 
LVL 1

Author Closing Comment

by:webressurs
ID: 39650837
Thanks!!

Jasonduan code worked perfect!

Awking00 code needed a tiny adjustment:

select avg(timediff) as avgtime from
(select datediff(day,createdate, logdate) as timediff
 from yourtable
 where siteid = '2' and createdate > '2013-01-01') as x
0

Featured Post

Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question