Solved

Subquery To Count User Signups Who Have Placed An Order

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

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 …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

856 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