Rolling Up Spans Without Breaks Between Them
Nov 10, 2006
I'm trying to figure out how to this do with TSQL and preferably
through joins rather than by using cursors or temp tables. I have
multiple time spans for multiple individuals that I would like to
rollup together where they don't actually have a gap in coverage.
ID StartDate EndDate
Z000001 01/01/1996 9/30/1996
Z000001 10/01/1996 12/31/1998
Z000001 01/01/1999 04/30/1999
Z000001 01/01/2000 12/31/2000
Z000001 01/01/2001 12/31/2001
I would like to be able to "roll these up" in order to reflect the
actual time frames so that it would like this:
ID StartDate EndDate
Z000001 01/01/1996 4/30/1999
Z000001 01/01/2000 12/31/2001
4/30/1999 to 01/01/2000 is, of course, a legitimate break where there
was a date difference of more than one day.
Thanks in advance for any ideas.
View 7 Replies
ADVERTISEMENT
Oct 4, 2001
Challenging Date Spans Problem:
A health insurance plan stores information about covered members in the following table:
CREATE TABLE enrollment
(
ssn CHAR(9) NOT NULL,
startdate DATETIME NOT NULL,
enddate DATETIME NOT NULL,
policy INTEGER NOT NULL,
CONSTRAINT pk_enrollment PRIMARY KEY CLUSTERED (ssn, startdate) ,
CONSTRAINT ck_noreversedate CHECK (enddate>=startdate))
INSERT enrollment
VALUES (111111111, 2000-01-01, 2000-04-30, 1)
INSERT enrollment
VALUES (111111111, 2000-06-01, 2001-12-31, 1)
INSERT enrollment
VALUES (222222222, 2000-01-01, 2000-06-30, 1)
INSERT enrollment
VALUES (222222222, 2000-07-01, 2001-12-31, 2)
INSERT enrollment
VALUES (333333333, 2000-01-01, 2000-06-30, 1)
INSERT enrollment
VALUES (333333333, 2000-07-01, 2001-12-31, 1)
INSERT enrollment
VALUES (444444444, 2000-01-01, 2000-06-30, 1)
INSERT enrollment
VALUES (444444444, 2000-07-01, 2000-11-30, 1)
INSERT enrollment
VALUES (444444444, 2001-03-01, 2001-06-30, 1)
INSERT enrollment
VALUES (444444444, 2001-07-01, 2001-12-31, 1)
Assume that there are no overlapping enrollments. We are able to enforce this at the time the records are inserted (e.g., through the front-end application).
Members can have multiple enrollments. These enrollments can be adjacent (e.g, member 222-22-2222 has one enrollment which terminates on 06/30/2000 and another that begins on 07/01/2000) or there can be gaps (e.g., member 111-11-1111 has one enrollment which terminates on 04/30/2000, the subsequent enrollment begins 06/01/2000).
Our task is to write a SELECT statement that will coalesce all adjacent enrollments where the policy is the same. E.g., member 333-33-3333 has two records in the source table, these records would be combined into a single enrollment with start date = 01/01/2000 and end date = 12/31/2001.
SELECT e.ssn, e.policy, MIN(e.startdate) AS startdate, MAX(e.enddate) AS enddate FROM enrollment e GROUP BY e.ssn, e.policy WHERE EXISTS (SELECT * FROM enrollment e2 WHERE e.ssn = e2.ssn AND e.policy = e2.policy AND e.startdate = DATEADD(y,1,e2.enddate)) OR EXISTS (SELECT * FROM enrollment e3 WHERE e.ssn = e3.ssn AND e.policy = e3.policy AND e.enddate = DATEADD(y,-1,e3.startdate))
UNION
SELECT e4.ssn, e4.policy, e4.startdate AS startdate, e4.enddate FROM enrollment e4 WHERE NOT EXISTS (SELECT * FROM enrollment e5 WHERE e4.ssn = e5.ssn AND e4.policy = e5.policy AND e4.startdate = DATEADD(y,1,e5.enddate)) AND NOT EXISTS (SELECT * FROM enrollment e6 WHERE e4.ssn = e6.ssn AND e4.policy = e6.policy AND e4.enddate = DATEADD(y,-1,e6.startdate))
This works for member 333-33-3333 who has only one set of adjacent enrollments.
However it fails for member 444-44-4444 who has two sets of adjacent enrollments. Can anyone suggest an approach that would return two rows for 444-44-4444, one with start date 01/01/2000 and end date 11/30/2000 and another with start date 03/01/2001 and end date 12/31/2001?
View 2 Replies
View Related
Apr 8, 2006
Data apx (5 million rows):
Span example:
-------------M---------------
___________-------------Rx-------------
Needs to b converted to this (ignore the underscore, used for spacing):
---M-------|-------M & Rx----|---Rx--
The time spans can slide either way.
Data example:
MemberID Eff_Date Term_Date Med_COB Rx_COB
1 20050101 20050912 Y N
1 20050310 20051120 N Y
1 20060101 <null> Y N
1 20060101 <null> N Y
Resulting Records need to be in this format:
MemberID Eff_Date Term_Date Med_COB Rx_COB
1 20050101 20050310 Y N
1 20050311 20050912 Y Y
1 20050913 20051120 N Y
1 20060101 <null> Y Y
Any help with this problem would be greatly appreciated. We are running SQL2K. I like most people,would like to stay away from cursors and loops if possible.
Thanks,
nemesis01
View 8 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
Mar 7, 2006
Hi,
I need to import and transform data from an Excel spread sheet where the information spans two rows. The file layout is something like:
Row1Product1 QTY Store1 Store2 Store3 ...
Row2Product1 AMT Store1 Store2 Store3
Row3Product2 QTY Store1 Store2 Store3
Row4Product2 AMT Store1 Store2 Store3
The output would look like
Product1 Store1 QTY AMT
Product1 Store2 QTY AMT
...
Product2 Store1 QTY AMT
Product2 Store2 QTY AMT
...
We currently use a VB6 program using Office Tools to handle this. Is there a way to handle this with the out-of-the-box SSIS?
Thanks in advance,
John
View 6 Replies
View Related
Mar 7, 2007
I have several nested sub-total groups that are all coded to "page break at end". I don't know how to tell RS to suppress the page break if one sub-total immediately follows another. The initial HTML output seems to be smart enough to do this suppression on its own, but when I export to PDF, all the page breaks appear. This causes several pages at the end of the report with one line per page. Perhaps, if I could explicitly suppress these page breaks, the PDF would render properly. How can I do this?
View 1 Replies
View Related
Jul 5, 2007
If a single row of data spans a page then the column header is not repeated until the next row on the next page.
Is there a way to overcome this?
View 1 Replies
View Related
Feb 25, 2004
I'm using PHP with SQLServer2k to create a page containing monthly counts of episodes at a facility occurring between two user selected month/year combinations. For instance, the user could select 10/2003 and 2/2004 and facility X and get a line for each month showing the count of episodes occuring in that month.
The problem is that the episode date is stored in three integer fields (epiday, epimonth, epiyear) and I'm having a terrible time getting them into a format where I can use them in a between statement.
I've tried evaluating the parts of the episode date seperately like:
where
(epimonth>=10 and epiyear=2003)
or
(epimonth<=2 and epiyear=2004)
and that works, but what happens when someone wants to see from 10/2002 to 2/2004?
Any suggestions on the best way to do this?
View 5 Replies
View Related
Mar 27, 2001
If I issue -
update x set y = 'z'
is there a way to undone/roll-back what was done in SQL 7 ?
Thanks very much.
-FB
View 2 Replies
View Related
Sep 8, 2004
I have created a DTS Package that does the following:
1 Delete all data from table 1 (SQL Task)
2 Import Data from .csv file into table 1 (Data import)
3 insert records from table 1 into table 2 when they dont exist in table 2 (SQl Task)
This all works fine, but now i want a rollback function in step 1 and 2.
So when Step 1 is finished and something goes wrong i want the deleted data back.
Can somebody help me?
Thanks in advance.
View 1 Replies
View Related
Feb 19, 2004
One of our engineers here by mistake deleted some very important data without any begin trans block and we need the data back very badly.. We have not closed the session as of now.. The engineer was logged in as sa to the DB.
Can someone please help soon on urgent basis...
View 6 Replies
View Related
Jun 23, 2008
Hi,
I'm writing a query that will be calculating a number of business performance measures including the following...
Average Stock:
This is calculated by taking the actual stock value of the product category per month and dividing it by 13 if we have over 12 month's history, or if a new product, then by the number of months we have had it in stock for.
Cost of Goods Sold Annualised:
This is an average figure for the year of the sold landed costs for the product category over a 13 month period if we have over 12 months history. If it's a new product, then it's for the number of months we have had stock.
My question is, how do I tell the query to go back 13 months from the current month?
View 1 Replies
View Related
Dec 1, 2014
We have an inventory of devices we service and wish to show the total numbers of active devices per month going 12 months back. How would I go about this?
View 2 Replies
View Related
Feb 16, 2006
I'm performing a stored proc that has 4 inserts. I only want the inserts to complete as a batch. If one fails, I want to rollback the whole transaction. Does anyone know the syntax?? :)
View 4 Replies
View Related
Mar 3, 2006
I want to insure that each of my insert statements in a stored proc are rolled back if any of the inserts fail. I already have the below statement with error handling but is this correct? It seems to me that all the steps should be made part of an entire transaction so if one part fails then it all fails. Can someone help me w/ the syntax of this??
CREATE PROCEDURE Addrecords AS
--USERS
INSERT INTO [Production].[dbo].[USERS]([LastName], [UserName], [EmailAddress], [Address1], [WorkPhone], [Company], [CompanyWebsite], [pword], [IsAdmin], [IsRestricted],[AdvertiserAccountID])
SELECT dbo.fn_ReplaceTags (convert (varchar (8000),Advertisername)), [AdvertiserEmail], [AdvertiserEmail],[AdvertiserAddress], [AdvertiserPhone], [AdvertiserCompany], [AdvertiserURL], [AccountNumber],'3',0, [AccountNumber]
FROM production
WHERE not exists (select * from users Where users.Username = temp.AdvertiserEmail)
AND validAD=1
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RETURN
END
--PROPERTY
INSERT INTO [Production].[dbo].[Property]([ListDate],[CommunityName],[TowerName],[PhaseName],[Unit], [Address1], [City], [State], [Zip],[IsActive],[AdPrintId])
SELECT [FirstInsertDate],[PropertyBuilding],[PropertyStreetAddress],PropertyCity + ' ' + PropertyState + ' ' + PropertyZipCode as PhaseName,[PropertyUnitNumber],[PropertyStreetAddress],[PropertyCity], [PropertyState], [PropertyZipCode],'0',[AdPrintId]
FROM [Production].[dbo].[Temp]
WHERE AdvertiserEmail IS NOT NULL
AND validAD=1
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RETURN
END
View 2 Replies
View Related
Feb 12, 2007
Hi,
trying to develop a report that shows the sum total for each month during a specified date range.
I have parameters asking for the start date and end date of the date range, the report needs to show the months in between this date range.
One of the fields in the dataset is durationminutes which I need to sum for each month then divide by 60 to show hours. I then need to show the total of each month in a bar graph (with each month along the x axis).
If anyone can shed some light on how I am going to do this it would be greatly appreciated.
Thanks.
View 5 Replies
View Related
Oct 3, 2007
Hi All,
I wanna pull a 12 month rolling data and donno how to do that, can anybody help ?
Problem:
lets say if i run the query today i should get data between oct-3-2006 and oct-3-2007, my main colums is monthyear which is date datatype.
Appreciate your help.
Thanks
Chinna
View 1 Replies
View Related
Mar 19, 2007
I have a stored procedure that calls another stored procedure with thefirst stored procedure opening a transaction:BEGINSET XACT_ABORT ONBEGIN TRANSACTIONdoes various updates/insertscalls 2nd stored procedure to proccess updates/inserts common to manyother stored proceduresdoes more various updates/insertscommitENDThe problem I'm having is that within the 2nd stored procedure is thatif it encounters an error, it does not roll back the entiretransaction and I finish up with missing records in the database. Amusing this in the 2nd stored procedure:if(@TypeId1 = @TypeId2 and @Line1 <'' and @Line2 <'')beginRAISERROR('error message', 16, 1)RETURNendWhat could the problem be? From what I've read, it seems as thoughyou can't have an open transaction within one sp that calls another spand it maintains the same transactoin? Is this corrrect?I tired the following too, and I still couldn't get it to work. Anyideas anyone?************ sp 1 ***********Declare @AddressError char(3)SET XACT_ABORT ONBEGIN TRANSACTIONexec Sp2@AddressError OUTPUT,@variable1,@variable2,etc. etc************** sp 2 *****************@AddressError char(3) OUTPUT,if(@TypeId1 = @TypeId2 and @Line1 <'' and @Line2 <'')beginRAISERROR('error message', 16, 1)RETURNendSET XACT_ABORT ONBEGIN TRANSACTIONprocess updates/insertsSet @AddressError = 'no'Commit******** back to sp 1************If @AddressError <'no'BEGINrollback transactionENDcontinue doing updates/insertscommit
View 1 Replies
View Related
Jun 15, 2007
Is database mirroring compatible between SQL Server 2005 (Standard Edition) SP1 and SP2? I currently run SP1, and will shortly be introducing a failover partner. I'd like to have the failover partner installed as SP2, start the mirroring, manually failover, then upgrade the existing server to SP2 as well.
Is this a supported upgrade method, or will I need to upgrade the existing server to SP2 before setting up database mirroring? The SP2 installation instructions and readme file don't appear to mention mirroring at all (at least not that I can find).
Many thanks for any assistance.
View 4 Replies
View Related
Oct 9, 2006
I'm trying to create an accumulating field based on a set of records. I need to fill in daily amount balances that accumulates on a daily basis. But I can't seem to figure out how to create a total for the daily dates and have it add on additional amounts if needed.
Here's some sample data:
5 6 20 1 200.00 5/5/20000
5 6 20 1 -149.00 5/8/2000
5 6 20 1 100.00 5/10/2000
Now I already have a table with the dates created via a stored procedure. I have a set of dates from 5/5/2000 to 5/8/2000. So that results set should look like this:
5 6 20 1 200.00 5/5/2000
5 6 20 1 200.00 5/6/20000
5 6 20 1 200.00 5/7/2000
5 6 20 1 51.00 5/8/2000
5 6 20 1 51.00 5/9/2000
5 6 20 1 151.00 5/10/2000
....
I'm trying to creating a rolling sum that accumulates the amount field for each daily record and if a new amount is listed, then roll that amount into the total. If you have any suggestions about how to perform this rolling total via TSQL or SSIS, I would greatly appreciate it.
Thanks
Greg
View 1 Replies
View Related
Jun 16, 2007
Hi,
I have a case where I read from SQL Server DB and write to a flat file.
I have one Data Flow Task inside which I have a OLEDB source component that feeds rows to a script component that writes to a flat file. I have set the txn attributes for the container to "Required" and "Read committed" . But I find that rows are written to flat file even when I throw an exception from my script component. Question is how do I prevent rows from being written to the flat file if error/exception happens. I want the whole process to be in a single transaction.
Thanks
View 3 Replies
View Related
May 13, 2008
Dear All
I have the following table structure and following data
create table emp(empid char(4),empname char(10),empsal money,empsalPercent money,empsalRuningTot money)
empid empname empsal empsalpercente001 tom 20.00 8.69e002 *** 40.00 17.39e003 harry 15.00 6.52e004 masum 25.00 10.86e005 rana 30.00 13.04e006 dipak 60.00 26.08e007 rana 40.00 17.39
now would like to make the following
1) want to sort first emp table based on empsalPercent field on descending order physicallyProb : am only able to select the same thru descending order but am unable to make it physical shortingselect * from emp order by empsalPercent desc
2) after make descending order for empsalpercent I want to make RunningTotal for the EmpsalPercent column
pls helpsamir
View 3 Replies
View Related
Sep 4, 2001
I had a need to update a column in a table with a calculated value based on the value of the preceeding value in the table (which in turn was based on the preceeding value, etc...).
Although this can easily solved via a cursor, I was able to solve this with a single set statement that first updates a variable (which can be part of a calculation using the 'current' row) and using the knowledge that SET statements are applied 'in order' (top-down).
Most importantly: the technique relies on my observation that the SET statement processes a table in clustered index sequence, or load sequence in a heap table.
I've included a very simple example of this technique below.
I'm curious if anyone is using this (undocumented?) technique and if my assumptions are valid (I'm using this on much larger tables with more complex calculations).
EXAMPLE:
CREATE TABLE #tmp (
num int IDENTITY(1,1) NOT NULL,
val int NULL)
-- load 100 test rows
DECLARE @x int
SET @X = 1
WHILE @X <= 100
BEGIN
INSERT #tmp (val) VALUES (NULL)
SET @X=@X+1
END
DECLARE @i int
SET @i = 1
UPDATE #tmp -- this is a -very- simple example of technique!
SET @i=num+@i, -- @i now has current totaled value + current-row int value
val=@i -- val now stores current subtotal
View 1 Replies
View Related
Feb 15, 2006
We have installed SQL Server 2000 Service Pack 4 recently and have had various issues with some of our Stored Procedures. (which we didn't have with SP 3)
We are looking at our options regarding rolling back to Service Pack 3.
Can this be done ?
&
If so, is there a method documented on how to do this ?
Any advice would be appreciated.
View 3 Replies
View Related
Apr 4, 2013
I have a stored proc that is executed from within another stored proc, both of these procs run transactions in them that update data.
Now if either of these 2 SPs fail i want to be able to roll back the transactions that have occured so that the data doesn't change.
View 5 Replies
View Related
Apr 9, 2008
Ive got an insert statement that fails, and below that I have code like the following:
IF @@ERROR <> 0
BEGIN
-- Roll back the transaction
ROLLBACK
-- Raise an error and return
RAISERROR ('Error INSERT INTO Address.', 16, 1)
print 'test was here'
RETURN
END
However, there is now rollback and the inserts below it are going through.
what do i have wrong ?
View 5 Replies
View Related
Mar 29, 2007
Don't know if this is possible or not:
Goal: set security on running specific stored procedures based upon user login and databse access
I have some DBA's who want to retain full control of databses / stored procedures as they now have but I want to restrict or rollback some of the changes that were implemented when the sql 2005 was set up. The sql 2005 EE is in a clustered system and uses Mixed Mode Authentication.
An example of what I want to restrict: The DBA's want to be able to view and kill processes for the different databases that are installed under their instance. The problem is other customer databases are also under the same instance.
Is their a way I can combine or have the stored procedure sp_lock only show the processes for the databases they have access to based upon their login? My concern is they will kill a process and affect the other customers.
Thanks for any info or pointers.
carl
View 1 Replies
View Related
Dec 4, 2007
Hi
I need to write something in T-SQL which will return a record set from a table of IT hardware. The record set should comprise all items that are older than 3 years from the current date. Each hardware item has a purchase date against it so it should not be too difficult. I am considering the DATEDIFF function but I am not sure how best to write it.
Any suggestions ??
Thanks
View 2 Replies
View Related
May 22, 2008
Thanks for taking the time to read my post. I greatly appreciate it!
What i'm trying to do is get a distinct count of account numbers within a rolling period. My actual take is rather large but i've created a smaller-like version below. Please reference this table.
Account
Date
1
1/1/08
2
1/2/08
3
1/2/08
2
2/8/08
4
2/9/08
1
2/15/08
1
3/5/08
5
3/6/08
4
3/9/08
3
3/10/08
1
4/1/08
5
4/9/08
2
4/15/08
3
4/26/08
1
5/3/08
2
5/15/08
3
5/29/08
6
5/30/08
Let's say i want to return distinct count of accounts within a 2-month rolling period meaning in February, i'd get a distinct count for accounts in January & February, then in March i'd get a distinct count for February & March, then in April i'd get it for March & April, and so on... my results table would like the table below:
Account
Month
3
1
4
2
5
3
5
4
5
5
I had asked this before but it was a summing equation and not a unique count. I've tried to play with the summing equation to kind of make it work, but i'm starting to get a headache. It's probably so simple!
Here's my previous post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2289509
View 8 Replies
View Related
Mar 17, 2008
hi,
i want to create a disaster recovery site, to which i can fail over (not automatically),
and also to have the option to return the database to a point in time?
for example,
if my principal server fails in 17:00,
i want to have the option to make the mirror server available for users from 17:00 (or at least close to that time),
and also to be able the return to the data from 16:00 (in the mirror site).
Is it possible, and what is the best way to do it?
Thanks.
View 1 Replies
View Related
May 15, 2008
Hi,
We are using SQL Server 2005 Analysis Services in x64 environment.
We use many 12M metrics implemented as calculated measures. A typical 12M calc measure looks like this:
Sum({LastPeriods(12, ClosingPeriod([Time].[Month],
Tail(Existing [Time].[Month].Members).Item(0)))}
, [Measures].[Revenue])
, FORMAT_STRING = '#,#';
The front end is based on OWC.
These are performing extremely poorly and worse compared to our older SQL Server 2000 environment. Is there anything obvious from this formula that would make it worse?
I appreciate any guidance as to what I should look for.
thanks
MJ
View 6 Replies
View Related
Aug 9, 2005
I would appreciate a bit of advice here. There is a largish complaint here regarding a cutomer who has entered data in 1 of our online forms, but we suspect this was then overwritten by a cached form she also had open.
Anyway to cut a long story short i need to roll back the database to a point in time.
Not something i have ever had to do.
The row where the id field is equal to 3352, and this would have been written to the database at 14:58:36 on 08-aug-2005. This was over written by the data in row with id 3380 at about 11am this morning, now is this is a live database on a webserver so i cant compromise its uptime as it get written to about 3 times a minute, so how should i go about this?
Anyway, what do you think?
Cheers
View 2 Replies
View Related
Feb 3, 2004
I have a Development database and I want to roll it back to Monday morning. I backed up the database and used the command:
RESTORE DATABASE ITTEST
FROM ITTEST20040203
WITH NORECOVERY
GO
RESTORE LOG MyNwind
FROM ITTEST20040203
WITH RECOVERY, STOPAT = 'FEBRUARY 2, 2004 09:00 AM'
GO
The transaction logs have never been truncated.
But it does not seem to have worked. It this the best way to do a roll back the database or have I missed out something.
thanks.
View 2 Replies
View Related
