Solved

Subquery To Count User Signups Who Have Placed An Order

Posted on 2014-03-13
3
293 Views
Last Modified: 2014-03-14
Struggled with the title for this one, so here goes :

	Select 
		N.Number as [Month],
		Year(@Year) as [Year],
		Count(tblUE.UserExternaliD)
	From
			dbo.Numbers as N
			left outer join tblUserExternal as tblUE    
			on n.Number = datepart(mm, tblUE.UserExternalDateSignUp) and
				tblUE.UserExternalDateSignUp >= @Year and
				tblUE.UserExternalDateSignUp < DATEADD (yy,1,@Year)
	where 
		n.Number between 1 and 12
	Group by 
		N.Number
	Order by 
		N.Number;

Open in new window


The above query works as intended, pulling back a count of everyone who has signed up for an account during the 12 months of the given year. I'd like to pull back a 4th column that pulls back a count of how many of those users went on to also place an order. The relationship between the userexternal table and orders table is on a column userexternalid in both tables.
0
Comment
Question by:AbeSpain
3 Comments
 
LVL 11

Accepted Solution

by:
John_Vidmar earned 500 total points
ID: 39926803
SELECT	N.Number as [Month]
,	Year(@Year) as [Year]
,	Count(distinct tblUE.UserExternaliD) as [everyone who has signed]
,	Count(distinct O.UserExternaliD) as [everyone who placed orders]
FROM	dbo.Numbers as N
LEFT
JOIN	tblUserExternal as tblUE	ON	n.Number = datepart(mm, tblUE.UserExternalDateSignUp)
					AND	tblUE.UserExternalDateSignUp >= @Year
					AND	tblUE.UserExternalDateSignUp < DATEADD (yy,1,@Year)
LEFT
JOIN	Orders as O			ON	tblUE.userexternalid = O.userexternalid
WHERE	n.Number BETWEEN 1 AND 12
GROUP
BY	N.Number
ORDER
BY	N.Number

Open in new window

0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39927860
Select
            N.Number as [Month],
            Year(@Year) as [Year],
            Count(tblUE.UserExternaliD) AS Signup_Count,
            Count(tblO.UserExternalId) AS Order_Count
      From
                  dbo.Numbers as N
                  left outer join tblUserExternal as tblUE    
                  on tblUE.UserExternalDateSignUp >= DATEADD(MONTH, N.Number - 1, @Year) AND
                        tblUE.UserExternalDateSignUp < DATEADD(MONTH, N.Number, @Year)
                  left outer join tblOrders as tblO
                  on tblO.UserExternalId = tblUE.UserExternalId AND
                       tblO.OrderDate >= DATEADD(MONTH, N.Number - 1, @Year) AND
                       tblO.OrderDate < DATEADD(MONTH, N.Number, @Year)                  
      where
            n.Number between 1 and 12
      Group by
            N.Number --WITH ROLLUP --if you want to add yearly totals
      Order by
            N.Number;
0
 

Author Closing Comment

by:AbeSpain
ID: 39929277
Worked out great.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

863 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now