Grouping The Records On The Time Difference

May 14, 2008

Hi Guys,

I want to group the records on the time difference


declare @tbl as table(id int,intid int,val int,dt datetime)
insert into @tbl
select 1,1,10,'03/31/2006 15:05:22' union all
select 2,1,12,'03/31/2006 15:10:22' union all
select 3,1,15,'03/31/2006 15:15:22' union all
select 4,1,12,'03/31/2006 15:25:22' union all
select 5,1,8,'03/31/2006 15:30:22' union all
select 6,1,6,'03/31/2006 15:35:22' union all
select 7,1,4,'03/31/2006 15:40:22' union all
select 8,1,3,'03/31/2006 15:45:22' union all
select 9,1,10,'03/31/2006 15:50:22'

declare @tbl1 as table(intid int,Tm int,val int)
insert into @tbl1
select 1,5,10

I want a output such that when the val in @tbl goes below the val in @tbl1 for the Tm mentioned in @tbl1
then the time difference should be shown.For example record 1 it starts with 10 the records remain more than 10 till
record number 5.From 5 the records remains lower than 10 till record number 9.So I need to show the the time
difference from record number 5 till 9.
But there is a catch.In @tbl1 there is column named Tm.The time difference sould be calculated only if the diff
more than Tm value in @tbl1.
For example if the value of Tm is changed to say 25 then the there is no need to show the time difference since the
time difference value from record 5 to record 9 is less than 25.
Hope I am clear.

Please help me out.

Regards

View 3 Replies


ADVERTISEMENT

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 Server 2008 :: Loop Through Date Time Records To Find A Match From Multiple Other Date Time Records?

Aug 5, 2015

I'm looking for a way of taking a query which returns a set of date time fields (probable maximum of 20 rows) and looping through each value to see if it exists in a separate table.

E.g.

Query 1

Select ID, Person, ProposedEvent, DayField, TimeField
from MyOptions
where person = 'me'

Table

Select Person, ExistingEvent, DayField, TimeField
from MyTimetable
where person ='me'

Loop through Query 1 and if it finds ANY matching Dayfield AND Timefield in Query/Table 2, return the ProposedEvent (just as a message, the loop could stop there), if no match a message saying all is fine can proceed to process form blah blah.

I'm essentially wanting somebody to select a bunch of events in a form, query 1 then finds all the days and times those events happen and check that none of them exist in the MyTimetable table.

View 5 Replies View Related

Grouping On Date Without Time

Apr 5, 2007

Hello,



I have the following problem.

I a making reports based on a database that i do not control.

In that database i have a table with statistical data including a field with datetime informtion.

The format of the data I receive is "5/04/2007 7:43:27".



In my report i want to create a group which groups my event by date : "05/04/2007"

In my output i always get subgroups by date & time so "5/04/2007 7:43:27", "5/04/2007 7:43:28", ....



How can i group only on the date.



Vincent



View 13 Replies View Related

Time Difference

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

Avg Time Difference

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

Grouping Date Field By Day Only, Not Time

Jan 17, 2002

I'm adding up quantities of an item that are entered in one after another, so the date is the same, but the time differs.

I used the "first" function in access which works the way I want, but sql7 doesn't use the same function. The "convert" function does not work in access but Here is the gist of the query:

SELECT first(CONVERT(varchar,transactions.tran_date,101)) AS [trandate], transactions.tran_type,
SUM(transactions.qty) AS totqty,...etc.

thanx,

Herb

View 1 Replies View Related

Grouping And Pairing Transactions Over Time

Jul 27, 2012

I have a table with the below columns. I would like to create the result table below that pairs transactions with "LIKE" transactions. The only exception is that "OUT" can basically stop anything.

compIDcommIDTRANSDATEUNIT
11MSF1/1/2012101
12CS11/1/2012101
13CS22/1/2012101
110OUT5/1/2012101
112MSF5/2/2012301
113CS15/2/2012301
114CS26/2/2012301
115OUT10/2/2012301
24MSF1/1/2012102

[code]...

View 1 Replies View Related

Grouping Based On Time Series

Mar 26, 2008



In SQL Server 2005, I want to do a set query on the following data that results in 3 groups:









Id
EventName
EventTime

1
First
41:40.2

2
First
41:41.6

3
First
41:43.1

4
First
41:44.4

5
Second
41:46.4

