quote:

---

Originally posted by mk_garg20

is [tblAffilUsers].affilID same as [tblTransactions].membershipType?
Can you explain freesignups, level2signups, level1signups

---

quote:

---

Originally posted by ffoiii

Mike,

I may have misunderstood your original query (from first thread), in that I thought some users could signup without being referred by an affiliate, not that some users could sign up for free.

As such, if _every_ user comes from/through an affiliate, there is no need for a separate tblUserAffil, and you can just set AffilID in tblUser. Additionally, although there is no dollar amount assoicated with the "transaction", you may still want to add a record to Transaction even if MembershipType is 'Free'.

It's difficult to give advice re schema design without extensive conversations regarding the usage and rules of the application. That's why Data Architects have a job!


ffoiii

---

quote:

---

Originally posted by mike123

Hi mk_garg20,

Looks great so far!. Except one thing.

A "Free" signup has no row in the tblAffilUsers table. I redesigned it as you suggested. How can I modify this query to accomodate that?

Thanks once again , really appreciated.

mike123

---

quote:

---

Originally posted by mike123

Example

Affil ID 5 could have these stats

Free signsup , level1 sales, level2 sales, level3 sales, level4sales,
100, 5, 2, 1, 9

---

DECLARE @tblUsers table (userID int, username varchar(20), affilID int)
insert into @tblUsers
select 500, 'username500', 2 UNION ALL
select 501, 'username501', 2 UNION ALL
select 502, 'username502', NULL UNION ALL
select 503, 'username503', 3 UNION ALL
select 504, 'username503', 3 UNION ALL
select 505, 'username504', NULL

DECLARE @tblAffils table (affilID int, password varchar(20))
insert into @tblAffils
select 2, 'password2' UNION ALL
select 3, 'password3' UNION ALL
select 4, 'password4'

DECLARE @tblTransactions table 
  (userID int, membershipType int, datestart datetime, dateEnd datetime)
insert into @tblTransactions
select 500, 2, '01/01/2000','01/01/2010' UNION ALL
select 500, 2, '01/01/2000','01/01/2010' UNION ALL
select 500, 3, '01/01/2000','01/01/2010' UNION ALL
select 502, 3, '01/01/2000','01/01/2010' UNION ALL
select 503, 3, '01/01/2000', '01/01/2010' UNION ALL
select 504, 3, '01/01/2000', '01/01/2010' UNION ALL
select 505, 2, '01/01/2000', '01/01/2010' 

select affilid,
  sum(case when membershiptype = 0 then total else 0 end) as freesales,
  sum(case when membershiptype = 2 then total else 0 end) as level2sales,
  sum(case when membershiptype = 3 then total else 0 end) as level3sales 
from 
  (select isnull(a.affilID, 0) as affilid,
  isnull(b.MembershipType, 0) as membershiptype,
  count(isnull(a.affilID, 0)) as total
  from @tblUsers a LEFT JOIN @tblTransactions b ON a.userID = b.userID 
  GROUP BY IsNull(a.affilID, 0), b.MembershipType) as tbla
group by affilid 
order by affilid

quote:

---

Originally posted by mk_garg20

select_AffiliateReports_All '01/01/2000', '01/01/2010'
ALTER PROCEDURE dbo.select_AffiliateReport


First thing Procedure names are different.
Second you are passing two parameters to stored procedure instead of passing 3 parameters.

Cheers

---

quote:

---

Originally posted by mike123
affilid / 10 / 20 / 30
1 / 0 / 0 / 1

---

quote:

---

Originally posted by mk_garg20

quote:

---

Originally posted by mike123
affilid / 10 / 20 / 30
1 / 0 / 0 / 1

---

I think you have to update results as well.

---