Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 302
  • Last Modified:

Subquery To Count User Signups Who Have Placed An Order

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
AbeSpain
Asked:
AbeSpain
1 Solution
 
John_VidmarCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
AbeSpainAuthor Commented:
Worked out great.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now