How To Calculate Average Row Size Of A Record.

Apr 22, 2008

 I want to calculate average row size of a record. By based on this i want to add some more columns into an existing table.
 Here is my table structure
CREATE TABLE patient_procedure(
    proc_id int IDENTITY(1,1) CONSTRAINT proc_id_pri_key PRIMARY KEY,
    patient_id int NULL,
    surgeon_name varchar(40) NOT NULL,
    proc_name varchar(20) ,
    part_name varchar(30),
    wth_contrast int ,
    wthout_contrast int ,
    wth_wthout_contrast int,
    xray_part varchar(60),
    arth_area varchar(30),
    others varchar(30) ,
    cpt varchar(20) ,
    procedure_date smalldatetime NOT NULL,
    mraloperrun varchar(20),CONSTRAINT patientid_foreign_key FOREIGN KEY(patient_id)
    REFERENCES dbo.patient_information (Patient_id))
 
Now i got a requirement that i have to add two more procedures with different columns.The columns overall size is 195 bytes.
I can place those two procedures as seperate tables. I dont want to do that becuase of front end requirements.
Here the problem is when the user enters these two procedures information remaining fields will store the  null value. I know that when we store the null values into corresponding columns min of 1 byte will be occupied. Please suggest me that shall i include these columns into the above table. If i add these columns is performance will be decreased or not. 
Waiting for valuable suggestions. 

View 2 Replies


ADVERTISEMENT

Calculate Average Within A Query

Nov 29, 2013

I am trying to calculate an average within a query:

coalesce(field1, 0) + coalesce(field2], 0) + coalesce(field3, 0)/Count [AVG]

It seems to be subtracting the value of field3 from the total of 3 fields at some points instead of giving me the average.Some of the results are here:

Count Field1 Field2 Field3 Total Average
===== ====== ====== ====== ===== =======
2NULL6NULL66
11NULL28134129
10NULL33NULL3333
3NULL12NULL1212

[code]....

View 4 Replies View Related

How To Calculate Average Of Fields That Are Not Zero

May 27, 2015

I have a few columns: Week1, Week2, Week3, Week4, Week5

i.e. 
Week1 = 5
Week2 = 0
Week3 = 10
Week4 = 7
Week5 = 0

How do I calculate the average based on the fields that are not zero?

Incorrect:
(5+0+10+7+0)/5 = 4.4
Correct:
(5+0+10+7+0)/3 = 7.3

How to let create a custom column to let the SQL know that I only want to divide by 3 instead of 5?

View 7 Replies View Related

Calculate A Variable Average In SQL Server?

Jun 11, 2007

ok so i have this table:


Code:


Products_Sold
priceSold - money
itemsSold - int


An example of 4 rows on my table would be like this
$1400 80
$1500 85
$1560 82
$1700 81

to calculate the average of the price sold related to the number of sold items just have to do
Select avg(priceSold*itemsSold)

But sometimes i just want the average price of the first 100 sold items, so how can i make my query to just use the first 100 sold items?

in math it would be like this
average= ( (1400*80) + (1500*20) ) / 100

but if i wanted the first 200 it would be like this
average= ( (1400*80) + (1500*85) + (1560*35)) / 200

and if i wanted the first 300 would be like this
average= ( (1400*80) + (1500*85) + (1560*82) + (1700*53)) / 300

but of course the number i want will always be a variable which is less than the total of the products sold. So, how the heck do i program this query where the number of the items sold is variable and it will take the rows of the database depending on how many items were sold.

I hope i didnt wrote my explanation too confusing and that i can get any help from you guys. thank you a lot for the help and byye

View 1 Replies View Related

How Do I Calculate Average Leadtime In Sqlserver...

Sep 2, 2007

Hi,
How do I Calculate Average Leadtime...
I have a Table named "iCalls_Calls" which has 2 Columns (start_Date and Closed_Date).I need to calculate average leadtime based on the columns from this table . I have a query and i need to add this ( calculate average leadtime) to this query.


Code:

SELECT B.USER_DIVISION,B.USER_DEPARTMENT,COUNT(*) FROM iCalls_Calls A INNER JOIN iCalls_Users B on A.REQUESTOR = B.USER_ID
GROUP BY USER_DIVISION,USER_DEPARTMENT



