asked on

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.

ASKER CERTIFIED SOLUTION

John_Vidmar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Pletcher

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;

AbeSpain

ASKER

Worked out great.