I 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
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]...
SELECT * ,[Due]
FROM [Events]
Where Due >= getdate() +90
This returns the error: Conversion failed when converting date and/or time from character string
Why would this be? How to cast or convert this so that it will work?
I have to build a database model, create tables and use triggers or/and procedures on it. It's about a library. Books are borrowed by students. They can make a reservation of a book.
I built the database model so far. My problem is how to represent following tasks:
- Students can borrow a maximum of 5 books at the same time.
- bookings/book reservations should be later processed in the order they occurred. That is, who first flagged the book also gets the book/books first
I have following entities:
- Book
- Book exemplar
- Borrowed by (a table between the entities Book exemplar and Student)
- Reserved for (a table between the entities Book exemplar and Student)
- Student
How would you represent the tasks I mentioned above in terms of triggers or procedures?...
I need to compare the contents of a column.
I have a table with 1 column. Below are the sample contents..
DateInterval
2012-06-01 00:30:00.000
2012-06-01 01:00:00.000
2012-06-01 01:30:00.000
2012-06-01 02:00:00.000
2012-06-01 02:30:00.000
[code]....
as you can see, the records have a 30minutes time interval. i need to create a query to know if there are missing records in the table. so basically the result should be this:
DateInterval
2012-06-01 18:00:00.000
2012-06-01 20:30:00.000
if it is not doable, we can get the nearest record of all missing records like this:
DateInterval
2012-06-01 17:30:00.000
2012-06-01 20:00:00.000
or this:
DateInterval
2012-06-01 18:30:00.000
2012-06-01 21:00:00.000
or this:
DateInterval
2012-06-01 17:30:00.000
2012-06-01 18:30:00.000
2012-06-01 20:00:00.000
2012-06-01 21:00:00.000
adate atime
08-21-2007 11:09
08-20-2007 16:49
08-03-2007 00:39
I would like to combine adate with atime to get adatetime
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 RelatedWe are maintaining 3 Shifts in our database. Problem in maintaining date and time for 3rd Shift. For example, today date is 13th July and third shift timing is 11 PM - 7 AM. Then I have to display the beginning date as 13/07/2015 11 PM and end date as 14/07/2015 7 AM. Please find the data(in seconds) available in database which I need to use for my calculation.
Date(Fcreacion)
Start time in Seconds(Hcreacion)
End time in seconds(Hcerrar)
turno(Shift)
[code]...
I 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'm trying to translate this portion of VFP code into LINQ query:
select COUNT(ID) as conflicts
from dbo.max4sale where <<thisform.cWhere>>
AND Start_Time >= <<VFP2SQL(m.ltBegin + m.lnStartTime)>>
and Start_time <= <<VFP2SQL(m.ltEnd)>>
AND CONVERT(varchar(5),Start_Time,108) <= <<VFP2SQL(m.lcEndTime)>>
AND CONVERT(varchar(5),End_Time,108) >= <<VFP2SQL(m.lcStartTime)>>
<<m.lcDays>>
Here is my non-working attempt:
var startTime = new DateTime(1900, 1, 1, beginDateTime.Hour, beginDateTime.Minute, 0);
var endTime = new DateTime(1900, 1, 1, endDateTime.Hour, endDateTime.Minute, 0);
var daysOfWeek = dailyLimits.Where(dl => dl.Selected == true).Select(ds => ds.WeekDay).ToList();
if (daysOfWeek.Count() < 7) // not all days of the week selected
[Code] .......
First of all, I see a bug in my logic now as the first part of the query I need to do all the time and only the second part if the count < 7. But that's not my problem - I can not figure out how to make times comparison only using LINQ. Ideally I think I'd like to have cast(start_time as time) >= @p1 as a result to be executed by LINQ.
BTW, I am only getting the error in run-time that Parse can not be interpreted. So, I need to figure out another way of making LINQ recognize my intent of checking time portion of the date only.
I need to convert Local date time field to GMT. [Does we need to consider daylight saving and so on?]
[URL]
I have 2 tables:
TransactionsImport (which is the destination table)
TransactionsImportDelta
I need to do the following:
Get the records with the latest date and time in the destination table TransactionsImport
Get the records with the latest date and time in the destination table TransactionsImportDelta table
Insert the records from the TransactionsImportDelta table into TransactionsImport that have a greater date & time than the current records in TransactionsImport table.
Problem is date & time are in separate columns:
Table structure:
Date Time ID
2011121305154107142201008300100
2011121305154122B1L13ZY0000A07YD
2011121304504735142201090002600
2011121304504737142201095008300
2011121304504737142201090002600
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 3 requests to do and all 3 are below in SQL , i am planning to keep them as job and run on regular intervals as per request ..
1.Poll the contract approval table for no prepared new contracts. Check every 30 minutes from 9am to 10pm – 7 days a week.
IF NOT EXISTS (SELECT * FROM ContractApproval WITH (NOLOCK) WHERE ContractPrePared>DATEADD(MINUTE, -30, GETDATE()))
BEGIN
SEND DBMAIL ---i have this part working already
END
2.Poll the contract approval table for no new signed contracts. Check every 30 minutes from 9am to 10pm – 7 days a week.
IF NOT EXISTS (SELECT * FROM ContractApproval WITH (NOLOCK) WHERE DateSigned>DATEADD(MINUTE, -30, GETDATE())
BEGIN
SEND DBMAIL ---i have this part working already
END
3.Poll the lead table for no new leads. Check every 5 minutes. 24/7.
IF NOT EXISTS (SELECT * FROM Lead WITH ( NOLOCK ) WHERE DateCreated > DATEADD(MINUTE, -5, GETDATE()))
BEGIN
SEND DBMAIL ---i have this part working already
END
I am planning to keep 1,2,3, in seperate sp's and run them as JOBS....how can i achieve below a,b ?
a.The procedures should accept a parameter that indicates the interval, in minutes, that they should look back
b.Each step in 3 jobs should invoke the procedures, specifying the interval with which the job runs at
I try to find out how many jobs where run in parallel on my server in an interval of time. For example: between 1:00 AM and 2:00 AM there were MAX 66 jobs that run in parallel and MIN 4 jobs. I am not sure if I can find this info out from a system view or I need to play with sysjobhistory view.
View 10 Replies View Relatedwhat would be the TSQL in trying to create a new table with date-time format ending via a select into like:
select
*
into tblResults_
+
SELECT
CONVERT(VARCHAR(10),GETDATE(),112)
+
'_'
+
REPLACE(CONVERT(VARCHAR(10),GETDATE(),108),':','_')
from qryResult
I am trying to pull the records which are being affected i.e, comparing the values and if not same then populating the record.
I am using the below condition in where clause however i am getting runt time error as "Conversion failure when converting date and/or time from character string"
Condition in Where clause:
cast(isNull(tab1.Col1,'') as datetime) <> cast(isNull(tab2.col1,'') as datetime)
Note: Both col1 columns are of type nvarchar
Below is the SQL Query i am currently having
SELECT IG_FinancialTransactionSummary.ClaimNum,IG_FinancialTransactionSummary.TransactionCode,
IG_FinancialTransactionSummary.TransactionDate,IG_FinancialTransactionSummary.Username,
FinancialTransactionSummaryTest.ClaimNum,FinancialTransactionSummaryTest.TransactionAmount,
FinancialTransactionSummaryTest.UserName--,FinancialTransactionSummaryTest.TransactionDate
[Code] ....
And here is the result dataset
ClaimNumTransactionDateUsername ClaimNum TransactionAmountUserName
2000074 20150209jerry.witt 2000074 -10000DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL
2000074 20150626DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL 2000074 -10000DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL
[Code] .....
So,if we look at the result set, we notice 2 conditions where the IG_FinancialTransactionSummary.Username is like 'Data' and if we see the transaction date then sometimes that is the max transaction date or sometimes there are transactions that happened after but that doesn't have like '%data%' in username . So, i need to add a new column to my sql query which should basically verify if the username is like '%data%' and if that is the max(transaction date) or even if there are any transactions after that doesn't have like '%data%' then YES else No.
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've imported a CSV file into a table in SQL Server 2012. It's a large file, 140,000+ rows, so I couldn't covert it to Excel first to preserve the date format due to Excel's row limit. In the CSV file, there were 3 column with date data in "31-Aug-09" format, and the import automatically transformed these in "31AUG09" format (varchar(50)) in SQL Server. Now I need to convert these 3 columns from varchar to datetime so I could work with them in date format.
I've tried several things,e.g,
select
convert(datetime,
right(admdate,4)+left(admdate,2)+substring(admdate,3,3))
or
select
convert(datetime,
substring(admdate,6,4)+substring(admdate,1,2)+substring(admdate,3,3))
but keep getting the same error message (Msg 241) "Conversion failed when converting date and/or time from character string".
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 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 RelatedI have problem to execute query with interval date.
If try this query I don't have problem and the output is correct:
Code:
SELECT * FROM dotable
WHERE
dotableDate BETWEEN CONVERT (datetime, '01/01/2015', 121)
AND CONVERT (datetime, '09/01/2015', 121);
Instead if try this I have error:
Code:
SELECT * FROM dotable
WHERE
dotableDate BETWEEN CONVERT (datetime, '25/01/2015', 121)
AND CONVERT (datetime, '28/01/2015', 121);
[Err] 22007 - [SQL Server]The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
I have a table in which each record has a initial date and a final date. I would like to create I query that gives me one row for each month between the initial date and the final date. It would be something like this:
Original:
Product|price|initial_date|final_date
A|20.50|2014-08-10|2014-10-01
B|50|2015-01-15|2015-02-20
Resulting view:
A|20.5|2014-08-01
A|20.5|2014-09-01
A|20.5|2014-10-01
B|50|2015-01-01
B|50|2015-02-01
I would like to do that, because these dates correspond to the time in which the products are in possession of sellers, so I would to use this resulting query to generate a pivot chart in Excel to illustrate the value of the goods that are with our sellers in each month.
Is it possible to do it? I think I could do that direct in VBA, but I think that maybe it would be faster if I could do it directly in SQL.
By the way, I am using MS SQL Server and SQL Server Manegement Studio 2012.
I have line chart and want to see the xaxis date on interval of 5 days and it should start from first date and end at last date.
If my range starts from 11 Nov to 1 Dec
11 Nov 16 Nov.....1Dec
My date column have null fields . Though I have tried putting dummy values it still does not work so its not the problem of NULL.
I am getting Below results:
If I keep Interval one it starts form 11th and ends at 01 dec but it shows all dates.
This is driving me nuts..
I'm trying to extract some data from a table in oracle. The oracle table stores date and time seperately in 2 different columns. I need to merge these two columns and import to sql server database.
I'm struggling with this for a quite a while and I'm not able to get it working.
I tried the oracle query something like this,
SELECT
(TO_CHAR(ASOFDATE,'YYYYMMDD')||' '||TO_CHAR(ASOFTIME,'HH24:MM : SS')||':000') AS ASOFDATE
FROM TBLA
this gives me an output of 20070511 23:06:30:000
the space in MM : SS is intentional here, since without that space it appread as smiley
I'm trying to map this to datetime field in sql server 2005. It keeps failing with this error
The value could not be converted because of a potential loss of data
I'm struck with error for hours now. Any pointers would be helpful.
Thanks
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.