# Calculating The Sum Of Columns...

Oct 31, 2006

It may be simple question, but how can i get the sum of column? with conditions like getting the some of clicks one user send and so...

## Calculating Difference Between Two Columns

Feb 12, 2014

To calculate how many months are between the current date minus the First_Post_Date

For example
The First_Post_Date is displayed as follows following “25/07/2012”

Current date is 12-02-2014

The difference between the two dates is approx 20 months ..To make the calculation easier, it might be easier to default the day of First_Post_Date to 01 and do the same with the currentdate

So it would be 01/07/12 and 01/02/14

## Problem Calculating The Division Of 2 Columns

Apr 20, 2008

Hi
I have 2 tables

table1 which gives the schedule for day
id sat sun mon tue wed thu fri
1 40 50 35 75 80 90 95
2 30 40 45 45 95 85 45
3 40 50 25 25 75 75 55
4 50 60 35 55 65 65 65
5 70 35 75 65 30 35 70

table2 which gives total schedule for week
id week
1 500
2 600
3 550
4 450
5 650

i want to calculate to percentage schedule for each day for week for each value in id column

something like
sat/week where table1.id = table2.id

what im doing is
select (sat/week)
from table1
inner join table2
on table1.id = table2.id
group by id

its giving me error because im using group by clause

what can i do ??

## SQL Server 2008 :: Calculating Median Over 2 Columns?

Aug 13, 2015

I have a database with 1million+ records in and i'm trying to collect the median values of column(2) for all distinct values in column (1)

Example DB:

Column 1 Column 2
978555 500
978555 502
978555 480
978555 490
978324 1111
978324 1102
978311 122
978311 120
978994 804
978320 359

and I need it to display on SELECT as

column 1 column 2
978555 495
978324 1106
978311 121
978994 804
978320 359

Is this possible on 2008 R2?

## Calculating Percentages And Showing In Separate Columns

Aug 31, 2013

SELECT DISTINCT CASE WHEN SM.SERVICE_TYPE_N = 1 THEN 'LABORATORY'
WHEN SM.SERVICE_TYPE_N = 2 THEN 'PODIATRY'
WHEN SM.SERVICE_TYPE_N = 3 THEN 'ADMINISTRATION'
WHEN SM.SERVICE_TYPE_N = 4 THEN 'DIET'
WHEN SM.SERVICE_TYPE_N = 5 THEN 'DENTAL'

[Code] ....

In the above query i need to calculate 100%,30% and percentage other than 100 and 30 and show them in separate columns how to do that?

1)ROUND(SUM(BM.CONCESSION_AMOUNT_M),2)AS [100_PERCENT]
WHERE BM.BILL_AMOUNT_M=BM.CONCESSION_AMOUNT_M

2)ROUND(SUM(BM.CONCESSION_AMOUNT_M),2)AS [30_PERCENT]
WHERE AND BM.CONCESSION_AMOUNT_M=BM.BILL_AMOUNT_M * 0.30

3) ROUND(SUM(BM.CONCESSION_AMOUNT_M),2)AS [OTHER_CONCESSION_PERCENT]
WHERE BM.CONCESSION_AMOUNT_M between BM.BILL_AMOUNT_M*0.001 and BM.BILL_AMOUNT_M*0.299
OR BM.CONCESSION_AMOUNT_M between BM.BILL_AMOUNT_M*0.301 and BM.BILL_AMOUNT_M*0.999

## Reporting Services :: Calculating Difference Between Columns Within Column Group

Jun 15, 2015

Given the attached report, is there an easy way of calculating the difference between the Today and QTR Start column? Because of the Account Group, the report looks like the sample shown on the second image.

Sample report:

## RS2k Issue: PDF Exporting Report With Hidden Columns, Stretches Visible Columns And Misplaces Columns On Spanned Page

Dec 13, 2007

Hello:

I am running into an issue with RS2k PDF export.

Case: Exporting Report to PDF/Printing/TIFF
Report: Contains 1 table with 19 Columns. 1 column is static, the other 18 are visible at the users descretion. Report when printed/exported to pdf spans 2 pages naturally, 16 on the first page, 3 on the second, and the column widths have been adjusted to provide a perfect page span .

User A elects to hide two of the columns, and show the rest. The report complies and the viewable version is perfect, the excel export is perfect.. the PDF export on the first page causes every fith column, starting with the last column that was hidden to be expanded to take up additional width. On the spanned page, it renders the first column on that page correctly, then there is a white space gap equal to the width of the hidden columns and then the rest of the cells show with the last column expanded to take up the same width that the original 2 columns were going to take up, plus its width.

