Adding up Values - SQL Server Forums

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

General SQL Server Forums

Script Library

Adding up Values

Author

Topic

JerryJay
Starting Member

1 Post

Posted - 2007-04-05 : 16:44:05

I have 21 Boolean fields that I would like to calculate a percentage from. If the boolean value is true I would like to be able to return a percentage of Outcomes available from a possible of 21. So If 7 of my boolean fileds are true then i would like to be able to calculate that 33% of Outcomes are present.

Does anyone have anything that will get me started?

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Client]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Client]
GO

CREATE TABLE [dbo].[Client] (
[RecordID] [int] IDENTITY (1, 1) NOT NULL ,
[FacilityNumber] [int] NOT NULL ,
[AdmitNumber] [int] NOT NULL ,
[LastName] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstName] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MiddleName] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Suffix] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BirthDate] [datetime] NULL ,
[AdmitDate] [datetime] NULL ,
[AdmitWeight] [real] NULL ,
[IdealWeightLow] [real] NULL ,
[IdealWeightHigh] [real] NULL ,
[Unit] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Bed] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MedicaidNumber] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GuardianName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GuardianAddress1] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GuardianAddress2] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GuardianCity] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Outcomeb01] [bit] NULL ,
[Outcomeb02] [bit] NULL ,
[Outcomeb03] [bit] NULL ,
[Outcomeb04] [bit] NULL ,
[Outcomeb05] [bit] NULL ,
[Outcomeb06] [bit] NULL ,
[Outcomeb07] [bit] NULL ,
[Outcomeb08] [bit] NULL ,
[Outcomeb09] [bit] NULL ,
[Outcomeb10] [bit] NULL ,
[Outcomeb11] [bit] NULL ,
[Outcomeb12] [bit] NULL ,
[Outcomeb13] [bit] NULL ,
[Outcomeb14] [bit] NULL ,
[Outcomeb15] [bit] NULL ,
[Outcomeb16] [bit] NULL ,
[Outcomeb17] [bit] NULL ,
[Outcomeb18] [bit] NULL ,
[Outcomeb19] [bit] NULL ,
[Outcomeb20] [bit] NULL ,
[Outcomeb21] [bit] NULL ,
[GuardianState] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Outcomea21] [bit] NULL ,
[Outcomea20] [bit] NULL ,
[Outcomea19] [bit] NULL ,
[Outcomea18] [bit] NULL ,
[Outcomea17] [bit] NULL ,
[Outcomea16] [bit] NULL ,
[Outcomea15] [bit] NULL ,
[Outcomea14] [bit] NULL ,
[Outcomea13] [bit] NULL ,
[Outcomea12] [bit] NULL ,
[Outcomea11] [bit] NULL ,
[Outcomea10] [bit] NULL ,
[Outcomea09] [bit] NULL ,
[Outcomea08] [bit] NULL ,
[Outcomea07] [bit] NULL ,
[Outcomea06] [bit] NULL ,
[GuardianZipCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Outcomea05] [bit] NULL ,
[Outcomea04] [bit] NULL ,
[Outcomea03] [bit] NULL ,
[Outcomea02] [bit] NULL ,
[Outcomea01] [bit] NULL ,
[GuardianPhoneNumber] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SSN] [nvarchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Gender] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Race] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdmittingDiagnosis] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CurrentDiagnosis] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Active] [bit] NULL ,
[CAPMRDDWaiverServices] [bit] NULL ,
[upsize_ts] [timestamp] NULL ,
[EventTriggerCount] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Client] ADD
CONSTRAINT [DF__Client__Facility__117F9D94] DEFAULT (0) FOR [FacilityNumber],
CONSTRAINT [DF_Client_AdmitNumber] DEFAULT (0) FOR [AdmitNumber],
CONSTRAINT [DF__Client__AdmitWei__1367E606] DEFAULT (0) FOR [AdmitWeight],
CONSTRAINT [DF__Client__IdealWei__145C0A3F] DEFAULT (0) FOR [IdealWeightLow],
CONSTRAINT [DF__Client__IdealWei__15502E78] DEFAULT (0) FOR [IdealWeightHigh],
CONSTRAINT [DF__Client__CAPMRDDW__164452B1] DEFAULT (0) FOR [CAPMRDDWaiverServices],
CONSTRAINT [aaaaaClient_PK] PRIMARY KEY NONCLUSTERED
(
[RecordID]
) ON [PRIMARY]
GO

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-04-05 : 16:47:37

The Script Library Forum is for posting working scripts, not for posting questions.

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-05 : 23:27:06

Some newbies often misunderstand that Script Library is the Forum where they can get some "Scripts" for their questions.

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources