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?
I have a table with multiple rows per staff person. Each of these rows has staff_id, start_date, and end_date. Per staff, if any start_date comes between the start_date and end_date of a different row, or if any end_date comes between the start_date and end_date of a different row, then I have to flag these records as being identical.
How can I do this? I have tried doing a Cross Apply because I thought that would do Cartesian product (comparing every row), and I've also tried temp tables. But I haven't gotten either of these to work. Here is some dummy data:
if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#staff_records') ) DROP TABLE #staff_records; create table #staff_records ( staff_id varchar(max),
I would like to include starttime-endtime ranges that do not overlap with any other integer range.
which in this case would be:
Rownumber starttime endtime 1 l 30 l 260 2 l 1330 l 1400
I was thinking of using a cursor and comparing each row to all of the other rows in the table and then setting a boolean in that row if it overlaps with another row in the table...is there a better way of doing this?
IF OBJECT_ID('tempdb..#OverLappingDates') IS NOT NULL DROP TABLE #OverLappingDates CREATE TABLE #OverLappingDates ( MemberID Varchar (50), ClaimID Varchar(50), StartDate DateTime, EndDate DateTime )
[Code] ....
I need to select only records where dates are over lapping for the same memberid...For this scenario i need an output First 2 records (MemberID 1 has 2 claiims overlapping dates).
I want generating Valid date ranges from any list of dates.
The List of Dates could be generated from the below TSQL -
SELECT '2015-06-02' [Date] UNION ALL SELECT '2015-06-13' UNION ALL SELECT '2015-06-14' UNION ALL SELECT '2015-06-15' UNION ALL SELECT '2015-06-16' UNION ALL SELECT '2015-06-22' UNION ALL SELECT '2015-06-23' UNION ALL SELECT '2015-06-24'
And the expected output should look like -
SELECT '2015-06-02' FromDate, '2015-06-02' ToDate UNION ALL SELECT '2015-06-13' FromDate, '2015-06-16' ToDate UNION ALL SELECT '2015-06-22' FromDate, '2015-06-24' ToDate
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?
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
So What I am trying to accomplish is sum up overlapping time ranges while also keeping Unique data rows within the table. So If a data row is unique meaning it is NOT within a overlapping group of data rows then I want to just insert it into a "final table", but if the data rows are overlapping then I want to grab the min(timestart) and the max(timestop) and the PKID of the data row with the max(timestop) within the overlapping group.
I accomplish this task using nested cursors, however When I place my method into a trigger which runs on INSERT, then my trigger never runs and nothing happens. Is there a way to accomplish this without using cursors. I have placed my cursor method into the sql fiddle to be inspected.
create table temp1 ( pkid int, line int, dateentry date, timestart time, timestop time )
i have a table containing following dataeffdate termdate uid----------- ----------- -----------1 2 13 4 25 8 37 9 411 12 512 13 63 6 75 9 8i need to replace all the overlapping records with one recordsuch that resultant table shud look likeeffdate termdate uid1 2 111 13 23 9 3Thanks
I am trying to create a check constraint that prevents entry of a date range that overlaps any other date range in the table. The below example script results in the error message: The INSERT statement conflicted with the CHECK constraint "ck_dt_overlap". what I am doing wrong?
CREATE TABLE t1 (d1 date,d2 date) go create function dbo.dt_overlap (@d1 date, @d2 date) RETURNS BIT AS BEGIN IF EXISTS (SELECT 1 from t1 where d1 between @d1 and @d2 or d2 between @d1 and @d2) RETURN 1 RETURN 0 END go alter table t1 add constraint ck_dt_overlap CHECK (dbo.dt_overlap(d1,d2)=0) go insert into t1 values ('2015-01-01','2015-02-01') insert into t1 values ('2015-01-15','2015-02-15')
--BOTH inserts result in the following message: The INSERT statement conflicted with the CHECK constraint "ck_dt_overlap".
select [Parent Name],[ID],[Year],[Sales Name], sum([Total VtM]) as 'Total Sales' from RegData group by [Parent Name],[ID],[Year],[Sales Name] order by [Total Sales] desc
I need to modify this query to get the top 5 of each category based on Total Sales amount.
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.
And I would like to produce the following outcome to the same table (using update statement): As what you all observe, it merge all overlapping dates based on same promotion ID by taking the minimum start date and maximum end date. Only the first row of overlapping date is updated to the desired value and the flag value change to 1. For other overlapping value, it will be set to NULL and the flag becomes 2.
The second part that I would like to acheive is based on the first table as well. However, this time I would like to merge the date which results in the minimum start date and End Date of the last overlapping rows. Since the End date of the last overlapping rows of promotion ID 1 is row with ID 4 with End Date 2015-05-29, the table will result as follow after update.
how to write a query to get current date or end of month date if we pass year and month as input
Eg: if today date is 2015-09-29 if we pass year =2015 and month=09 then we have to get 2015-09-29 if we pass year =2015 and month=08 then we have to get 2015-08-31(for previous months we have to get EOMonth date & for current month we have to get current date).
I'm new to sql. Can someone help me to write a script to select overlapping start dates for each client records. For example: Clientid 1 have 3 episode as below(I only want to see the first two records with overlapping start date records)
I need to identify time spans where members identified as having a condition have NOT had any of 5 specified services in the past 12 months. I have a table (DiabStrata) that identifies time frames for which my data shows a member as having the condition, and I have 5 separate tables with the dates of the relevant services.
I can easily identify when a member hasn't had the service at all, or is lacking it at the start or end of the time frame for which they have the condition, but I'm hitting a wall on how to deal with gaps between the minimum and maximum identification dates.
insert DiabStrata( select '1',1,'20060101','20070302' union select '1',1,'20070803','20080804' union select '2',1,'20020101','20080503')
insert hba1c(
select '1','20060301'
union
select '1','20070301'
union
select '2','20050101')
--Missed Service Begin select * into #eval from DiabStrata where strat=1
delete #eval from #eval left join hba1c on #eval.memberid=hba1c.memberid where hba1c.memberid is null --repeat for other indicators
update e set stratstart=min(dos) from #eval e join hba1c on e.memberid=hba1c.memberid having min(dos)>stratstart
update e set stratend=max(dos)+365 from #eval e join hba1c on e.memberid=hba1c.memberid having max(dos)+365<stratend
delete from #eval where stratstart>stratend --repeat for other indicators Desired output is into DiabStrata with a strat of 2 for the time frame for which they have strat 1 but do not have all 5 services within the prior 365 days. MID Strat StartStrat EndStrat 1 2 1/1/06 - 2/28/06 1 2 3/2/08 - 8/4/08 2 2 1/1/02 - 12/31/04 2 2 1/2/06 - 5/3/08
I need to generate the week ranges like this format :
Here from date and to date would be picked up from the table but just to make you understand i have hard coded it but this is the real date which is falling inside the table.
Note : Week should be generated from Monday to Sunday within desired date range
I have to insert YEAR WEEKNUMBER STARTDATE ENDDATE values to a datatable (sayweekrange), if I pass 2011 as year.
Week range starts at 2011-03-28 to 2011-04-03 (because in my database 2010 last week range ends with 2011-03-27) like this I have to generate for 52 weeks.
I want to write a stored procedure, that takes only year as parameter. with this year I have to generate week ranges and insert into my table as shown above.
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
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.
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
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."
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.
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.
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?
I can link a product to a category. Now, if I just link a product to a single category, such as the bottom leaf category:
Self Help / Personal Development / Spiritual / Meditation
I would link a product to the Meditation category. However if I click on Self Help while browsing, I want to see all items underneath Personal Development, Spiritual and Meditiation. So my question is is this a good way to store the product-category relationships, or should I put many entries into CategoryProducts to keep the queries simlpe and faster? Are they faster doing it this way? In this way there would be 4 entries for a product in meditation. My personal idea is that adding all entries up a tree arm of a category path will be cumbersome to manage, but it does solve the problem of clicking on Self Help and seeing all products that exist within sub-categories. I am sure an SQL query would be able to work this out, but I dont know if performance would be something to consider on an ecommerce site? Are there any patterns fo rthis stuff - seems a reasonably repeatable pattern for business sites?
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
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.
