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 1402 2012 July 2 2002 2012 October 2 2008 2013 February 8 4007 2013 March 7 35011 2013 April 11 157.14285714285718 2013 May 18 257.1428571428576 2013 June 6 3001 2013 July 1 1003 2013 August 3 3003 2013 September 3 1502 2013 October 2 20016 2014 January 16 228.57142857142924 2014 February 24 342.85714285714314 2014 March 14 28010 2014 April 10 25053 2014 May 53 311.76470588235316 2014 June 16 177.777777777778An 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 1004 2013 May 4 1002 2014 February 2 2002 2014 May 2 2005 2014 June 5 125This is fine but I need to see the other months also, not just the months where registrationdate = lastlogindateIve 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
Looking to add a where to only a few rows
Posted - 2014-06-17 : 15:36:24
