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 )
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!
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/
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. There is a section for public holidays there. We have a table for that you might not so that piece needs to be fixed.
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
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.
A client of mine is trying to upgrade their SQL Server box. They are on SQL 2000 now and the new box will be SQL 2000 as well. Before you ask, they don€™t want to go to SQL 2005 for licensing issues (i.e. cost).
The problem I€™m having is that in addition to moving the database they need to add an update field (smalldatetime) with an index. Adding the new field and index to each table is taking around 16 hours on the new box. This client is an online retail shop and they are completely dependant on SQL being up. The have given me a 2-3 hour window to make the move. So how do I get the database moved and updated in 2-3 hours?
This is what I have done so far: Argued for a longer install window. Being down for 16 hours is a no go, not even an option. Restored a copy to the new server, add the new field and index. Created a temp database on the old server and triggers that log the PK any time a change is made to the current prod db. Then use a script to pull the changed data into the new prod. This looked like it was going to work but it when it gets to the larger and more heavily used tables it takes way to long (25+ minutes per table). That shoots the 2-3 hour window.
Next plan is to move the current database to the new box. Then each night update as many of the tables as I can until it€™s complete. I estimate this will take six to eight days. They are not very excited about being down every night for eight days.
So my question to all of you is; have you had to deal with a situation like this before and if so how did you resolve it? If not do you have any ideas as to how I can get the database moved and updated while keeping my client happy? I would be most appreciative of any advice or suggestions you may have.
Hello,I need to create a column that will store hours bigger than 24. Forexample '25:00:00', '129:23:12', etc).That column will be used too, for perform calculations betweendatetime intervals: 'time'.In MySQL there is a datatype that perfect fits that necessity.Does anyone know what is the corresponding datatype in SQL Server?Thanks a lot!
I have a brand-new Toshiba laptop, running Vista Business, that I installed SQL Express onto. Prior to installation, I was sure to install all the requisite IIS components so SSRS would install.
The installation ran fine -- installed all components. The configuration ran fine. Everything that is supposed to be green shows green
But, when I go to http://localhost/ReportServer, I get:
Server Error in Application "Default Web Site/ReportServer"
HTTP Error 404.2 - Not Found Description: The page you are requesting cannot be served because of the ISAPI and CGI Restriction list settings on the Web server.
No handler mapping for this request was found. A feature may have to be installed. The Web service extension for the requested resource is not enabled on the server. The mapping for the extension points to the incorrect location. The extension was misspelled in the browser or the Web server. What you can try:
Install the feature that handles this request. For example, if you get this error for an .ASPX page, you may have to install ASP.NET via IIS setup. Verify that the Web service extension requested is enabled on the server. Open the IIS Manager and navigate to the server level. In the Features view, double-click ISAPI and CGI Restrictions to verify that the Web service extension is set to Allowed. If the extension is not in the list, click Add in the Actions pane. In the Add ISAPI and CGI Restrictions dialog box, type the path of the .dll or .exe file in the ISAPI or CGI Path box, or click Browse to navigate to the location of the file. In the Description box, type a brief description of the restriction. (Optional) Check "Allow extension path to execute" to allow the restriction to run automatically. If you do not check this option, the restriction status is Not Allowed, which is the default. You can allow the restriction later by selecting it and clicking Allow on the Actions pane. Click OK. NOTE: Make sure that this Web service extension or CGI is needed for your Web server before adding it to the list. Verify that the location of the extension is correct. Verify that the URL for the extension is spelled correctly both in the browser and the Web server. Create a tracing rule to track failed requests for this HTTP status code. For more information about creating a tracing rule for failed requests, click here. More Information... This error occurs when the necessary Web service extension is not enabled, the location or the name of the extension are misspelled or incorrectly entered.
-------------------------------------------------------------------------------- Server Version Information: Internet Information Services 7.0.
The only lead I could find when I googled this error was a reference to running appcmd to ensure that asp.net was enabled. It sure looks like it is:
Honestly... I'm out of ideas. I've been messing with this for 8 hours now, and I'm ready to fling the laptop out the window. I've completely UNinstalled SQL Server, IIS, reinstalled both, repeated the uninstall/reinstall after double-checking all files were deleted, and so on.
Does *anyone* know how to resolve this error? I checked IIS.NET and although they have a few references to it (not within the Reporting Services context) there never seems to be a definitive answer as to what the solution is.
Hi, I have a problem with working out some dates. I have a query that has a start date field and a number of days field. I know i can create another field that could provide the return date (DATEADD function) by adding the number of days to the start date. However the problem I have is that i need to discount the weekends from the return date. For example if the start date was a wednesday and the number of days was 3 the datadd sum would give a return date of saturday when in reality it should be monday. I am not sure if i am making sense but if anyone out there has any ideas it would be more than welcome. Andrew
Does anyone know how to write code to tell the amount of days between 2 dates excluding Saturday and Sunday. Datediff will tell me the total amount of days and I guess I need some logic to look at the startdate and manipulate the total after the fact.
I have a query and am trying to just return the difference between two dates but not include weekends.For instance, if I have 08/21/2006 - 08/28/2006, there are 6 weekdays. I tried this, but I am getting 7 as a result.SELECT DATEDIFF(weekday, request_start_date, request_end_date) AS days_off, request_id FROM request Any help would be greatly appreciated.
I have data coming from a telephony system that keeps track of when anemployee makes a phone call to conduct a survey and which project numberis being billed for the time the employee spends on that phone call in aMS SQL Server 2000 database (which I don't own).The data is being returned to me in a view (see DDL for w_HR_Call_Logbelow). I link to this view in MS access through ODBC to create alinked table. I have my own view in Access that converts the integernumbers for start and end date to Date/Time and inserts some otherinformation i need.This data is eventually going to be compared with data from someelectronic timesheets for purposes of comparing entered hours vs hoursactually spent on the telephone, and the people that will be viewing thedata need the total time on the telephone as wall as that total brokendown by day/evening and weekend. Getting weekend durations is easyenough (see SQL for qryTelephonyData below), but I was wondering ifanyone knew of efficient set-based methods for doing a day/eveningbreakdown of some duration given a start date and end date (with theday/evening boundary being 17:59:59)? My impression is that to do thiscorrectly (i.e., handle employees working in different time zones,adjusting for DST, and figuring out what the boundary is for switchingfrom evening back to day) will require procedural code (probably inVisual Basic or VBA).However, if there are set-based algorithms that can accomplish it inSQL, I'd like to explore those, as well. Can anyone give any pointers?Thanks.--DDL for view in MS SQL 2000 database:CREATE VIEW dbo.w_HR_Call_LogASSELECT TOP 100 PERCENT dbo.TRCUsers.WinsID, dbo.users.username ASInitials, dbo.billing.startdate, dbo.billing.startdate +dbo.billing.duration AS EndDate,dbo.billing.duration, dbo.projects.name ASPrjName, dbo.w_GetCallTrackProject6ID(dbo.projects.descript ion) AS ProjID6,dbo.w_GetCallTrackProject10ID(dbo.projects.descrip tion) AS ProjID10,dbo.billing.interactionidFROM dbo.projects INNER JOINdbo.projectsphone INNER JOINdbo.users INNER JOINdbo.TRCUsers ON dbo.users.userid =dbo.TRCUsers.UserID INNER JOINdbo.billing ON dbo.users.userid =dbo.billing.userid ON dbo.projectsphone.projectid =dbo.billing.projectid ONdbo.projects.projectid = dbo.projectsphone.projectidWHERE (dbo.billing.userid 0)ORDER BY dbo.billing.startdateI don't have acess to the tables, but the fields in the view comethrough as the following data types:WinsID - varchar(10)Initials - varchar(30)startdate - long integer (seconds since 1970-01-01 00:00:00)enddate - long integer (seconds since 1970-01-01 00:00:00)duration - long integer (enddate - startdate)ProjID10 - varchar(15)interactionid - varchar(255) (the identifier for this phone call)MS Access SQL statement for qryTelephonyData (based on the view,w_HR_Call_Log):SELECT dbo_w_HR_Call_Log.WinsID, dbo_w_HR_Call_Log.ProjID10,FORMAT(CDATE(DATEADD('s',startdate-(5*60*60),'01-01-197000:00:00')),"yyyy-mm-dd") AS HoursDate,CDATE(DATEADD('s',startdate-(5*60*60),'01-01-1970 00:00:00')) ASStartDT,CDATE(DATEADD('s',enddate-(5*60*60),'01-01-1970 00:00:00')) AS EndDT,DatePart('w',[StartDT]) AS StartDTDayOfWeek, Duration,IIf(StartDTDayOfWeek=1 Or StartDTDayOfWeek=7,Duration,0) ASWeekendSeconds,FROM dbo_w_HR_Call_LogWHERE WinsID<>'0'
We have a few Servers that have space issues that need full DB Backups every day. As a result, we have to carefully watch how many DB Backup files we keep on the Server. On the Maintenance Plan, if I set the 'Delete files older than:' to '1' 'Day', will this delete anything over 24 hours old? What I have noticed, is that it tends to delete on Tuesday anything that was created on Sunday, but not Monday. Therefore, I am assuming that instead of '1 Day' being 24 hours, it is anything less than 47 hours and 59 minutes...
Is my assumption correct or do I need to look at something else. Thanks!
SELECT sysjobs.name, sysjobservers.last_run_outcome, CONVERT(varchar(8), sysjobservers.last_run_date, 112) AS LastRunDate, CONVERT(varchar(8), sysjobservers.last_run_time, 8) AS LastRunTime, GETDATE() AS CurrentDateTime FROM sysjobs LEFT OUTER JOIN sysjobservers ON sysjobs.job_id = sysjobservers.job_id WHERE (sysjobservers.last_run_outcome = 0) AND (CONVERT(varchar(8), sysjobservers.last_run_date, 112) >= DATEADD(day, - 1, GETDATE()))
I am wanting a similar solution, I need to convert an "int" to a time format, when I use this where ">= DATEADD(minute, - 720, GETDATE())" for the last_run_time I get this error:
Syntax error converting datetime from character string.
I have a job that runs between the hours of 10 PM and 9 AM. It launches a controller stored procedure that will call other stored procedure until the entire process is done.I would like the controlling stored procedure to only call the steps between the hours of 10PM and 9AM also.. So at 8:59 AM it will start the next step, but at 9:00 AM it will exit.
I use SQL 2K with an Access 2000 project as front end. I have four date fields in a table. The first one should contain a date (eg 20th of February), the second one a starting hour (eg 8:00h), the third one the finishing hour (eg 10:00h) and the fourth should calculate the difference between the second and third column. So I created four datetime fields. Storing a date in the first column is no problem, but storing only hours doesn't seem to do what I want. When I enter 8:00 from within the Access, it is stored 1/1/1900 8:00 in SQL Server. How can I make sure that the hours are saved with the date of the second column, so either as 8:00 without further information or as eg 20-2-2004 8:00?
I am querying a database that has hours worked listed by pay rate. I want to return sum of hours per pay rates I have been given but hours are stored in multiple columns. So If I run the below query for one worker it returns.
Select Sum(Hours_1) As Hours,Bill_rate_1,Sum(Hours_2) as Hours_2,Bill_rate_2--,Hours_3,Bill_rate_3 From Valid_Timesheets Where (Department = '938' and tax_period = 3 and tax_year = 2015 AND PERSONNEL_REF = '991A001198') and ((Bill_Rate_1 = £10.26 or Bill_Rate_1 = £9.67 or Bill_Rate_1 = £8.27 or
Hi,I have the following query:SELECT p.employee_code, p.employee_name,CONVERT(VARCHAR(12),t.tran_date,101) AS TranDate,CONVERT(VARCHAR(12),t.post_date,101) AS PostDate, SUM(tobill_hrs) ASTotalHoursFROM tat_time t, hbm_persnl pWHERE t.tk_empl_uno = p.empl_uno AND t.tran_date BETWEEN '2005-03-01'AND '2005-03-31' AND p.employee_code IN ('0340')GROUP BY p.employee_code, p.employee_name, t.tran_date, t.post_dateORDER BY p.employee_codeI would like to sum the tobill_hrs by week (Monday-Sunday) instead ofby day. For instance the user will put in a date range (a from dateand to date).Is this possible to do?Thanks!
I am creating a report that has call duration and if i run it over amulti-day span the duration is limited to 24 hours and starts over atzero. is there a way to not limit hours?I am usign the following in my select statement:convert(varchar(12), dateadd(s,SUM(ISNULL(Calls.OutgoingCallDuration,0)), '19900101'), 108) AS[Outgoing Call Duration]
Is there a function in SQL Server 2005 that can add GMT deviation hours to the existing date. I have a data field with the GMT_Deviation_Hours listed in numeric format e.g. -1, 12 etc. I have another date field to whom i wish to deviate the datetime according to the GMT_Deviation hours.
I have to compare 1 hour between two days , example:
I have this datetime value (BitacoraProcesoUsuario.FechaInicioProceso) in 108 format : 23:35:22
and I make this query:
Select TOP 100 * from BitacoraProcesoUsuario INNER JOIN Turno ON convert(varchar,BitacoraProcesoUsuario.FechaInicioProceso,108) > = '24:00:00' AND convert(varchar,BitacoraProcesoUsuario.FechaInicioProceso,108) <= '06:29:00'
and returns nothing........ this because 23:35:22 is gtreater than 06:29:00' value, my question is how can I compare this hours in this format.
I would like to know the best method or the simplest method to do the following. Take a datetime value and subtract a given number of hours. I am taking a UTC time and subtracting a given number or hours in a query. I seem to be getting mixed results.
What is the best data type to use when tracking hours, for example; 2.30 < this is stored in DB (two hours and thirty minutes) 5.15 < this is stored in DB (5 hours and fifteen minutes)
MS SQLJust to confirm, I need to store the number of hours a user has spent doing something, am I correct using the smalldatetime field for the MS SQL database?I will eventually need to be able to calculate totals using SQL Reporting Services so it's important that the hours add up correctly (to 60 minutes, not 100).ASP.NETI'm trying to find the correct sort of formatting to use? Short time stores the time as 9:50AM, which isn't what i'm after.I need to store the hours as in 1 hour, or 01:00:00 (1 hour, 0 minutes, 0 seconds) or just 01:00 for 1 hour. I do need minutes but seconds are not required.I can't find the right formatting for this, would Long time be more suited?
I have a SQL 7.0 backend for a new DB of mine. This is the first time I have developed a SQL backend from scratch, so the problem may be my design. The problem may lie with my Access frontend (I have tried both 97 and 2k), but I am inclined to think not considering the behavior. On only one table (the master table no less), I cannot add records during business hours. I can add records to other tables and edit records on this table though. This troublesome table has a field indexed as unique that is not the PK (don't know if that has any bearing, but it is the only table in this DB that has that property). The adding records problem appears to be network traffic related because sometimes records can be added at lunchtime and definitely after hours. I currently only have 3 connected users. I had these problems with only one connected user, so the DB itself is not being strained by traffic. I often have more traffic on the DB after hours to avoid the problems. I have worked on the connection and network traffic angle for awhile, when I stumbled upon the bizarre part. If I add a record via a stored procedure and run this SP on the SQL query analyzer on the server (no network is involved) it is still slow to add the record (30 minutes or more). Needless to say this would equate to an failure on the front end. The same query will run in 0-3 seconds after hours! Does anyone have any clue how this could happen? I am stumped. I do have a budget for a consultant if I need one, but I need to find someone who has a chance to fix this problem. If you don't know the answer but can refer someone in the DFW area, I am in your debt.
hello this is my store procedure and i want to set the hour of the first date to be 00:00:00 and the hour of the second date to be 23:59:59 but i don't know how to do this,so please HELP me
CREATE PROCEDURE dbo.Proba @date1 smalldatetime , @date2 smalldatetime, @s float output AS Select @s=sum(Pay) from RKO where Dates between @date1 AND @date2 GO
