T-SQL (SS2K8) :: Finding Gaps Within Date Ranges
Sep 13, 2013
I have a group of date ranges and wanted to identify all of the date gaps within the ranges, outputting the dates as another date range dataset.
Example dataset SQL below:
CREATE TABLE #test (daterow int identity, obj_id int, datestart DATETIME, dateend DATETIME)
INSERT INTO #test
SELECT 1, '20130428', '20130523'
UNION
SELECT 1, '20130526', '20130823'
[Code] ....
I would expect a dataset to be returned consisting of:
1, 24/05/2013, 25/05/2013
1, 24/08/2013, 25/08/2013
2, 16/05/2013, 24/05/2013
I have found a lot of examples of problems where I have just a single date column, and then I find the gaps in between that, but I'm having difficulty finding examples where it works with start and end date columns...
View 9 Replies
ADVERTISEMENT
Mar 25, 2014
I'm trying to find gaps in times in a table of sessions where the session endings aren't sequential. That is, session 1 can start at 10:00 and finish at 10:30, while session 2 started at 10:05 and finished at 10:45, and session 3 started at 10:06 and finished at 10:20. Only the starting times are in order; the ending times can be anywhere after that.Here's a bunch of sample data:
CREATE TABLE #SessionTest(SessionId int,Logindatetime datetime, Logoutdatetime datetime)
INSERT INTO #SessionTest
SELECT '1073675','Mar 3 2014 1:53PM','Mar 3 2014 1:53PM' UNION ALL
SELECT '1073676','Mar 3 2014 2:26PM','Mar 3 2014 3:51PM' UNION ALL
SELECT '1073677','Mar 3 2014 2:29PM','Mar 3 2014 3:54PM' UNION ALL
SELECT '1073678','Mar 3 2014 2:29PM','Mar 3 2014 5:47PM' UNION ALL
SELECT '1073679','Mar 3 2014 2:30PM','Mar 3 2014 3:37PM' UNION ALL
[code]....
View 6 Replies
View Related
Dec 10, 2014
See sample data below. I want hourly breakdown for the last X years, the month and day will be the same for each year.
SELECT '2013-12-10 04:00:00.000' as dt, 220.50 as amt UNION ALL
SELECT '2013-12-10 06:00:00.000' as dt, 24.50 as amt UNION ALL
SELECT '2013-12-10 07:00:00.000' as dt, 527.50 as amt UNION ALL
SELECT '2013-12-10 08:00:00.000' as dt, 28.50 as amt UNION ALL
SELECT '2013-12-10 11:00:00.000' as dt, 25.50 as amt UNION ALL
[Code] .....
-- expected result
SELECT '2013-12-10 00:00:00.000' AS dt, NULL AS Amt UNION ALL
SELECT '2013-12-10 01:00:00.000', NULL AS Amt UNION ALL
SELECT '2013-12-10 02:00:00.000', NULL AS Amt UNION ALL
SELECT '2013-12-10 03:00:00.000', NULL AS Amt UNION ALL
SELECT '2013-12-10 04:00:00.000', 220.50 AS Amt UNION ALL
[code].....
View 9 Replies
View Related
Mar 20, 2015
I am trying to find a beginning date from multiple date ranges, for example:
RowNumberidBegin dtEnd Dt
107933192014-09-022015-06-30
207933192013-09-032014-09-01
307933192012-09-042013-09-02
407933192011-09-062012-09-03
For this id: 0793319, my beginning date is 2011-09-06
108203492014-09-022015-06-30
208203492013-09-032014-09-01
308203492012-09-042013-09-02
408203492011-12-122012-07-03--not a continuous date range
For this id: 0793319, my beginning date is 2012-09-04
108203492014-09-022015-06-30
For this id: 0820349, my beginning date is 2014-09-02
To find continuous date, you look at the beginning date in row 1 and end date in row 2, then if no break in dates, row 2 beginning date to row 3 end date, if no break continue until last date There could multiple dates up to 12 which I have to check for "no break" in dates, if break, display beginning date of last continuous date.
View 9 Replies
View Related
Jan 14, 2014
I have a table of employee assignments that I'm narrowing down to a specific group. Employees have multiple assignments (job positions essentially) and each has start and end dates. I need to identify the date range(s) that are covered by the assignments in my data set. Here's an example of data for one person ID. NULL in the end_date just means it is an active assignment. The pos_id column isn't necessary, but it define the data I'm looking at. The end result won't use it.
IDCOMPANYPOS_IDSTART_DATEEND_DATE
999119/2/20119/9/2012
999119/10/20129/10/2012
999119/11/20129/11/2012
999119/12/20126/2/2013
999116/3/20136/30/2013
[Code] ....
In this case I want results to say that ID 999 has a range from 9/2/2011 to NULL. There are no gaps in the date range. Or to say it differently, there's always an assignment starting the next day after an end_date. Or an assignment that overlaps the end and beginning of another assignment.
Here's another example where there is a gap in the ranges.
IDCOMPANYPOS_IDSTART_DATEEND_DATE
333112011-09-022012-08-31
333112012-09-012012-09-10
333112012-09-112012-09-11
333112012-09-122013-01-06
333112013-09-01NULL
There would be 2 result rows for this with a range from 2011-09-02 to 2013-01-06 and a second for 2013-09-01 to NULL.
The end result would be to have a row per date range for every ID. I've written a script that will find the ranges but it is a painful RBAR solution that takes forever to run. Every different path I've gone down trying to solve it ends in evaluating row by row grouped by the Person ID. The assignments are not always continuous, so I can't use a MAX and MIN and there may be 1 or more gaps in the dates to account for.
View 9 Replies
View Related
Dec 13, 2004
hello, i have quite a challenge on my hands here and would appreciate any help. :confused:
I have a table variable that stores integer ranges representing times of the day:
select * from @reservations
room date | starttime | endtime
1 2004-12-11 0 1440 (represents an entire day in minutes)
2 2004-12-12 420 1020
3 2004-12-14 200 600
4 2004-12-15 0 200
4 2004-12-15 500 1000
I need to be able to return the minutes that are open for each room. The @reservations table shows me the times that are blocked.
I'd like to analyze each row and return an integer range representing gaps in the day, where 0-1440 represents an entire day.
Based on the @reservations table above, I'd like to write something that returns:
room date starttime endtime
2 2004-12-12 0 420
2 2004-12-12 1020 1440
3 2004-12-14 0 200
3 2004-12-14 600 1440
4 2004-12-15 200 500
4 2004-12-15 1000 1440
This result represents the times in minutes that are available.
I have no clue how to do this without using a numbers table and checking each minute in each day for each row in the table. Id like to not do that because of sheer performance reasons. There is a possiblity that I will have hundreds of rows in the @reservations table.
I was hoping someone could provide some insight as to how to approach this. Thank you ahead of time! :)
View 3 Replies
View Related
Mar 25, 2014
I'm trying to write some T-SQL to return the previous even numbered month and appropriate year from given date.
Examples given:
03-25-2014 should return 02-xx-2014
01-01-2014 should return 12-xx-2013
View 2 Replies
View Related
Mar 17, 2014
This seems simple enough but for some reason, my brain isn't working.
I have a lookup table:
Table A: basically dates every 30 days
1/1/2014
2/3/2014
3/3/2014
4/3/2014
I have Table b that has records and dates created assocated with each record
I want all records that fall between the 1st 30 days to have an additional column that indicates 30
union
records with additional column indicating 60 days that fall between the 30 and 60 day
union
records with additional column indicating 90days that fall between the 60 and 90 day mark.
Is there an easy way to do this?
View 6 Replies
View Related
Feb 27, 2008
Hi all,
I have two tables - Planning and Appointments:
Planning - contains a list of planned items. Used to define boundaries for a work day and defines based on type what can be done for each item.
Id,
TypeId - the type of the planned items
BeginTime DateTime - begin date and time of the planned item
EndTime DateTime - end date and time for the planned item
In the Planning table we can have as many records per day as we need:
1, First Meeting, 1 Jan 2008 09:00, 1 Jan 2008 11:00
2, First Meeting, 1 Jan 2008 11:00, 1 Jan 2008 12:00
3, First Meeting, 1 Jan 2008 13:00, 1 Jan 2008 15:00
4, First Meeting, 1 Jan 2008 15:00, 1 Jan 2008 18:00
Appointments - contanis a list with appointments
Id,
BeginTime DateTime
EndTime DateTime
1, 1 Jan 2008 09:00, 1 Jan 2008 09:30
2, 1 Jan 2008 10:00, 1 Jan 2008 11:00
3, 1 Jan 2008 11:00, 1 Jan 2008 11:30
4, 1 Jan 2008 14:00, 1 Jan 2008 15:30
What is needed?
What I need is to a find a way to compare the planned items with the appointments and to return all the periods for which a planned time exists:
Free planned time:
1, 1 Jan 2008 09:30, 1 Jan 2008 10:00
2, 1 Jan 2008 11:30, 1 Jan 2008 12:00
3, 1 Jan 2008 13:00, 1 Jan 2008 14:00
4, 1 Jan 2008 15:30, 1 Jan 2008 18:00
So, having two multitudes of periods,where the one specifies the planning templates and the other real used time, I need to find all the periods which can be used for another appointments.
I've tried several aproaches, but I always faced performance problems.
Thanks in advance.
View 1 Replies
View Related
Feb 22, 2008
Hi all,
I have two tables - Planning and Appointments:
Planning - contains a list of planned items. Used to define boundaries for a work day and defines based on type what can be done for each item.
Id,
TypeId - the type of the planned items
BeginTime DateTime - begin date and time of the planned item
EndTime DateTime - end date and time for the planned item
In the Planning table we can have as many records per day as we need:
1, First Meeting, 1 Jan 2008 09:00, 1 Jan 2008 11:00
2, First Meeting, 1 Jan 2008 11:00, 1 Jan 2008 12:00
3, First Meeting, 1 Jan 2008 13:00, 1 Jan 2008 15:00
4, First Meeting, 1 Jan 2008 15:00, 1 Jan 2008 18:00
Appointments - contanis a list with appointments
Id,
BeginTime DateTime
EndTime DateTime
1, 1 Jan 2008 09:00, 1 Jan 2008 09:30
2, 1 Jan 2008 10:00, 1 Jan 2008 11:00
3, 1 Jan 2008 11:00, 1 Jan 2008 11:30
4, 1 Jan 2008 14:00, 1 Jan 2008 15:30
What is needed?
What I need is to a find a way to compare the planned items with the appointments and to return all the periods for which a planned time exists:
Free planned time:
1, 1 Jan 2008 09:30, 1 Jan 2008 10:002, 1 Jan 2008 11:30, 1 Jan 2008 12:00
3, 1 Jan 2008 13:00, 1 Jan 2008 14:00
4, 1 Jan 2008 15:30, 1 Jan 2008 18:00
So, having two multitudes of periods,where the one specifies the planning templates and the other real used time, I need to find all the periods which can be used for another appointments.
I've tried several aproaches, but I always faced performance problems.
Thanks in advance.
View 1 Replies
View Related
Aug 26, 2015
currently I am facing a complex escenario related with gaps and sequences, but I was trying with diferent cases but I did not get the correct results, I am sure about the use of windows functions. I have a table with the information grouped by PublicationId, Provider, MetricId and Amount by Date, one row by each month, but in some cases these data don't have a sequencial values, for example I have the data for the next sequence:
I need to get the sequence by each month, in this case I need to project the month from February to May (with the last previous value, for this case of January) , this is:
The data for testing are:
DECLARE @PublicationsByUser AS TABLE
(
Id INT,
PublicationId INT,
MetricId INT,
ProviderId INT,
DateCreated DATE,
Amount FLOAT
[code]....
View 14 Replies
View Related
Jun 29, 2015
I have a data set (snippet below) and I need to count the number of consecutive years based on a date in time for each ID as represented below.
ID DATE
------ --------
1 2000-05-03
1 2001-06-10
1 2002-04-02
1 2005-07-29
1 2010-12-15
4 2001-05-07
4 1999-08-01
4 2000-07-05
4 2001-08-01
9 2002-05-01
9 2000-04-02
My result set needs to be something like:
ID Count of Consecutive Years
------- -----------------------------
1 2
4 2
9 0
I know this is a gaps and islands type problem but nothing I have been able to find is working once I attempt modification so that it can fit my dataset. Please note that I am going to use the data return to populate another table that is currently being populated using a cursor that utilizes an insert statement based on different codes.
View 9 Replies
View Related
Mar 28, 2014
I have data like the following:
ID COUNTER DATA
1 10 BLAH
1 20 BLAH
2 10 BLAH
3 10 BLAH
2 20 BLAH
2 30 BLAH
What I want to return is:
1 20 BLAH
2 30 BLAH
3 10 BLAH
I want the top 1, having the highest counter from each ID. This is a highly simplified version of that I am pulling which also is between a date range, but same principle.
IE: SELECT * FROM Table WHERE ID in (SELECT DISTINCT ID FROM Table WHERE Date BETWEEN <date> AND <date>
I'd rather keep it in one statement if possible, but if I have to do it in multiple passes then so be it.
View 6 Replies
View Related
Jan 30, 2005
I need to write a procedure to check if I have any gaps in my dates...
Start Date............End Date
10/6/2004............10/6/2005
10/6/2003............10/6/2004
7/10/2003..........10/6/2003
7/10/2002.............7/10/2003
What I need to do is test the End Date to the next row under the Start Date Column. (in bold to clarify) (moving upward from bottom)
sSQL011$ = "SELECT * FROM TableName " & _
"WHERE ID = '" & ID & "'"
Set rs011 = DB.OpenRecordset(sSQL011$, dbOpenDynaset)
Set Data1.Recordset = rs011
rs011.FindFirst "ID = " & ID
With rs011
do while .eof = False
if .eof then
exit sub
end if
if rs011("StartDate") 'from one record, if it is not equal to the Start
Date in the next record then
MsgBox "Gap in Dates for: " + ID
loop
end with
pseudo...
if the end date in one consecutive row is not the same as the start date in the next row then there is a date gap
thanks...
View 3 Replies
View Related
Mar 5, 2008
Hi,
I have a table with date ranges for activitys. I need to get the userid , firstdayidle, lastdayidle for when a user has been idle in the last 90 days.
it gets tricky since it also must include users that have no records in activity table or only one record so it has 2 idle periods, etc
I have a hard time wrapping my head around the logic, weither using a datetables or not, so any help would be appreaciated
--my first step is to get the activitys that enter the last 90 days
select * from Activity2
where (enddate > DateAdd(d, -90, getDate()) AND enddate < getDate()) OR (startdate > DateAdd(d, -90, getDate()) AND startdate < getDate())
CREATE TABLE [dbo].[InternalUser](
[userid] [int],-- IDENTITY(1,1) NOT NULL,
[fullname] [varchar](50)
}
insert into [InternalUser] values(1,'a')
insert into [InternalUser] values(2,'b')
insert into [InternalUser] values(3,'c')
insert into [InternalUser] values(4,'d');
insert into [InternalUser] values(5,'e');
insert into [InternalUser] values(6,'f');
CREATE TABLE [dbo].[Activity2](
[activityid] [int] NOT NULL, --IDENTITY(1,1)
[userid] [int] NOT NULL,
[startdate] [datetime] NULL,
[enddate] [datetime] NULL
)
insert into [Activity2] values(1,1,'2007-02-15 00:00:00.000','2008-03-15 00:00:00.000')
insert into [Activity2] values(2,2,'2007-01-01 00:00:00.000','2008-01-02 00:00:00.000')
insert into [Activity2] values(3,2,'2008-01-20 00:00:00.000','2008-04-10 00:00:00.000')
insert into [Activity2] values(4,3,'2008-02-20 00:00:00.000','2008-10-10 00:00:00.000')
insert into [Activity2] values(5,4,'2007-01-01 00:00:00.000','2008-01-16 00:00:00.000')
View 5 Replies
View Related
Jul 23, 2005
I have a table containing typed log entries. One log entry is supposedto be created every twelve hours, but sometimes there are gaps. I needto create a report showing the time of entry, and the actual log entry.I can't just list the contents of the log table, because if I do thatthere will be dates missing. Instead, when there isn't a log entry fora date, I need to print the date, and then just leave the log entryblank.The SQL bellows shows what the output should look like. HOWEVER, thecode below makes use of a temp table containing all possible dates. Myquestion is, is there a better way to do this - one that doesn'tinvolve the temp table? Thanks in advance.create table StationLog (LogDate datetime, LogText char(11))insert StationLog values ('1/1/2005 00:00:00','entry one')insert StationLog values ('1/1/2005 12:00:00','entry two')insert StationLog values ('1/2/2005 00:00:00','entry three')insert StationLog values ('1/3/2005 00:00:00','entry four')create table Date_List (TempDate datetime)insert Date_List values ('1/1/2005 00:00:00')insert Date_List values ('1/1/2005 12:00:00')insert Date_List values ('1/2/2005 00:00:00')insert Date_List values ('1/2/2005 12:00:00')insert Date_List values ('1/3/2005 00:00:00')insert Date_List values ('1/3/2005 12:00:00')select TempDate, LogTextfrom Date_Listleft outer join StationLog on Date_List.TempDate = StationLog.LogDatedrop table StationLogdrop table Date_List
View 6 Replies
View Related
Dec 17, 2006
Can someone please help me with this?
I need a query that will pull clients that made payments last year but not this year.
I need the query to use date parameters so I can select any date range for the past year and the current year.
I have listed the tables and fields that might be needed: I'm hoping to do this without temp tables.
Date range is based on the tblPaymentReceipts.PaymentDate
tblClients, ClientID
tblPayment, PmtID, ClientID, Paystartdate, Paygroup
tblPaymentReceipts, PmtRcptID, PmtID,CleintID,PaymentDate,PaymentAmount
View 6 Replies
View Related
Mar 18, 2004
I have two sets of dates to work with. One is an existing booking with a start and an end date. The other is a new booking with a start and an end date. I want to compare them and calculate how much overlap there is. If the overlap is over a certain amount (say 4 days), then I want to flag the user.
Is there any thing I can use in terms of a SQL query to assist in this comparison? I'm relatively new to SQL so I'm not entirely sure what functions and keywords are available to me to make this comparison.
View 3 Replies
View Related
Oct 25, 2005
I've gone cold here. Dunno if I've had too little coffee - as I'm currently drinking some seriously wicked green tea - or whether my brain has locked down from yesterdays "bad eggs for lunch" experience.
Anyway... I have database with a customer, for each customer is a related history table with assigned consultant.
The assigned consultant table has information on consultant id, name, the start date of his assignment and the end date.
I need to find all customers that currently have (or have had) two or more consultants actively assigned. In other words, I need to see if the start/end times overlap.
At my current state, I'm just done.. i can't maintain the perspective... how do I do this?
View 5 Replies
View Related
May 22, 2008
Hey,
I woudl like to take an age range (Say 22-27) and determine the min and max year, and then take those two DateTime Year values and do a SELECT from a column titled "Birthday Year", which is a an int value like '1984'. Could someone give me a hand with this?
/* Value 1: 1978
Value 2: 1988
*/
SELECT(???) FROM User WHERE ['BirthdayYear'] <> (?Age Function?)
GO
Thanks.
View 3 Replies
View Related
Apr 16, 2015
I have a set of MS SQL reports, that need to always run on a certain day of the month. Generally the 20th. If the report was to run few days before the 20th, say on the 10th, I wish to retrieve those days between the 20th from the previous month, till the current date.
e.g: '2015-4-10' should only return 20 days worth of data.
I have tried the following query:
SELECT
DATEADD(D, 1, MAX(CAST(DateTimeStamp AS DATE))) As EndDate,
MIN(CAST(DATEFROMPARTS(DATEPART(YEAR, DateTimeStamp),DATEPART(MONTH,
(SELECT CASE WHEN DATEDIFF(DAY,DATEPART(DAY, GETDATE()),28) <0 THEN (SELECT DATEPART(MONTH, GETDATE()))
ELSE (SELECT DATEPART(MONTH, GETDATE()) -1) END AS Date)),28)AS DATE)) AS StartOfMonth
FROM
tbLogTimeValues
WHERE
DATEPART(YEAR, DateTimeStamp) = DATEPART(YEAR, DATEADD(M, -1, GETDATE()))
Which parses ok and managed to test all individual queries, however, as a whole, I get the following error message "Cannot perform an aggregate function on an expression containing an aggregate or a subquery."
View 5 Replies
View Related
Jul 23, 2005
Hello,I am importing data that lists rates for particular coverages for aparticular period of time. Unfortunately, the data source isn't veryclean. I've come up with some rules that I think will work to clean thedata, but I'm having trouble putting those rules into efficient SQL.The table that I'm dealing with has just under 9M rows and I may needto use similar logic on an even larger table, so I'd like somethingthat can be made efficient to some degree using indexes if necessary.Here is some sample (simplified) code:CREATE TABLE Coverage_Rates (rate_id INT IDENTITY NOT NULL,coverage_id INT NOT NULL,start_date SMALLDATETIME NOT NULL,end_date SMALLDATETIME NOT NULL,rate MONEY NOT NULL )GOINSERT INTO Coverage_Rates VALUES (1, '2004-01-01', '2004-06-01',40.00)INSERT INTO Coverage_Rates VALUES (1, '2004-03-01', '2004-08-01',20.00)INSERT INTO Coverage_Rates VALUES (1, '2004-06-01', '2004-08-01',30.00)INSERT INTO Coverage_Rates VALUES (2, '2004-01-01', '9999-12-31',90.00)INSERT INTO Coverage_Rates VALUES (2, '2004-03-01', '2004-08-01',20.00)INSERT INTO Coverage_Rates VALUES (2, '2004-08-01', '2004-08-01',30.00)GOThe rule is basically this... for any given period of time, for aparticular coverage, always use the coverage with the highest rate. So,given the rows above, I would want the results to be:coverage_id start_dt end_dt rate----------- ---------- ---------- --------1 2004-01-01 2004-06-01 40.001 2004-06-01 2004-08-01 30.002 2004-01-01 9999-12-31 90.00There can be any combination of start and end dates in the source, butin my final results I would like to be able to have only one distinctrow for any given time and coverage ID. So, given any date @my_date,SELECT coverage_id, COUNT(*)FROM <results>WHERE @my_date >= start_dtAND @my_date < end_dtGROUP BY coverage_idHAVING COUNT(*) > 1the above query should return 0 rows.Thanks for any help!-Tom.
View 9 Replies
View Related
Dec 11, 2007
Hello Gang,
I have a strange problem that I haven't dealt with before.
I need to execute a piece of code based on date ranges. If the date range is:
Scenario 1:between 02/28 (Feb 28) and 07/31 (July 31) do x
-----------------------------------------------------------
Scenario 2:between 08/01 (Aug 1) and 01/31 (Jan 31) do y
I am trying to automate a report. The report is supposed to generate a result that will differ based on the date ranges going into the future. E.g.
[1]. If the run date of the report is between '2/1/20xx' and '7/31/20xx' display <ABC> or
[2]. If the run date of the report is between '8/1/20xx' and '1/31/20xx' display <PQR>
In example # 2. I am moving from one year to the next (July to Dec and the one extra month of Jan). So for example, if the guy runs the report between August of 2008 and January of 2009, display <PQR>.
How do I achieve both # 1 & 2 above in a code? Does this explain better.
Joshi
View 1 Replies
View Related
Mar 15, 2007
I'm currently using Reporting Services for SQL Server 2005. I have been able to setup, and configure the Report Manager interface, as well as generate reports via the Report Builder. What I have been unable to do is allow the user to dynamically set the date range that my SQL query will use. Can someone suggest / is it even possible wihtout using a custom web interface?
View 1 Replies
View Related
Jun 2, 2006
We have some seasonal products that we would like to turn on and off automatically on our website. To accomplish this, we've added StartDate and EndDate fields to the product table. See simplified table:
--------------Simple Table:--------------ID int identity (PK)Value varchar(50)StartDate datetimeEndDate datetime
Normally I could just do something like SELECT ID, Value FROM PRODUCTS WHERE StartDate <= @MyDate AND EndDate >= @MyDate
The catch is that we want to ignore the year part of the dates so that we don't have to go back through and update all the records every year. I'm pretty sure I've done this in the past, but I'm having a brain fart right now and can't remember how. One of the issues is that the start month could be > the end month (e.g. - October 15 - January 1). Using code in the application I could work around this (see below), but I would like to handle this in SQL if possible so I don't have to return more records than are needed and weed out the bad records.
'dtStart and dtEnd are Date Objects, StartDate and EndDate are strings containing the MM/DD partIf dtStart.CompareTo(dtEnd) > 0 And dtStart.CompareTo(myDate) < 0 Then dtEnd = Date.Parse(EndDate & "/" & (myDate.Year + 1))ElseIf dtStart.CompareTo(dtEnd) > 0 And dtStart.CompareTo(objDate) > 0 Then dtStart = Date.Parse(StartDate & "/" & (myDate.Year - 1))End If
I appreciate any help you can provide.
Thanks,Sam
View 1 Replies
View Related
Apr 23, 2008
Hello, I am trying to automate our FTE calculations, and I need to be able to determine the total days employed for a given employee for a given period of time. I have the date ranges they worked, but am not sure how to total those based on the required period, For example
empployee|Start Date|End Date
1|1/1/2005|3/1/2006
1| 4/15/2006| 1/1/2008
How do I total the number of days employed for this employee between 2/1/2006 and 2/1/2007?
Thanks in advance for any advice?
View 11 Replies
View Related
May 31, 2008
I have a table with date like this.
InstId--Date----Readings
--1---10/12/2008--10
--1---11/12/2008--10
--1---12/12/2008--10
--1---13/12/2008--9
--1---14/12/2008--10
--1---15/12/2008--10
--1---16/12/2008--10
--1---17/12/2008--10
--2---05/03/2008--8
--2---06/03/2008--6
--2---07/03/2008--8
--2---08/03/2008--8
--2---09/03/2008--8
--2---20/03/2008--8
Guys I want to get the date ranges instrument wise for which the instrument readings are constant.
For example for instrument 1 the readings are constant i.e 10 from 10/12/2008
till 12/12/2008 & then again it is constant from 14/12/2008 till 17/12/2008.
Same goes for instrument id 2.It is constant from 07/03/2008 till 20/03/2008.
I need to get the output like this.
StartDate EndDate Readings
10/12/2008 12/12/2008 10
14/12/2008 17/12/2008 10
17/03/2008 20/03/2008 8
Thanks for any help.
View 10 Replies
View Related
Nov 6, 2013
Just want to check if my query is the standard way to check if date ranges overlap in a price table as I need to check any that overlap as I can't have two prices on one day.
For example if in a table there was:
Product TROUSER Colour BLUE
Start Date 01-NOV-13 End Date 20-NOV-13 Price £20.00
Start Date 10-NOV-13 End Date 12-NOV-13 Price £18.00
Start Date 21-NOV-13 End Date 25-NOV-13 Price £15.00
The top two overlap.I'm doing this which is giving me nothing returned which I'm hoping means I have no overlapping date ranges:
SELECT a.[PriceList]
,a.[ProductID]
,a.[Colour]
,a.[Start Date]
,a.[End Date]
,a.[Product Price]
[code]....
View 5 Replies
View Related
Jul 23, 2005
I'm trying to create a query that will tell me which requeststook longer than 10 days to move one from particular state to anotherstate. The query I've created returns the correct requests,but not always the correct 'NextActionDate'/'NextStatus'/'NextState'.I'm sure I'm missing something easy, but I can't figure out what itmight be. Any help is appreciated! Thanks,Myron-- remove SPAM-KILL from address to reply by email --DDL for table creation and data population:CREATE TABLE [dbo].[ReqHistory] ([Id] [int] NOT NULL ,[ReqId] [int] NOT NULL ,[ReqIDStateId] [tinyint] NOT NULL ,[ActionDate] [datetime] NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[RequestStates] ([ID] [tinyint] NOT NULL ,[StateText] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[Requests] ([ID] [int] NOT NULL ,[ShortDescription] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[StatusChangeDate] [datetime] NULL ,[Status] [tinyint] NULL) ON [PRIMARY]GOinsert into Requests values(361, 'Test ID: 361', cast('2004-06-03 08:52:03.013' as datetime),98)insert into Requests values(1400, 'Test ID: 1400', cast('2004-05-13 04:01:55.250' as datetime),97)insert into Requests values(30051,'Test ID: 30051', cast('2004-09-15 10:10:25.093' as datetime), 96)insert into ReqHistory values(904,361,1,cast('2004-05-03 00:20:55.983' as datetime))insert into ReqHistory values(931,361,2,cast('2004-05-03 01:07:14.157' as datetime))insert into ReqHistory values(959,361,20,cast('2004-05-03 01:29:20.157' as datetime))insert into ReqHistory values(20250,361,31,cast('2004-06-03 08:51:58.950' as datetime))insert into ReqHistory values(20251,361,98,cast('2004-06-03 08:52:03.013' as datetime))insert into ReqHistory values(20249,361,30,cast('2004-06-03 08:51:51.107' as datetime))insert into ReqHistory values(939,361,10,cast('2004-05-03 01:10:36.093' as datetime))insert into ReqHistory values(7318,1400,1,cast('2004-05-13 03:48:01.420' as datetime))insert into ReqHistory values(7346,1400,2,cast('2004-05-13 03:56:37.857' as datetime))insert into ReqHistory values(7347,1400,12,cast('2004-05-13 03:57:03.293' as datetime))insert into ReqHistory values(7356,1400,22,cast('2004-05-13 04:00:58.497' as datetime))insert into ReqHistory values(7357,1400,97,cast('2004-05-13 04:01:55.250' as datetime))insert into ReqHistory values(53218,30051,1,cast('2004-08-06 10:12:33.050' as datetime))insert into ReqHistory values(53223,30051,2,cast('2004-08-06 10:15:32.500' as datetime))insert into ReqHistory values(53246,30051,13,cast('2004-08-06 10:26:34.850' as datetime))insert into ReqHistory values(53264,30051,23,cast('2004-08-06 10:47:38.993' as datetime))insert into ReqHistory values(70138,30051,3,cast('2004-09-15 09:21:18.230' as datetime))insert into ReqHistory values(70257,30051,96,cast('2004-09-15 10:10:25.093' as datetime))insert into RequestStates values(1,'Awaiting CSMC')insert into RequestStates values(2,'CSMC Review')insert into RequestStates values(3,'Reject Awaiting CSMC')insert into RequestStates values(10,'Awaiting MA Review')insert into RequestStates values(12,'Awaiting FO Review')insert into RequestStates values(13,'Awaiting IS Review')insert into RequestStates values(20,'MA Review')insert into RequestStates values(22,'FO Review')insert into RequestStates values(23,'IS Review')insert into RequestStates values(30,'Func Approval')insert into RequestStates values(31,'Func Approval Complete')insert into RequestStates values(96,'Resolved')insert into RequestStates values(97,'Planning')insert into RequestStates values(98,'Open')insert into RequestStates values(99,'Closed')The query that almost works:select irh.ReqID, irh.MAactiondate, irh.reviewstate,irh2.Nextactiondate, irh2.irh2state as NextStatus, irh2.statetext as NextStatefrom (select distinct irh.ReqID, max(irh.actiondate) as MAactiondate,irh.ReqIDStateID As IRHState, irs.statetext as ReviewStatefrom ReqHistory IRHjoin requeststates irs on irs.id = irh.ReqIDStateIDwhere irh.ReqIDStateID in (20, 23)group by irh.ReqID, irs.statetext, irh.ReqIDStateID) as irhjoin (select irh2.actiondate as NextActiondate, irh2.ReqID, irh2.IRH2State, irs.statetextfrom (select min(actiondate) as actiondate, ReqID,min(ReqIDStateID) as IRH2Statefrom ReqHistory--the WHERE is wrong, and I believe should be irh2.Nextactiondate > irh.maactiondate,--but I couldn't make it workwhere ReqIDStateID > 23group by ReqID) as irh2join RequestStates irs on irs.id = irh2.irh2state ) as irh2 on irh.ReqID = irh2.ReqIDjoin requests ir on ir.id = irh.ReqIDwhere irh.MAactiondate + 10 < irh2.Nextactiondateorder by irh.ReqIDThe data being returned is:(the 'time' portion of the dates edited out for space)ReqID MAActionDate Review State NextActiondate NextStatus NextState361 2004-05-03 MA Review 2004-06-03 30 Functional Approval30051 2004-08-06 IS Review 2004-09-15 96 ResolvedThe data that should have been returned:(the 'time' portion of the dates edited out for space)ReqID MAActionDate Review State NextActiondate NextStatus NextState361 2004-05-03 MA Review 2004-06-03 30 Functional Approval30051 2004-08-06 IS Review 2004-09-15 3 Reject Awaiting CSMC
View 3 Replies
View Related
Jun 27, 2006
Hi there, I'm trying to generate a report for an old database and I'mhaving trouble coming up with an elegant way of going about it. Usingcursors and other 'ugly' tools I could get the job done but 1) I don'twant the report to take ages to run, 2) I'm not a big fan of cursors!Basically there are tables that track history and each table tends totrack only a specific value housed within a date range. I'm trying tocombine the tables to get a snap-shot of the complete history. I'mhaving problems dealing with the Start/End Dates from the two tablesand building the dates in the final table to be broken down by 'historytype'.Here are a few sample records and the results I'm trying to achieve:Table 1:CAgyHist (ProdID,AgyID,StartDate,EndDate)1 1 Jan 1, 2006 Jan 5, 20061 2 Jan 5, 2006 Jan 25, 20061 1 Jan 25, 2006 NULLTable 2:CInvHist (ProdID, InvID,StartDate,EndDate)1 1 Jan 1, 2006 Jan 23, 20061 2 Jan 23, 2006 Jan 15, 20061 1 Jan 15, 2006 NULLDesired End Result:CTotalHist (ProdID,AgyID,InvID,StartDate,EndDate)1 1 1 Jan 1, 2006 Jan 5, 20061 2 1 Jan 5, 2006 Jan 15, 20061 2 2 Jan 15, 2006 Jan 23, 20061 2 1 Jan 23, 2006 Jan 25, 20061 1 1 Jan 25, 2006 NULLMy challenge thus far has been dealing with the dates as they don'tnecessarily correspond - from one table to the other.I am by no means a database expert of any level and any help would begreatly appreciated.Thanks,Frank.
View 7 Replies
View Related
Aug 2, 2004
Masters,
The below queries return the data that I seek, but I have no idea how to combine them into a single query.
SELECT SUM(TOTALSVCAMT) - SUM(TOTALPAYMENTAMT) - SUM(TOTALADJAMT) as [0 to 30]
FROM MDM2
WHERE DATEDIFF(day, SERVICEDATE, getdate()) between '0' and '30'
SELECT SUM(TOTALSVCAMT) - SUM(TOTALPAYMENTAMT) - SUM(TOTALADJAMT) as [31 to 60]
FROM MDM2
WHERE DATEDIFF(day, SERVICEDATE, getdate()) between '31' and '60'
SELECT SUM(TOTALSVCAMT) - SUM(TOTALPAYMENTAMT) - SUM(TOTALADJAMT) as [61 to 90]
FROM MDM2
WHERE DATEDIFF(day, SERVICEDATE, getdate()) between '61' and '90'
SELECT SUM(TOTALSVCAMT) - SUM(TOTALPAYMENTAMT) - SUM(TOTALADJAMT) as [90+]
FROM MDM2
WHERE DATEDIFF(day, SERVICEDATE, getdate()) > '90'
Any assistance that can be provided will be greatly appreciated.
Grasshopper
View 1 Replies
View Related
Jul 24, 2015
I've got two measure groups with a dimension (Dimension A) that is related to one measure group but not the other. I want to find the date ranges of the members of an attribute in Dimension A in Measure Group 1 and apply that to Measure Group 2 in a calculation. This way I can find the sum of a measure from Measure Group A that falls within the time periods of the attributes in Measure Group B. Part of my MDX for this new calculated member starts like this, but doesn't work.
WITH MEMBER [Measures].[New Measure] AS
IIF( ISEMPTY ([Measures].[Measure 1]), NULL,
SUM(([Date].[Hour].[Hour], [Dimension A].[Attribute].[Attribute]), [Measures].[Measure 2]))
View 3 Replies
View Related
May 19, 2005
I am building a small app that will display availability data for properties. I have a calendar that displays the dates for a whole year in month rows with each days colour representing the availability status, i.e. booked, on hold etc.
My question is about how to store these dates in the db. At the moment I have the table below:
TABLE Availability [PropertyID] [int] NOT NULL , [StatusID] [tinyint] NOT NULL , [StartDate] [smalldatetime] NOT NULL , [EndDate] [smalldatetime] NOT NULL
I was planning on having four status's for any given date, unknown, available, on hold or booked.
Displaying the dates has proved pretty simple but updating availability means I would need to query the db to see if any of the dates overlapped, I would then have to add the new date range/status as well as change any date ranges that overlapped, either in the sp or in the code and this is what made me wonder if there was a better way.
Does this sound a reasonable approach? Any advice or pointers would be greatly appreciated, This is the first time I have had to store date ranges and I want to make sure I am doing it right.
View 2 Replies
View Related
