Calculate with a replace from another table

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

SQL Server Development (2000)

Calculate with a replace from another table

Author

Topic

csmconsulting
Starting Member

3 Posts

Posted - 2008-05-14 : 15:58:18

Ok guys...need some help here.

here are the tables i am having trouble with.

this is for a weight loss competition database.

Members weigh in at certain intervals(weigh in periods)
As a member weighs in a record is created in WeightLossMemberWeighIn with the MemberID,WeighInPeriodID, and their weight.

Members are members of a team which is of a certain team type. team types only categorize teams and their limits of numbers of members.

i need some help with several efficient queries to display results.

1. Display a list of members from a particular team type and their weight loss for a particular weigh in period.
2. Display a list of members from a particular team type and their weight loss for all weigh in periods.
3. Display a list of teams from a particular team type and their weight loss for a particular weigh in period.
4. Display a list of teams from a particular team type and their weight loss for all weigh in periods.

Gotcha on each of these is...if a member misses a weigh in, I need to replace within the % loss calculation their previous weigh in.

% loss is calculated as (InitialWeighIn(weighinperiodid=1)-currentweighin)/initialweighin

hopefully i have explained myself well enough.

here is the DDL

----------------------------
/****** Object: Table [dbo].[WeightLossManagerMembers] Script Date: 5/14/2008 2:48:25 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WeightLossManagerMembers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[WeightLossManagerMembers]
GO

/****** Object: Table [dbo].[WeightLossManagerTeamTypes] Script Date: 5/14/2008 2:48:25 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WeightLossManagerTeamTypes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[WeightLossManagerTeamTypes]
GO

/****** Object: Table [dbo].[WeightLossManagerTeams] Script Date: 5/14/2008 2:48:25 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WeightLossManagerTeams]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[WeightLossManagerTeams]
GO

/****** Object: Table [dbo].[WeightLossManagerWeighInPeriods] Script Date: 5/14/2008 2:48:25 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WeightLossManagerWeighInPeriods]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[WeightLossManagerWeighInPeriods]
GO

/****** Object: Table [dbo].[WeightLossMemberWeighIn] Script Date: 5/14/2008 2:48:25 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WeightLossMemberWeighIn]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[WeightLossMemberWeighIn]
GO

/****** Object: Table [dbo].[WeightLossManagerMembers] Script Date: 5/14/2008 2:48:27 PM ******/
CREATE TABLE [dbo].[WeightLossManagerMembers] (
[CompanyID] [int] NULL ,
[MemberID] [int] IDENTITY (1, 1) NOT NULL ,
[MemberFirstName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MemberLastName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MemberGender] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MemberPhoneNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[WeightLossManagerTeamTypes] Script Date: 5/14/2008 2:48:27 PM ******/
CREATE TABLE [dbo].[WeightLossManagerTeamTypes] (
[CompanyID] [int] NULL ,
[TeamTypeID] [int] IDENTITY (1, 1) NOT NULL ,
[TeamTypeName] [nvarchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TeamTypeMemberLimit] [int] NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[WeightLossManagerTeams] Script Date: 5/14/2008 2:48:28 PM ******/
CREATE TABLE [dbo].[WeightLossManagerTeams] (
[CompanyID] [int] NULL ,
[TeamID] [int] IDENTITY (1, 1) NOT NULL ,
[TeamName] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TeamTypeID] [int] NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[WeightLossManagerWeighInPeriods] Script Date: 5/14/2008 2:48:28 PM ******/
CREATE TABLE [dbo].[WeightLossManagerWeighInPeriods] (
[CompanyID] [int] NULL ,
[WeighInPeriodID] [int] IDENTITY (1, 1) NOT NULL ,
[WeighInPeriodName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WeighInPeriodStatus] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WeighInPeriodLastDataUpdate] [datetime] NULL ,
[WeighInPeriodClosedDate] [datetime] NULL ,
[CurrentWeighInPeriod] [int] NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[WeightLossMemberWeighIn] Script Date: 5/14/2008 2:48:28 PM ******/
CREATE TABLE [dbo].[WeightLossMemberWeighIn] (
[WeighInID] [int] IDENTITY (1, 1) NOT NULL ,
[MemberID] [int] NULL ,
[WeighInPeriodID] [int] NULL ,
[WeighInWeight] [numeric](10, 2) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[WeightLossManagerMembers] WITH NOCHECK ADD
CONSTRAINT [PK_WeightLossManagerMembers] PRIMARY KEY CLUSTERED
(
[MemberID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[WeightLossManagerTeamTypes] WITH NOCHECK ADD
CONSTRAINT [PK_WeightLossManagerTeamTypes] PRIMARY KEY CLUSTERED
(
[TeamTypeID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[WeightLossManagerTeams] WITH NOCHECK ADD
CONSTRAINT [PK_WeightLossManagerTeams] PRIMARY KEY CLUSTERED
(
[TeamID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[WeightLossMemberWeighIn] WITH NOCHECK ADD
CONSTRAINT [PK_WeightLossMemberWeighIn] PRIMARY KEY CLUSTERED
(
[WeighInID]
) ON [PRIMARY]
GO

CREATE INDEX [IX_WeightLossManagerTeamTypes] ON [dbo].[WeightLossManagerTeamTypes]([TeamTypeID]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[WeightLossManagerTeams] ADD
CONSTRAINT [DF_WeightLossManagerTeams_CompanyID] DEFAULT (1) FOR [CompanyID]
GO

CREATE INDEX [IX_WeightLossManagerTeams] ON [dbo].[WeightLossManagerTeams]([TeamID]) ON [PRIMARY]
GO

CREATE INDEX [IX_WeightLossManagerTeams_1] ON [dbo].[WeightLossManagerTeams]([TeamTypeID]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[WeightLossMemberWeighIn] ADD
CONSTRAINT [IX_WeightLossMemberWeighIn] UNIQUE NONCLUSTERED
(
[WeighInID]
) ON [PRIMARY]
GO

CREATE INDEX [IX_WeightLossMemberWeighIn_1] ON [dbo].[WeightLossMemberWeighIn]([MemberID], [WeighInPeriodID], [WeighInWeight]) ON [PRIMARY]
GO

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-14 : 16:38:28

Have you tried writing these queries yet? Are you having trouble with any particular one? Or being a consultant you just want someone else to do the work

Be One with the Optimizer
TG

csmconsulting
Starting Member

3 Posts

Posted - 2008-05-14 : 16:46:39

here is a sample of what i have...very quick in my dev environment with only a few rows to work on...but production has about 12000 rows.

here you go

----------------------------

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

--NewWeightLossManagerNewReporting 'I','P'
ALTER Procedure NewWeightLossManagerNewReporting
@Type nvarchar(2),
@Environment nvarchar(2)
as
set nocount on
if @Environment = 'D'
Begin
if @Type = 'I'
BEGIN
IF EXISTS (SELECT * FROM tempdb..sysobjects
WHERE id = object_id(N'[tempdb]..[#WeightLossManagerMembers]'))
drop table #WeightLossManagerMembers
IF EXISTS (SELECT * FROM tempdb..sysobjects
WHERE id = object_id(N'[tempdb]..[#WeightLossMemberWeighIn]'))
drop table #WeightLossMemberWeighIn
IF EXISTS (SELECT * FROM tempdb..sysobjects
WHERE id = object_id(N'[tempdb]..[#WeightLossManagerWeighInPeriods]'))
drop table #WeightLossManagerWeighInPeriods

IF EXISTS (SELECT * FROM tempdb..sysobjects
WHERE id = object_id(N'[tempdb]..[#tmp1]'))
drop table #tmp1

CREATE TABLE #WeightLossManagerMembers (
[CompanyID] [int] NULL ,
[MemberID] [int] IDENTITY (1, 1) NOT NULL ,
[MemberFirstName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MemberLastName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MemberGender] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MemberPhoneNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL )

insert into #weightlossmanagermembers
(companyid,memberfirstname,memberlastname,membergender,memberphonenumber)
select 1,'Member','One','M','222-222-2222'
insert into #weightlossmanagermembers
(companyid,memberfirstname,memberlastname,membergender,memberphonenumber)
select 1,'Member','Two','M','222-222-2223'
insert into #weightlossmanagermembers
(companyid,memberfirstname,memberlastname,membergender,memberphonenumber)
select 1,'Member','Three','F','222-222-2224'

CREATE TABLE #WeightLossMemberWeighIn (
[WeighInID] [int] IDENTITY (1, 1) NOT NULL ,
[MemberID] [int] NULL ,
[WeighInPeriodID] [int] NULL ,
[WeighInWeight] [numeric](10, 2) NULL ,
[WeighInChange] [int] NULL)

insert into #WeightLossMemberWeighIn
(MemberID,WeighInPeriodID,WeighInWeight)
VALUES
(1,1,222)
insert into #WeightLossMemberWeighIn
(MemberID,WeighInPeriodID,WeighInWeight)
VALUES
(1,2,210)

insert into #WeightLossMemberWeighIn
(MemberID,WeighInPeriodID,WeighInWeight)
VALUES
(2,1,190)

insert into #WeightLossMemberWeighIn
(MemberID,WeighInPeriodID,WeighInWeight)
VALUES
(3,1,350)
insert into #WeightLossMemberWeighIn
(MemberID,WeighInPeriodID,WeighInWeight)
VALUES
(3,2,349)

insert into #WeightLossMemberWeighIn
(MemberID,WeighInPeriodID,WeighInWeight)
VALUES
(3,3,330)
insert into #WeightLossMemberWeighIn
(MemberID,WeighInPeriodID,WeighInWeight)
VALUES
(3,5,335)

CREATE TABLE #WeightLossManagerWeighInPeriods (
[CompanyID] [int] NULL ,
[WeighInPeriodID] [int] IDENTITY (1, 1) NOT NULL ,
[WeighInPeriodName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WeighInPeriodStatus] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WeighInPeriodLastDataUpdate] [datetime] NULL ,
[WeighInPeriodClosedDate] [datetime] NULL ,
[CurrentWeighInPeriod] [int] NULL )

insert into #WeightLossManagerWeighInPeriods
(CompanyID,WeighInPeriodName,WeighInPeriodStatus)
VALUES
(1,'Initial Weigh In','N')
insert into #WeightLossManagerWeighInPeriods
(CompanyID,WeighInPeriodName,WeighInPeriodStatus)
VALUES
(1,'Week 1','N')
insert into #WeightLossManagerWeighInPeriods
(CompanyID,WeighInPeriodName,WeighInPeriodStatus)
VALUES
(1,'Week 2','N')
insert into
#WeightLossManagerWeighInPeriods
(CompanyID,WeighInPeriodName,WeighInPeriodStatus)
VALUES
(1,'Week 3','N')
insert into
#WeightLossManagerWeighInPeriods
(CompanyID,WeighInPeriodName,WeighInPeriodStatus)
VALUES
(1,'Week 4','N')

Create Table #tmp1
(
MemberID int,
MemberName nvarchar(200),
WeighInPeriod int,
WeighIn numeric(9,4),
WeightLossPercent numeric(9,4)
)

insert into
#tmp1
(MemberID,MemberName,WeighInPeriod)
select
memberid,
memberfirstname+' '+memberlastname,
weighinperiodid
from
#weightlossmanagermembers,
#WeightLossManagerWeighInPeriods

update
#tmp1
set
weighin = w.weighinweight
from
#weightlossmemberweighin w
where
#tmp1.memberid = w.memberid
and
#tmp1.weighinperiod = w.weighinperiodid

while exists(select memberid from #tmp1 where weighin is null)
begin
update
t1
set
weighin = t2.weighin
from
#tmp1 t1
INNER JOIN
#tmp1 t2
on
t2.weighinperiod = t1.weighinperiod-1
and
t1.weighin is null
and
t1.memberid = t2.memberid
end

update
t1
set
WeightLossPercent = (t2.weighin - t1.WeighIn) / t2.weighin
from
#tmp1 t1
INNER JOIN
#tmp1 t2
ON
t1.memberid = t2.memberid
--and t1.weighinperiod = t2.weighinperiod
and t2.weighinperiod = 1
where
t1.WeighInPeriod > 1

select * from #tmp1

END
END --end of dev environment
if @Environment = 'P'
BEGIN
set nocount on
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#tmp2]'))
drop table #tmp2

Create Table #tmp2
(
MemberID int,
MemberName nvarchar(200),
WeighInPeriod int,
WeighIn numeric(9,4),
WeightLossPercent numeric(9,4)
)
CREATE INDEX
ndx_tmpWeighIn
ON
#tmp2 (weighin)
CREATE INDEX
ndx_tmpWeighInMember
ON
#tmp2 (memberid)
CREATE INDEX
ndx_tmpWeighInWeighInPeriod
ON
#tmp2 (weighinperiod)

insert into
#tmp2
(MemberID,MemberName,WeighInPeriod)
select
memberid,
memberfirstname+' '+memberlastname,
weighinperiodid
from
weightlossmanagermembers,
WeightLossManagerWeighInPeriods

update
#tmp2
set
weighin = w.weighinweight
from
weightlossmemberweighin w
where
#tmp2.memberid = w.memberid
and
#tmp2.weighinperiod = w.weighinperiodid

while exists (select memberid from #tmp2 where weighin is null)
update
t1
set
weighin = t2.weighin
from
#tmp2 t1
INNER JOIN
#tmp2 t2
on
t2.weighinperiod = t1.weighinperiod-1
and
t1.weighin is null
and
t1.memberid = t2.memberid
where
t1.weighin is NULL
--and
--t1.memberid < 750

update
t1
set
WeightLossPercent = (t2.weighin - t1.WeighIn) / t2.weighin
from
#tmp2 t1
INNER JOIN
#tmp2 t2
ON
t1.memberid = t2.memberid
--and t1.weighinperiod = t2.weighinperiod
and t2.weighinperiod = 1
where
t1.WeighInPeriod > 1

--and
--t1.memberid < 750
-- select * from #tmp2
select * from #tmp2
where memberid = 19

END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources