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

View 3 Replies


ADVERTISEMENT

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:

View 4 Replies View Related

Calculating Time Difference

Jul 20, 2005

Hi,I have a table called Bookings which has two important columns;Booking_Start_Time and Booking_End_Time. These columns are both of typeDATETIME. Given any day how can I calculate how many hours are availablebetween the hours of 09.00 and 17.30 so a user can see at a glance how manyhours they have unbooked on a particular day (i.e. 8.5 hours less the timeof any bookings on that day), can this be done with a queryor do I have to work it out in my code?Thanks for your help

View 2 Replies View Related

Calculating Time Difference Between Two Different Dates

Dec 9, 2011

I have data in which i need to calculate employees working hours for a day...

name time in_out
manisha 2011-01-01 9:30:00.000am 1
manisha 2011-01-01 10:30:00.000 0
manisha 2011-01-01 10:45:00.000 1
manisha 2011-01-02 1:00:00.000am 0

How can i calculate time in that two dates as 1 is for entry an 0 is for exit..

View 2 Replies View Related

Calculating 'time Difference' Between Two Records....

Dec 21, 2005

I have a data set like so:UTC_TIME Timestamp NodeID Message FlagLineStation11/19/2005 10:45:07 1132397107.91 1 3 5 1028103411/3/2005 21:05:35 1131051935.20 2 3 5 1009104311/25/2005 21:12:16 1132953136.59 3 3 5 10371049I added the UTC_TIME column in as aconversion of the unix timestamp inthe TIMESTAMP column.Keeping things simple and straightforward, I need to be able tocalculate the difference from one record to the next (ordered byTIMESTAMP or UTC_TIME) and output the result into another column in thetable.NODEID is the unique id.First, what is the function to do so if, say, I only wanted tocalculate the difference between 2 records as just a basic SELECTstatement. That way I can answer quick question based on any one or twoNODEID's.Second, how would I further that to continually calculate (as statedabove)?WOuld this be a stored procedure? A trigger? A cursor?I am learning as I go here. Any help is greatly appreciated.R.

View 4 Replies View Related

SQL 2012 :: Calculating Difference Between Two Times With A Twist (between 9am And 5pm)

Mar 25, 2014

I have Two Time fields in a table. Time(0). An "opening time" and a "closing time". They can hold any legit time.

I want to calculate in a SELECT Statement how many minutes within this range are within 9am to 5pm (which I'll convert to hours).

For example, here's an easy example:

OPEN: 9:00:00
CLOSE: 17:00:00
8 Hours/480 minutes

I could get this easy enough with a DATEDIFF function.

But what about:

OPEN: 08:00:00
CLOSE: 18:00:00

10 Hours total but only 8 of those 10 are within 9am-5pm.

Or what about:

OPEN: 10:00:00
CLOSE: 20:00:00

10 Hours total but only 7 are within 9am-5pm range.

I can calculate the total hours/minutes between the two times but not within that special range.

View 4 Replies View Related

Calculating Time Difference In Msdb..sysjobhistory

Dec 28, 2006

I am setting up a monitor to alert me if an SQL job has failed in the "last 20 minutes". This should run 24 hours a day, 7 days a week. My query looks something like this.

select * from TALMAIN.msdb.dbo.sysjobhistory where job_id = '7139D5D1-CD88-46E8-8324-5D5A0D8D3A27' and run_status <> 1 and
DATEPART(YYYY,GETDATE()) = substring(convert(char(8),run_date),1,4)and
DATEPART(MM,GETDATE()) = substring(convert(char(8),run_date),5,2) and
DATEPART(DD,GETDATE()) = substring(convert(char(8),run_date),7,2)and DATEPART(HH,GETDATE()) = substring(convert(char(8),run_time),1,2)and (DATEPART(MI,GETDATE()) - substring(convert(char(8),run_time),3,2)) <= 20.

The run_date and run_time columns in msdb..sysjobhistory are stored as integers. Tried a couple of things, but I am unable to convert both of them to datetime data type. The last conditions in the above logic hold true for only "2 digit" hour and minute values.

DATEPART(HH,GETDATE()) = substring(convert(char(8),run_time),1,2)and (DATEPART(MI,GETDATE()) - substring(convert(char(8),run_time),3,2)).

What about time values like 00:05 AM and single digit time values like 1:00 AM and 9:05 AM, for example?. I pasted some sample run_date and run_time values from sysjobhistory below.

run_date run_time

2006122821510 -- 02:15:10 AM (how to get the minute count?)
2006122821510 -- 02:15:10 AM (same as above)
20061227233014 -- 23:30:14 PM (this is strt forward)
20061227233014 -- 23:30:14 PM (same as above)
200612273016 -- 00:30:16 AM (how to get minute count?)
200612273015 -- 00:30:15 AM (how to get minute count?)

Is there a simpler logic to achieve this? Hope I was clear, else let me know. Please advise. Thank you.

View 5 Replies View Related

Transact SQL :: Calculating Date Difference In Days

Apr 16, 2015

I'm trying to calculate the time difference between a date field and today's date in days. The date field is not mandatory and can therefore be blank. I'm trying to execute the following query:

SELECT employee_code, Civil_ID, DATEDIFF(Day, Civil_ID, GETDATE())
FROM ODEV_VIEW_Credentials_Expiry_Dates
WHERE Civil_ID IS NOT NULL AND Civil_ID != ''
ORDER BY employee_code

I keep getting the following message:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Warning: Null value is eliminated by an aggregate or other SET operation.

No matter what filter I use to process non-blank dates, it never works.

View 12 Replies View Related

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...

View 5 Replies View Related

Remove Weekends And Non Working Days When Calculating Days Difference Between Two Dates

Jan 7, 2014

I have an SQL code below which removes weekends and non working days when calculating days difference between two dates:

ce.enquiry_time represents when the enquiry was logged

(DATEDIFF(dd, ce.enquiry_time, getdate()) + 1)
-(DATEDIFF(wk, ce.enquiry_time, getdate()) * 2)
-(CASE WHEN DATENAME(dw, ce.enquiry_time) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, getdate()) = 'Saturday' THEN 1 ELSE 0 END)
-(SELECT COUNT(*) FROM nonworking_day WHERE nonworking_day.nonworking_date >= ce.enquiry_time AND nonworking_day.nonworking_date < dateadd(dd,datediff(dd,0,getdate()),1))

