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.

All Forums  SQL Server 2000 Forums  Transact-SQL (2000)  Calculating total of points based upon date

Author  Topic  jns Starting Member 19 Posts Posted - 2001-10-29 : 12:16:13 I've got a list of people who are participating in various programs. We want to assign a points system to each program to see who is participating more. The problems are two fold. 1. Points for a given program will change based upon time period so we want the proper points to be assigned during their time period. 2. Some programs we will not count points past the first entry into a program, so additional entries past the first need to be disregarded. Here are my existing tables and some sample data. CREATE TABLE [dbo].[contacts] ( [id] [int] NULL , [created] [datetime] NULL , [firstname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [lastname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO insert into contacts (id, created, firstname, lastname) values (1, getdate(), 'Bob', 'Smith') insert into contacts (id, created, firstname, lastname) values (2, getdate(), 'John', 'Doe') insert into contacts (id, created, firstname, lastname) values (3, getdate(), 'Sally', 'Jones') insert into contacts (id, created, firstname, lastname) values (4, getdate(), 'June', 'Cleaver') CREATE TABLE [dbo].[promo_codes] ( [id] [int] NULL , [created] [datetime] NULL , [name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO insert into promotions (id, created, name) values (1, getdate(), 'Completed Web Survey') insert into promotions (id, created, name) values (2, getdate(), 'Attended Tasting') CREATE TABLE [dbo].[promotions] ( [id] [int] NULL , [created] [datetime] NULL , [contactid] [int] NULL , [promoid] [int] NULL , ) ON [PRIMARY] GO insert into promotions (id, created, name) values (1, '5/15/2001', 1, 1) insert into promotions (id, created, name) values (2, '7/15/2001', 1, 2) insert into promotions (id, created, name) values (2, '7/15/2001', 3, 2) insert into promotions (id, created, name) values (2, '8/15/2001', 4, 2) insert into promotions (id, created, name) values (2, '8/15/2001', 2, 2) insert into promotions (id, created, name) values (2, '7/16/2001', 3, 1) CREATE TABLE [dbo].[points] ( [id] [int] NULL , [points] [float] NULL , [startdate] [datetime] NULL , [enddate] [datetime] NULL , ) ON [PRIMARY] GO insert into points (id, promoid, points, startdate, enddate) values (1, 1, 2, '1/1/2001', '5/19/2001') insert into points (id, promoid, points, startdate, enddate) values (2, 1, 2.5, '5/20/2001', '12/19/2001') insert into points (id, promoid, points, startdate, enddate) values (1, 1, 2, '1/1/2001', '8/1/2001') insert into points (id, promoid, points, startdate, enddate) values (1, 1, 2, '8/2/2001', '12/1/2001') The following is the query I am running but it is not calculating the various points for the different time periods, just the most recent period. SELECT c.id, c.firstname, c.lastname, pc.name, SUM(pt.points) as points FROM contacts c, promotions p, points pt, promo_codes pc WHERE c.id=p.contactid AND p.promoid=pt.promoid and p.promoid=pc.id AND p.created BETWEEN pt.startdate AND pt.enddate GROUP by c.id, c.firstname, c.lastname, pc.description, pt.points ORDER by c.lastname, c.firstname Any ideas?