We have tried several different settings to see if it helps this issue or makes it worse. So far cangrow/canshrink/keep together have made no impact. It is not possible to increase the page size due to limited page size selection availablility for the client. There are far too many combinations of what the user can elect to show or hide to put together different tables to show and hide on the same report to remove this effect.

Any help or suggestion on this issue would be appreciated

## T-SQL And Calculating A Sum

Sep 26, 2006

I want to sum up a column, but only summing up the top 25 quantites and then I want to subtract the sum of all quantities from the total of the top 25...how would this be done with t-sql?

## Calculating PK

Jul 2, 2001

SQL7

I am interested in your opinion on the creation of primary keys in a table - simple ones that is. It would be the replacement for an Identity column.

Some say calculate the value off a lookup table, others say use an external object like c++ dll reading/incrementing from a flat file.

Using an identity column reduces portability of the table.

Thoughts ?

Craig

## Help !!!calculating Age

Nov 15, 2001

Hi,
What the best way to calculate the age given two datetimes.
I tried using the DATEDIFF(yyy,startdate,enddate) but that seems to only compare the year and not look at the specific day.

I am looking for something that would return the following output

birthday= 12/6/1973
EndDate = 11/30/2001
then the age should be 27

If birthday = 12/6/1973
enddate = 12/11/1973
then the age should be 28

Zoey

## Calculating RFM

Apr 17, 2007

I'm in the process of developing a Kimball-method CRM data warehouse and I'd like to have Recency, Frequency, Monetary "scores" at the customer level. Am I better off creating these in the relational database and feeding them to SSAS or have SSAS do the aggregations and assign the "scores"?

Also, has anyone located any SSIS transform scripts that would calculated RFM scores? This is probably a pretty easy task in VB.net but I haven't gone down that road yet.

Steve

## Calculating Ages

Jul 3, 2007

When users register with my site they give me their DoB, how can I work out an age from it. I was thinkin about doing it from the year, but that isnt very accurate. Can anyone help? thanks si! ps Id only want to work out how many years old they are. si!

## Calculating ID Numbers

Mar 28, 2008

I needed to come up with an algorithm to create unique user- friendly account numbers such as AC0000000001, AC0000000002, etc...Where they increment by 1. I created a SQL function that retrieves the previous number generated, adds 1 to it, inserts the new value into  the table, then returns the new value. I started thinking, what if the function is ran at the same time? What if function # 1 creates the new number, and function #2 creates a new number as well before function #1 inserts it? Is this a possible scenario? If so, how do I lock the process until the function completes to prevent this?  Thanks for any help you provide.

## Calculating The 3rd Value By The First 2's Selection

May 30, 2008

Hi,
First off, let me state that i am new to ASP.net... and have had to hit the ground running... I have looked on the net but have not found a suitable explanation on how to do this.
Let me explain what i am trying to do: In my aspx page, i have 2 dropdown boxes where the user will select a skill catagory, and then a skill. Based on that selection, i would like to compute what the Service line will be. There is a primary and foreign key on CatID on the skills and SkillCat tables. This data would need to be inserted into the another table aswell, and this would need to go into a report later.
How do i go about doing this? Some examples would be greatly appreciated. And if you know of a good link, please post it for me.
Skills Table

CatID
SkillID
Skills
SL_ID

1
4
Skill 1
3

2
5
Skill 2
2

2
6
Skill 3
2

3
7
Skill 4
4

3
8
Skill 5
4

4
9
Skill 6
NULL

4
10
Skill 7
NULL

5
11
Skill 8
3

6
12
Skill 9
3

7
13
Skill 10
4

8
14
Skill 11
1

9
15
Skill 12
3

9
16
Skill 13
3

10
17
Skill 14
6

11
18
Skill 15
1
Skills Catagory

CATID
Org
Skillcat

1
Org1
Skill Category 1

2
Org2
Skill Category 2

3
Org3
Skill Category 3

4
Org4
Skill Category 4

5
Org5
Skill Category 5

6
Org6
Skill Category 6

7
Org7
Skill Category 7

8
Org8
Skill Category 8

9
Org9
Skill Category 9
Service Line Table

SL_ID
Service_line

1
Service Line 1

2
Service Line 2

3
Service Line 3

4
Service Line 4

5
Service Line 5

6
Service Line 6

7
Service Line 7

Dash

## Calculating Avg Rowsize In My Db...

Nov 23, 2001

Hi,

I'm using SQL Server 7.0 SP3.

I need to calculate the average size of a record in my database to assist in estimating growth.

