I am trying to calculate the number of hours a device has been used and I cant find how. I need a query that calculated and does an average of the number of hrs used in a week.
View 5 RepliesThis function will return working hours between given 2 dates. This function assumes that the break is between 9:45 AM and 10 AM and that Lunch is between 12:30 PM and 1 PM. This function also assumes that the working hours are between 7:30 AM and 4 PM. There is a section for public holidays there. We have a table for that you might not so that piece needs to be fixed.
CREATE function [dbo].[fnc_myHinkley_ASSY_CalcWorkingMinutes] (@StartDate datetime, @EndDate datetime)
RETURNS decimal(14,2)
/*
Programmer: Goran Borojevic
Date: 5/14/2014
This function will return working hours between given 2 dates. This function assumes that the break is between 9:45 AM and 10 AM and that Lunch is between 12:30 PM and 1 PM. This function also assumes that the working hours are between 7:30 AM and 4 PM.
*/
AS
BEGIN
--check if one of the dates is null
if @StartDate is null or @EndDate is null
RETURN 0
[code]...
providing a query for fetching the data for CPU Usage, Memory usage, blocking and all details ...
I want to create a job which will run on a Node every 15 min and store data in a table for each instance...
DMV is not giving more stuff and xtended events not sure if i can store that data into a table?
I am trying to calculate business hours that an order that is open. The rules are 8am-5pm. For instance the first row, the clock would stop at 17:00, and pick up again at 8am and add on to total business hours. If the order was created after business hours, and the endstamp was before business hours, this would be 0. If created after 5pm friday, and the endstamp was before 8am monday, this would be 0. In the second set of timestamps I have here the order was recieved at 14:39 but took until the following day at 14:49 to be ordered. I imagine I have to use datepart and datediff for this, but other than that I am not sure on how to do it. Any help would really be appreciated!
StartStampEndstamp
6/27/2005 14:356/27/2005 17:41:11
6/27/2005 14:396/28/2005 14:49
6/27/2005 18:486/27/2005 18:54
6/27/2005 11:416/27/2005 11:45
All,I have a table with start and end dates/times in it, and would like tobe able to calculate the number of hours represented, accounting foroverlapping records.Note that I am looking for an answer on HOW to do this--I don'tnecessarily need it to be written for me (although it would not gounappreciated!).CREATE TABLE [dbo].[session_temp] ([session_pk] [int] IDENTITY (1, 1) NOT NULL ,[date_start] [smalldatetime] NULL ,[date_end] [smalldatetime] NULL) ON [PRIMARY]GO--These values make a very simplistic example,--as they only represent one--session,so using min and max would work in this case,--but would not work if there--were multiple sessions involved.--hopefully you get the idea of what I am going for:INSERT INTO session_tempVALUES('4/4/04 9 pm','4/4/04 10pm')INSERT INTO session_tempVALUES('4/4/04 9 pm','4/4/04 10:30pm')INSERT INTO session_tempVALUES('4/4/04 9 pm','4/4/04 10:45pm')INSERT INTO session_tempVALUES('4/4/04 9 pm','4/4/04 11pm')INSERT INTO session_tempVALUES('4/4/04 9 pm','4/5/04 2am')--the query I am looking to write would return "5"--the one below obviously does not do what I am looking forSELECTSUM(CAST(DATEDIFF(ss,date_start,date_end) AS NUMERIC(8,2))/3600)FROMsession_tempThanks very much for any insight.Phil---Check out my poker-only weblog at:http://www.livejournal.com/users/chicago_phil/Download my session-tracking spreadsheet at:http://www.geocities.com/fibby70/
View 3 Replies View RelatedHi All,
I am having some problems to calculate the basic work hours for a particular month.
Example is I chose for the month of November, I would like to calculate the number of working days (not weekends) and then multiply by 8 (assuming work hour is 8). The month also would be dynamically chosen from a list and not a fix attribute
Can this be done on reporting services?
Basically I am trying to port the same calculation from crystal report.
The crystal report code is as below:
(if DayOfWeek ({?From}) in 2 to 5 then
8
else if DayOfWeek ({?From}) = 6 then
7
else 0)
+(DateDiff ("ww",{?From} ,{?To}, crMonday)* 8) + (DateDiff ("ww",{?From} ,{?To}, crTuesday)* 8)
+ (DateDiff ("ww",{?From} ,{?To}, crWednesday)* 8) +
(DateDiff ("ww",{?From} ,{?To}, crThursday) * 8)+
(DateDiff ("ww", {?From}, {?To}, crFriday)*7 )
Thanks in advance for your help.
Regards,
Mohd Fadzli
I need to be able to add minutes to a datetime value, which only cover working hours.
I have a holiday table as below:
Examples: (dd/MM/yyyy hh:mm)
Date..........Description
01/01/2015 New Years Day
26/12/2014 Boxing Day
25/12/2014 Christmas Day
25/08/2014 August Bank Holiday
Our Business hours are 08:00-18:00 Mon-Fri (unless the day is in the holiday table)
Start Date............Minute to Add......Expected outcome
01/09/2014 10:00........30...................01/09/2014 10:30
01/09/2014 17:00........65...................02/09/2014 08:05
29/08/2014 17:00........65...................01/09/2014 08:05
22/08/2014 17:00........65...................26/08/2014 08:05
31/08/2014 02:30........65...................01/08/2014 09:05
01/09/2014 19:00........65...................02/08/2014 09:05
01/09/2014 10:00........3005...............08/09/2014 10:05
22/08/2014 17:00........3005...............01/09/2014 17:05
I have tried to create a function to do this (fn_pp_AddMinutesWithinWorkingHours(@StartDate,@Minutes)) but I am unable to come up with a solution which factors in everything correctly.
I have a data file listing open and close hours for the day of the week. I need to format it like
Mon-Thurs 7:00 AM - 5:00 PM
Fri 8:00 AM - 5:00 PM
Sat 8:00 AM - 1:00 PM
Sun Closed
Here is what the data looks like now.
Mon OpenMon ClosedTue OpenTue ClosedWed OpenWed ClosedThu OpenThu ClosedFri OpenFri ClosedSat OpenSat ClosedSun OpenSun Closed
7:30 AM5:30 PM7:30 AM5:30 PM7:30 AM5:30 PM7:30 AM5:30 PM7:30 AM5:30 PMClosed Closed
7:00 AM6:00 PM7:00 AM6:00 PM7:00 AM7:00 PM7:00 AM6:00 PM7:00 AM6:00 PM8:00 AM5:00 PMClosed
There seems like I would end up 5 lines max?
So I have been struggling with a way to perform inventory turn calculations in SQL. I'll start off with the formula. Basically the formula is Cost of Goods Sold over last 12 months / Average Inventory Value over the last 12 months. Short hand as COGS/AvgInventoryValue = Turns
In order to obtain the COGS value I take the Avg(UnitCost) * Sum(InvoicedQty) in the last 12 Months for Sales documents. This is represented as DOCUMENTTYPE = 2
Then divide AvgInventoryValue
To obtain the AvgInventoryValue I need to know the Quantity purchased in the 12 month period * Avg(UnitCost) + QuantityOnHand at the start of the period * Avg(UnitCost)
The purchased documents are represented with DOCUMENTTYPE = 6
The purchases will be positive since we are putting value in. The sales are negative since value is going out. I'll start with a simple example where the product is at zero Quantity On Hand at the beginning of the period.
--CREAT THE TEMP TABLE
create table #VALUEENTRY
(
ITEMNO VARCHAR (50) NOT NULL
, POSTINGDATEDATETIME NOT NULL
, DOCUMENTTYPEINT NOT NULL
[code]....
Is there another approach I should take to obtain the inventory turns?Also how do I account for periods earlier than 12 months where inventory is left over?In the sample data this item happened to be at zero inventory on hand at the start of the period. Also I believe it is my subquery's which are throwing the numbers off but I'm not sure how to isolate the subquery to just perform the result for a specific item. It is aggregating the entire table. How can I make the subquery more specific?
I need only the count of databases that last fullbackup was older then 24 hours or null. and status is online. I have tried
SELECT Count(DISTINCT msdb.dbo.backupset.database_name)
From msdb.dbo.backupset
where datediff(day,backup_finish_date,GETDATE()) > 1 -- or is null
and Database_Name not in ('tempdb','ReportServerTempDB','AdventureWorksDW','AdventureWorks') --online also
group by Database_name, backup_finish_date
Tried using where max(backup_finish_date) < datediff(day,backup_finish_date,GETDATE()) .But get the aggregate in where clause error. get a count of databases with backups older than 24 hours not including the samples, report service, and tempdb. I would also want to put status is online but havent gotten the above to work so havent tried to add that yet.
I have a function with multiple if ( condition) which is CPU intensive. How could I avoid this.
CREATE FUNCTION prici.[fn_pricipalamt]
(
-- Add the parameters for the function here
@Tcode char(10),
@SecTypeCode1 char(10),
@SecTypeCode2 char(10),
@TradeAmount float,
[Code] .....
I have a table which gets updated with the usage figure every week. Any similar t-sql which returns the increase in usage percentage of all the columns.
View 8 Replies View RelatedHi,
I'd like a function that returns the number of hours in a specific month (or the number of days which I could then multiply by 24). The function would have to consider leap years for February.
Any ideas?
Thanks,
Skip.
I have around 100 packages (all [packages run at same time) each package calls a stored Procedure once the Stored proc Execution is completed the package will write the log information into a log table, here how can i capture the CPU and Memory usage for execution of each stored proc.
View 2 Replies View RelatedI have been having issues with our SQL server for awhile now. It seems to run out of memory every few days and when I look at the memory dump, the MEMORYCLERK_SQLOPTIMIZER seem to take over memory and eventually cause the server to crash.
Here is the SQL verison we are using: Microsoft SQL Server 2012 (SP1) - 11.0.3460.0 (X64) Jul 22 2014 15:22:00 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.2 (Build 9200: ) (Hypervisor)..It is on a VM on Windows 2012 server. It has 20gb of RAM allocated to it and the MAX Server Memory is set to 16.5gb.
I have seen the MEMORYCLERK_SQLOPTIMIZER grow to about 11gb at the time of the server crash. Why that is happening? What is causing the memoryclerk_sqloptimizer to get so high? I have looked it up and it looks like it has to do with ad hoc requests, but is there something I can do to bring that memory down when it gets so high so that I can prevent a server crash?Do we just need to add more memory or is there a memory leak somewhere?
I need to write a SQL to find number of hours between a begin time and end time. The fields are varchar. There are several date functions in sql, but I am not able to figure out how to get the hours between two times that is not in date format.
BEGIN_TIME END_TIME
0900----- 1500
1000----- 1700
1000----- 1230
0930----- 1030
Hi,
We are trying to get a rough estimate of the size of the warehouse in terms of number of bytes. Now I understand that when I say char(2) datatype requires 2 bytes of memory. If this is correct then how many bytes does the following data type need -
1. smalldatetime
2. decimal(14,2)
3. decimal(12,2)
4. int
5. smallint.
6. decimal(9,0)
Also can you explain the byte allocation for a varchar column. Say varchar(20) for example.
Any help is appreciated.
Vivek
I am trying to find out the the Average number of contact hours per student. in Reporting Services 2005. The contact hours is the in the Totaltime field
Is this formula correct
=Sum(Fields!TotalTime.Value)/Avg(Fields!TotalTime.Value) is in the =Fields!StateServices.Value Group
My groups are
=Fields!Student_ID.Value
=Fields!StateCategory.Value
=Fields!StateServices.Value
Code:
SELECT Student_ind.[Student ID], ParticipantActivity.ActivityDate, School_tbl.[Studentschool Id], School_tbl.schoolID, ParticipantActivity.TotalTime,
ParticipantActivity.Services, ParticipantActivity.Activity, Student_ind.SSID, ParticipantActivity.StateCategory, ParticipantActivity.StateServices
FROM ParticipantActivity INNER JOIN
School_tbl INNER JOIN
Student_ind ON School_tbl.[Student ID] = Student_ind.[Student ID] ON ParticipantActivity.[Student ID] = Student_ind.[Student ID]
[code]....
I am calculating the average number of patients per day as like this
COUNT(DISTINCT PATIENTNAME) * 1.0/NullIf(COUNT(DISTINCT COALESCE(ARRIVEDATE,DEPARTDATE)),0) AS [AvgNo.ofpatients PerDay]
but i am getting results as like this 5.111111111111 , 8.000000000000,1.000000000000
we don't want to get that many digits after point we want only two digits like this 5.11,8.00 or 8, 1.00 or 1.
How can i do this?
How to obtain the row number in a calculated column using DAX (PowerPivot 2013) ?
I do not have any column that would contain unique values (would be easy in that case !). I just want to obtain a different value in each row.
Hi 2 all,
I need to calculate the number of working days in a month. In clear, i need to ignore saturday.sunday and holiday in a month.
can anyone say the solution?
thanks.
When viewing a report our users see that it is x number of pages, however when they print the report is actually y number of pages. For example once a report is ran, it says that it is 43 pages, however in actuality it is 62 pages. If they only want to print the last page, they choose to print 43 from the print dialog box, but that gives them a page in the middle of the report not the last page. Is there a way to remedy this?
Thank you,
Kaysie
A vehicle loading confirm after that what time its gated out so i want to take the time duration between finish loading and gate out, find sample table records , i want to take more than 5 hrs difference between finish loading and gate out.
tld_tripno
tld_sno
tld_activitycode
tld_location
tld_actualdate
TLM3004242015
[Code] .....
I want to take the result like this
Tld_tripno
Finish Loading
Gate Out
Date and Time difference
TLM3004242015
2015-05-11 19:58:00
2015-05-12 08:42:00
12:44:00
is it possible to use the window functions to count the number of rows in the previous 24hours from the current row.
I have a table of events like:
User, TimeStamp, etc...
I want to identify the row which is the event number 50 in the past 24 hours.
does the window functions can do this? and how?
the ROW PRECEDING etc... appear to not have enough feature to support time related function.
Stream-insight is able to create this type of query, but I have to do it directly in SQL.
Hey,
Is there somewhere in MSDE (or SQL) where you can see how many transaction are made to a sertain database or by a sertain user? At this way i could figure out witch database/user uses most (or least) recources (cpu) over a period of time.
What is the proper way to ensure when pulling date between two getdates, that you include from midnight of the first getdate to 23:59 hours in the second getdate?
WD.WRKD_WORK_DATE
and WD.WRKD_WORK_DATE between DATEADD(DD, - 11, GETDATE())
and DATEADD(DD, - 5, GETDATE())
I have Two Time fields in a table. Time(0). An "opening time" and a "closing time". They can hold any legit time.
I want to calculate in a SELECT Statement how many minutes within this range are within 9am to 5pm (which I'll convert to hours).
For example, here's an easy example:
OPEN: 9:00:00
CLOSE: 17:00:00
8 Hours/480 minutes
I could get this easy enough with a DATEDIFF function.
But what about:
OPEN: 08:00:00
CLOSE: 18:00:00
10 Hours total but only 8 of those 10 are within 9am-5pm.
Or what about:
OPEN: 10:00:00
CLOSE: 20:00:00
10 Hours total but only 7 are within 9am-5pm range.
I can calculate the total hours/minutes between the two times but not within that special range.
I have a table that contains an employee id and dates signifying time periods that those employees were working. I need to calculate anniversaries, such as 20 year, which are the sum of all periods spend working projected out to 20 years. For example,
emp_idstart_date end_date
1001 1998-01-01 2003-06-21
1002 1999-05-23 2008-03-28
1001 2004-08-19 NULL
1003 2004-10-12 2006-07-25
1004 2005-04-28 NULL
1002 2008-11-02 NULL
1003 2009-05-17 NULL
The periods in which the employees were inactive (the time period between active ranges) would push back their anniversary date, obviously. I'm only concerned with employees that are currently active (ie. the most recent record has a NULL end date). I thought about trying to use datediff to calculate the time between active periods, but I'm not sure how to go about doing it.
I am having problems getting my last revision number out when i am trying to use inner joins along with the row_number over partition
I am using 2 tables, tbl_acyear_lookup & tbl_targets
tbl_acyear_lookup columns = (pk)- academic_year_id, academic_year
looks like this:
1, 2010/2011
2, 2011/2012
3, 2012/2013
tbl_targets columns = targetID, Academic_Year_ID,Course_Mode,UK_ENROL, INT_ENROL, Notes, Revision_Number
I have one stored proc that uses the Row_number over partition that looks like this:
Select TargetID, Academic_Year_id, Course_Mode, UK_Enrol, Int_Enrol, Notes, Revision_Number from
(SELECT ROW_NUMBER() OVER (partition by [Academic_Year_id] order by [Revision_Number] DESC) as [RevNum],TargetID, Academic_Year_id, Course_Mode, Target_Year, UK_Enrol, Int_Enrol, Notes, Revision_Number
FROM tbl_targets where course_mode=@course_mode) RV where (RV.RevNum=1)
Now the next store proc needs to use the above but i need to add the Academic_year from the tbl_acyear_lookup table also add filter the target_year ='year 1'
I have a requirement to be able to calculate the transit time between international locations when I have the flight departure time and flight arrival times and departure and arrival locations.
We have a problem though, in that the datetime stored are local times, and the journeys can involve crossing the international date line in either direction, so it is possible for the arrival time to be earlier than the departure time!
To give a famous example, if you flew Concorde from London to Washington, you could depart at 10am on the 1st of June (London time) and arrive at 8.00am on 1st June (Washington time)
Even worse, you could leave Brisbane at 2.00 am on the 1st June, cross the International Date Line and arrive at San Francisco at 23:00 on 31st May!
With this query i get only the records i need, but i would like to output in this way
1 - 20
21 - 30
31 - 40
of course in the real environment the ID are not consecutive, this is just one example of data.
declare @temp table (ID int)
declare @i int = 1
while(@i<1000) begin
insert into @temp values (@i)
set @i=@i+1
end
select ID from (
select ID, row_number() over (order by ID)
as rn
from @temp
) q where (rn % 20=0) OR (rn % 20=1)
Based on the description below on average how many hours a month would it take to monitor and maintain the MSSQL Server databases?
Description of IT infrastructure.All Windows Servers and MSSQL Servers are up to date on patches and best practices.
Corporate site with 3 remote sites.
All remote sites have one DC and one MSSQL Server.
The corporate site has one MSSQL Server.
Replication is performed between the remote MSSQL databases and the corporate office MSSQL database.
There is no in-house DBA. All DBA services will have to be outsourced. I am trying to determine what is reasonable in budgeting for time involved for this service.
There is one project written in MS Access using Visual Basic for Applications (VBA) with the backend residing on these database.
The question is on average approximately how many hours a month would it take to monitor and maintain the health of the MSSQL Servers database by a MSSQL DBA. The DBA will not have to create any user reporting, queries, etc. Just maintain the existing MSSQL Servers database.
I need a Query for calculating the fiscal_week based on the input dates (start_date and end_date), though I got a query from this forum, it is not giving me exact result.
the sample is in the excel file with the attachment.
In the excel:
First tab tells you the raw_data what I am using to find the Fiscal_week
Second tab tell you the data where i found the mistake, and how I am expecting the output.
I also have attached the query I have got from this forum, query I have modified for fiscal week.