Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
 Posted - 2004-08-09 : 17:26:37
|
| Hi guys,This one is pretty complex for me, any help I can get from you guys is greatly appreciated.I'm designing effectively an affiliate program where I can track which affiliates are responsible for signups on the site im working on etc.When a user signs up, they either have a referal cookie or not. If they have a referal cookie # I will insert that into their row. If not I will insert a "0" (or should I keep this NULL ) I have 3 tables involved in this query.tblUsers (stores the users info and the related fields userID, affiliateID)tblAffils (stores each affiliates info and the related field affilID)tblTransactions (stores transactions of each user and fields userID)I need to run a SP that returns data for each Affiliate. I need to be able to get stats for a certain date, or for range of dates. I need to know the total number of signups each affiliate has, which is the count of total users with their affiliateID.The ultimate goal of this SPROC is to determine how much money each affiliate brings in, which means the number of sales has to be determined. Currently there are 4 different levels of membership that can be purchased, all with different pricing.In the transaction table, all sales are recorded. I need to join on the number of the day's sales for each type of membership. I need the date the sale was made on to line up with the date selected. By this I mean, if the user joins August 1st, and converts to a paying person by the 9th. This should count as a sale on the 9th, not first.Ideally, this report would show the results with a row for each date and total and then a grand total at the end.Thanks alot for any assistance,mike123 |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-08-10 : 08:49:19
|
| The best way to get this answered is a little sample data and the desired result...Corey |
 |
|
|
ffoiii
Starting Member
12 Posts |
Posted - 2004-08-10 : 15:20:21
|
| create proc SalesReport @StartDate datetime = null,@EndDate datetime = nullasselect isnull(a.AffilID, 'No Affiliate'), convert(varchar,t.Date, 101), count(t.*)from tblUsers u left join tblAffils a on a.AffilID = u.AffilID inner join tblTransactions t on t.UserID = u.UserIDwhere (@StartDate is null or t.Date >= @StartDate) and (@EndDate is null or t.Date <= @EndDate)order by isnull(a.AffilID, 'No Affiliate'), convert(varchar,t.Date, 101)Of course, this doesn't consider transaction types, recurring user signups, nullability of tblAffils.Name, etc...For programming ease, you could create a default affiliate that would be used when no Affiliate Cookie information came with the user. This will simplify reporting in some ways and make your querying easier. However, it is technically "wrong" and Chris Date amongst many others, myself included, would design the tables so that AffiliateID is stored in a separate table than tblUsers, such as tblUserAffiliate, which only has a record when the User was brought to the site by an Affiliate.ffoiii |
|
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2004-08-10 : 16:48:09
|
| thansk for the help ffoiii !I will redesign my tables, as your suggestion sounds great. I'll repost from there with sample dataThanks again! |
|
|
|
|
|
|
|
|
Help with Query (joins, sum)