Is there a quick and/or easy way of doing this?

Darrin

## Help With Calculating Dates.

Aug 26, 2005

Hi,

I'm fairly new to MS SQL, but I'm trying to query the time elapse between two dates. Example below...

Select
dbo.res.book_date,
dbo.res.arrive_date,
Cast (dbo.translog.systemdate as datetime)
From
dbo.res
Where
dbo.res.resid > '500'

The third line is where I need help with a statement. I need the elapse time. Ex. if the book date is 20050820100000 and the arrive date is 20050820120000, I would like the third Select statement to return 2:00 hours or something similar. Thanks.

## Calculating Time

Aug 27, 2005

Hello,
I am fairly new to this, but I need to write a query that calculates whether an order made a shipping cutoff based on creation time, and when it actually shipped, based on local customer time. All times in the table below are central time.
Heres where it gets complicated for me. If the order was created after 8:00am local customer time the same day, and shipped prior to 5pm local customer time, it would be considered a pass, or a 1. If an order was created after 8am local customer time, BUT didnt ship until AFTER 5pm local customer time, it would be considered a failure or a 0. And to complicate things even more, if the order is created after 5pm local customer time, the order create time would be treated as it was created at 8am local customer time the following day local customer time, and would need to be shipped prior to 5pm local customer time that day. Would CASE be appropriate for this? Or am I thinking wrong? Any help would really be appreciated!!

Ordernum OrderCreate OrderShipDate TimeZ
67890 5/5/2005 11:575/6/2005 15:33 EST
35789 5/5/2005 13:575/5/2005 14:19 MST
44465 5/5/2005 13:58 5/5/2005 21:58 CST
87654 5/5/2005 18:00 5/7/2005 21:58 PST

## Calculating Median In SQL

Aug 25, 2004

Hi All,
I have a table that of server names and their execution times that run in to hundreds of thousands of records. What i need is some SQL that gives me the median execution times for each of these different servers. At the moment i have some SQL that only gives me the median for all the records in the table not the median execution time for every different server name. For example my tables looks something like this;

ServerName | ExecTime
-----------------------
server1 | 0.07
server2 | 0.17
server1 | 0.27
server1 | 0.37
server2 | 0.47
server1 | 0.57
server1 | 0.67
server2 | 0.77

My SQL below gives me

ServerName | ExecTime
-----------------------
server1 | 0.37

Where as i want

ServerName | ExecTime
-----------------------
server1 | 0.37
server2 | 0.47

Here is my SQL, hope someone can modify it and thanks in advance.

Code:

SELECT DISTINCT instance, exec_time AS median
FROM (SELECT instance, exec_time
FROM (SELECT TOP 1 exec_time = exec_time * 1.0, instance
FROM (SELECT TOP 50 PERCENT exec_time, instance
FROM llserverlogs
ORDER BY exec_time) sub_a
ORDER BY 1 DESC) sub_1
UNION ALL
SELECT instance, exec_time
FROM (SELECT TOP 1 exec_time = exec_time * 1.0, instance
FROM (SELECT TOP 50 PERCENT exec_time, instance
FROM llserverlogs
ORDER BY exec_time DESC) sub_b
ORDER BY 1) sub_2)

## Calculating Dates

May 16, 2004

I have a table in my sql server that calculates renewal dates for me, that date is based on the final suit date. The table has the ssn, and the FinalSuitDate its in a one to many relationship with the employeetable.

