Transact SQL :: Total Difference Time In Last Column?
May 23, 2015
I have the below query
DECLARE @GivenDate DATE='2015-05-13'
create table #table (LedgerID int,AttDate Date, checkedtime time,checkedtype varchar(1))
insert into #table (LedgerID,AttDate,checkedtime,checkedtype) values (1232,'2015-05-13','09:01:48.0000000','I')
insert into #table (LedgerID,AttDate,checkedtime,checkedtype) values (1232,'2015-05-13','13:05:52.0000000','O')
insert into #table (LedgerID,AttDate,checkedtime,checkedtype) values (1232,'2015-05-13','14:10:25.0000000','I')
[code]....
the result is like below
i need 'TotalDiffernceTime' column as new column (OUT1-IN1)+(OUT2-IN2).
i am using SQLServer 2008 R2
View 8 Replies
ADVERTISEMENT
Jun 21, 2015
I have a table data like below
id type timestamp
1001 start1 10:34:23:545
1001 start2 10:34:24:545
1001 end2 10:34:24:845
1001 end1 10:34:25:545
1002 start1 10:34:25:645
1002 start2 10:34:25:745
1002 end2 10:34:25:945
1002 end1 10:34:25:965
I need the result as follows
id millisecond diff start1end1 millisecond diff start2end2
1001 end1 timestamp-start1 timestamp end2 timestamp-start2 timestamp
1002 end1 timestamp-start1 timestamp end2 timestamp-start2 timestamp
SQL Server 2008 R2
View 5 Replies
View Related
Jul 10, 2015
Currently my script is using the below mentioned query to find the time difference.
DATEDIFF(HH,DATEADD(SS,hcreacion,fcreacion) ,DATEADD(SS,hcerrar,fcreacion))
If there is 1 hr 30 minutes time difference, I am getting 2 hours as output. But we need 1.30 as output. is there any way to achieve this?
View 14 Replies
View Related
May 16, 2015
I have a table like below
i need to calculate total working time against 'AttID' (where I - intime, O - OutTime)
ex :
AttID TotalTime
1 08:02:00
2 07:45:00
View 7 Replies
View Related
Aug 5, 2015
equipmentid downtimestartdate downtimeenddate dowtime
a3er 2015-03-15 02:00 2015-03-17 23:00 69
b6e4 2015-03-18 13:00 2015-03-20 04:00 39
i have many rows(in our production table, thousands of rows are there) like above in a table and i want like below output(in output total 6rows only)
equipmentid downtimestartdate downtimeenddate dowtime
a3er 2015-03-15 02:00 2015-03-15 24:00 22
a3er 2015-03-16 00:00 2015-03-15 24:00 24
a3er 2015-03-17 00:00 2015-03-15 23:00 23
[code]...
View 2 Replies
View Related
Jul 14, 2015
I have a table like below, which is contain area, zone, branch wise value. In that i want to get company total as a column and area total as a another column and zone total in next column
AREAZONEBRANCHVALUE
TNCENTRALAMB100
TNCENTRALTNF100
TNCENTRALSDGD100
TNCENTRALERW100
TNNORTHAMB50
[Code] ....
My expected output is like below
AREAZONEBRANCHVAL comtot areatot zonetot
TNCENTRALAMB1001200560400
TNCENTRALTNF1001200560400
TNCENTRALSDGD1001200560400
[Code] ...
I achieved this by using query but its will affect performance because my real time scenario dealing with laks of records
select a.*,b.company_tot,c.area_tot,d.zone_tot from FESIBILITY_CHECK a cross join (select SUM(value)company_tot from FESIBILITY_CHECK )b
join (select SUM(value)area_tot,area from FESIBILITY_CHECK group by area)c on a.AREA = c.area
join (select SUM(value)zone_tot,area,zone from FESIBILITY_CHECK group by area,zone)d on a.AREA=d.area and a.ZONE = d.ZONE
So I planned to use cube and roll up but i don't get desired result ...
View 8 Replies
View Related
May 31, 2015
How can I add a Total Column as the last column in this query and also add a total column to the bottom row of the query? Then after the total column on the right, add a % column. So my expected returned set would be like so:
Location
Train
Bat
Ball
Nuggets
Total
%
[code]..
View 12 Replies
View Related
Nov 19, 2015
If I just use a simple select statement, I find that I have 8286 records within a specified date range.
If I use the select statement to pull records that were created from 5pm and later and then add it to another select statement with records created before 5pm, I get a different count: 7521 + 756 = 8277
Is there something I am doing incorrectly in the following sql?
DECLARE @startdate date = '03-06-2015'
DECLARE @enddate date = '10-31-2015'
DECLARE @afterTime time = '17:00'
SELECT
General_Count = (SELECT COUNT(*) as General FROM Unidata.CrumsTicket ct
[Code] ....
View 20 Replies
View Related
Nov 9, 2015
I am trying to calculate the the running total but also tried to reset to reset to zero based on a value of a column.
here I am trying to calculate the value of CalcVal column based on column Flag value...actually it is running total but it reset to zero if Flag value is 0.
Here is the example of data along with required column
MonthId Flag CalcVal
201401 1 1
201402 1 2
201403 1 3
201404 1 4
201405 1 5
and so on .........
View 8 Replies
View Related
Mar 17, 2015
How do I find the time difference when the dates are in one column? I need to find hours and minutes between each row.
CREATE TABLE #Time ([TimeStamp] DATETIME, TimeDiff INT)
INSERT INTO #Time (TimeStamp)
VALUES ('2014-09-02 07:51:02.810'), ('2014-09-02 07:48:09.567'), ('2014-09-02 08:37:09.647')
, ('2014-09-02 16:16:42.593'), ('2014-09-02 08:06:13.387'),('2014-09-02 14:32:00.113')
DROP TABLE #Time
View 6 Replies
View Related
May 29, 2015
How can I get time difference of the following record :
STARTTIME ENDTIME
3:30 PM 4:30PM
7:30 PM 8:30PM
I have tried it by below query,
SELECT CONVERT(TIME,STARTTIME,108) - CONVERT(TIME,ENDTIME,108) FROM BATCH_MASTER
but it gives following error message
[color=red]Operand data type time is invalid for subtract operator.[/color]
View 6 Replies
View Related
Jul 14, 2015
How to include a time difference column using 2 other date columns during creation of a table ?
The requirement is to create a table and include the following columns:
1.Downtime start date & time
2.Downtime end date & time
3.Downtime Duration in hh:mm (calculated date difference between column 1 and 2)
View 3 Replies
View Related
Jun 9, 2004
Does anyone know how I can determine the number of page writes that have been performed during a set period of time? I need to figure out the data churn in that time period.
TIA
View 5 Replies
View Related
Aug 3, 2015
How can I calculate a DateTime column by merging values from a Date column and only the time part of a DateTime column?
View 5 Replies
View Related
Nov 17, 2015
I have a table like below
CREATE table #TempTable (ID integer,CTime time(7),CType Varchar(1))
insert into #TempTable VALUES (1001,'16:50:05.0000000','I')
insert into #TempTable VALUES (1001,'13:27:49.0000000','O')
insert into #TempTable VALUES (1001,'20:44:00.0000000','O')
insert into #TempTable VALUES (1001,'21:12:00.0000000','O')
I need result like below screen shot
here 'I' stands for In,and 'O' for Out, in this example first In time not available
View 7 Replies
View Related
May 18, 2015
i have a table like below,
CREATE TABLE #ATTTABLE
(
Name VARCHAR(20),
AttDate DATE,
PresntTime TIME
[code]....
and then i pivot table by date as column wise using the below query and also displays total time by rowswise
SELECT t1.*, t2.Total
FROM (
SELECT name,[2015-08-01],[2015-08-02]
FROM (
SELECT name, AttDate,PresentTime
[code]....
now what i need is to display sum of time at last row as well, means total time of against date
View 16 Replies
View Related
Aug 3, 2015
With Sql Server Management Studio, while creating/modifying a table I want to specify one of its columns to store values in lower case only. Can it be done through the designer or by some other means?
N.B.: Using SQL Server 2005.
View 22 Replies
View Related
Aug 13, 2004
How can I change the output into the total hours between 2 smalldatetime data? TIF
DECLARE @iDAY AS SmallDatetime
SET @iDAY = '2004-08-12 10:05:00'
PRINT @iDAY
PRINT GETDATE()
PRINT GETDATE()-@iDAY
+++++++++++++++
Aug 12 2004 10:05AM
Aug 13 2004 10:05AM
Jan 2 1900 12:00AM <--- I want to get the total hours (i.e. 24)
View 4 Replies
View Related
Aug 19, 2005
I want to create a View, which contains columns from two tables, and an additional column, which should calculate the difference between two dates.01/05/2005 7:30 AM - 01/05/2005 8:00 AMThe column should be a calculated column,Can I assign the value of a column to a user defined function?In the UDF, how to get the difference between those two dates to be: "00:30"Are there SQL Server functions to time and date that allows me to do so? I have dateadd, datediff, but I am unable to figure out the 2 problems above.thank you,
View 10 Replies
View Related
Aug 21, 2005
In my table i have two cols and datatype datetime, for example they has the values,--------StartDate --------------------------------------- StopDate ------______________________________________________01/05/2005 7:30 AM ------------------------ 03/05/2005 10:00 AM13/05/2005 2:30 PM ------------------------- 01/08/2005 8:00 PM_____________________________________________How do I find out the avg time difference between StopDate and StartDate? I tried AVG(StopDate - StartDate ), but it's giving me the following error message. "The average aggregate operation cannot take a smalldatetime data type as an argument."Thanks for any reply.
View 1 Replies
View Related
Sep 23, 2015
I tried my syntax below, but it said that the field were not in the group by. This is syntax and for each of the 2 names (only small subset of real data) I need a Total column to display between each different person like so:
Total, 29, 150, 2400/60
Total, 25, 143, 2400/60
Here is syntax:
Create Table #Farquard
(
empID varchar(50),
fullname varchar(500),
break1 int,
break2 int,
dailytotal int,
workday date
)
[Code] ......
View 8 Replies
View Related
Sep 2, 2015
My query below displays a total row, but it displays above the city, I want it to display below the city. But now their is an additional caveat, I have a table that sets the order each city & total row should display in. How can that be added to my query to set the display order?This is what I have that displays the total above the city, could this be ammended to join in the order table and display in that order? SQL Server 2008
Create Table studentdata
(
homecity varchar(100),
usernumber int,
userstatus varchar(100),
usernumbermanager varchar(100)
[code]....
View 8 Replies
View Related
May 14, 2008
I am re-writing a old query that update's it's value based on values at run time. Is there anyway to accomplish the following query where column s1.AI referances the values that were updated during run-time.
I want to avoid a Loop (i.e. cursor or a CTE).
Please help!
Declare @Stage Table(StartDate datetime,BenefitInterestID INT PRIMARY KEY, Amount MONEY, InterestAmount MONEY, Interest DECIMAL(10, 4), ai DECIMAL(10, 4))
Insert Into @Stage
Select
convert(datetime,'2006-12-01 00:00:00.000',101) as StartDate,1 as BenefitInterestID,1701.00 as amount,79.605 as InterestAmount ,0.1000 as Interest,0.0000 as ai
Union all
select '2007-12-01 00:00:00.000',2,172.80,7.92,0.0500,0
Union all
select '2008-12-01 00:00:00.000',4,0.00,0.00,0.0700,0
UPDATEs1
SETs1.ai = s1.Interest * coalesce((SELECT SUM(coalesce(s2.Amount,0) + coalesce(s2.InterestAmount,0)+coalesce(s2.ai,0)) FROM @Stage AS s2 WHERE s2.StartDate < s1.StartDate ),0)
FROM@Stage AS s1
select * from @Stage
My desired Results are
2006-12-01 00:00:00.00011701.0079.6050.10000.0000
2007-12-01 00:00:00.0002172.807.920.050089.0303
2008-12-01 00:00:00.00040.000.000.0700143.5300
But I am geting
2006-12-01 00:00:00.00011701.0079.6050.10000.0000
2007-12-01 00:00:00.0002172.807.920.050089.0303
2008-12-01 00:00:00.00040.000.000.0700137.2928
Due to as you can see on row 3 that the amount does not factor in the previous Years ai column when I sum, due to the update takes place at runtime.
Please help!
View 15 Replies
View Related
Aug 29, 2005
i hv a problem in sql,
i) if in data hv time_start, time_end, date , id, how i can calculate the total time for very sesion user login
ii) how to make it if the total come out hv same id(may be two id hv two diffrent total), i want make it just one id then the total time...
post me at jeffliew2001@yahoo.com
View 6 Replies
View Related
Oct 3, 2015
I hope to update a DateTime column value with a Time input parameter. Poor attempt below but it looks like the @ApptTime param is coming in as 10:45:00.0000000 and I might have an existing @SendOnDate as: 2015-10-05 07:00:00.000...I hope to end up with 2015-10-05 10:45:00.000
ALTER PROCEDURE [dbo].[SendEditUPDATE]
@QuePoolID int=null
,@ApptTime time(7)
,@SendOnDate datetime
[code]...
View 14 Replies
View Related
Apr 21, 2015
SELECT
CONVERT(VARCHAR(10),attnc_chkin_dt,101) as INDATE,
CONVERT(VARCHAR(10),attnc_chkin_dt,108) as TimePart
FROM pmt_attendance
o/p
indate 04/18/2015
time part :17:45:00
I need to convert this 17:45:00 to 12 hours date format...
View 8 Replies
View Related
Oct 21, 2015
I need to get a cumulative total for row by row basis. I need this grouped on name, id, year and month. ID is not a auto incremented number. ID is a unique number same as name. The out put I need is as below,
Name ID Year Month Value RunningValue
XX 11 2013 Jan 25 25
xx 11 2013 Feb 50 75
yy 22 2015 Jan 100 100
yy 22 2015 Mar 200 300
How I could get this query written? I am unable to use SQL Server 2012 version syntaxes. Writing a cursor would slow the process down because there is a large data set.
View 12 Replies
View Related
Sep 16, 2015
I have table named #table and three columns. SinGroup(it does not matter, can be anything), Column1, and Column2.
CREATE TABLE #table(
sinGroup NVARCHAR(10)
,column1 INT
,column2 int
);
And i have some data:
INSERT INTO #table(sinGroup,column1,column2) VALUES
('y1',100,0),
('y2',0,60),
('y3',40,20),
('z1',150,0),
('z2',0,50),
('z3',0,50)
I want to know how to get this result set (c1 - c2 and group by Y and Z and the result shown under the column 1):
SinGroup C1 C2
y1 100 0
y2 0 60
y3 40 20
Y 60 0
z1 150 0
z2 0 50
z3 0 50
Z 50 0
And also result like this (c1-c2 and grop by each row,the result show under the column 1):
SinGroup C1 C2
y1 100 0
y1 100 0
y2 0 60
y2 -60 0
y3 40 20
y3 20 0
z1 150 0
z1 150 0
z2 0 50
z2 -50 0
z3 0 50
z3 -50 0
View 5 Replies
View Related
Oct 23, 2007
I have the following staement :
AVG( CASE WHEN EventTypeName=''EventA'' THEN CAST(DateDiff(s,EntryTime,EventDateTime) as float) END ) as Duration1
but the problem here is that i need to find the difference between two EventTypes EventA and EventB NOT EntryTime and EventDateTime of EventA
so how can i re-do the above query to get:
EventA - EventB
Not
EntryTime - EventA
NOTE: EventDateTiem is the time that the EventA occurred.
thanks
robby
View 8 Replies
View Related
Apr 10, 2008
advance thanks to all...can anybody helpme soon...........
i am using sql server 2005........i want to find out time difference between two dates in HH,MM,SS format
eg i want the time difference of these two dates
02/Nov/06 9:14:21 AM
19/Apr/07 11:52:31 AM
now i am using this calculation in my procedure is as follows...but i think this is wrong...if we will seperatly calculate like this wrong i am getting
set @totaltravelHr=datediff(HH,@datediff1,@datediff2)
set @totaltravelMI=datediff(MI,@datediff1,@datediff2)
set @totaltravelSE=datediff(SS,@datediff1,@datediff2)
View 9 Replies
View Related
Apr 23, 2002
I have a table containing following Columns plus other columns:
Start Date 04-01-2002(mm-dd-yyyy) varchar(10)
Start Time column 09:30:30(hh:mm:ss) varchar(8)
End Date 04-05-2002(mm-dd-yyyy) varchar(10)
End Time column 09:45:30(hh:mm:ss) varchar(8)
1]We want to create a view for Report Users So that they could just select
the other columns and difference in start Date/time to End Date /Time.
2]The Difference in time should be based on Business Day. i.e.
Saturday & Sunday should be excluded. &
3]Each business day is defined as 8.00 am to 5 .00pm
( 1 Business day = 9 hrs)
4]The time is stored as 24 hour clock.
For e.g.
Start Date Start TimeEnd Date End Time Desired Output
04/01/2002 10:00:0004/01/2002 3:30:00 0 Days 5:30 Hrs
04/01/2002 16:00:0004/02/2002 10:45:00 0 Days 3:45 Hrs
04/01/2002 09:00:0004/03/2002 10:45:00 2 Days 1:45 Hrs
04/05/2002 16:30:0004/08/2002 9:45:00 0 Days 2:15 Hrs******
******In last case as 04/06/2002 & 04/07/2002 were Saturday & Sunday ,hence
excluded.So the difference of time is just 2:15 hrs
Can you please give me a suggestion/pseudo code/logic on how I might do this?
We cannot use cursors as we want to make a view.This is for SQL Server 2000
View 1 Replies
View Related
Jan 15, 2003
I have a start_datetime column and an end_datetime column that records when a process we run starts and stops. I am trying to write a query that can calculate the difference between the start time and the end time and display it as hh:mm:ss but cannot seem to come up with a solution. Any ideas?
View 2 Replies
View Related
May 21, 2007
hi to all,
very urgent query
how to minus the two times(hh:mm:ss)in sql server
regards
ammu
View 2 Replies
View Related