Can anyone send me the correct query to calculate the average time ?
Thanks..

View 14 Replies View Related

Calculate Average Analysis Servives

Aug 6, 2004

Hi this might be pretty simple to you guys out there but i am having issues doing it.

Please help!!!!!!!!!!

Calculate Average of a measure called DIST irrespective of any dimension the page field.

Ie. to say it should calculate the average for any doension i bring on the rwo field.

Looking forward to your help.


you can mail me at hem_k01@yahoo.com

View 6 Replies View Related

Calculate Average Growth Rate

Apr 30, 2008

I've got a statistics table that I've been writing to for about 2 years now. Every saturday night, a size (in MB) snapshot of each DB file is taken and dumped into this table. I'm then emailed a copy for that week.

Now, I'm trying to figure out what the fastest growers are. Here's the table ddl

CREATE TABLE [dbo].[DBSizeStats] (
[statid] [int] IDENTITY (1, 1) NOT NULL ,
[LogDate] [datetime] NULL ,
[Server] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DBName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MDFName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MDFSize] [decimal](18, 0) NULL ,
[LDFName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LDFSize] [decimal](18, 0) NULL ,
[TotalSize] [decimal](18, 0) NULL
) ON [PRIMARY]
GO


What I'm trying to figure out is how to query the average monthly and yearly growth percentages per DB on the MDFSize column.

I'm usually pretty good at this sort of thing, but I just can't seem to wrap my head around how to solve this issue. I'm not having a very good math day.

what am I missing here?

View 10 Replies View Related

How To Calculate Average Number Of Days Taken

Jan 16, 2014

How to calculate the overall average number of days taken to complete something.

The two fields are enquiry_date (date enquiry is recorded) and complete_date (date enquiry completed/closed).

Each enquiry has a enquiry_number

Sample data typically looks like:

Enquiry number - enquiry_time - complete date
1 - 01/01/2014 - 12/01/2014
2 - 01/01/2014 - 11/01/2014
3 - 01/01/2014 - 10/01/2014
4 - 01/01/2014 - 07/01/2014
5 - 01/01/2014 - 12/01/2014
6 - 01/01/2014 - 04/01/2014

etc.

What is the piece of SQL which looks at the average date difference for each enquiry and then sums it all up to give an overall average number of days it takes?

View 2 Replies View Related

How Do I Calculate The Average Variable Length For A Varchar?

Feb 26, 2008

im trying to learn how to calculate table size.
i understand some of it, but im stuck at calculating the varchars

Ex. i have 2 varchar columns
- varchar(50)
- varchar(100)

i'm suppose to find the average length for them?

i'm suppose to use that to add up to my ROW SIZE

and also after i got the average, do i add 2 for variable columns overhead and another 2 for Row pointer in row offset array

please help me asap before 2morrow night.
Thanks!
i have a test

View 2 Replies View Related

Calculate Rolling Average Cost From Two Dataset

Sep 11, 2013

I need calculating a rolling 3 month average cost from the two dataset below. Which is the 3 month Average of Dataset1 / Dataset 2.

Dataset 1:

SELECT(factAdmissions.ContractCode + '-' +factAdmissions.BenefitPlanCode) AS [Contract Code],
factAdmissions.AdmitCCYYMM,
ISNULL(sum(AmountPaid),0)As [Amount Paid]
FROM factAdmissions

[Code] ....

Dataset2:

Select

(factMembership.ContractCode+'-'+ factMembership.BenefitPlanCode) As Product,
EffectiveCCYYMM,
ISNULL(count(Distinct MemberId),0) As MemberCount
From factMembership
Where EffectiveCCYYMM >= '200701'

[Code] ....

View 20 Replies View Related

Calculate Sum And Average - Patient Information For All Nurses

Oct 8, 2013

I have one table with columns patientName , Nurse,ArrivalDate, DepartDate . It has all the patient information for all the Nurses.

I need to calculate Number of patients per Nurse and Average number of patients per day per nurse.

We need to calculate Average Number of patients per day per nurse = Total Patients per nurse/No.of unique days they worked

I need my report as like this.

DistinctNurse No.ofPatients AvgNo.ofpatients PerDay
Tina 100 25
Sony 50 16.6

How to get the result as above.