the Finalsuit table is suppose to calculate the renewal dates(which I'm trying to do in a query) my original expression in access was using dateserial FirstRenewal: DateSerial(Year([FINALSUITDONE])+2,Month([FINALSUITDONE])+1,Day([FINALSUITDONE])=30), but sql does not recognize that.

For Example

If the Finalsuit is 12/01/2000
then the renewal would be 12/31/2002
the renewal are to be done on the last day of the month and two years from the finalsuit date. Problems is I'm having problems doing this in sql servers query?

Can someone out there help please

## Calculating Age In Years

Dec 19, 2001

--Use the following formula to calculate a person's age in years in a stored procedure,
--where @dob is the person's date of birth and @yyyymmdd is the date on which to determine the age:

DECLARE @age int
DECLARE @dob datetime
DECLARE @yyyymmdd varchar(11)

SELECT @dob = '12/06/1966'
SELECT @yyyymmdd = GETDATE()
SELECT @age = FLOOR(DATEDIFF(day, @dob, @yyyymmdd) / 365.25)
PRINT CONVERT(varchar, @age)

--Notes:
--Substitute "getdate()" for the @yyyymmdd variable if you want to determine the person's age right now.
--The function divides by 365.25 to allow for leap years and uses the FLOOR function to make sure the function returns an integer.
--The function DATEDIFF(year, @dob, @yyyymmdd) doesn't work because Microsoft chose to implement
--the "year" part of the function to calculate the number of year boundaries crossed.

## Calculating Between Tables

Nov 15, 2005

Hi All,

I have a very complicated project and for some reason I have decided to use SQL server as the backend database. I am new to it and have encountered my first problem. In basic terms what I want to be able to do is increment/decrement a field in a table according to something that happens in a different table. Where do I start?

So for example, a stereo system/cd player can have 10 cds at one particular time, when 2 cds are removed, then 8 slots are available. Then when 1 of the 2 remaining slots are filled then 1 slot remains and so on....so I want some sort of a counter field. Using that field I want to be able to generate a report on what is currently available.

Thanks

Ben

## Calculating Interval

Mar 16, 2006

If two records containing datetime stamps are SELECTed, how can the interval between them be quickly calculated?

## Calculating Delays With Sql

Jul 11, 2006

I have three columns in my table,'StartDate',EndDate','BatchID'

When I upadate the the table for 'EndDate' I want to calculate the

I want to store this modified value somewhere and calculate the difference in (Value of 'End Date' Before update)-(Value of 'EndDate' after Update')

How it is possible?
Swati

## Calculating Duration

Jan 18, 2007

I am trying to get a query that will allow me to report the time taken to complete a certain training module.

The database itself does not have a duration field so I am tring to get the duration by using MIN and MAX. I can get the timing for when the module was opened and the time for the last mouse click on it, from this I need to be able to calculate the time taken to complete.

Query I am using to get the basic info comes from 3 tables so I have only attached the relevent output. Query used is as follow:

SELECT *
FROM PPS_SCOS, PPS_TRANSCRIPTS, PPS_TRANSCRIPT_DETAILS, PPS_PRINCIPALS
WHERE PPS_SCOS.SCO_ID = PPS_TRANSCRIPTS.SCO_ID
AND PPS_TRANSCRIPTS.TRANSCRIPT_ID = PPS_TRANSCRIPT_DETAILS.TRANSCRIPT_ID
AND PPS_TRANSCRIPTS.PRINCIPAL_ID = PPS_PRINCIPALS.PRINCIPAL_ID
AND PPS_SCOS.NAME LIKE 'MTM-106 The Dangers of Smoking'
AND PPS_PRINCIPALS.NAME LIKE 'Nigel Cordiner'
AND PPS_TRANSCRIPTS.TICKET NOT LIKE 'l-%'
ORDER BY PPS_TRANSCRIPT_DETAILS.DATE_CREATED

Output:

pps_scospps_scospps_transcript_detailspps_principalspps_principals
SCO_IDNAME DATE_CREATED PRINCIPAL_ID NAME
136850MTM-106 The Dangers of Smoking08:17:2516287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:17:2516287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:17:4016287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:18:2516287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:18:5716287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:19:1416287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:19:4716287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:20:2116287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:20:4416287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:21:2616287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:22:1316287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:24:5516287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:25:1216287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:25:2916287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:26:4916287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:27:0216287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:27:2916287Nigel Cordiner
136850MTM-106 The Dangers of Smoking08:27:4316287Nigel Cordiner

Have added the column heading and the tables the output comes from.

Relatively new to SQL so any help would be greatly received.

## Calculating Percentages For Each Row

Dec 10, 2007

One more for you while I'm trying to remember my SQL skill from a few years ago (last one of the day - promise):
My database looks like:

(Ignore that the ERD is in Access, the database is SQL Server 2005)

How do I calculate sales percentages?
I.E. how do I calculate the percentage that each product has sold as a percentage of the total sales?

What I'm aiming for is each row of products is shown, with its percentage of the whole sales.

AVG is involved this time, isn't it?

The productID is of the int datatype (not that you need to know that field - but never mind) and the quantity is a smallint, before you ask

Thank you in advance for any replies.

Paul

## Calculating In A Statement

Mar 3, 2008

Hi,
Im building a DB for a uni project and im having a little trouble creating a column that makes a calculation using 2 of the other columns.

I need to do this;
Select Reps, Weight, ((Reps / 30) + 1) * Weight AS [1RM]
From ......

But, SQL server 2005 doesnt seem to like my syntax and gives a wrong result.
Can anyone help?

Thanks,
M4tt

## Calculating A Percentage

Mar 25, 2008

My site has members. Each member has a certain goal, an amount that they are hoping to raise. Here's my tables:

---------------------
Members
---------------------
ID | Goal
---------------------

---------------------
Donors
---------------------
Amount | MemberID
---------------------

I'm trying to get the percentage of money raised compared to their goal. Basically 100*(SUM(Amount)/Goal). Here's my stored procedure:

CREATE PROCEDURE sproc_GetGoalPercentage
(
@memberid int
)
AS
SELECT(SUM(D.Amount)/M.Goal)*100
FROMMembers AS M INNER JOIN Donors AS D
ON M.ID = D.MemberID
WHEREM.ID = @memberid
GROUP BY M.Goal
RETURN

All it does is return 0.

## Calculating Row Sizes

Feb 20, 2008

I'm putting together a manual system that tracks data growth in a certain database. I was going to use sp_spaceused as a part of it, but then realized the datatypes for size are CHAR, not INT or BIGINT. I was going to do counts, averages, etc. on those columns but that wouldn't work against a CHAR field obviously. I could easily write a little something to strip out the KB, but was hoping there was another way to get those figures.

Secondly...has anynoe seen a stored procedure/code/etc. that just calculates the largest/smallest/average row size for a table? I haven't been able to find anything anywhere...

Any insight would be greatly appreciated...

Thanks!

Aug 20, 2007

Hi,

I am wondering what is vb.net method of calculating the prior business day (Monday - Friday).

For example, if today is Monday, Aug. 20, the last business day would have been Friday, Aug. 17.

I have a way to do this in SQL, but not vb.net.

Thanks much

## Calculating Distince

Jul 12, 2006

I have a table with only one column each for longitude and latitude. They will be passing the sa_property_id which has a long and lat listed. I need to show what else is withing 1 mile or 5 miles of that properties long/lat

--Table
CREATE TABLE [dbo].[PropertyDetails] (
[SA_PROPERTY_ID] [int] NOT NULL ,
[SA_X_COORD] [float] NULL ,
[SA_Y_COORD] [float] NULL ,
[Longitude] [float] NULL ,
[Latitude] [float] NULL ,
) ON [PRIMARY]
GO

Data output from above table

SASA_PROPERTY_ID Longitude Latitude
-------------- ----------------------------------------------------- -----------------------------------------------------
23790208 -120.619821 39.568587999999998

(1 row(s) affected)

Passed parameter = SA_PROPERTY_ID

Need list of matching records within 1 mile of above record

Would this be the best query for it? how do I define the radius?

I also have a function, but am not sure it can do what I need it to.

/*
* History: 14-Nov-05 CNH Simplified to resolve Divide By Zero problems
* 29-Mar-02 DKS Created by Deepak K Srinivasan
*
* Test Data:
*
* City Latt Long
* ---- ---- ----
* NYC 40.77 74
* SF 37.75 122.68
* Oakland 37.73 122.22
* Burbank 34.2 118.37
*/
CREATE FUNCTION dbo.CalculateDistance(@LatDeg1 FLOAT, @LonDeg1 FLOAT, @LatDeg2 FLOAT, @LonDeg2 FLOAT)
RETURNS FLOAT AS BEGIN
@DotProd AS FLOAT

SET @EarthRadiusMiles = 3958.755 -- Volumic Radius of the Earth in Miles

-- T-SQL provides ACOS. So, there is no need to implement it via ATAN:

END

## Calculating Next Reference

Feb 6, 2008

Hi,

Our tables have a reference field which users can enter their own references into.

However, they can choose to have the system calculate the next reference number.

The reference can contain any characters from a-z, A-Z and 0-9.

In our system, reference AAAA is greater than reference ZZZ; the next reference after AAAA is AAAB.

How would I calculate the greatest current reference in the table?

## Calculating Dates

Nov 1, 2007

I have a project where I need to populate a table with a Begin and an end date based on another field with in the table. my example is that the other field will have a value of "monthly" I want then a record to be entered on the table with a begin date of November 1 and the en date as November 30th. I want this record to get populated only on business days. my thinking is a stored procedure that runs daily and only creates a record at a time. i am thinking if the user selects weekly the dates populated would be november 1 = begin date and Novemeber 2nd = end date
i want to fill out a current month at a time as if the account closes i will not need the extra cases.

thanks

Don S

## Storing Vs Calculating Data

Dec 19, 2007

Hi, I'm in the process of creating a database table and was wondering if it's better to store calculated values or recalculate them each time. So for example, I am creating a table that stores articles and then a table to store the pages to the article. If a new page is added should I update the pages field in the articles table or should I calculate the number of pages for an article when it's queried? Thanks,John