It works but I don't understand how it works it out. I am having issues understanding each coloured piece of code and how it works together.

View 1 Replies View Related

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 ??
please help

View 3 Replies View Related

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?

View 7 Replies View Related

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

View 3 Replies View Related

Find Records Where There Is Difference Between 2 Columns

Jan 24, 2014

I am trying to produce a report in Application express. I want to find records where there is a difference between 2 columns eg

Debt_amount - billed_amount

where debt >billed amount

I only want to see records where the debt is more than the amount billed

View 1 Replies View Related

Difference Between Xtype And Type Columns In ....

Feb 6, 2007

Hi

Difference between xtype and type columns in sysobjects table?

Thanks in advance...

Regards
Magesh

View 2 Replies View Related

Finding Difference Between Two Date Columns And Deleting It

Apr 14, 2008

I have two columns in my table. Both the columns contains datetime datatypes. I need to write a stored procedure which will calculate the date difference between the two columns and if it exceeds more than 5 days then that record should get deleted. How to do it?Thanx 

View 2 Replies View Related

Compare 2 Rows To Find The Difference In Columns?

Aug 14, 2014

How would I compare 2 rows to find the difference in the columns?

Example:
ID Column1 Column2 Column3
1 Text1 Text4
2 Text1 Text2 Text3

Result:
Column1: Text2 New
Column3: Text4 Old Text3 New

View 6 Replies View Related

Difference Between 2 Columns Key And A Third-party Column With Identity

Jul 22, 2007

Hi all,



Just a silly wonder I had a few days ago:



I have a table named 'CustomerOrders' and I ponder between the posibilities of PrimaryKey(s) i can set :

1. CustomerId + OrderID - a two columns key providing exactly what i need (representing the actual relation).

2. CustOrderId - one column key with identity insert (so i could directly delete/update the record).



Which one should I choose and why? Would i gain anything by choosing one over the other?



Thanks in advance,

iTaY.

View 6 Replies View Related

Transact SQL :: Pivot Columns Into Rows With Difference

Sep 22, 2015

Here is the table:

empid lastname firstname title titleofcourtesy ModifiedOROriginal

1 Davis1 Sara CEO Ms. Modified
1 Davis Sara CEO Ms. Original

We need an output like this:

empid
1
1

lastname
Davis1
Davis

firstname
Sara
Sara

title
CEO
CEO

titleofcourtesy
Ms.
Ms.

ModifiedOROriginal
Modified
Original

View 2 Replies View Related

Difference Of Two Datetime Columns Caused Overflow At Runtime.

Mar 30, 2007

I used this query to get a result



select round(cast(DateDiff(ss, convert(datetime,rf.RECVD_DTTM), convert(datetime,con.ARRIVED_DTTM))/60 as float)/60,2) as LengthOfTime

from customer rf



but i am getting an error ?

"Difference of two datetime columns caused overflow at runtime."



Any idea ?

View 10 Replies View Related

Error : Difference Of Two Datetime Columns Caused Overflow At Runtime.

Sep 23, 2005

