Calculating Length Of Service Categories Based On Start Dates
May 1, 2007
Ok, so I've been struggling with the logic behind this one for a while, so I thought I'd ask you guys for some ideas :)
Basically, I have the following table structure
Employee(employee_number, continuous_start_date, ...)
The problem lies in working out a summary of service categories (0-6months, 7-12months, 13-24, 25+).
I can work out the length of service in months with the following code
SELECT DateDiff(mm, continuous_start_date, GetDate()) AS 'Service in months'
FROM employee
So the first stage is to summarise the length of service into groups as mentioned above.
Then the final stage is working out how many people are in each group!
Hope I have given enough information - and please do not post a full solution - just some hints on how to get the desired result ;)
Thanks later, and in advance :p
-GeorgeV
View 14 Replies
ADVERTISEMENT
Aug 19, 2014
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.
View 4 Replies
View Related
Nov 13, 2015
I am working with a data set containing several years' of monetary values. I have entries for past dates and the associated values, and I also have entries for future dates. I need to populate the values of the future date records with the values from the same date the previous year. Is there any way this can be done in Power Pivot?
View 6 Replies
View Related
Mar 7, 2007
I'm trying to find a solution to for a report i am building and was hoping that some of you will share your expertise with me. Basically, I need to work out how long an Incident Ticket was suspended (ie it's status is "on hold").
The data is stored something similar to the following...
Ticket Date Status
------ ---- ------
333 14/03/2005 10:24:19 "to on hold"
333 14/03/2005 15:23:01 "from on hold"
334 14/03/2005 11:14:11 "to on hold"
334 14/03/2005 16:26:15 "from on hold"
335 15/03/2005 10:10:15 "to on hold"
335 15/03/2005 11:15:35 "from on hold"
335 15/03/2005 13:26:10 "to on hold"
335 15/03/2005 14:30:59 "from on hold"
335 16/03/2005 14:00:05 "to on hold"
335 16/03/2005 16:45:15 "from on hold"
336 16/03/2005 10:10:15 "to on hold"
336 16/03/2005 12:45:12 "from on hold"
This is the result i'd like to see....
Ticket Start Hold End Hold Time Suspended
------ ---------- -------- --------------
333 14/03/2005 10:24:19 14/03/2005 15:23:01 datediff(...
334 14/03/2005 11:14:11 14/03/2005 16:26:15 datediff(...
335 15/03/2005 10:10:15 15/03/2005 11:15:35 datediff(...
335 15/03/2005 13:26:10 15/03/2005 14:30:59 datediff(...
335 16/03/2005 14:00:05 16/03/2005 16:45:15 datediff(...
336 16/03/2005 10:10:15 16/03/2005 12:45:12 datediff(...
Has anybody done something along the same lines? If so, could you point me in the right direction?
Thanks in anticipation
Jon
View 6 Replies
View Related
Aug 26, 2005
Hi,
I'm fairly new to MS SQL, but I'm trying to query the time elapse between two dates. Example below...
Select
dbo.res.book_date,
dbo.res.arrive_date,
Cast (dbo.translog.systemdate as datetime)
From
dbo.res
Where
dbo.res.resid > '500'
The third line is where I need help with a statement. I need the elapse time. Ex. if the book date is 20050820100000 and the arrive date is 20050820120000, I would like the third Select statement to return 2:00 hours or something similar. Thanks.
View 11 Replies
View Related
May 16, 2004
I have a table in my sql server that calculates renewal dates for me, that date is based on the final suit date. The table has the ssn, and the FinalSuitDate its in a one to many relationship with the employeetable.
the Finalsuit table is suppose to calculate the renewal dates(which I'm trying to do in a query) my original expression in access was using dateserial FirstRenewal: DateSerial(Year([FINALSUITDONE])+2,Month([FINALSUITDONE])+1,Day([FINALSUITDONE])=30), but sql does not recognize that.
For Example
If the Finalsuit is 12/01/2000
then the renewal would be 12/31/2002
the renewal are to be done on the last day of the month and two years from the finalsuit date. Problems is I'm having problems doing this in sql servers query?
Can someone out there help please
View 8 Replies
View Related
Nov 1, 2007
I have a project where I need to populate a table with a Begin and an end date based on another field with in the table. my example is that the other field will have a value of "monthly" I want then a record to be entered on the table with a begin date of November 1 and the en date as November 30th. I want this record to get populated only on business days. my thinking is a stored procedure that runs daily and only creates a record at a time. i am thinking if the user selects weekly the dates populated would be november 1 = begin date and Novemeber 2nd = end date
i want to fill out a current month at a time as if the account closes i will not need the extra cases.
thanks
Don S
View 5 Replies
View Related
Dec 9, 2011
I have data in which i need to calculate employees working hours for a day...
name time in_out
manisha 2011-01-01 9:30:00.000am 1
manisha 2011-01-01 10:30:00.000 0
manisha 2011-01-01 10:45:00.000 1
manisha 2011-01-02 1:00:00.000am 0
How can i calculate time in that two dates as 1 is for entry an 0 is for exit..
View 2 Replies
View Related
Jul 12, 2000
Hello,
Can anyone out there tell me if there's a simple way to calculate the number of week days between two dates in TSQL? Need it to calc. average turnaround times, excluding weekends. Can do it v. easily in VB, but gets a little more tricky in TSQL as there's no way to return the number of Sundays and Saturdays between the two dates. Any help much appreciated !
Jon Reade
Sql Server DBA
NEC Technologies (UK) Ltd.
View 2 Replies
View Related
Jul 12, 2000
Hello,
Can anyone out there tell me if there's a simple way to calculate the number of week days between two dates in TSQL? Need it to calc. average turnaround times, excluding weekends. Can do it v. easily in VB, but gets a little more tricky in TSQL as there's no way to return the number of Sundays and Saturdays between the two dates. Any help much appreciated !
Jon Reade
Sql Server DBA
NEC Technologies (UK) Ltd.
View 1 Replies
View Related
Jul 20, 2007
Hi Engine room.
I want to automatically populate an alternative date column with hijri (Arabic) dates. So I will have two date columns in my datawarehouse table, one is for gregorian dates, the other for the equivalent in Hijri - a bilingual date system if you like. Now I guess I could do it in the SSI ETL tool and use Julian Dates, but the problem is storing the resulting value. - what data type should I use for hijri dates?
The dates can be input by the user in either format, which means the alternate date has to be populated.
Eventually I hope to make a bilingual system, I don't understand the user-defined types or how to define them, but I suspect they could be used in someway to help. Why am I doing this? - well eventually I hope to populate a cube for analysis and deliver a dual-language-dashboard using the new CTP3 stuff.
Any suggestions on how I can make this dual-date system? - with a view to making a bilingual system eventually.
adv(thnx)ance..Mark.
View 1 Replies
View Related
May 22, 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. 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]...
View 9 Replies
View Related
Oct 10, 2007
Hi All,
We seem to be being plagued by the error below by our SQL Server agent. This happens almost everytime we restart the server that has been running for a day or two.
Our SQL Server Agent uses a none expiring domain credential. I understand that this problem only happens when the profile being used by the SQL Servr Agent has changed (password change). What puzzles me is that the login is A ok and no changes has been made to it's password.
We always resolve this problem by changing the login used in the SQL Server Agent to local and after that, returning it back to it's original domain login. Unfortunately, we cant always do this everytime something goes wrong.
Can anyone please help us shed a light on this? We're using SQL2k with SP3a. Thanks!
Error:
An error 1069 - )The service did not start due to logon failure) occurred while performing this service operation on the SQLServerAgent service.
Regards,
Joseph
View 29 Replies
View Related
May 20, 2008
I have two tables and they have a foreign key relationship. It will be a 1 to Many realationship.
Table1- the primary key table has the following columns
ContentID - int identity field
DateAdded- datetime
Table2 - The foreign key table, has the following relevant fields
FK - int foreign key
version - int
When an initial record is added there is a record added to each table. The ContentID in Table1 will match the FK field in Table2. Then a user will be able to "edit" the record in Table2, but instead of writing over the record that is already there, a new record will be added to Table2. I would like to calculate the version field where each time a record is added for the particular ContentID/FK it adds 1 to the last version that was added to that particular ContentID/FK.
Does that make sense. I am trying to implement some type of revision tracking for my CMS that I am building and this is the only way I can come up with because my client is worried that somebody will go in and make incorrect changes to their site and they want to be able to roll it back to a previous version.
I would appreciate any ideas.
Thanks,
laura
View 5 Replies
View Related
Aug 2, 2007
Hey all! First post-- I apologize for the newbness of the question, but I'm having an issue with a basic query... here goes:
I want to calculate the percentages of statuses found from two tables. Let say T1 is my list of statuses:
T1
Passed
Failed
Not Completed
and T2 is the table where I derive my total # of statues for each id and how many in each status.
T2
ID, Desc, Status , numtimesinstatus
1, Test1, Failed, 3
1, Test1, Passed, 1
3, Blah, Failed, 5
3, Blah, Not Completed, 1
What I'd like to do is get the percentages for each available status in T1, from T2-- I can do this; however, I'm having trouble in the cases where a particular ID hasn't been in one of the status (in other words, calculating a 0%). What I'd like to get from these two tables is:
Result
ID, Desc, Status, %age
1, Test1, Failed, 75.0
1, Test1, Passed, 25.0
1, Test1, Not Completed, 0.0
3, Blah, Failed, 83.3
3, Blah, Passed, 16.67
3, Blah, Not Completed, 0.0
Can anyone shed some light? Thanks!
View 5 Replies
View Related
Sep 14, 2013
I have created an SQL report that returns order data from a shop DB.
How can you get the output to total the number of order instances and the total value of those orders, both per user_id? Is this possible?
View 3 Replies
View Related
Sep 10, 2007
If I return the Average, Minimum, and Maximum values for the counter Physical Disk: Avg. Disk Queue Length, and those values are 10, 0, 87 respectively, which value do I use to compute the Avg. Disk Queue Length for a 4 disk array(RAID 10): Average, Minimum, or Maximum? The disk(lun) is on a SAN.
View 1 Replies
View Related
Jul 21, 2001
Does anyone know of a way to calculate the date 'x' number of business days after another date?
View 2 Replies
View Related
Oct 2, 2014
I have two tables that look like this (below). One tells me the name of my product, the Amazon Category it is in & the amount that I want to sell it for. The other tells me the Category & the fee for that category. So far so good. Though it gets tricky in the sense that some categories have two tiers. So in Electronics, the fee for $0.00 - $100.00 is 15%. But from $100 and up it is 8%.
Since it has two columns & both of the new columns pertain to the fee of my product, I can't figure out how to use both at once. For my $599.99 example it would be ($100 * 0.15) + ($499.99 * 0.08) = $55.00. Would I pivot the data? If not, how would I group it to be considered together?
Category Example
IDAmazonCategoryIDAmazonCategoryNameFeePercentageStartPriceEndPrice
1apsAllDepartments0.150.000.00
2instant-videoAmazonInstantVideo0.000.000.00
3appliancesAppliances0.150.000.00
Product Example
1Product1Electronics9.99
2Product3Electronics99.99
3Product2Electronics599.99
Raw SQL
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE #Amzn_Category_FeeStructure(
[ID] [int] IDENTITY(1,1) NOT NULL,
[Code] ....
I use Microsoft SQL 2008
View 2 Replies
View Related
Jun 14, 2006
I need to be able to take the latitude and logitude of two locations and compare then to determine the number of miles between each point. It doesn't need to account for elevation, but assumes a flat plane with lat and long.
Does anyone have any algorithms in T-SQL to do this?
View 5 Replies
View Related
Oct 10, 2005
Hey all.
I'm looking around for a solution on how to pull records back based on legnth.
So, if field A is more then 2 characters, return that row.
Thanks
Caden
View 2 Replies
View Related
Nov 7, 2004
hi may i know how do i load a report based on the entered value for start and end date?
I understand that i have to create parameters and dataset but how to make use of them?
View 1 Replies
View Related
Oct 8, 2004
Hi, I'm a bit lost on this SPROC, hoping somebody can jumpstart my brain a bit please.
What I have is tbl_Classes, with a row for every class that's been taught at a school. Each class was taught on certain dates, stored in tbl_Class_Dates. tbl_Class_Dates stores EVERY day the class met, not just the start and end dates. However, in this one case, I want to list class history only showing start and end date... something like:
SELECT Class_ID,
MIN(Class_Date) AS 'Start_Date',
MAX(Class_Date) AS 'End_Date'
FROM tbl_Class_Dates
WHERE ...
GROUP BY Class_ID, Class_Date
HAVING Class_Date > @SomeCutoffDate
Problem is, when I run the above format, it still gives me EVERY class row, and the MAX and MIN functions don't seem to work...
I think I'm on the wrong track here. Incidentally, the old PROC is using a CURSOR and looping through, getting the start and end date and storing it all in a temp table. I'm hoping to improve on that efficiency.
Thanks very much for any help, or let me know if I can clarify better. Cheers!
- james
View 9 Replies
View Related
May 15, 2015
I need to split a field based off it's length. Initial thought was to take Left(field, characters), and Right(field, characters), but now I see I am duplicating the data as I am capturing too much of the data in. However, what I actually need to achieve is in field1 capture the left 1000 characters, and in field 2 take from character 1001 to the end. How can I update my syntax so that field2 will ONLY capture from character 1001 to end?
field1 = COALESCE(CASE WHEN DATALENGTH(exitinterviewnotes) > 998 THEN LEFT(CAST(exitinterviewnotes AS VARCHAR(MAX)), 1000) ELSE exitinterviewnotes END,''),
field2 = COALESCE(CASE WHEN DATALENGTH(exitinterviewnotes) > 998 THEN RIGHT(CAST(exitinterviewnotes AS VARCHAR(MAX)), 1000) END,'')
View 5 Replies
View Related
May 17, 2007
I have two parameters in my report (StartDate and EndDate). I want to default these parameters to the previous month.
For example... If today is 5/17/2007, I want StartDate to be 4/1/2007 and EndDate to be 4/30/2007. If today would be January 30th 2007, I would want StartDate to be 12/1/2006 and EndDate to be 12/31/2006.
How can I do this?
View 2 Replies
View Related
Jul 12, 2006
Hi
I want to create a table that has a datetime column, data type=datetime and I want the date to start form 01/01/2004 00:00:00 to 01/01/2008 00:00:00 the increment will be by minute like
01/01/2004 00:01:00
01/01/2004 00:02:00
01/01/2004 00:03:00 and so on up to 01/01/2008 00:00:00
I will use this time dimension in bussiness objects.
please provide me with the SQL sript to do this.
View 3 Replies
View Related
May 28, 2015
I need getting the HH:MM:SS format when we do datepart.
For example, am using a variable to capture the time taken to execute the package using Start and End time stamps captured.
I would like to get the HH:MM:SS format of the datediff.
View 4 Replies
View Related
Jan 7, 2014
I have an SQL code below which removes weekends and non working days when calculating days difference between two dates:
ce.enquiry_time represents when the enquiry was logged
(DATEDIFF(dd, ce.enquiry_time, getdate()) + 1)
-(DATEDIFF(wk, ce.enquiry_time, getdate()) * 2)
-(CASE WHEN DATENAME(dw, ce.enquiry_time) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, getdate()) = 'Saturday' THEN 1 ELSE 0 END)
-(SELECT COUNT(*) FROM nonworking_day WHERE nonworking_day.nonworking_date >= ce.enquiry_time AND nonworking_day.nonworking_date < dateadd(dd,datediff(dd,0,getdate()),1))
It works but I don't understand how it works it out. I am having issues understanding each coloured piece of code and how it works together.
View 1 Replies
View Related
Mar 16, 2015
I've SSRS sales report to which I need to pass the dates for previous month's start date and end date which I am able to pass using below code. However, since the sales report has data from the past year(2014) I need to pass the dates for last year as well. The below code gives StartDate1 as 2015-02-01 and EndDate1 as 2015-02-28. I need to get the dates for past year like 2014-02-01 as StartDate2 and 2014-02-28 as EndDate2
SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000201', GETDATE()), '19000101') AS StartDate1,
DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '18991231') AS EndDate1
View 1 Replies
View Related
May 4, 2007
Has anyone written or come across a routine that validates start and enddate in a slow changing dimension? (eg verifies there is no overlap in any of the records).
thanks in advance
View 3 Replies
View Related
Jan 18, 2012
I have a table Category whose structure is as follows:
id
name
parentid
So i have number of nodes from the root of a specific category. How to get all the sub categories(ie nodes) for all parent categories?
View 14 Replies
View Related
Aug 9, 2007
I have an application which has a database table called Category. Its very simple, with fields id and categoryname.
My client now wants functionality for sub categories. Whats the best way to do this. My initial thought was to keep the same database table but have a 3rd column called ParentID which contains the ID of the parent category.
Is this the best way? Or something else.
Thanks
View 2 Replies
View Related
Mar 29, 2007
Hi all,
I am trying to use the custom code in the report but I don't think I am understanding how this is being used.
I have a function to get starting months for a report parameter.
The function is below:-
--------------------------------------------------------------
Shared Function GetStartingMonths() as String
dim strDefault as string
dim CurrentMonth as String
Dim SqlString as String
'strDefault = Month(Now) & "/1/" & Year(Now)
CurrentMonth = "5/1/2002"
Do While CDate(CurrentMonth) <= Now
SqlString = SqlString + "Select " & CurrentMonth & " as value, " & MonthName(Month(CurrentMonth)) & " " & Year(CurrentMonth) & " as MonthYear"
CurrentMonth = dateadd("m",1,CDate(CurrentMonth))
if Cdate(CurrentMonth) = Now then
Exit Do
else
sqlString = SqlString & " Union "
end if
Loop
return SqlString
End Function
---------------------------------------------------------------
what i am trying to do here, and hopefully produce a sql string that would fill my dataset of dates and their representation.
In the dataset, I had put the following expression
=Code.GetStartingMonths()
However, I can't seem to get the parameter to display the dates. shows up as disabled in my report.
Am I doing something wrong here or is there a better way to doing this ?
Additionally, I was wondering whether there is a better SQL code that would achieve the same thing I am doing ?
thanks !
Bernard Ong
View 15 Replies
View Related