View 11 Replies View Related

Transact SQL :: Calculate Average Based On A Scenario

Oct 30, 2015

Col1                      Col2       Col3          Col4
54763.00              21           0              0             
--Row1
59574.00              23           0              0             
--Row2
64085.00              20           0              0             
--Row3
0.0               0.00      0           0              0             
--Row4

I'm trying to calculate Average of Col1 of above table based on below scenario:

CASE WHEN all the columns in the above table are “0” (as highlighted) THEN I want AVERAGE of Col1 as (Row1+Row2+Row3)/3

ELSE if at least one of the column of highlighted row has value other than “0”, THEN I want the AVERAGE of Col1 to be (Row1+Row2+Row3+Row4)/4

View 7 Replies View Related

Average Size Of A Row In A Table

Feb 20, 2001

How do I find the average size of a row in a table? I need to calculate a row size in a number of tables, then sum those to find the average size of one record ( a hotel guest in this case), which includes entries in a dozen tables.

Thanks

View 1 Replies View Related

Average Row Size For A Table

Aug 21, 2002

I want to find out the average rwo size of a table in sql server 2000. How can i achive this requirment? Please let me know how can i do this?

Thanks & regards,

Dishant Sharma

View 1 Replies View Related

T-SQL (SS2K8) :: Calculating Average For Each Record

Jul 4, 2014

How to calculate Average sal foe below scenario.

I am having tables with 12 columns as jan,feb,.......dec.

Now I want to calculate average salary for each record, but condition is that if any month salary is zero then that column also exclude from average calculation.

For example : if jan and feb column values are zero then i want to calculate (mar+apr+...+dec)/10.

View 5 Replies View Related

How To Calculate Database Size

Nov 6, 1999

How to calculate database size ?

When u setup e-commerce site for selling product then
how to calculate datbase size ?

Navnit

View 1 Replies View Related

Calculate Table Size

Oct 10, 2001

Hi all!

How can I calculate the combined table size for the following:
The Destination and the ShipmentWages tables on a database of a courier service have approximately 10,000 rows and 15,000 rows, respectively. The average row size of the Destination table is 4KB and that of the ShipmentWages table is 3KB.

Thanks,
ndba

View 1 Replies View Related

How Can I Calculate Table Size ?

Jan 29, 2008

this is the table i have created .

CREATE TABLE [dbo].[Table] (
[BCode] [varchar] (3) ,
[RefNo] [varchar] (12) ,
[RType] [varchar] (2) ,
[TheirRef] [varchar] (50) ,
[Amount] [money] NULL ,
[AccountNo] [varchar] (20) NULL ,
[EnteredDate] [datetime] NULL ,
[EnteredBy] [varchar] (6) ,
[IsTrue] [varchar] (1) ,
[RDate] [datetime] NULL ,
[Details] [varchar] (255) ,
[PostBy] [varchar] (6) ,
[SeqNo] [int] IDENTITY (1, 1) ,
[ThisCode] [varchar] (3) ,
[VDate] [datetime] NULL ,
[Id] [varchar] (20) ,
[Flag] [varchar] (1) ,
[TDate] [datetime] NULL ,
[Type] [char] (1) ,
[SerialNo] [int] NULL
) ON [PRIMARY]
GO

if i insert a single row in a table with values :

INSERT INTO [Table]
SELECT '100','1','DD',1000000,'999999000',
'2008-01-29','sa','F','2008-01-29','PARTIAL DATA','11502',
'101','2008-01-29','ABC1111111','T','2008-01-29','S','11204'

EXECUTE sp_spaceused 'Table'

name | rows |reserved | data |index_size | unused
[Table] | 1 |16KB | 8KB | 8KB | 0KB

i couldnot understand
i) how the system calculate this 16 KB and 8 KB ?
ii) if i changed the VARCHAR(255) to VARCHAR(50) ,will there be difference in size of the table ,if so then how ?
iii) how can i see the change in size of table by reducing the size of datatype ?

can anybody help me ?

View 3 Replies View Related

Calculate The Size Of A Database And Backup It.

Feb 13, 2004

Hi all,
please show me the way how to calculate the size of a database in SQL 2000. Whith this size we have already calculated, how much space need to make a backup file for this database.