At my job is a dts package that is failing in SQL 2005. I am not a SQLexpert. I am just trying to fix. I put the query in Query Analyzerand get this error:(4322 row(s) affected)Server: Msg 535, Level 16, State 1, Line 1Difference of two datetime columns caused overflow at runtime.I am just trying to understand what this means, what I should belooking for and what could be wrong. Here is the query:SELECT i.SerialNumber, '' AS mac_number, DATEDIFF([second], 'Jan 1,1970', s.DateOrdered) AS Support_StartDt, DATEDIFF([second], 'Jan 1,1970',s.Warranty_Enddate) AS Support_EndDt,DATEDIFF([second], 'Jan 1, 1970', c.Registration_Date) ASRegistration_Date, c.FirstName AS enduser_fname,c.LastName AS enduser_lname, c.CompanyName ASenduser_companyname, c.ContactEmail AS enduser_email, c.Address ASenduser_address1,c.Address2 AS enduser_address2, c.City ASenduser_city, c.State AS enduser_state, c.Zip AS enduser_zip,c.WorkPhone AS enduser_phone,c.Fax AS enduser_fax, d.DealerName ASdealer_companyname, d.ContactFirstName AS dealer_fname,d.ContactLastName AS dealer_name,d.Address1 AS dealer_address, d.City ASdealer_city, d.State AS dealer_state, d.Zip AS dealer_zip,d.ContactPhone AS dealer_phone,d.ContactFax AS dealer_fax,ISNULL(SUBSTRING(p.ProductName, 11, LEN(p.ProductName) - 10), 'unknownIWP product') AS product_type, '' AS extra1,'' AS extra2, '' AS extra3, '' AS extra4, '' ASextra5, '' AS extra6, '' AS extra7FROM tblInventory i full outer JOINtblDealers d ON i.DealerID = d.DealerID fullOUTER JOINtblSupport s ON i.InventoryID = s.InventoryIDfull outer JOINtblCustomers c ON s.InventoryID = c.InventoryIDLEFT OUTER JOINtblProducts p ON LEFT(i.SerialNumber,PATINDEX('%-%', i.SerialNumber)) = p.SerialPrefixWHERE i.SerialNumber <> ''Any ideas would be greatly appreciated.

View 2 Replies View Related

SQL Server 2014 :: Identify Difference Between Two Table Columns And Output Only The Differences

Sep 4, 2015

IF Object_id('GoldenSecurity') IS NOT NULL DROP TABLE dbo.GoldenSecurity;
IF Object_id('GoldenSecurityRowVersion') IS NOT NULL DROP TABLE dbo.GoldenSecurityRowVersion;

CREATE TABLE dbo.GoldenSecurity (securityID INT, CompanyId INT, Securityname VARCHAR(50), issuedate SMALLDATETIME, currencyID INT)

[Code] ......

View 6 Replies View Related

Transact SQL :: Any Reason For Difference In Order Of Columns Between Index Of Constraint And Its Statistics Definition?

Sep 5, 2015

I am really puzzled by an apparent difference between table index key column order and its statistics order. I was under understanding that index statistics mirror index definition. However, in my db 2470 index ordinal definitions match statistics definition but 66 do not. I also can reproduce such discrepancy in 2008 R2, 2012 and 2014.

As per definition,

stats_column_id
int

1-based ordinal within set of stats columns

This script duplicates this for me.

BEGIN TRAN
GO
use tempdb
GO
CREATE TABLE [dbo].[ItemProperties](
[itmID] [int] NOT NULL,
[cpID] [smallint] NOT NULL,
[ipuID] [tinyint] NOT NULL,

[Code] ....

The result I get is this:

object_id       stats_name                                     
stats_column_list
1525580473 PK_ItemProperties_itmID_ipuID_cpID itmID,  cpID,  ipuID,

and

object_id      index_name                                     
index_column_list
1525580473 PK_ItemProperties_itmID_ipuID_cpID itmID,  ipuID,  cpID,

Also a query I used to discover this in my db is:

WITH stat AS
(
SELECT
s.object_id
,s.name as stats_name
,(
SELECT
c.name + ', ' as [data()]
FROM sys.stats_columns as sc

[Code] .....

View 6 Replies View Related

Can Anyone Give Me A Layman's Explanation Of The Difference Between CURRENT_TIMESTAMP And GETDATE() (if There Is A Difference)?

Oct 24, 2007

Question is in the subject.

Thanks in advance
-Jamie

View 7 Replies View Related

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

View 1 Replies View Related

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?

View 3 Replies View Related

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

View 2 Replies View Related

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

thanks in advance
Zoey

View 3 Replies View Related

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

View 1 Replies View Related

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! 

View 7 Replies View Related

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.

View 2 Replies View Related

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
 
Thanks for all your help in advance.
Dash

View 1 Replies View Related

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?

Thanks in advance,
Darrin

View 1 Replies View Related







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