6
Second
41:48.3

7
Second
41:49.7

8
First
41:51.2

9
First
41:53.3

10
First
41:55.0

So, I want to have a query that returns one aggregate row for each of rows 1-4, 5-7 and 8-10 based on the EventName. Every time EventName 'changes' in the order that I sort it, I want to start a new grouping:








Group
EventName
Count

1
First
4

2
Second
3

3
First
3

With this query, I could also get the Min() and Max() EventTime for each group, etc.

However, this is proving difficult to do in set SQL. Obviously, if I group on EventName, then rows 1-4 *and* 8-10 will be rolled into my 'First' group. However, there is no other partitioning information that I can factor in that splits this data into *only* 3 groups, based on the order of the Event Time.

I have tried the various ranking functions, but the problem persists through any combination of function, PARTITION BY and ORDER BY that I can find.

Any insights would be appreciated!

View 7 Replies View Related

Concatenating A Field While Grouping Records

Jul 20, 2005

All,Given multiple records with identical values in all fields except asingle varchar field, is there an efficient query that will group therecords into a single record and concatenate the aforementionedvarchar field into a single field with each of the source records'values separated by commas?Example:Record 1 'Doug' , '1'Record 2 'Doug' , '2'Output record 'Doug' , '1,2'Thanks in advance,Doug

View 2 Replies View Related

Dynamic # Of Records In An Artificial Grouping

May 12, 2008



I have a report that requires SubTotals for each page.

To do this successfully, I had to introduce the following grouping:


