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
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Looking to add a where to only a few rows
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

twill227
Starting Member

8 Posts

Posted - 06/17/2014 :  15:36:24  Show Profile  Reply with Quote
Problem: I want to find the % of people who's last login date is the same as their registration date. Basically I want to find out who created a account and never logged in again. I want to put this into a percentage taking every instance of lastlogindate = registrationdate and dividing that by the total amount of users signed up for each month.

CODE:

	SELECT 
	count (((userdata.userid))) as [distinct users]
	,datename(year,saleschannel.createddate) as [year]
	,datename(month,saleschannel.createddate) as [month]
	,count(saleschannel.saleschannelid) AS [# of saleschannels]
	,((count(*) /cast((count (distinct(userdata.userid)))as float)) * 100) as [% sales channels]
	
	FROM saleschannel
	INNER JOIN company on company.companyid = saleschannel.companyid 
	INNER JOIN userdata on userdata.companyid = company.companyid and roleid = 1 AND company.createduserid = userdata.userid
	--INNER JOIN product on product.companyid = saleschannel.companyid
	--WHERE company.registrationdate = userdata.lastlogindate 
	
	GROUP BY  month(saleschannel.createddate)
	, year(saleschannel.createddate)
	, datename(year,saleschannel.createddate)
	,datename(month,saleschannel.createddate)
	
	order by year(saleschannel.createddate),month(saleschannel.createddate)


It outputs this
[distinct users] [year] [month] [# of saleschannels] [% sales channels]
7 2012 June 7 140
2 2012 July 2 200
2 2012 October 2 200
8 2013 February 8 400
7 2013 March 7 350
11 2013 April 11 157.142857142857
18 2013 May 18 257.142857142857
6 2013 June 6 300
1 2013 July 1 100
3 2013 August 3 300
3 2013 September 3 150
2 2013 October 2 200
16 2014 January 16 228.571428571429
24 2014 February 24 342.857142857143
14 2014 March 14 280
10 2014 April 10 250
53 2014 May 53 311.764705882353
16 2014 June 16 177.777777777778

An obvious fix would be to add in
WHERE company.registrationdate = userdata.lastlogindate


But doing so would only show the cases of users not logging on and would ignore the other data that I have.

[distinct users] [year] [month] [# of saleschannels] [% sales channels]
2 2012 June 2 100
4 2013 May 4 100
2 2014 February 2 200
2 2014 May 2 200
5 2014 June 5 125

This is fine but I need to see the other months also, not just the months where registrationdate = lastlogindate

Ive tried doing a subquery butI had issues with keeping the same structure that the main query had using the GROUP BY statement.

Sorry if this was long but wanted it to be thorough

tkizer
Almighty SQL Goddess

USA
36845 Posts

Posted - 06/17/2014 :  15:38:58  Show Profile  Visit tkizer's Homepage  Reply with Quote
Use the criteria as an OUTER JOIN?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

twill227
Starting Member

8 Posts

Posted - 06/17/2014 :  15:40:57  Show Profile  Reply with Quote
quote:
Originally posted by tkizer

Use the criteria as an OUTER JOIN?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/



What do you mean?

OUTER JOIN company.registrationdate = userdata.lastlogindate ?

Oh you mean like OUTER JOIN userdata on userdata.companyid = company.companyid and roleid = 1 AND company.createduserid = userdata.userid AND company.registrationdate = userdata.lastlogindate

But I'm already joining userdata and company together?

NVM figured it out thanks man

Edited by - twill227 on 06/17/2014 15:49:03
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