thanks.

View 5 Replies View Related

Transact SQL :: Calculate Size Of All Tables In A Database

Nov 16, 2015

I need to build a query to calculate the size of all tables in a database ...

View 5 Replies View Related

Transact SQL :: How To Calculate Size Of Files That Have UNC Paths For

Mar 27, 2014

I am on SQL Server 2008 R2. I have a table that contains a field called [Location]. In that field is a UNC path to the physical file on the repository. Is there a way in SQL Server that I can say give me the select sum([Location] UNC file) where criteria? I saw some posts about xp_filesize or xp_GetFileDetails, but I do not see them in master. I am unable to add anything and wondering if there is any native functionality that would allow me to accomplish this!?

View 4 Replies View Related

How To Calculate The Size Of A VARBINARY(max) Field Or Variable

Jan 7, 2008



How can i do the following:


Calculate the size of a varbinary(max) field or variable

Calculate the average of a varbinary(max) table column
I am using SQL 2005
Thanks for your posting

View 3 Replies View Related

Disaster Recovery: Calculate DB Size Based On .dmp Files

Feb 7, 2000

I have a client with no backup of MASTERdb or Help_rev_devices, all I have is a couple of .dmp's of the production data. In order to run DISK REINIT I need to know the size of the database, is there any way of finding out the size of the database device, if all you have to go with is a backup dump file?

I know it won't be pretty.

