Hello ,
I have a stored procedure that group a series of event by day. My table Tevent has a timestamp column for every event.
Tevent structure:
idevent| event| DatetimeEvent
I just found out that I need to group the result by production shift of the day , therefore I have to group the result for everyday from 8 AM until 2 AM the next day.
Any idea of how to group by time interval?
Thanks
Hello,
I'm trying to create a group by a set (but can change it later on) time interval.
What I mean by this is the following. I would like to group my data by 5 second intervals, and perhaps have a count for it as well - but that's not required.
For example I have the following data:
PRODUCT, DOOR, TIME_LOCATED
-------------------------------------
chips, 1, 3/5/2008 12:33:30 PM
harddisk, 1, 3/5/2008 12:33:30 PM
tea, 1, 3/5/2008 12:33:31 PM
software, 1, 3/5/2008 12:33:31 PM
chips, 1, 3/5/2008 12:33:32 PM
chips, 1, 3/5/2008 12:33:33 PM
chips, 2, 3/5/2008 12:33:34 PM
software, 1, 3/5/2008 12:33:40 PM
tea, 1, 3/5/2008 12:33:40 PM
software, 1, 3/5/2008 12:33:41 PM
Result in 5 second intervals (so group by values between 3/5/2008 12:33:30 and 3/5/2008 12:33:35)
Something like this?, or does have somebody a better idea?
PRODUCT, DOOR, TIME_LOCATED, COUNT
-------------------------------------------
harddisk, 1, 3/5/2008 12:33:30 PM 1
tea, 1, 3/5/2008 12:33:31 PM 1
software, 1, 3/5/2008 12:33:31 PM 1
chips, 1, 3/5/2008 12:33:33 PM 3
chips, 2, 3/5/2008 12:33:34 PM 1
software, 1, 3/5/2008 12:33:40 PM 2
tea, 1, 3/5/2008 12:33:40 PM 1
THANKS!
I have a table(work_order) with time as varchar(5).The values in table looks like thiswork_order_id rtim1 08:152 08:453 10:134 14:56and so on...I want to count how many work orders for every half an hour.The result should look like thisHours Count8 108:30 159 349:30 03and so on....really 8 hours means the work_orders issued (rtim)between 8:00 AND 8:30.Any Help is Appreciated.Thankyou.Jaidev Paruchuri
View 4 Replies View RelatedI need to add the Job Interval and Time to the following script:
SELECT DISTINCT 'CYP_BI' AS Server,Jobs.Name AS Job_Name, Jobs.description AS Alias,
'Enabled' = CASE (Jobs.enabled)
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
ELSE '??'
END,
[Code] .....
This query contains the job interval and time but when I add it to the first script I can't get my syntax right:
SELECT DISTINCT
'CYP_BI' AS Server,
S.name AS JobName,
S.description AS Alias,
'ScheduleName' = left(ss.name,25),
'Enabled' = CASE (S.enabled)
[Code] .....
Time Interval for hour and 30 minutes
--------------------------------------------------------------------------------
Hi Everyone
I Have column in sql server databas as "HHMMSS" and data as and i am doing a substring to get values for hours and minutes. since my calculations based on hour interval and 30 minutes interval
for ex: Now i want to show all the transaction done b/w 6 to 7 am or pn.
and for 30 minutes interval i have get the calculation as transactions done b/w 6:00 to 6:30 and 6:30 to 7:00 either it's am or pm. now how i can write my sql statements that calculates hour and 30 minutes intervals
HHMM
-------------
06:43
09:26
09:26
11:58
12:25
18:17
20:45
00:43
00:53
16:47
Thanks
Phani
I have a table (tbl_entries) in my db that has a timestamp field (startDate). What I need to do is run a query that counts the number of records on a 15 min interval.
something like
start_date
2008-01-01 00:00:00.000
2008-01-01 00:00:00.000
2008-01-01 00:00:00.000
2008-01-01 00:01:00.000
2008-01-01 00:01:00.000
2008-01-01 00:01:00.000
2008-01-01 00:14:00.000
EVERY THING ABOVE HERE IS IN GROUP 1
2008-01-01 00:35:00.000
EVERY THING ABOVE HERE IS IN GROUP 2
2008-01-01 01:01:00.000
2008-01-01 01:03:00.000
2008-01-01 01:03:00.000
2008-01-01 01:04:00.000
EVERY THING ABOVE HERE IS IN GROUP 3
2008-01-01 01:29:00.000
EVERY THING ABOVE HERE IS IN GROUP 4
2008-01-01 01:41:00.000
EVERY THING ABOVE HERE IS IN GROUP 5
2008-01-01 02:25:00.000
2008-01-01 02:28:00.000
2008-01-01 02:31:00.000
2008-01-01 02:33:00.000
EVERY THING ABOVE HERE IS IN GROUP 6
Hope this is enough Info
I want to check a record in my table which has time ins and time outs feilds. I want to check on insertionof any new record that a new record should not be inserted on same timings and before 45 mins of timein and after 45 mins of timeout. How do i do it sql query ?
View 1 Replies View RelatedI have a simple table as shown:
I want to have values on the last column to represent the time interval between the 2 date columns (visits); i.e for event-ID 2 for example, I will have
entry(EventID = 2) - exit(EventID = 1), and so on
how to retrive image from database change automatically into time interval
View 2 Replies View Relatedhi there
i am using .net framework 1.1 with SQL 2000 .
i want the data in table to get deleted automatically after 30 days of inserting data.
so how do i achieve this?
Is it possible to change the default detection interval time to reduce to less than 5 seconds.
We have latency in trouble shooting the deadlocks and causing blockings more on our critical Production server.
I'm trying to create a report which will give me a break down of how many unique vehicles have been seen between two dates via a 15 minute interval and what Lane they were seen. My current script looks like this
SELECT l.Name [Name], count(l.Name) Total, p.Created
FROM PlateReads p
inner join Lanes l on p.Lane_ID = l.ID
where LicencePlate in (Select Plate from LPRnet_MelAir_C.dbo.TempVehiclePlates)
group by Name
Name being the Lane they were in and the Total being the amount of times a unique vehicle has been seen and p.Created being the date they were seen (thats what I need the interval powered off)
Ideally the output would look like this
16/03/201408:00 to 08:15Bus Lane 15
16/03/201408:00 to 08:15Elevated Road150
16/03/201408:00 to 08:15Public Pickup75
16/03/201408:15 to 08:30Bus Lane 13
16/03/201408:15 to 08:30Elevated Road120
16/03/201408:15 to 08:30Public Pickup55
All the way to 12/04/2014
I’ve got it so it says Lane and Count just can’t get the interval part
i want to show data Party Name and Time interval wise. here is my table from where i will fetch data. so pasting table data here.
Call start Call duration Ring duration Direction Is_Internal Continuation Party1Name Park_Time
------------------------- ---------------- ------------- --------- ----------- ------------ --------------- -----------
2015/06/08 08:06:08 00:02:28 2 I 0 0 Emily 0
2015/06/08 08:16:38 00:00:21 0 I 0 1 Line 2.0 0
2015/06/08 08:16:38 00:04:13 5 I 0 0 Jen 0
[code]...
now i am not being able to cross join this CTE with my table to get data party name wise and time interval wise. say for if no data exist for a specific time interval then it will show 0 but each party name should repeat for time interval 9:00:00 - 9:30:00 upto 17:30:00. i like to add what filter need to apply to get data for incoming, outgoing, call transfer and miss call.
For Incoming data calculation
where direction='I' and
Is_Internal=0 and continuation=0 and
RIGHT(convert(varchar,[call duration]),8)<> '00:00:00'
For outgoing data calculation
[code]...
Implement time interval type in the form of a user defined type in SS2k8r2? Specifically an interval type described in the book Temporal Data and the Relational Model by C. J. Date at all. As an example, an interval is below:
1/4/2006:1/10/2006
which would mean the time period from 1/4 to 1/10.
Hi there,
With a DateStamp field
Im trying to group by Day Name
and also group it By Time ranges
i.e. Morning 00:00 to 11:59
Afternoon 12:00 to 17:59
Evening 18:00 to 23.59
I can do the group By Day Name ok
Like Below
Code:
SELECT COUNT(ISNULL(createdDate, 1)) AS DayCount, ISNULL(DATENAME(dw, createdDate), 'No Date Set') AS DayOftheWeek
FROM dbo.MyTable
GROUP BY ISNULL(DATENAME(dw, createdDate), 'No Date Set')
its just the grouping by time ranges im having trouble with..
Code:
M | T | W | Th | F | S | Su
Morning ||
Afternoon ||
Evening ||
Thanks for any help ..
I have a database where user events are recorded quite frequently. I'd like to be able to get a count of the 'good' events that happen in each 5 second period. Unfortunately I don't know how to display and group by a time range.
Here is the query I would like to change:
SELECT count(*), clientTime
FROM dbo.V_COMBINED
WHERE (sessionId = '122b') AND (type = N'sys_goodaction') AND (paraName = 'value')
GROUP BY clientTime
It returns records like:
1 |2006-02-16 23:21:05.250
1 |2006-02-16 23:21:05.267
1 |2006-02-16 23:21:06.470
I'd like it to return records like:
5 |2006-02-16 23:21:06 - 23:21:10
3 |2006-02-16 23:21:11 - 23:21:15
4 |2006-02-16 23:21:16 - 23:21:20
Anyone know how I could do this? Is it even possible?
Thanks
Hello
I am trying to pull data from a table. I would like to group this dataset by a datetime field.
The result I want is for the records to be grouped together by their create time ( in this case using half hour increments.) All records created from the first half hour get grouped together, all records from the second half hour get grouped together etc. This dataset will contain data from a 24 hour time period (48 groups of data). What is the best way to accomplish? Do I need to use a cursor to accomplish?
Thanks
How could i restore a filegroup to a certain point of time (not to apply all
transaction log up to the latest one) ??
I have an SSRS 2012 table report with groups; each group is broken ie. one group for one page, and there are multiple groups in multiple pages.
'GroupName' column has multiple values - X,Y,Z,......
I need to group 'GroupName' with X,Y,Z,..... ie value X in page 1,value Y in page 2, value Z in page 3...
Now, I need to display another column (ABC) in this table report (outside the group column 'GroupName'); this outside column itself is another column header (not a group header) in the table (report) and it derives its name partly from the 'GroupName' values:
Example:
Value X for GroupName in page 1 will mean, in page 1, column Name of ABC column must be ABC-X Value Y for GroupName in page 2 will mean, in page 2, column Name of ABC column must be ABC-Y Value Z for GroupName in page 3 will mean, in page 3, column Name of
ABC column must be ABC-Z
ie the column name of ABC (Clm ABC) must be dynamic as per the GroupName values (X,Y,Z....)
Page1:
GroupName Clm ABC-X
X
Page2:
GroupName Clm ABC-Y
Y
Page3:
GroupName Clm ABC-Z
Z
I have been able to use First(ReportItems!GroupName.Value) in the Page Header to get GroupNames displayed in each page; I get X in page 1, Y in page 2, Z in page 3.....
However, when I use ReportItems (that refers to a group name) in the Report Body outside the group,
I get the following error:
Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope
I need to get the X, Y, Z ... in each page for the column ABC.
I have been able to use this - First(Fields!GroupName.Value); however, I get ABC-X, ABC-X, ABC-X in each of the pages for the ABC column, instead of ABC-X in page 1, ABC-Y in page 2, ABC-Z in page 3, ...
If two records containing datetime stamps are SELECTed, how can the interval between them be quickly calculated?
View 8 Replies View RelatedDoes anybody know what's the best way to do a count with a 1 day interval? This to me seems like the best approach, my data looks like this
work order, analyst, analysis, dateanalyzed
080154705, ZT, Special, 1/31/2008 9:35:00 PM
080154706, ZT, 8260B, 1/31/2008 10:07:00 PM
080154706, ZT, Special, 1/31/2008 10:07:00 PM
080154706, ZT, Special, 1/31/2008 10:39:00 PM
080154706, ZT, Special, 1/31/2008 11:11:00 PM
080154706, ZT Special, 1/31/2008 11:43:00 PM
080165001, ZT, 624, 2/1/2008 4:30:00 AM
080165001, ZT, 624, 2/1/2008 5:02:00 AM
080166901, ZT, 624, 2/1/2008 12:50:00 PM
080166801, ZT, 624, 2/1/2008 2:51:00 PM
080154711, ZT, Special, 2/1/2008 10:39:00 PM
080124711, ZT, Special, 2/1/2008 10:11:00 PM
What I would like is to have my data displayed as follows:
Analysis, myCount, dateanalyzed
Special, 5, 1/31/08
Special, 2, 2/1/08
624, 4, 2/1/08
8260B, 1, 1/31/08
As you can see, I will need to count the number of occurences for analysis for each day, such as Special has 5 and 2 for those two days, we can have difference analysis done each day done. A Union Join will not work because the table is huge and I will need to do at least 30 days. Is interval the best approach, I plan to use Access but can do it in SQL Server if necessary.
Hello
I was wondering where I can change the polling interval settings in sql server management studio..
It appears to be different from the one in Enterprise Manager.
very little info can be found on the Internet
Thanks!
Worf
Hello,
I have something i called a deletelist, inside this list is items that is waiting to be deleted.
The deletelist looks like this:username nvarchar(20),itemtype int,deletedate datetime
The SP that runs once everyday and deletes post is called Data_DeleteFromDeleteList.
Inside of this SP i have afew delete functions like this one:DELETE FROM reg_user WHERE (username = (SELECT username FROM data_deletelist WHERE (deletedate < getdate() AND itemtype = 0)))
Now i want to add a new delete function to this list, this function is to execute a SP when the itemtype occurs, like this:SELECT EXEC [dbo].[Data_CompletlyDeleteAUser] @UserName = username, @QResult = @QResult OUTPUT FROM data_deletelist WHERE (username = (SELECT username FROM data_deletelist WHERE (deletedate < getdate() AND itemtype = 2)))
any idea of how i can rewrite this function so it works??in this SP im not using the @QResult for anything, thats in another SP
At the end of this SP, i just delete all the old records from the deletelist like this,
DELETE FROM data_deletelist WHERE (deletedate < getdate())
I have a stored procedure that needs to executed every 10 seconds (either
at a fixed 10 second interval or 10 seconds from the completion of the stored
procedure). I initially assumed that the SQL Server Agent in SQL Server
7 would allow me to schedule a recurring job at this interval but it appears
that the lowest resolution for scheduling recurring jobs is minutes. The
task needs to begin when SQL Server starts and then run repeatedly.
I would appreciate any suggestions for resolving my problem.
Thanks
I am new to this group and to Sql query also. My requirement is the following
I have a table with these values...
colacolb colc cold
16:01 1 2 3
17:01 1 3 4
18:01 5 6 8
16:01 8 2 1
17:01 9 5 1
18:01 3 5 1
16:01 4 6 1
17:01 2 3 5
18:01 8 4 3
Now I want to get the SUM of colb, colc on each interval between 16:01 to 18:01...
I'm trying to migration a database from PostgreSQL to SQL Server, but i've found a problem with interval data type, SQL server does not have an interval data type, can someone help me please? I need the solution ASAP due to work deadline =(
View 2 Replies View RelatedHi,
I am trying to develop a custom destination component. Is there a way to enable transaction / commit interval in OLE DB drivers?
Thanks,
Vipul
Hi, I have a table with 3 columns ( Id int, theValue float , aDate datetime). I want to select the number of rows (count) for which the interval of aDate is less than 7 seconds. Is there a way to achieve that in a single query.
PS: This query will be perform on a dataset, that means that I can't use cursor.
Thanks
Sylvain
I am currently using the code below to convert data collected in 1 minute intervals to other time intervals (e.g., 5 minute intervals). The code works fine except for one thing. I would like the DateTime value in the new interval to coincide with the end DateTime value of that interval rather than the ceiling DateTime value (the values of Field1 thru Field4 must be extracted from existing data). The problem occurs when there is no data for a particular 1 minute time period. An example might make this more clear (in this case 1 min data is converted to 5 min data).
'2007-09-30T08:01:00'
'2007-09-30T08:02:00'
'2007-09-30T08:03:00'
'2007-09-30T08:04:00'
'2007-09-30T08:05:00'
Above values are used to create a 5 min interval record with
DateTimeValue for this record (in DestTableName) = '2007-09-30T08:05:00'
That's how it should work. However, missing data causes a problem
in the next 5 min time interval.
'2007-09-30T08:06:00'
'2007-09-30T08:07:00'
'2007-09-30T08:08:00'
'2007-09-30T08:09:00'
Missing value at '2007-09-30T08:10:00'
Above values are used to create a 5 min interval record with
DateTimeValue for this record (in DestTableName) = '2007-09-30T08:09:00'
Instead, I want the DateTime value in DestTableName to be '2007-09-30T08:10:00'
The values in Field1 - Field4 in DestTable have to be drawn from existing data; the only 'artificial' data would be the DateTime time value needed to coincide with the time interval.
USE tempdb;
IF OBJECT_ID('#Data') IS NOT NULL
DROP TABLE #Data;
IF OBJECT_ID('#Grouped') IS NOT NULL
DROP TABLE #Grouped;
DECLARE @dtStart datetime, @dtEnd datetime, @dtDivisor integer;
SET @dtStart = '2007-07-01T08:01:00';
SET @dtEnd = '2007-09-30T16:30:00';
SET @dtDivisor = 5
Select
[DateTime],
[Field1],
[Field2],
[Field3],
[Field4],
CEILING(DATEDIFF(minute,@dtStart,[DateTime])/@dtDivisor) Frame
Into #Data
From
[SourceDbName].[SourceSchemaName].[SourceTableName]
WHERE
(convert(varchar, [DateTime],126) BETWEEN '2007/07/01' AND '2007/09/30') AND
(convert(varchar, [DateTime],114) BETWEEN '08:01:00' AND '16:30:00')
Select
Frame,
MAX(t1.[DateTime]) [Max_DateTime],
MIN(t1.[DateTime]) [Min_DateTime],
MAX(t1.[Field2]) AS [Field2],
MIN(t1.[Field3]) AS [Field3]
Into #Grouped
From
#Data t1
Group By
Frame
Select
Opn.[DateTime],
[Field1],
[Field2],
[Field3],
[Field4]
Into
[DestDbName].[DestSchemaName].[DestTableName]
From
( Select
G.[Frame],
G.[Max_DateTime] as [DateTime],
D.[Field1],
G.[Field2],
G.[Field3]
From
#Data D
Join #Grouped G On D.[DateTime] = G.[Min_DateTime] And D.Frame = G.Frame
) as Opn Join
( Select
G.[Frame],
G.[Max_DateTime] as [DateTime],
D.[Field4]
From
#Data D
Join #Grouped G On D.[DateTime] = G.[Max_DateTime] And D.Frame = G.Frame
) as Cls On Opn.Frame = Opn.Frame And Opn.[DateTime] = Cls.[DateTime]
I'm trying to consolidate time intervals.
It's like the packing date and time intervals tsql challenge from Itzig Ben Gan.But unfortunalty I have to cope with time intervals priories, e.g. if one time interval with a higher prio overlaps a time interval with a lower one something like this:
time interval 1 prio 2: [--------------]
time interval 2 prio 1: [---]
result: [---][---][----]
I can't get this aligned, but my point is that from the 2 intervals above you get one interval result with the classic packing solution but because of the higher priority of interval 2, I should get 3 intervals as a result.
IF OBJECT_ID('dbo.Sessions') IS NOT NULL DROP TABLE dbo.Sessions;
CREATE TABLE dbo.Sessions
(
sessionid INT NOT NULL,
actid INT NOT NULL,
starttime DATETIME2(0) NOT NULL,
endtime DATETIME2(0) NOT NULL,
[code]....
I have a problem that I can't quite get started on solving.I have a table of asset statuses. Each time the asset status changes, anew row is inserted into the table.CREATE TABLE dbo.Tbl_EMStatusHistory (EMStatusID int IDENTITY (1, 1) NOT NULL ,EMSessionID int NULL ,AssetID int NOT NULL ,AssetStatus int NOT NULL ,StatusComment varchar (250) NULL ,StatusDate smalldatetime NOT NULL ,InsertUser sysname NOT NULL ,InsertDate smalldatetime NULL ,CONSTRAINT PK_EMStatusHistory PRIMARY KEY NONCLUSTERED(EMStatusID))Here is some sample data:EMStatusID AssetID AssetStatus StatusDate DeviceType4772622624OUT2003-10-05Monitor3810022624IN2003-10-16Monitor4726122624OUT2003-10-25 Monitor3819322624IN2003-11-02 Monitor3917122624RV2004-05-02 MonitorFor asset 22624, the current (most recent) status = RV. Before that, itlastchanged status on 2003-11-02, when it was IN. So it was IN from2003-11-02 to2004-05-02.I need to produce a report that counts the devices by type that were INon a given report date,like so:ReportDateMonitorsTransmitters05/01/2004342605/02/2004373005/03/2004393205/04/20043531The end user supplies the date range.I am unsure how to approach this. I came up with oneEinsteinian-Newtonian method that involved three temp tables, a gardenhose, and a duck, and it was getting uglier by the minute. Has anyonehad experience with a requirement like this? Thanks, Dave"Are you thinking what I'm thinking, Pinky?"*** Sent via Devdex http://www.devdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedHi
How can commit interval for OLE DB destination be set when the data access mode is not "fast load".
What happens in oledb destination in case of a failure in package? How does the roll back happens. I mean how is the commit point set in oledb destination? I know about the transaction options which are at the package level.
Thanks,
Vipul
I have a SSRS report where I am displaying the data for 3 years in a column chart. I am trying to get the x-axis labels to be the first of the month. e.g. using UK date syntax 01/01/07;01/02/07 (1st Feb); 01/03/07 .....
If I check 'numeric or timescale' I get the result I want in 6 month steps when I would like monthly steps. If I use an interval value that does not help as months have different number of days. So an interval of 30 means I end up display 02/03/07 when I want 01/03/07
Can anyone confirm how I can change the label frequency from 6 months to 1 month?
Regards
Leo