Overlapping Partitions And Join Filters
Oct 2, 2006
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?
Thanks for any help
Graham
View 2 Replies
ADVERTISEMENT
Jul 26, 2006
I expect that this is not possible but:
I've created a initial filter on our tblLocation.
I've created a join filter on the header table for our inventory data.
I've create a join filter on the header table for our inventory detail data.
The joins between the inventory header table and the inventory details table is on a three field key which are of type nvarchar.
When I try to sync a subscription, I receive an error which indicates that it cannot bulk copy into the inventory details table on the subscriber because it would create duplicate keys. There isn't any duplication at the publishing server so, I expect this has something to do with the data type of the key/join fields.
Am I correct? If so, is there a workaround.
I essentially only want to send that inventory data to a subscriber that the subscriber needs.
View 1 Replies
View Related
Sep 12, 2014
I need to find;
Card number and date of borrowers earliest loan for all borrowers who had a loan before the 03/Jan/2004 OR who borrowed a book published before 1920
So far my query looks like this but it is bringing back date out after 03/Jan
select cardno, min(l.dateout)
from loan l right outer join book b
on l.isbn = b.isbn
where b.yearpublished < '1920'
or l.dateout < '03/Jan/2004'
group by cardno
order by cardno;
View 4 Replies
View Related
Apr 24, 2007
I have the following table structure
CREATE TABLE [dbo].[QDisc](
[Id] [int] NOT NULL,
[MinVal] [int] NOT NULL,
[MaxVal] [int] NOT NULL,
[PerVal] [int] NOT NULL,
CONSTRAINT [PK_QDisc] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
I need to be able to select unique overlapping sets of data based on the minval and maxval.
Simple Example
minval maxval
5 15
16 25
10 20
query would produce
minval maxval
5 10
11 15
16 20
21 25
More Complex example
minval maxval
5 15
16 25
10 20
7 7
1 100
query would produce
minval maxval
1 5
6 6
7 7
8 10
11 15
16 20
21 25
26 100
Extra points if anyone can do this without SP and cursor but I'd be satisfied if someone supplied it that way.
View 9 Replies
View Related
Oct 4, 2006
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
View 8 Replies
View Related
Aug 28, 2006
I have a table with following fields
CURRENT_DAY FROM_DATE TO_DATE
1 1899-12-20 09:00:00.000 1899-12-20 10:00:00.000
1 1899-12-20 09:50:00.000 1899-12-20 11:00:00.000
1 1899-12-20 12:00:00.000 1899-12-20 02:00:00.000
I need the count of records that overlap with each other. [OR]
1 if there is overlap, 0 if there is no overlap.
I am new to sql, thanks for any help.
Yog
View 2 Replies
View Related
Feb 7, 2000
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.
View 1 Replies
View Related
Jul 26, 2004
:confused: Dont know if this will be tough for the rest of you but for someone who is fairly new to SQL...I cannot figure it out...
I have a table:
Rownumber starttime endtime
1 l 30 l 240
2 l 40 l 120
3 l 50 l 260
4 l 1300 l 1400
Rows 1, 2, and 3 over lap with one another and I am trying to obtain the starttime and endtime values which can cover them all.
I would like to find the overlapping (starttime - endtime) ranges and accept the lowest starttime value and the highest endtime value.
Row 1: 30--------------------240
Row 2: 40--------------120
Row 3: 50----------------------260
Row 4: ...1300---------1440
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?
Thank you for the help!
View 14 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
Apr 9, 2008
Hello,
How can I create a query that pulls all records for a given ID (road) where the distances (based on start & end fields) overlap?
Eg. RoadNm Start End
Road 1 0 500
Road 1 300 800
Road 2 0 500
Road 2 500 800
I need to write SQL that flags Road 1 as a road with overlapping sections (whereas Road 2 is fine).
Thanks!
Amber
View 3 Replies
View Related
May 7, 2008
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?
Thanks
View 5 Replies
View Related
Apr 17, 2007
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
View 3 Replies
View Related
Nov 9, 2006
hi guys,
i have a booking table which has the following columns...
booking
-------------------------------------------
dCheckin (format 11/9/2006 12:00:00 AM)
dCheckout (format 11/11/2006 12:00:00 AM)
when a new booking is entered, we want to make sure that the period entered does not conflict with an existing record.
not sure how to go about building the query required. any help would be greatly appreciated.
mike
View 4 Replies
View Related
Sep 17, 2014
Using SQL 2008 R2 and stuck on this.
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
[Code] ....
View 8 Replies
View Related
Jun 5, 2008
ok folks, I need help.
Here's the table and some sample data:
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.
elsasoft.org
View 20 Replies
View Related
Jul 20, 2005
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/
View 3 Replies
View Related
Aug 20, 2007
Hi,
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?
Somiya
View 1 Replies
View Related
Oct 20, 2015
[URL] ...
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
)
[Code] ....
DESIRED RESULTS:
1060
1
2015-10-01
16:30:00
17:00:00
30
NULL
[Code] .....
View 11 Replies
View Related
Sep 29, 2015
How can I check the overlapping in a simple table like:
Create table forum (cid int, bfrom date, tfrom date, fval int)
Insert into forum values (1, '2014-01-01', '2014-01-31',10),(2, '2014-01-01', '2014-01-31',12),(1, '2014-02-01', '2014-02-28',8),(2, '2014-02-01', '2014-02-28',6),(1, '2014-03-01', '2014-03-31',11),(2, '2014-03-01', '2014-03-31',5),(1, '2014-04-01',
'2014-04-30',14), (2, '2014-03-01', '2014-04-30',12)
In the example above there is an overlapping for the cid 2 in March. How can I check, which select should I apply?
View 5 Replies
View Related
May 16, 2007
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 .
Any ideas why this would happen?
View 1 Replies
View Related
Dec 2, 2005
... 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!
View 5 Replies
View Related
Oct 13, 2014
I want to count persons for non overlapping intervalls by room number. Here the data:
SET DATEFORMAT mdy;
DECLARE @PersonTime TABLE
(
ID INT,
Person INT,
Room INT,
Coming DATETIME,
Going DATETIME
[code]....
View 4 Replies
View Related
Apr 4, 2007
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)
clientid StratDate EndDate
1 2004-01-01 2004-05-01
1 2004-04-01 2004-05-01
1 2005-04-01 2006-01-01
Table create
CREATE TABLE [dbo].[TABLE_TEST] (
[Client_ID] [varchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[STARTDate] [datetime] NULL ,
[ENDDate] [datetime] NULL ,
)
GO
INSERT
INSERT INTO [TABLE_TEST]([Trust_Wide_ID], [STARTDate], [ENDDate])
VALUES('1','2004-01-01','2004-05-01')
INSERT INTO [TABLE_TEST]([Trust_Wide_ID], [STARTDate], [ENDDate])
VALUES('1','2004-04-01','2004-05-01')
INSERT INTO [TABLE_TEST]([Trust_Wide_ID], [STARTDate], [ENDDate])
VALUES('1','2005-04-01','2006-04-01')
INSERT INTO [TABLE_TEST]([Trust_Wide_ID], [STARTDate], [ENDDate])
VALUES('2','2004-06-01','2004-07-01')
INSERT INTO [TABLE_TEST]([Trust_Wide_ID], [STARTDate], [ENDDate])
VALUES('3','2004-09-01','2004-010-01')
Go
Thanks for help
Husman
View 1 Replies
View Related
Oct 23, 2007
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.
Code Block
create table dbo.DiabStrata(memberid char(11),Strat tinyint, StratStart datetime, StratEnd datetime)
create table dbo.hba1c(memberid char(11),dos datetime)
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
View 1 Replies
View Related
Oct 18, 2015
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).
View 2 Replies
View Related
Jul 8, 2015
how can I check the overlapping and the LostPeriod by chargeid?
create table #forum (contractid int, chargeid int, ByFrom date, ByTo date)
insert into #forum values ('7','18','2005-04-01','2007-03-31'), ('7','19','2008-06-01','2010-03-31'),
('7','20','2014-04-01','2015-06-01'),
('8','10','2003-10-01','2005-03-31'),('8','11','2006-12-01','2007-07-31'),
('9','11','2003-10-01','2005-03-31'),('9','12','2004-10-01','2015-03-31')
As lost period I mean that period that is not covered by any chargeid. By overlapping I mean having two or more charge id in the same period.
View 9 Replies
View Related
Jan 9, 2002
Does anyone have any statistics on the performance gains one can get using raw partitions. The database in question is very IO intensive and performs about 1,000,000 inserts/updates per select.
Thanks...
View 2 Replies
View Related
Aug 18, 2015
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),
[Code] ....
View 12 Replies
View Related
May 14, 2007
Hi,
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.
Regards,
Sumeet
View 4 Replies
View Related
Oct 6, 2015
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".
drop table t1
drop function dbo.dt_overlap
View 14 Replies
View Related
Oct 12, 1999
Please help!
I have a server that has SQL Server installed on both C and D drives. The SQL Server software is currently running from the C drive and the live databases and backups are stored on the D drive.
I need to have everything on the D drive. Is there an easy way to make the registry point to the D drive without reinstalling SQL Server? The software will needs to run from the D drive because the C drive is running out of disk space. I will also need to delete the whole C:mssql directory.
Thanks.
View 2 Replies
View Related
Jun 19, 2008
assuming that you have two databases, the OLTP db and the OLAP db (take not that both have the same structure -- archiving purposes)... using table partitioning, is there a way where we can move 1 partition from the OLTP db to the OLAP db???
i'm actually trying to use this example with both tables in the DB.. I tried to modify to use two databases but sql server is unable to move the partition...
ALTER TABLE [Production].[TransactionHistory]
SWITCH PARTITION 1
TO [Production].[TransactionHistoryArchive] PARTITION 2;
SlayerS_`BoxeR` + [ReD]NaDa
View 9 Replies
View Related
Aug 23, 2007
Hi experts,
In SQL Server 2005 database we have partitioned a very big table into 30 partitions each holding few million of records.
Im just curious to know whether there are some configuration related to processors or system hardware in order to benefit from partitioning ? (Ex : If we have multiple processors Whether they need be configured to do a parallel processing ? )
Any real time experience (other than referring links) would be really helpful for me.
Thanks in advance,
Hariarul
View 1 Replies
View Related
