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.
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 have a table of People and their ID, the starting month (a fixed number of months, say 10 for this), the ending month, and the percent of work time (0-1 being 0-100%). If they have a % work of 0, I do not want to see anything. But if the % changes, from say .5 to .75, I would need the first and last month they were at .5, and the first and last month they were at .75
The Table:
/****** Object: Table [dbo].[TestProject] Script Date: 02.07.2014 10:15:08 ******/ IF OBJECT_ID('TempDB..#TestProject2','U') IS NOT NULL DROP TABLE [dbo].[#TestProject2] GO CREATE TABLE [dbo].[#TestProject2]( ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[Code] ....
The data:
--===== All Inserts into the IDENTITY column SET IDENTITY_INSERT #TestProject2 ON INSERT INTO #TestProject2 ("ID","PersonID", "PercentLoad","MonthID") SELECT 1,123456,0,1 UNION ALL
I need a formula to calculate the time (let's say in minutes) between two dates/times. The problem is that I have to exclude the time between 06 PM and 06 AM and also exclude the time in the weekend (Saturday and Sunday). I will use this in a couple of reports made in Reporting Services. If anyone have an algoritm that could be modified for this and is willing to share this I would be very grateful. Many thanks! /Per Lissel
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.
Hi i am trying to make a trigger that calculates a new due time from a start time. I have a hour target like 24 hours, but the dates have an starttime and stoptime or the days could even be closed. I need it to calculate the new due datetime using the 24 hours as effective working time.
How do you build a query to calculate the time of employement or any space of time for each person in a table? I'm thinking I would want to use a "Current Date" and "Select Datediff" calculations of some sort and reference the hiredate against the current date but I have been unable to find much on this type of query.. Please help..
Can anyone help with the following Transact SQL question? Thanks. Ineed a store procedure to return the the result recordset which will beexecute from a web page. The database has tables, A and B. For each Arecord, there are many related B records. In the B table there is atimestamp field which tracks the change of A record. For example, A1has B like the followings:ID TimeStamp Chg Code Descption== ========= ======= ========A1 1138375875 E null //end of the eventA1 1138025002 S resumeA1 1137092615 S don't careA1 1137092570 S stopA1 1137092256 I null //start of theeventI need to generate all records in table A and total elapse time foreach record, but B with Chg Code 'S' that has "don't cacre" to bededucted from the total time, so that the result will be like this:ID Name TotalTime(seconds)== ==== =======A1 xyz 351187
CREATE TABLE #Attendance( [ID] [int] IDENTITY(1,1) NOT NULL, [StudentID] [int] NOT NULL, [ClassID] [int] NOT NULL, [DateAdded] [datetime] default getdate() NOT NULL ) ON [PRIMARY]
insert into #Attendance(StudentID,ClassID,DateAdded) values(1,1,'2014-10-07 10:38:02.900')
[Code] ....
DateAdded column in first table is nothing but in and out time.
Now I want to prepare a query where I want to consider MIN DateAdded and max DateAdded and calculate the duration of student present in the class.
Validations i need to consider are:
If class is starting at 10am then student can come at 9:50am, i.e. Dateadded column should consider as student present in that class if value is less that 10 minutes of StartTime from #ClassAttendance table. Class End time i want to calculate depending upon ClassMinutes from #ClassAttendance
Also DateAdded column should be 10 minutes plus compared to calculated endtime. If its more than that consider lower DateAdded time.
And by using this thingIi want to calculate total number of minutes student present in the class and number of minutes absent.
If there is only one DateAdded for class then consider as a absent student.
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.
I am using SQL Server 2000. I need to query my database for all thecontracts that came in during a certain time frame (user is promptedfor reportingperiodid).Table - PeriodsFields - Reporting Period id intReporting Period desc varchar(30)Reporting Period Begin Date datetimeReporting Period End Date datetimeIf the user selects a 3 then the begin date is Jan. 1, 2004 and theend date is June 30, 2004.Now I need to calculate did any money come in for each week in thattime frame. I need to create a weekly list of all the weeks in thattime frame. Each time frame begins on a Monday. So my list wouldlook like1/5/20041/12/20041/19/20041/26/2004All the way to the end of that time period.How do I create this weekly list from a given time period using T-SQL?I would appreciate any and all help on this.Thanks,Tony
I have a number of databases with large tables. I need to update them from time to time. I want to get the recordcount of the table and calculate based on that the amount of time it would take to update the table. Any idea who I can do this? I'm using coldfusion 8 with sql to do this. Any advice would be appreciate!
How to calculate estimated completion time of a job and what is the variance/difference in time based on previous job history. Looking for tsql query which can accomplish this.For example)...Daily a job is taking 10 mins to complete. However, today due to some reason, the job is running over an hour and still running. It could be a blocking issue or some performance issue on the server due to which the job is still running.
In such cases, using a tsql query or a stored proc which monitor these jobs every 3 mins (Configurable value), so every 3 mins , query has to check, if they are any jobs which are taking more time than its usual completion time/avg completion time in that case shoot an email using dbmail functionality i.e. sp_Senddbmail .. From there, DBA can dig further using waits or sql trace etc...
Basically I want to calculate the time spent by S_Users on a particular S_ACTV_CODE:
- S_ACTV_CODE_PREV means the previous active records.
- S_START_TIME is the time of S_DATETIME when a S_ACTV_CODE starts
- S_END_TIME is the time before a S_ACTV_CODE changes to another S_ACTV_CODE
- For the first record, S_ACTV_CODE is null, so there is no S_ACTV_CODE_PREV, so S_ACTV_CODE_PREV is NULL
- For the second record S_ACTV_CODE has some value, but S_ACTV_CODE_PREV is NULL for first record. So second record S_ACTV_CODE_PREV is also NULL
- For the last record (means S_ACTV_IND = 1), the user is currently working on it and S_ACTV_CODE is not changed. So S_END_TIME is a open time and we want to keep it as NULL
I have two nvarchar fields with time data 12:34:34 and the second one 12:34 I want to calculate the difference in Hours. The first field is called (OTIM) the second field is called (ReportedTime) if the name matters. I tried substring to trim the OTIM, I am unable to make it work.
I am trying to calculate the time difference between the value in the row and the min value in the table. So say the min value in the table is 2014-05-29 14:44:17.713. (This is the start time of the test.) Now say the test ends at 2014-05-29 17:10:17.010. There are many rows recorded during that start and end time, for each row created a time stamp is created. I am trying to calculate the elapsed time and have it as a row in the results.
min(timestamp) - timestamp(value in row) = elapsed time for that test where Channel = '273'
Here is the table DDL
CREATE DATABASE SpecTest; USE SpecTest GO
CREATE TABLE [dbo].[Spec1]( [Spec1ID] [int] IDENTITY(1,1) NOT NULL, [Channel] [int] NOT NULL,
I created a PowerPivot measure which is a ratio Ratio = Number of Events/Time calculated on 12 months. I would like the grand total to be this Rate Sum(Number Of Events)/Sum(Time calculated).
In my Pivot I have one measure which I called Value and this value can have different types depending on one attribute.For instance one attribute is number of events, an other one is time and the third one is ratio.I want to display a custom grand total simple sum for events and time and a calculation of my ratio for ratio.
For instance                             201501 201502 201503 TOTAL Number of events           8        10        10    28 Time                             5           5         4    14 Ratio                           8/5      10/5     10/4 28/14
I am struggling to calculate Full year in my SSAS Cube. Meaning, regardless of what fiscal year hierarchy level I am in; i need a measure aggregating from 01/01/year of current member to 12/31/year of current member.
I want to replicate it using the Year To Date below:
FY-FQ-FM is the fiscal year quarter hieararchy
I am using for built in time intelligence.
Create Member  CurrentCube.[DimTime].[FY-FQ-FM DimTime Calculations].[Year to Date]  As "NA";     /*Year to Date*/  (   [DimTime].[FY-FQ-FM DimTime Calculations].[Year to Date],  [Code] ....
Hi there, I have a query with a rather large (46 table) 1:1 join. Technically, I'm creating a view then trying to select from that view. I seem to be able to create the view ok but cannot resolve a select from it. The error message is:
Msg 8180, Level 16, State 1, Line 1 Statement(s) could not be prepared. Msg 191, Level 15, State 1, Line 1 Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries. Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near 'WHERE'.
This worked in 2000 without a problem
Here is the join:
FROM [HUSKY].USEP0712E.dbo.AGAF_us T1 INNER JOIN [HUSKY].USEP0712E.dbo.AGAM_us T2 ON T1.CODE = T2.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGASN_us T3 ON T1.CODE = T3.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGBF_us T4 ON T1.CODE = T4.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGBLK_us T5 ON T1.CODE = T5.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGBM_us T6 ON T1.CODE = T6.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGHF_us T7 ON T1.CODE = T7.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGHM_us T8 ON T1.CODE = T8.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGHSP_us T9 ON T1.CODE = T9.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGIF_us T10 ON T1.CODE = T10.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGIM_us T11 ON T1.CODE = T11.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGIND_us T12 ON T1.CODE = T12.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGNH_us T13 ON T1.CODE = T13.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGNHF_us T14 ON T1.CODE = T14.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGNHM_us T15 ON T1.CODE = T15.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGOF_us T16 ON T1.CODE = T16.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGOM_us T17 ON T1.CODE = T17.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGOTH_us T18 ON T1.CODE = T18.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGWF_us T19 ON T1.CODE = T19.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGWHT_us T20 ON T1.CODE = T20.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGWM_us T21 ON T1.CODE = T21.CODE INNER JOIN [HUSKY].USEP0712E.dbo.WH_AOH_us T22 ON T1.CODE = T22.CODE INNER JOIN [HUSKY].USEP0712E.dbo.WNH_AONH_us T23 ON T1.CODE = T23.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGAF_us T24 ON T1.CODE = T24.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGAM_us T25 ON T1.CODE = T25.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGASN_us T26 ON T1.CODE = T26.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGBF_us T27 ON T1.CODE = T27.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGBLK_us T28 ON T1.CODE = T28.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGBM_us T29 ON T1.CODE = T29.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGHF_us T30 ON T1.CODE = T30.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGHM_us T31 ON T1.CODE = T31.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGHSP_us T32 ON T1.CODE = T32.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGIF_us T33 ON T1.CODE = T33.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGIM_us T34 ON T1.CODE = T34.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGIND_us T35 ON T1.CODE = T35.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGNH_us T36 ON T1.CODE = T36.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGNHF_us T37 ON T1.CODE = T37.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGNHM_us T38 ON T1.CODE = T38.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGOF_us T39 ON T1.CODE = T39.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGOM_us T40 ON T1.CODE = T40.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGOTH_us T41 ON T1.CODE = T41.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGWF_us T42 ON T1.CODE = T42.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGWHT_us T43 ON T1.CODE = T43.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGWM_us T44 ON T1.CODE = T44.CODE INNER JOIN [HUSKY].USEP0712P.dbo.WH_AOH_us T45 ON T1.CODE = T45.CODE INNER JOIN [HUSKY].USEP0712P.dbo.WNH_AONH_us T46 ON T1.CODE = T46.CODE
I have SQL 2000 Standard edition installed on a Windows server 2003 on a virutal box. SQL was upgraded to 2005 but during the install wizard, it did not prompt me for the Instance Name to upgrade. When done, I see the SQL Management Studio and when I connect, my databases are listed in the studio but the version number says 8.0.760 across the top of management studion which is SQL 2000 sp3.
Enterprise Manager is still available. It appears SQL 2005 was installed side by side with SQL 2000. Does that sound correct?
I downloaded SQL 2005 standard edition from my open license agreement. Don't know if that makes a difference.
I also tried upgrading SQL 2005 via the command prompt and it still shows verison number 8.0.760.
In €˜X€™ server, I have a linked server named €˜CustSrv€™ which is connecting to €˜Z€™ server In the €˜Z€™ server I have a Database named €˜SalesDB€™
I have a view name vw_CusgtomerData in my €˜X€™ server which is selecting data from SALESDB..Customer_Tbl from the €˜Z€™ server through that linked server (€˜CustSrv€™)
The View is simple selecting data from Customer_Tbl from SalesDB
SELECT * FROM CustSrv.SalesDB.dbo.CUSTOMER_Tbl
[Note here using * for all columns?? Is it ok for performance aspects]
Now I have some Application which are using that view through some stored procedure.Few of them passing some parameters like Cust_Id etc
Now my query is that.. Am I fulfilling all performance issues?
Or
What is the suggestive way to fetch data from that remote (Linked Server) server to get good performance benefit?
In my opinion we can fetch data 4 different way from that linked server€¦
A.SELECT * FROM CustSrv.SalesDB.dbo.CUSTOMER_Tbl
B.exec GetCustomerData 65 [Sp_GetCustomerData is a Storedprocedure which is passing a parameter 65 that is Customer_ID and the procedure is selecting data from the view vw_CusgtomerData]
C.SELECT cu_customer_id, cu_customer_name FROM vw_CusgtomerData ORDER BY cu_customer_name Or SELECT * FROM vw_CusgtomerData where Customer_ID=65
D.select * FROM OPENQUERY (CustSrv,'SELECT Customer_ID,cu_customer_name FROM SalesDB.dbo. CUSTOMER_Tbl ORDER BY cu_customer_name ')
Am I bypass the concept of view and fetch data directly in the stored procedure through the linked server ??
Is that like some interval function for dates, like i want to group my data in intervals of 15 minutes and 30 minutes. Is there such a function in T-SQL
I am working on a line chart. The variable on the X- axis is DateTime. The requirement is to have the values displayed with an interval of 4 hours (or mabe 5 or 6 hours - basically every regular intervals). I am using a list control to Subreports to show around 10 reports.
HOW can I do this? The Major and MInor intervals only help in separating which samples will be displayed. But we cannot configure for regular intervals. Please help.