Code Snippet=Int((RowNumber(Nothing)-1)/5




This groups every 5 records.

Unfortunately, the length of data in my records is not uniform. For example, certain records have long(over 4 lines) comment field while others are short(1 line).


This means that even though I have an artificial grouping, the group may still be split up over two or more pages.

Is there a way to Group records PER PAGE dynamically?

View 5 Replies View Related

Finding And Grouping Records Off Of A Given Field Value

Jun 16, 2007

In the "tblEmailGroupLink" table...

I need to find all records with the "UnSubscribed" field having a "True" value. All these records will have a corresponding "Emailid" field.



In the "tblEmailAddress" table...

The same "Emailid" field has a corresponding "EmailAddress" field.



What needed is all the email addresses found in the "EmailAdddress" field of these records.





I'm very new at this so I hope I explained this right. I'd really appreciate any help I can get.



Thanks,

Bill

View 2 Replies View Related

SQL 2012 :: Accurate Sorting Data Each Time With Millions Of Records Without Time Field?

Apr 25, 2014

Sample Table

USE [Testing]
GO
/****** Object: Table [dbo].[Testing] Script Date: 4/25/2014 11:08:18 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON

[Code] ....

It seems to work fine with one million records.

Each primary key is unique, but the begindate is non-unique, and i guess even if i use datetime2 and add nanoseconds, from what i have read, there is a chance that i could have a duplicate datetime since the date is imported via XML from multiple sources.

View 7 Replies View Related

Difference Between The Fuzzy Lookup And Fuzzy Grouping In Ssis

Aug 14, 2007

Dear Friends,



i think fuzzy lookup

COMPARES WHAT WE ARE MAPING THE COLUMNS WITH SPELLING (IT WILL REJECT ATLEAST 1 LETTER IS DIFFRENT IN ANY RECORD MAPPED COLUMN) EX: RAVI != REVI


what is fuzzy grouping ???? please explain

regards
koti




View 3 Replies View Related

Need Help In Working Out A Time Difference ..

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

Time Difference Between Two Dates

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

Date/Time Difference

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

Finding Time Difference

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

Time Difference In Sql Server

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

How To Get Sum Of Counter With Time Difference

Dec 9, 2013

Table

DateField--------------CNT-1 CNT-2 CNT-3
2013-12-09 09:00:00.000 8 1 2
2013-12-09 09:01:00.000 3 7 1
2013-12-09 09:02:00.000 1 2 3
2013-12-09 09:03:00.000 2 5 2
2013-12-09 09:04:00.000 1 1 7
2013-12-09 09:05:00.000 2 3 2

My requirement is to get the output in the following format if i take the time with 2 minutes difference

DateField ------------------ Sum-CNT-1 Sum-CNT-2 Sum-CNT-3
09:00:00.000 - 09:02:00.000 12 10 6
09:02:00.000 - 09:04:00.000 3 6 9

Is there anyway i can write down a query to get the sum of the counter with 2 minutes difference. I may take time slot between 9 AM to 10 AM so i should get the sum of the counter with 2 minutes gap in a single query.

View 1 Replies View Related

Time Difference In SQL Server

Dec 6, 2007

I am new to SQL Server and am trying to write a query that subtracts the time difference in the same column. I need to know the time difference between BatchSequence 2, LoadSequence 1 and BatchSequence 13, LoadSequence 3. Below is an example of the data. Thanks in advacne for the help!

BatchSequenceLoadSequenceScanLabelProcessDate
13 1Part Number11/1/2007 6:08:02 AM
13 2Scan Trace11/1/2007 6:08:03 AM
13 3Slot Position11/1/2007 6:08:04 AM
2 1Part Number11/1/2007 5:53:06 AM
2 2Scan Trace11/1/2007 5:53:07 AM
2 3Slot Position11/1/2007 5:53:08 AM

View 20 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

How To Get Average Value Of Time Difference?

Apr 2, 2008

My table has two datetime columns (TheatreArivalDate and TheatreDepartDate). Can I get an average value of the time differences of them? Thanks

View 1 Replies View Related

Date Difference Between 2 Different Records

Mar 24, 2008

how do you determine the date difference between the current record and the next record. i want to use the enddate on record 1 and calulate the time it took the next occurance to happen (start time) on record 2? and so forth.

View 1 Replies View Related

Calculate Difference Between Two Records?

Aug 28, 2015

I have table that contains below data

  CreatedDate               ID             Message
  2015-05-29 7:00:00      AOOze            abc
  2015-05-29 7:05:00      AOOze            start

  2015-05-29 7:10:00      AOOze            pqy
  2015-05-29 7:15:00      AOOze            lab
  2015-05-29 7:20:00      AOOze            lmn   

  2015-05-29 7:30:00      AOOze            start
  2015-05-29 7:35:00      AOOze            stop

  2015-05-29 7:40:00      AOOze            pqy
  2015-05-29 7:45:00      AOOze            stop
  2015-05-29 7:50:00      AOOze            lmn   

I need to Find Maximum interval time for between message like Start and Stop as per order of createdDate.

For example:

OccuranceCount       MinDate                    MaxDate          DurationInSeconds
1                 2015-05-29 7:05:00      2015-05-29 7:35:00   30
2                 2015-05-29 7:30:00      2015-05-29 7:45:00   15

View 6 Replies View Related

SQL Server 2008 :: Grouping By Records That Are Already Grouped?

Jul 27, 2015

I have results from a query that have anywhere from 1-4 results. I have a subid of 1-4 which is grouped by a certain criteria. so for example...

id subid text
1 processing A records
2 1000 records processing
3 importing A records
4 1000 records processed
1 processing B records
2 500 records processing
3 importing B records
4 1000 records processed

Here is what my desired output is giving each group of subids 1-4 an id to be grouped together.

id subid text
1 1 processing A records
1 2 1000 records processing
1 3 importing A records
1 4 1000 records processed
2 1 processing B records
2 2 500 records processing
2 3 importing B records
2 4 1000 records processed

View 1 Replies View Related

Grouping Records && Assigning Sequential Number

Jul 20, 2005

I need to group records and assign a setid to the group. I have atable with data that looks like thisColA ColB94015 0106594016 0106594015 0108594015 0108633383 0091232601 00912I need to create a resultset using just sql to look like thisColA ColB GRP94015 01065 194016 01065 194015 01085 194015 01086 133383 00912 232601 00912 2The tricky part is resolving the many to many issue. A value in ColAcan belong to multiple values in ColB and a value in ColB can havemultiple values in ColA.

View 6 Replies View Related

Fuzzy Grouping: Any Success With &&> 3 Million Records?

May 18, 2006

I have tried to process > 3 million Fuzzy grouping records on two different servers with no success. 3 mill works but anything above 4 mill doesn't. Some background:

We are trying to de-dup our customer table on: name (.5 min), address1 (.5 min), city (.5 min), state (exact). .8 overall record min score.
Output includes additional fields: customerid, sourceid, address2, country, phonenumber
Without SP1 installed I couldn't even get a few hundred thousand records to process
Two different servers - same problems. Note that SSIS and SQL Server are running locally on both
The higher end server has 4GB RAM, the other 2.5 GB RAM. Plenty of free disk space on both
SQL Server is configured to use 2 GB of RAM max
The page file is currently at 15GB

After running a number of test on both servers trying different batch sizes etc. the one thing I noticed is that it seems to always error out when SSIS takes over and starts chewing up all the available RAM. This happens after the index is created and SSIS starts "warming caches". On both servers SQL Server uses up about 1.6GB of RAM at this point while SSIS keeps taking over RAM until all physical RAM is used up.

Some questions:

Has anyone been able to process more then 3 million records and if so what is your hardware configuration?
Should we try running SSIS from a different server so it has access to the full amount of physical RAM? (so it doesn't have to fight for RAM with SQL Server)
Should we install Win 2003 Enterprise Server so we can add more RAM?
Any ideas why switching to the page file might be causing errors?

Thanks!!

Keith Doyle





View 17 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

Compare Time Difference Action

Jan 13, 2012

Is it possible to send a sql statement to update column 1 where the current time is 10 mins older than the time in col 2.

Column 1 is holding a true statement that we need to update to false if over 10 mins old...

View 8 Replies View Related

Time Difference Between Sequence Dates?

Feb 27, 2013

It has been a while since I have used SQL server and require to show a time difference between a start and end time, with only 1 date to determine these times

SQL
select
Arrival_date
locationdate as LocDate,
locationid as Loc_ID,
locations.loc_name as Location_Name
from current_locations
left outer join locations on
locations.loc_id = current_locations.location_id
where current_locations.Attend_ID = '1234567'

Results
Arrival Date LocDate Loc_IDloc_name
26/02/2013 19:2126/02/2013 19:271270Queue
26/02/2013 19:2126/02/2013 19:341278Dept 1
26/02/2013 19:2126/02/2013 21:10222Dept 2
26/02/2013 19:2127/02/2013 02:5631Left Department

What I want to acheive is the following where the Start location date is either the arrival date or the next location date and the endLocDate is the next finish date.

StartLocDateEndLocDateTime DifferenceDept
26/02/2013 19:2126/02/2013 19:2700/01/1900 00:06Queue
26/02/2013 19:2726/02/2013 19:3400/01/1900 00:07Dept 1
26/02/2013 19:3427/02/2013 02:5600/01/1900 07:22Dept 2

View 4 Replies View Related

Minimum Time Difference Between Tables

Sep 3, 2013

I receive the following result set from TableA (In Time)

7/9/2013 9:27:00.000 AM
7/9/2013 10:24:00.000 AM
7/9/2013 11:25:00.000 AM
7/9/2013 1:23:00.000 PM
7/10/2013 7:27:00.000 AM

Then we receive the following result from TableB (Out Time)

7/9/2013 9:30:00.000 AM
7/9/2013 10:29:00.000 AM
7/9/2013 1:37:00.000 PM
[NULL]
[NULL]

We may not always get Out Times in TableB so I want to merge these into one table to have the In Time and Out Time in separate columns in that one table. In this example with the red type those should be In Time and Out Time for mapped unique identifiers from each table and yet the purple color coded example would have an In Time of 11:25 AM and the Out Time would remain as NULL.

I am using this block of code but is not working the way I want it to because the 11:25am In Time is getting mapped to the 1:37pm Out Time.

and out_time = (select min (out_time)
FROM tableB WHERE
tableB.record# = tableA.record#
and tableB.loc_id = tableA.loc_id

GROUP BY tableB.record#, tableB.loc_id )

It seems I need to focus on the minimum datediff for each record line but can't figure that part out.

View 2 Replies View Related

Calculate The Difference Time Between Two Times?

Oct 9, 2013

i am using this expression to get the time difference between two times.

{%Z.elapsed.time(@AK.VD.depart.date,@AK.VD.depart.time,@AK.VD.depart.date,@DV.VD.arrival.time,"hh.hh")*60} as [LOS (min)]

When Arrival time and depart time both are on same day above expression working to get the diference .

But if arrival date 2013-09-20 00:00:00.000 and arrival time 0800 and depart date 2013-09-21 00:00:00.000 and depart time 0050 when i calculate the time difference(using above expression) between these two i am getting -429.60 which is wrong. i have to get around 990.

View 1 Replies View Related







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