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 have 3 seperate jobs to backup my database. 1 for a full backup, 1 for differentials, and the 3rd for the transaction log. Each is on its own schedule. The problem I have is that sometimes the transaction log job will try to start while one of the other two are in process. When this happens, an error is generated and I get paged (which is very anoying at midnight) Is there any way to block the transaction job until the other two are complete? It should still run, just not at the same time.
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?
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?
Hey I am trying to create a report where I want overlapping images and rectangles... but when I upload it to report manager it seems to push them all seperate??? How do I stop doinh this... it does print okay! Just looks wrong on screen?
Hello Everyone, I have a web form that allows the user to select a time to reserve. I am trying to make it so that when a user selects a date to schedule something (which i have working) a drop down list will fill with times that have not been reserved. The data is stored in two tables: tblCalendar and tblTime. tblTime contains pkTimeID and times (which are all possible times to select from in half hour intervals). tblCalendar contains a few fields but timeID and date (which is in the format M/d/yyyy) are what I need to test against. timeID is the foreign key of pkTimeID. Basically when the user selects the date, a function gets called that will run a SELECT statement to get the times available. All of this works, I am able to fill the ddl with all times available no matter what the date is or what has already been reserved. I want to check if a time has been already selected based on the date selected by the user and only allow times not selected to be listed. After acheiving this I would like to prevent the immediate time before and immediate time after from being displayed because each reserved time will last for one hour but the data is stored in half hour increments. Any help/suggestions/links will be greatly appreciated. If I need to provide anything else please let me know. Thanks in advance, Brian
I have several sets of timedate ranges and I need to merge the ranges where there is no overlap with the jobs on resource1. In my example data, I want all jobs from ResourceID 1 and those jobs from all other resources where they do not overlap with EXISTING jobs on resource 1 (i.e. imagine I'm trying to select candidates from other resources to fill ResourceID 1 with continuous jobs)
Below is some sample data, my failed attempt and expected results. I managed to excluded everything that should be excluded except job 10
-- Need to select all other jobs from all other resources that can be merged into resource 1 where there is no overlap with existing jobs in resource 1 only
CREATE TABLE #Jobs ( resourceID INT ,JobNo INT ,StartTime SMALLDATETIME ,EndTime SMALLDATETIME ,ShouldBeOmitted BIT
declare @t table ( segment int, subsegment int, primary key (segment,subsegment) ) insert @t select 1,33 union all select 1,22 union all select 2,33 union all select 2,22 union all select 3,33 union all select 3,22 union all select 3,44
What I want is to find all segments that are in some sense complete duplicates of other segments. a segment is made up of subsegments. a subsegment is not a segment - it's a completely different entity. this table is not hierarchical.
So in the sample data above, segments 1 and 2 are dupes because they share exactly the same subsegments: 22 and 33. Segment 3 is not a dupe because it has a third subsegment the other two don't have: 44.
when a duped segment is found, I need to know which other segment it duplicates. so an acceptable result set for the above sample data would be:
segment partner ------- ------- 1 2
this would also be fine:
segment partner ------- ------- 1 2 2 1
ps: i already posted this on dbforums - just broadening the audience a little.
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/
I am using Sql 2005 and merge replication with push subscriptions. I have several dynamic join filters on some of my tables.
The join filters all use a central table that maps say a server location name (something that is returned from HOTNAME() in my case) to an for a store branch ID. This is a retail system database.
When I add a new new subscription I update this table with the new server location name and it's corresponding branch ID. My filtered tables all have a foreign key in them that is the branch ID. I can then effectively join from the server location name to a Branch ID.
What I have noticed is that if I update one row in the map table, sql server will re-generate all partitioned rows for all subscribers, even for rows that haven't been updated.
The net result is that when I add a subscription, my existing subscriptions all get about 52,000 row updates.
Am I seeing this because I said my partitions will overlap when I created the table articles?
The firefox preview comes totally messed up.One line overlapping over the other
In my report i have a table in which a row has 5 text boxes. the 3rd and 5th text box has can grow property set to true. i have tried placing the text boxes into rectangles but no help there. the text in that row still over laps . does anyone have any other fix for this?
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 set the option 'Headers should remain Visible while scrolling', in order to freeze the headers. However, on some reports, when I scroll down the report , the headers overlap each other .
... and preserving the relationships.(Note, this is more of a SQL question than a SQL-related ASP.NET question...)Say I have two databases, D1 and D2, with the same three tables:CompaniesDepartmentsEmployeesWith the standard one-to-many relationships down the line, with eachtable having a PK, IDENTITY field like CompanyID, DepartmentID, andEmployeeID.I have a smattering of data in each of D1 and D2 for these tables withoverlaps in the ID field values. What I want to be able to do is copyover D1's data to D2, preserving the relationships in D1 even thoughthere are ID overlaps in D2. So the tool I'd use would have to besmart enough to check for ID dups in D2 and appropriate change the IDvalues in D1's tables, maintaining the relationships.Is there some built-in SQL tool to do this or do I have to do this myself?Thanks!
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
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 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),
We have created a report chart, a simple bar graph, and need to put 2 vertical lines (target limits) on this chart, At the end we need to export this charts in excel and html, but it says that the overlapping of reports items is not supported in all renderers.
It seems that HTML and Excel donot support the overlapping of this 2 items.
Can anyone please help me in this issue? or is their any other alternative to get the chart and the static lines placed on this charts.
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".
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.
I'm new to MDX and was hoping someone could answer this question. I'm working on an MDX query, a simplified version is below.
What I'm trying to do is to pull 2 sets, based on a date range. Based on those, I'd like to see the total "cost" measure for each set, and divide one by another.
What I'm finding is that when I run the query below, the cost is broken out by month. So, my select statement gives me the cost for January 2008 and February 2008 in the CurrentPeriod set, and for January, February, etc. 2007 in the PriorPeriodData set. What I want instead is to get the total "cost" measure for all the months in each set. I believe this is what I'd get if I put the date range in a subcube or a where clause - but in that case, I wouldn't be working with 2 distinct sets.
Is there some other way to write this, or to combine all the months in the set before the measures?
PLEASE SEE my update (post #3) for a better simpler explanation!!!!!
Hi, I need a query which is basically
(query 1) Except (query 2)
SQL server supports (query 1)Union (query 2) but I can't get the Except to work.
Alternatively, I also tried to implement it by using the "NOT IN" but didnt work. Background: 3 tables, accnts, Opty, Opty_postn I want the accounts who have 1 or more opty's at 100% (sold) before 1/1/2002 but zero opty's at 100% after 12/31/2001. accnts has fields ID & name, ROW_ID opty has fields acct_id, prob, ROW_ID opty_postn has fields opty_id, close_dt
My query: SELECT accnt.name FROM accnt WHERE accnt.ROW_ID IN ( SELECT acct_id FROM opty where prob = 100 AND opty.ROW_ID IN (SELECT opty_id FROM opty_postn WHERE close_dt<1/1/2002) ) AND accnt.ROW_ID NOT IN ( SELECT acct_id FROM opty where prob = 100 AND opty.ROW_ID IN (SELECT opty_id FROM opty_postn WHERE close_dt >12/31/2001) ) AND accnt.ROW_ID = opty.accnt_ID
my intent was select account.names where id is in set(prob = 100 & sale before 1/1/02) but not in ( prob = 100 & sale after 21/31/01) SO ,the accounts which have sales both before and after get counted. But I want only those accoutns which have sales only before 1/1/02. I hope i explained this right. Thanks in advance for your help.
