Solved

Subquery To Count User Signups Who Have Placed An Order

Posted on 2014-03-13
3
297 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
[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 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
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…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

752 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