`Ken

View 1 Replies View Related

How To Calculate The Space (size In Bytes) Used By A BLOB Column In A Row

Dec 3, 2007

What is the easiest way to calculate the space (size in bytes) used by a BLOB column that is already stored in a particular row?

Thanks.

View 3 Replies View Related

SQL 2012 :: Check Columns Before Process And Calculate Size In Byte In SSIS

Apr 10, 2014

I need find out the number of columns in flat file before i process that particular file.

I have file name in @filename variable and file path is @filepath variable.

But do not not that how i will check the column name in before i will process that file.

@filePath = C:DatabaseSourceFilesCAHCVSSourceFiles

And I am using for each loop container to read the file one by one and put the file name in @filename variable.

and my file name like

Product_20120607060930.txt
Product_20130708060930.txt

My file structure is:

ID,Name,City,Country,Phone
1,Riya,Pune,India,454564
2,Jiya,New Jersey,India,454564
3,Riya,St Louis,USA,454564
4,Riya,Belleville,USA,454564
5,Riya,Miami,USA,454564

Now what i have to do is i need to make sure that ID,Name,City,County,Phone is there in flat file. if it is not there then i have to send mail to client saying that file is not valid.

Let me know how i will do it.I need to also calculate the size of flat file.

View 0 Replies View Related

SQL 2012 :: Calculate TempDB Size For Read Committed Snapshot Enabled

Apr 14, 2014

I receive Error: 3967, Severity: 17, State: 1. Insufficient space in tempdb to hold row versions. We have 8 data files for temp db of 10210 GB size and given 10240 GB as max size.

As MS suggest to calculate the temp db file size and growth rate we need to monitor the perform counters Free Space in Tempdb (KB) and Version Store Size (KB) in the Transactions object.

basic formula: [Size of Version Store] = 2 * [Version store data generated per minute] * [Longest running time (minutes) of your transaction

My report disk utilizations says tempdb is full ? I thonk I need a shrink for the file .

Still I am confused in calculating the size , My perform counter gives me data as such

Free Space in tempdb (KB)               279938496
Version Generation rate (KB/s)           53681040
Version Cleanup rate (KB/s)       53422320
Version Store Size (KB)      258720
Version Store unit count        22
Version Store unit creation                      774
Version Store unit truncation         752

View 4 Replies View Related

SQL Server 2012 :: Calculate Number Of Groups And Group Size With Multiple Criteria

Jun 15, 2015

I need to calculate the last two columns (noofgrp and grpsize) No of Groups (count of Clientid) and Group Size (number of clients in each group) according to begtim and endtime. So I tried the following in the first Temp table

GrpSize= count(clientid) over (partition by begtime,endtime) else 0 end
and in the second Temp Table, I have
select
,GrpSize=sum(grpsize)
,NoofGrp=count(distinct grpsize)
From Temp1

The issue is for the date of 5/26, the begtime and endtime are not consistent. in Grp1 (group 1) all clients starts the session at 1030 and ends at 1200 (90 minutes session) except one who starts at 11 and end at 1200 (row 8). For this client since his/her endtime is the same as others, I want that client to be in the first group(Grp1). Reverse is true for the second group (Grp2). All clients begtime is 12:30 and endtime is 1400 but clientid=2 (row 9) who begtime =1230 but endtime = 1300. However, since this client begtime is the same as the rest, I wan that client to be in the second group (grp2) My partition over creates 4 groups rather than two.

View 9 Replies View Related

Record Size

Oct 5, 2007

What can be the possible maximum size of one record in SQL 2005 Enterrprise Edition server?

View 1 Replies View Related

Calculating The Record Size.

Feb 8, 2000

How do I calculate the Record size? I mean the Row size for each table.
I heard that there are formulas to do that and the calculation is different for Fixed lenght Columns and Variable length Columns.

I need to calculate the record size for nearly 500 tables.This will help me to decide on the DISk capacities.

Suggest me if any scipts or calculations already available.

Thanks in advance.

Samson

View 4 Replies View Related

Record Size More Than 8060B

Jun 6, 2006

Hi,In MS SQL Server, while creating the table, I am getting a warningmessage saying like "maximum row size can exceed allowed maximum sizeof 8060 bytes".Is there any way in SQL Server, to increase this allowed maximum rowsize?The setting like "set ANSI_WARNINGS OFF" is not suitable in my case. Ineed creation of table with around 11000 bytes in the record (Summationof precision of all the columns). And more over, I do not want to looseany data (truncation) in inert operation to keep on the whole recordsize to 8060B.Thanks a lot in advance.Ramakrishna.

View 3 Replies View Related

How To Know The Size Used By A Record In A Table

Jan 24, 2008

Hi,

I need a query to know (calculate) the size used by a record in a field by table, I was tried with the follow query (but this only show me the max length of the data type):

SELECT c.name AS column_name
,c.column_id
,t.name AS type_name
,c.max_length
,c.precision
,c.scale
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
WHERE c.object_id = OBJECT_ID('dbo.bloqueos_2005')
ORDER BY c.column_id;
GO

I was tried whit the system views that contains column info, but no one give me the correct information.

If any one have the query or know some way to do this, please help me.

Thanks,

Diego Jaramillo Celis

View 8 Replies View Related

Analysis :: Calculation Of average Using DAX AVERAGE And AVERAGEX

Jun 21, 2015

Calculation of an average using DAX' AVERAGE and AVERAGEX.This is the manual calculation in DW, using SQL.In the tabular project (we're i've noticed that these 4 %'s are in itself strange), in a 1st moment i've noticed that i would have to divide by 100 to get the same values as in the DW, so i've used AVERAGEX:

Avg_AMP:=AVERAGEX('Fct Sales';'Fct Sales'[_AMP]/100)
Avg_AMPdollar:=AVERAGEX('Fct Sales';'Fct Sales'[_AMPdollar]/100)
Avg_FMP:=AVERAGEX('Fct Sales';'Fct Sales'[_FMP]/100)
Avg_FMPdollar:=AVERAGEX('Fct Sales';'Fct Sales'[_FMPdollar]/100)

The results were, respectively: 701,68; 2120,60...; -669,441; and  finally **-694,74** for Avg_FMPdollar.i can't understand the difference to SQL calculation, since calculations are similar to the other ones. After that i've tried:

test:=SUM([_FMPdollar])/countrows('Fct Sales') AND the value was EQUAL to SQL: -672,17
test2:=AVERAGE('Fct Sales'[_Frontend Margin Percent ACY]), and here, without dividing by 100 in the end, -696,74...

So, AVERAGE and AVERAGEX have a diferent behaviour from the SUM divided by COUNTROWS, and even more strange, test2 doesn't need the division by 100 to be similar to AVERAGEX result.

I even calculated the number of blanks and number of zeros on each column, could it be a difference on the denominator (so, a division by a diferente number of rows), but they are equal on each row.

View 2 Replies View Related

Computing Full Record Row Size

Dec 19, 2001

Is there a quick easy way to calculate rowsize for a record in a table that has data in each column?

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved