ID Date Time Length Interval_Num
1 10/11/2014 9:00 420 14
Basically, length represents the # of minutes a person is scheduled for. We have 30 minute intervals. Interval_Num = (Length/30). I need the data to show each interval. For the above example it should only be 14 intervals.For the above example the solution should look like....
ID Date Time Length
1 10/11/2014 9:00 30
1 10/11/2014 9:30 30
1 10/11/2014 10:00 30
1 10/11/2014 10:30 30
I would like to make a function to convert a datetime to half-hour. E.g. If the timestamp is 1:23:05 then converts to 1:30:00, if 1:35:27 then converts to 2:00:00.
I use ASP.NET 2.0 and SQL 2005, the SQLCacheDependency didn't work stable for me, it usually works and after a while, it stops working. Recently, It doesn't work. Today I reset service broker by
Alter DB set new_broker
It seems all work, but too early to be happy, it doesn't work now anymore. I don't see any record in
sys.transmission_queue
And I don't see new entries in
sys.dm_qn_subscriptions neither when I modify a record which bind to SQLCacheDependy.
I see some errors in log like
Message The query notification dialog on conversation handle '{8F8CC642-6340-DB11-8F09-0014227B7B80}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8490</Code><Description>Cannot find the remote service 'SqlQueryNotificationService-f79776f7-9ca5-4c5f-8a66-0d81f7673683' because it does not exist.</Description></Error>'.
Ok, I know that there is a very smart programmer out there that can resovle my issue.
I am trying to calculate time worked by 15 minute intervals.
Example: Emp 1 started work at 13:00:00 and worked 183 minutes Emp 2 started work at 17:15:00 and worked 150 minutes Emp 3 started work at 08:30:00 and worked 17 minutes
I have a client that collects data from a manufacturing facility a one minute intervals. I already have sql statments to convert the 1 minute data to other timeframes (e.g. 30 min, 60 min, daily). However, now the client wants to look at data converted to irregular time intervals. For example, instead of looking at the first, second, third, etc. 60 minutes of a day, they wish to see data grouped irregularly: first 30 minutes, next 1 hr & 45 min, next 2 hours, next 1 hr & 30 min, etc. These irregular intervals could change; they may later want to look at the first hr, next 2 1/2 hrs, next 1/2 hour, etc.; or whatever strikes their fancy.
So far, all I've come up with is run one query for each desired time session and then do a join on all the resulting tables. Anybody have a better idea on how to do this?
how can I get the time differences between them.Let's say , a person who click on break @ 12:00 PM and he is back and select I am back option @ 12:15 the total break time is 15 minutes. However, can I display this difference of break time.
I need to query data from a table which has order_date which is datetime field. How to pull orders by hour by day per month for the last 4 months. Any idea how to write the query?
if now the system time is 2014-12-23 23:45:345 . then I want to calculate the sales amount with total and get current date and current hour filter by hh:00-hh:59 , my desire output is like below :
Current time:23:50 run this query Date Time amount 2014-12-23 23:40 $10 2014-12-23 23:01 $5 2014-12-23 23:39 $5 Total :$20
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
I need to convert the given time into 24 hour clock .
I have the two tables out of which one contain time in 12 Hr clock and another contain time in 24 Hr clock and i need to make a join on this colum by converting 12 Hr time in to 24 hr clock time.
SSRS 2008 R2. I am writing a report that uses a DB4 database. Because of this many SQL commands that I normally use are not available and I don't know what commands I can use. Because of this I am doing much of my calculations inside of SSRS.
I need to round a time field to the nearest quarter hour inside of SSRS.
How to backup half of dbs from a server on C drive and the other half on D drive and vice versa, first half on D drive and other half On C drive using only one job and one stored procedure??
Using scheduling from job add 2 schedules to the job so first schedule backup first half to C and second half to D , the second schedule backup first half to D and second half to D.
i need to be able to do an sql call whereby i can check the day and hour for publishing.eg select story where day = ?? AND hour = ?? any one know the best way to do this. i need to call out date as a day number or someting and the getdate as just the hour??? anyone know how??
I know how to do this in Excel, but not sure about Power Pivot. Our fire department works on a 24 hour shift that starts at 0700 each morning. I want to look up what shift is working depending on the date and time of dispatch. In Excel, I would do a look up table, but instead of using false() as the last parameter, I would choose true().
I am not sure if this would be best created as a calculated column in the table that holds the incident date and time values, or have a separate table and create a relationship to use the shift in the rows/columns of my pivot table.
I have two fields ID and Log data and log data is a 96 character long string of numbers representing 15 minute intervals from midnight to midnight.
I need to convert these 96 characters to a full 1440 characters which would mean taking each of the 96 characters one by one and making 1 character into 15.
I had Vb macro to do the conversion but now it's broken and I can't fix it. Getting it done in SQL would solve a lot of problems.
I then go from the 1440 fields and do log analysis like total time doing a specific activity but my query is dependent on having all 1440 characters.
I have a table that is recording hits to a website. Everytime someone views a page, the datetime of the hit is recorded in a field called hit_date_time. I would like to be able to come up with a query that will show how many hits occured on a given day or given days, broken down by hour.
The resulting table for two days would look something like: Time Hits 1/1/01 12:00 1 1/1/01 1:00 23 1/1/01 2:00 54 1/2/01 1:00 15 1/2/01 2:00 14
I can't seem to figure out how to write the query so that I can take into consideration the date and hour of the event so that I can count it.
Hi, i am trying to add milliseconds to a time. For example if i have a time of 01:01:05:000 and i want to add 0.297 milliseconds to it i use the following simplified query
SELECT CONVERT(nvarchar(20), DATEADD(ms, 0.297, '00:01:05:000'), 14) AS Expr1
However instead of getting 01:01:05:0.297 i get 01:01:05:000. Can somebody please tell me what i am doing wrong.
Hi, i am trying to add milliseconds to a time. For example if i have a time of 01:01:05:000 and i want to add 0.297 milliseconds to it i use the following simplified query
Code:
SELECT CONVERT(nvarchar(20), DATEADD(ms, 0.297, '00:01:05:000'), 14) AS Expr1
However instead of getting 01:01:05:0.297 i get 01:01:05:000. Can somebody please tell me what i am doing wrong.
WITH MEMBER [Measures].[Rank] AS 'Rank ( Product.CurrentMember,Order( {Product.CurrentMember.Parent.Children} ,[Profit], DESC) ) ' SELECT {[Profit] , [Rank]} ON COLUMNS, Drink.Children ON ROWS FROM Sales
I am trying to get a total for times, but I am having trouble gettinganything to work. Basically I created a query to pull up severalpeople's name that performed certain details and how long each detailwas. I now want to add up all the hours to a total for each person. Cananyone point me in the right direction?*** Sent via Developersdex http://www.developersdex.com ***
I have a shopping cart that will get full from time to time becausecustomers click out of the site before they confirm their purchase,therefore leaving a full cart behind. I'd like to have a timestamp onthis table, so that I can delete anything that I find is more than a dayold.How can I do this?Thanks,Bill*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
Hi all,I'm writing a website with Cold Fusion and when a user submits arequest and it's stored in the MS SQL database, I want the unique ID(Identity field in table) to be given to the user on screen plusemailed to user.Now can I store data to the database (where the ID is created) andreturn this as a variable in the same statement? I've seen this doneon many websites, but I have no idea how to do it in one step.Thanks,Alex.
The sp "up_GetConfigurableDayInterval", will return number of days to add the the current date time. But print getdate() + @Id , is not producing the updated result.But when ever i have replaced the variable "@Id" with a value, Say "5", it is producing the expected result.I have also tested the above code by the following:-
create table Temp(value datetime); insert into Temp values(getdate() + @Id); select * from Temp
I know that I can add a column to a mining structure and/or model more than once using XMLA. However, I am having a problem doing the same using DMX. I now that DMX does not support data binding and that it relies on the INSERT INTO... construct to essentially map the structure columns to the incoming comluns from the data source. I tried to use "AS" like I would in SQL but it doesn't seem to like it.
Can anyone confirm that this can only be done using XMLA and not DMX? If I am mistaken, it would be greatly appreciated if you could provide an example on how to do this using DMX.
For background on why I would want to add the same column to a model more than once please read this post.
Hello all. I was just wondering how I could add the current date and time to the name of the .bak file. I perform a full backup daily and would like to keep a weeks worth of backups, but everytime I run the job, it just overites the previous one.
The command in the wizzard is:
BACKUP DATABASE [snl] TO DISK = N'C:Backups_for_FTPSNL_Full.bak' WITH NOINIT , NOUNLOAD , NAME = N'snl backup', SKIP , STATS = 10, DESCRIPTION = N'SNL Backup', NOFORMAT
I am trying to add the hours between each time block stored in a database.
In this database a user enters the begin time and the end time. For example the course MATH0001 would start at 8am and end at 10am. Therefore the user would enter 0810 in the start field and 1000 in the end field. The course MATH0001 doesn't run the entire semester it may only run from 8th Jan - 15th March and the course is scheduled in a room called GR4. Now because a course can be scheduled modularly, one room could have several courses scheduled in this manner.
The problem: I need to find out how many hours GR4 is used but it contains the following courses
A day only has 13 hours. Therefore the total hours spent in GR4 should be 12 hours. This is calculated by adding the hours between 8am and 5pm = 9 hours and 5pm an 8pm = 3 hours. I would not include 9am to 1pm because it is a subset of the 8am to 5pm slot.
Now, how to accomplish this but below is the code that i have thus far:
I forgot to mention that this code was to just test my 'final code' results and it outputs the table shown above. Anyway for testing purposes I have limited the search to the room GR4 and the day Tuesdays.
Code: select DISTINCT ssrmeet_room_code, ssrmeet_start_date, ssrmeet_end_date, ssrmeet_crn, ssrmeet_begin_time, ssrmeet_end_time, (((CAST(M.SSRMEET_END_TIME AS INT))-(CAST(M.SSRMEET_BEGIN_TIME AS INT)))+10)/100 As HoursPerClass,
